A two-variable Data Table is a grid: one input runs down the left, another runs across the top, and every cell shows the result for that pair. For a savings goal, put the monthly saving down the side, the number of months across, and the amount saved at the corner. You build it with Data, What-If Analysis, Data Table, using both the Row input cell and the Column input cell.
What formula sits behind the grid?
Keep the maths simple. The amount saved is the monthly saving multiplied by the number of months, with no interest, which makes the grid easy to check by hand. Use three cells:
- B1: monthly saving, 10,000 for now.
- B2: number of months, 12 for now.
- B3: amount reached, =B1*B2.
The grid will repeatedly replace B1 and B2 with other values and record what B3 becomes.
How do I build the grid?
- In A6 type =B3. This corner cell is the formula the grid reads.
- Across B6:F6 type the months to test: 6, 12, 18, 24 and 36.
- Down A7:A11 type the monthly savings to test: 5,000; 7,500; 10,000; 12,500 and 15,000.
- Select A6:F11.
- Choose Data, What-If Analysis, Data Table. Put B2 in Row input cell, since the months run across a row, and B1 in Column input cell, since the savings run down a column.
Excel fills B7:F11 with {=TABLE(B2,B1)}. The first argument is always the row input cell and the second the column input cell, which is a handy way to spot a swap.
What does the finished grid show?
| Monthly saving (₹) | 6 months | 12 months | 18 months | 24 months | 36 months |
|---|---|---|---|---|---|
| 5,000 | 30,000 | 60,000 | 90,000 | 1,20,000 | 1,80,000 |
| 7,500 | 45,000 | 90,000 | 1,35,000 | 1,80,000 | 2,70,000 |
| 10,000 | 60,000 | 1,20,000 | 1,80,000 | 2,40,000 | 3,60,000 |
| 12,500 | 75,000 | 1,50,000 | 2,25,000 | 3,00,000 | 4,50,000 |
| 15,000 | 90,000 | 1,80,000 | 2,70,000 | 3,60,000 | 5,40,000 |
Spot-check a cell: 12,500 a month for 24 months is 12,500 times 24, which is 3,00,000. The pattern also shows a trade-off at a glance. To reach ₹1,80,000 you could save ₹5,000 for 36 months, ₹7,500 for 24, ₹10,000 for 18 or ₹15,000 for 12.
How do I highlight the cells that reach my goal?
Say your goal is ₹2,00,000. Select B7:F11, choose Home, Conditional Formatting, New Rule, Use a formula, and enter =B7>=200000. Cells that reach the goal turn the colour you picked. Better, put the goal in a cell such as H1 and use =B7>=$H$1 so the shading changes when the goal does.
What if my savings earn interest?
Then the simple multiplication understates the result. Replace B3 with a formula that uses the rate from your own deposit's terms, for example =FV(rate,B2,-B1) where rate is the monthly rate, and leave the grid as it is. It then recalculates every cell. Check the rate and compounding with your bank, because these differ between products and change over time.
What are the usual problems?
- Row and column input cells swapped, which gives a grid of wrong-looking numbers. Delete the block and rebuild it.
- The corner cell is empty or typed as a number instead of =B3.
- The input cells are on a different sheet. Excel requires them on the same sheet as the table.
- Everything shows the same value because calculation is set to Automatic except Data Tables. Press F9.
What next?
- Choose the combination that fits your income without squeezing food and rent.
- Set a standing instruction for that amount on the day your salary arrives.
- Check progress monthly against the grid's value for the same month.
Google Sheets has no Data Table feature, but a grid of direct formulas gives the same result. Tracking whether you actually saved what you planned is a matter of looking at real transactions, and SpendixAI has savings goals on its Pro plan alongside imported statements, if you want that in one place.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


