Data tables in MS Excel allow us to experiment with different kinds of input values. They also put an impact on the outcomes in a model without restructuring calculations. They're utilized in personalized apps, like calculating monthly payments that rely on changing interest rates and different loan expenses. Lastly, data tables are used in scenarios which involving the what-if or else sensitivity exploration. In another context, single and multiple variable data tables are beneficial tools that inspect the ways in which one or more alternative sets impact a given situation. To learn more about the importance of Excel data tables, talk to our experts!
Data Tables in Excel - Explore!
In MS Excel, a data table is a tool of what-if analysis that lets a person test input values for formulas. It also observes how modifications in these values affect the output of the formula. In general, data tables are mainly beneficial while the formula is reliant on many values. You might prefer to keep experimenting with different mixtures of inputs, then compare with results.
Although limited to a maximum of two other kinds of input cells, the data table allows you to assess numerous variable values according to your requirement. To efficiently use data tables in Microsoft Excel, consider the following:
- For the data table to generate effectively, input cells should be on a similar sheet.
- Since the results of a data table are calculated with a collection of formulas, resulting cells would not be separately edited. You can only keep editing or delete the whole range of cells, which we exlain below.
Building a One-Variable Data Table in Excel
A one-variable data table in MS Excel allows you to test multiple values for only one input cell while showing how these values are influencing the outcome of the associated formula. To better understand this, let's explore a specific example.
You deposit your money in a bank that pays 5% interest, compounding every month. To evaluate other options, you have put together this compound in Excel:
- B8 encompasses the formula of FV, which is calculating your final balance.
- B2 is a similar variable you are interested in assessing (starting investment).
Let’s use the simplest what-if examination to check what your amount would be in the next five years - though it all depends on the sum of your starting investment, which ranges from $1,000 - 6,000 dollars.
These are the phases to create a one-variable data table:
- Keep entering variable values, whether in a single column or row. In this example, let's use a column. Type the values of your variable in column D3:D8, then leave a single blank column towards the right for your outcomes.
- Write your formula in the cell a row above, and another cell towards the right of your variable (E2). Then, link that cell towards the formula within your actual dataset (if you modify the formula later, you only need to upgrade a single cell). We will select the final option and keep entering this formula in the E2: =B8.
- Select the data table range, and include your formula, variables, as well as any empty cells for your outcomes (D2:E8).
- In the Data tab, select Data Tools and press What-If Analysis, then click Data Table.
- In the dialog window of Data Table, click in the cell box of Column Input (since the investment values are in a column), then click the variable cell referenced within the formula. In this example, we chose B3, the primary value of your investment.
- Clic OK, and Excel will instantly occupy the blank cells with results equivalent to the value of a variable in a similar row.
- Implement the preferred number format towards the pattern of outcomes (money in this scenario), and you've finished!
Building a Two-Variable Data Table in Excel
If you’re building a two-variable data table, here's how you set it up:
- Keep entering the formula in an empty link or cell. Ensure that you have enough blank columns towards the right, and enough blank rows below for your variables. On the other side, link a cell (E2) towards the unique formula of FV that is calculating the remaining balance: =B8.
- Write down new values of input sets underneath the formula in the same column (the values of investment in E3:E8).
- Pass in another value of a variable set towards the right of the formula, in the same row (total years in F2:H2).
- Select the whole range of E2:H8.
- Generate the data table as explained previously: Data tab > What-If Analysis > Data Table.
- Within the cell box of row input, enter the cell of input for values of a variable within the row (in this example, B6 is the value of years).
- You can also format the outputs per your requirement by implementing the layout of currency, then evaluate the outcomes.
Final Thoughts
Now that you know how to build one- and two-variable data tables in MS Excel, you can use them to your advantage. Its efficiency saves time when calculating decisions. While copy and paste can be helpful at times, it's better overall to build data tables. For a more in-depth look, check out our Data Science Bootcamp!
Connect with our experts to learn more. Start your 30-day free trial to gain access to over 900 self-paced courses.