Spreadsheets

One-variable Data Table: see savings at different spending cuts

Build a one-variable Data Table in Excel to see monthly savings at different spending cuts, and avoid the row and column input cell mix-up.

Editorial illustration for One-variable Data Table: see savings at different spending cuts

A one-variable Data Table shows one result for many values of a single input. List the spending-cut percentages down a column, put a cell that holds your savings result at the top of the next column, select both, and choose Data, What-If Analysis, Data Table. Because the percentages run down a column, you fill in the Column input cell, not the Row input cell.

How do I set up the sheet?

Use a small model with invented figures:

CellMeaningValue
B2Monthly income₹80,000
B3Fixed spending₹36,000
B4Flexible spending₹30,000
B5Cut applied to flexible spending (input cell)0%
B6Savings: =B2-B3-B4*(1-B5)₹14,000

Cell B5 is the input that the table will change again and again. B6 is the formula it will read.

How do I build the Data Table?

  1. In A9:A13 type the cuts to test: 0%, 5%, 10%, 15% and 20%.
  2. In B8, one row above the first result and one column to the right of the percentages, type =B6.
  3. Select A8:B13, that is the formula cell, the percentages and the empty results area.
  4. Choose Data, What-If Analysis, Data Table.
  5. Leave Row input cell empty. In Column input cell, click B5, then OK.

Excel fills B9:B13 with an array that looks like {=TABLE(,B5)}. You cannot edit single cells inside it; you can only clear the whole block.

What does the filled table show?

Cut to flexible spendingSavings (₹)Check
0%14,00080,000 - 36,000 - 30,000
5%15,50030,000 x 5% = 1,500 more
10%17,00030,000 x 10% = 3,000 more
15%18,50030,000 x 15% = 4,500 more
20%20,00030,000 x 20% = 6,000 more

The extra savings are the flexible spending multiplied by the cut, so a 10 percent cut adds ₹3,000 a month, or ₹36,000 over twelve months. Seeing the whole ladder helps you ask what an honest cut would be, and what it would take to get there.

What is the row and column input cell mix-up?

This is the usual stumbling block. The names refer to where your list of test values sits, not to where the input cell sits. Test values running down a column mean a Column input cell. Test values running across a row mean a Row input cell. If you put B5 in the wrong box, Excel substitutes the percentages into the wrong place and the table fills with identical numbers or with odd results. Delete the block and rebuild it with the other box.

Why are my results all the same?

  • Calculation is set to Automatic except Data Tables. Press F9, or change it under Formulas, Calculation Options.
  • The formula in B8 does not depend on B5, so changing B5 changes nothing. Trace the precedents with Formulas, Trace Precedents.
  • The percentages are text. Type them as numbers with a percent format.

Can I test two things at once?

Not with this table; you need a two-variable one for that, where one input runs down the side and another across the top. Another limit: Data Tables are a desktop Excel feature, and Google Sheets does not have one.

A short checklist

  • One input cell, one result formula.
  • Percentages in a column, formula in the cell above and to the right.
  • Column input cell for values listed down.
  • Spot-check one row by hand.

To read the table like a plan, compare the effort with the reward. A 5 percent cut on a ₹30,000 flexible line is ₹1,500 a month and may be easy; 20 percent is ₹6,000 and probably means real changes. Pick the row you could keep for a full quarter, not the one that looks best on paper.

A what-if table is only as sound as the starting figures. SpendixAI can import statements and show spending by category, so your fixed and flexible lines come from your own transactions rather than guesses.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports