Using Excel Data Table to Compare Mathematical Results

Excel, Microsoft’s powerful spreadsheet/data calculating software, can be the most useful tool you have at your disposal, whether you are adding up monetary amounts for an invoice or alphabetizing a long list of data. However, Excel can also be a daunting and intimidating tool for many, largely because it is a program packed with different features and formulas that aren’t always inherently easy to find or use. If you have been trying to navigate Excel and have found yourself repeatedly getting lost, you might consider taking Udemy’s course on how to master Excel 2010 in a series of simple steps. (Udemy also offers a course for Microsoft Excel 2013.)

One function you might not know about on Microsoft Excel is called data table. An Excel data table essentially allows you to use Excel to automatically switch out different values in your calculation formulas. These variable switches then impact the results of your formulas, helping to determine compare variables based on the different mathematical results they yield.

In other words, a data table makes it easy for you to envision a variety of different mathematical scenarios without forcing you to continually and manually switch different values in and out of a slot in your formula. An Excel data table is what Microsoft calls a “what if?” scenario. It can accommodate up to two different variables at once and is used to see what sort of impact the variables have on the results of the formula calculation.

A Note on Data Table Formulas 

If you’re confused, don’t be discouraged. As I said earlier, Microsoft Excel can be a confusing program with a surprisingly steep learning curve, and that description certainly applies to any facet of the program that is using mathematical formulas, variables, and tables for a comparative analysis. Udemy even offers a course designed to provide you with an in-depth understanding of all of the formulas and functions hidden away in the various menus and buttons of Microsoft Excel.

Luckily, Excel data table is actually significantly easier to use than it is to describe. Keep in mind as you read through the following directions that your data table may differ depending on a variety of different factors. For instance, while this blog will teach you how to create a one-variable data table to compare mathematical results, your data table needs may involve two different variables. Furthermore, since data tables can be used for a wide range of applications – from looking at potential loan payments by manipulating the interest rate to projecting a business’s revenue streams as a result of production variables – the Excel formulas you use for your data table will vary as well.

Arranging the Spreadsheet

In order to get started with Excel data table, you have to have a “what if?” scenario that you want to predict using variable manipulation. For the sake of this tutorial, I will use a basic scenario of projected earnings growth for an independent business. Let’s say your business had a profit of $50,000 in 2013, it’s first year of operation. You want to see how much that profit will go up in 2014 under various growth scenarios. You imagine your business growing 5 percent, 10 percent, 25 percent, 50 percent, etc., and you want to see how much your revenue stream will increase as your business grows accordingly. You can do this using a one-variable data table.

First, you need to set up the various columns and rows so that you can use them for variable-based calculations. Create a title heading for your data table in slot A1 (something like “Business Growth Projections for 2014” will do perfectly), then move on to setting up the table. Row A2 will represent “Revenues 2013,” with B2 containing our theoretical business revenue stream of $50,000. A3, on the other hand, will be used to predict “Revenue Growth 2014,” with B3 set up as the shifting variable slot for the equation. You can either plug in actual percentage figures into slot B3 (5%, 10%, 25%, etc.) or their corresponding decimal points (1.05, 1.1, 1.25, etc.), whichever you prefer.

Once you have your “Revenues 2013” and “Revenue Growth 2014” rows set up, skip a row in Excel and move onto A5. Title this slot as “Projected Revenues 2014.” Skip another two slots and move over to B8 to type out your list of input variables. Input any growth percentages you want to see in your “what if?” scenario. For the sake of this demonstration, it doesn’t really matter what the percentages are, whether they are realistic growth percentages (figures like 1.10 or even 1.50) or out of this world suggests (1,200% or something similar). Pick the numbers you like, just make sure to choose five different values and to list them, in order, in slots B8 through B13.

Inputting the Formulas 

Now that you’ve set up your spreadsheet, you are ready to move on to inputting the variables. There are three slots on your spreadsheet that need formulas. The first is B5, where you need to use a multiplication equation involving the 2013 revenue figure and the 2014 revenue growth percentage. For now, put a simple value in B3, just so it’s there (1.20 or 20% will do), then click on slot B5 and type “=B2*B3” to create a formula slot. The figure of $60,000 should pop up in the B5 slot. Click on C7 and type “=B5”, which will mimic the predicted figure so that you can use it in your data table.

Setting up the Data Table

Finally, you are ready to input the data table formula. Highlight slots B7 through C12 – your table of input variables as well as the duplicated predicted revenue figure in slot C7 – then select the “Data” menu from the Excel bar at the top of the page and select “Data Table.” You will be greeted by a small command box, which will ask you for the row or column input cell you wish you use. You want the latter, where you will merely input “B3.” Your data table should instantaneously fill itself out, presenting revenue projections for each percentage growth variable you gave. You will see that 5 percent growth would net your theoretical company $52,500, that 30 percent growth would bring in $65,000, and so on and so forth.

This scenario is just a basic example of the sort of applications that Excel data tables can have. In most cases, data tables will be used in more complicated mathematical situations, with longer lists of input variables or even with a pair of changing variables affecting the outcomes of the formulas.

However, the basic way of using and setting up Excel data tables is very similar regardless of scenario, meaning that you can apply the above instructions to virtually any situation where you need to compare variable results in Excel. These calculations are especially popular in the world of finance, and if you wish to learn more about using Excel to make financial calculations, then click here to take a class on the financial functions of Microsoft Excel.