A Guide to Setting Up Data Tables in Excel

MS Excel is a widely used spreadsheet tool that is used for recording and analyzing numerical data. For example, many people in today’s advanced world list out and maintain their expenses from the monthly income that they earn in order to spend wisely. MS Excel helps in recording such data and data tables make it easier to record such data.

A table consists of rows and columns of cells that you can fill in with the required text. Within each cell, text wraps just as it does between margins in a document. With the help of data tables you can convert the wide range of data into tables and then you can use these tables to work more efficiently and effectively. This will not only save you time but also will it help you keep a tab of things easily.

How to create data tables in MS Excel

You must know that it is very easy to make data tables in excel. For example, we have to make a data table for the information given below:

In the above data set, we have the three variables as the loan amount, the interest rate, and the time period.

We will calculate the monthly payment amount based on these three variables (monthly payment is shown in a red box as there is an outflow of money). The following is the excel formula that will be used to calculate monthly payment value:

=PMT(B3/12, B4*12, B2)

We need to put down this formula in the cell B5, where we want the monthly payment to appear after calculation.

Now we will try to figure out different monthly payments by varying the rate of interest using one variable data table. Following are the steps for making a one-variable data table:

  1. First, you need to write the above data in the table. Then using the above formula you can calculate the monthly payment.
  2. Then in order to calculate different monthly payments, you can write a range of different interest rates in column B below monthly payment as shown in the figure below.

  3. After writing the different rates of interest we will copy the formula in cell C8 that will be used to calculate the monthly payments.
  4. In order to copy the formula you will have to write =B5 and press enter.
  5. Now in order to calculate different monthly payments based on different rates of interest you will have to select different rates of interest and the formula.
  6. Then go to data tab-data tools-what if analysis-data table.
  7. In the data table dialogue box, since we are making one variable table we will leave the row input cell empty and in the column input cell we will reference the variable that is changing which here is the rate of interest so now we will select cell B3 as reference and press enter.
  8. After doing the above step you will see the monthly payment values for different rates of interest.

In this way, you can create data tables in MS Excel for different kinds of data.

MS Excel’s what-if analysis feature

The what-if analysis feature of MS Excel helps in swapping multiple sets of data in a worksheet and it even helps in comparing them side by side. This feature is very beneficial as it saves your time and makes your work easier. Therefore, it is a very important and special feature of MS Excel. There are three tools of the what-if analysis features:

Scenario Manager

It is one of the tools of what-if analysis feature and it helps you in comparing multiple sets of data. If you want to change two variables in your data then you can use this tool to do so. By using this tool if you change the value of two variables then it will automatically make the rest of the changes and you don’t have to do it manually.

Mortgage calculator

It is another tool of the what-if analysis feature and it is used to calculate many different things like rate of interest or loan, monthly payment, etc. It makes our work simpler by making doing calculations extremely easy. Therefore it saves a lot of time and lengthy calculations are done easily through it.

Goal Seek

This is also one of the tools of the what-if analysis feature and it helps to adjust a value used in a formula to achieve a specific goal. It is very beneficial as with the help of this tool one can easily adjust values in the formula.

By using these tools one can save time and make data tables easily.

A few tips and tricks for making data tables in excel

  1. You can change the format of the table by going to the ‘format as table’ button in the home ribbon if you are bored with the old format of the table.
  2. You can use zebra lines when you create data table as it will save your time because then you don’t have to manually format alternative rows in a different color.
  3. You can use structured references instead of cell references because structured references will automatically get updated when you add or remove rows.
  4. You can use calculated columns as they will save your time. When you use calculated columns and then when you write a formula in one cell, it automatically gets filled in the rest of the cells.
  5. If you want to convert the table back to named ranges, then you can use the ‘convert to range’ tool, as it will again save your time.
  6. If you want to analyze your table then you can convert your export table into pivot table by choosing to summarize with the ‘pivot table’ tool.
  7. You can use an export table to SharePoint list tool if you want to publish your data table.
  8. If you want to print your data table then instead of adjusting print settings, you can select the table and then hit control+P. This way of printing table is much easier and it also saves your time.

So these were the tricks and tips that you can use while making data tables in MS Excel to make your work easier and hence, save time.

Data tables in MS Excel are very useful. They help in storing a wide range of data which helps in making one’s work easier. You can do simple mathematical operations or more advanced calculations on the numbers you enter with a few simple clicks. It eliminates the need for doing time-consuming calculations manually. You can make one variable table or two variable tables depending upon the data and your need. You can filter your data by using the drop-down filters. There are shaded rows in the table that make the data easier to read it and therefore the reader does not find any difficulty in interpreting the data.

One of the major benefits is that you can name your table and you can expand it whenever you want to by adding more data to the table whenever you want. There are various magic formulas that make the calculations and many such tasks very easy. The feature of MS Excel forms makes the entry of the data very simple. There are column headers that make the heading visible. Another benefit is that reviewing of the data and analyzing of the data is very straightforward and quick.

In data tables, the totals can be calculated in different ways. You can also add charts to your data. There are plenty of benefits of using data tables in MS Excel and the list of benefits just goes on. You must explore the various features and make the best use of tables to manage your work easily. Make sure to make a note of the tips given above to increase your efficiency while handling data tables in excel. All the best!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!