Repeat a calculation in excel using Data Tables

Repeat a calculation in excel using Data Tables

A common requirement in business is to repeat a calculation in excel many times. Depending on how you built the spreadsheet this could be as easy as just copying the formulas down or across and changing the inputs.

However, sometimes you have a spreadsheet that calculates a complex calculation e.g. electricity tariffs in South Africa have multiple bands, in a way that would make it difficult to copy or replicate many times. It is almost a black box where you put a single number in and you get an answer but the actual calculations are too complex, or you do not have enough time to rebuild the spreadsheet so that you can run multiple different inputs through it.

In this instance you may want to consider using the Data Tables feature in Excel.

In short, Data Tables are normally used to run sensitivities in a financial model. It allows you to run various combinations of inputs through the model and see what all the results would be at the same time.

In this case however, all we would want is for the Data Table to repeat the same calculation many times based on a different input. In the electricity tariff example mentioned above, the original calculation may have allowed you to enter the MW used in one cell.

With Data Tables you can create a table of all the possible MW used (say from 0 to 200 in steps of 1) and then get Excel to run through and use the single calculation 200 times to see what the answer would be at 0, 1, 2, 3 … etc.

The main benefit is time saved and as it is linked to the model, if you change some of the calculations, the results will change with it.