Excel data tables
Data tables are defined as a range of cells that are used for testing and analyzing outcomes on a large scale. It is a way to see how altering the values in a formula affect the results. Data tables can store the results of multiple scenarios in your spreadsheet, and saves you time in calculating multiple formulas. These tables are written in array formulas; this allows the user to perform multiple calculations in one location.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
How to set up data tables
There are two different data tables that you can use depending on what you would like to calculate. Either one-variable data tables or two-variable data tables both are equally easy to set up and complete. This step by step guide will assist you in setting up your own data tables and cutting the time you spend on calculating scenarios by more than half.
For the purpose of this exercise we will use the example of DVD rentals. A DVD rental is R20.00, costs for the company is R2000.00. We want to work out the net profit.
- Open up a new Excel spreadsheet
- Set up a Base Model for the function to work from:
- Cell 1 (C1): will be the rental price: R20.00
- Cell 2 (C2): will be the number of rentals: 100 (any number)
- Cell 3 (C3): total income, add the formula =C1*C2, cell now reads R2000.00
- Cell 4 (C4): costs: R2000.00
- Cell 5 (C5): net profit: is the formula =C3-C4, cell now reads 0
To determine how many rentals the company requires to show a positive net profit, therefore you will be using one-variable option of the data tables:
- Along side the base model set up a column that contains various amounts that are predicted rentals, example: 100; 150; 200; 250; 300; 325 (from Cell E2 – E7)
- Take your cursor and select the cell from E1 down to E7 and the cells adjoining to the right.
- From the Data menu in the Excel menu at the top of the window select Table from the drop down menu.
- A dialogue box will appear with two blocks that require values, for the purpose of a one-variable data table you will only fill in one of the fields.
- The two field are: Row input cell and Column input cell; as you have put your values in a column you will use the Column input cell field and you will put the cell that corresponds to your values, ie C2 the number of rentals.
- The other field can be left open as we are only working with one variable.
- Click OK, Excel will automatically add in the values alongside
- You can also use two-variable data tables, for these tables you will put the one set of variables horizontally and the corresponding cell name will go in the Row input cell field in the dialogue box. The other set of variables will be put in vertically and the corresponding cell name will be put in the Column input cell field.
Excel data tables training
For the in depth look at data tables and how it can help your company with auditing purposes contact the experts in the field, Miricle Solutions. The staff of Miricle Solutions are experienced in Excel, Accounting and Auditing and have designed courses to help you get the most use out of your Excel package and save you time and money. Miricle Solutions can provide training on data tables, goalseek and numerous other functions.