Spreadsheets

BYROW and BYCOL: totals and peaks for a month-by-category grid

Use BYROW and BYCOL in Excel to find each month's biggest category and each category's average in a 12-month by 8-category grid, with no helper columns.

Editorial illustration for BYROW and BYCOL: totals and peaks for a month-by-category grid

BYROW runs a calculation on each row of a grid and BYCOL runs one on each column, each returning a single result per row or column. On a 12-month by 8-category expense grid, BYROW can name the biggest category in every month and BYCOL can give every category's average, all without helper columns. Both need Microsoft 365.

What does the grid look like?

Suppose months are in A2:A13, categories are in B1:I1, and the amounts are in B2:I13. Each cell is the total spent in that category for that month. The small version below shows three months and three categories, with made-up festival-season numbers.

MonthFoodTravelShopping
October₹11,200₹3,400₹14,800
November₹10,600₹2,900₹6,200
December₹12,300₹7,800₹5,900

Which category was biggest each month?

BYROW passes each row to a LAMBDA as an array. MAX finds the biggest value in it and MATCH finds where it sits, and INDEX turns that position into a category name:

=BYROW($B$2:$I$13,LAMBDA(rowvals,INDEX($B$1:$I$1,MATCH(MAX(rowvals),rowvals,0))))

Type it once beside the grid, in K2, and it spills twelve answers. For the small table above it returns Shopping for October, Food for November and Food for December. If two categories tie, MATCH returns the first one from the left.

What was the average for each category?

BYCOL works the same way across columns. For averages, you can pass the function name straight in:

=BYCOL($B$2:$I$13,AVERAGE)

Or write it out as =BYCOL($B$2:$I$13,LAMBDA(colvals,AVERAGE(colvals))) when you want to add a step. Put it in a row under the grid, say B15, and it spills eight averages sideways. In the small table, Food averages (11,200 + 10,600 + 12,300) divided by 3, which is 34,100 divided by 3, or about ₹11,367. Travel averages 14,100 divided by 3, which is ₹4,700. Shopping averages 26,900 divided by 3, about ₹8,967.

And the totals per month?

=BYROW($B$2:$I$13,SUM) gives a total for each month. For the three months in the sample: October is 11,200 + 3,400 + 14,800 = ₹29,400, November is 10,600 + 2,900 + 6,200 = ₹19,700, and December is 12,300 + 7,800 + 5,900 = ₹26,000.

Which month was each category's peak?

BYCOL can return text as well as numbers. This finds the month in which each category peaked:

=BYCOL($B$2:$I$13,LAMBDA(colvals,INDEX($A$2:$A$13,MATCH(MAX(colvals),colvals,0))))

In the sample, Food peaks in December, Travel in December and Shopping in October. A pattern like that, with Shopping high in the Diwali month, is the kind of thing a grid makes visible.

Turning the results into a decision

The formulas give you facts; you decide what they mean. In the sample, Shopping in October was ₹14,800 of that month's ₹29,400, which is just over half. If that is Diwali shopping, it is a known yearly bump and worth setting money aside for, not a sign that something went wrong. If the same pattern appears in an ordinary month, that deserves a closer look. Put the same grid for last year beside this year's and compare the biggest category per month; a change in the answer is a useful prompt.

What should I watch for?

  • The grid must contain numbers only. A text cell such as a dash in a month with no spending will make AVERAGE skip it, which changes the answer; enter a zero instead if you mean zero.
  • Spill room: keep the cells to the right of or below your formula empty.
  • Before Microsoft 365, use helper columns: =INDEX($B$1:$I$1,MATCH(MAX(B2:I2),B2:I2,0)) in K2 and fill down, and =AVERAGE(B2:B13) in B15 and fill right.

Why bother with BYROW and BYCOL?

The case for them is fewer moving parts. With helper columns, someone eventually deletes one, or sorts the grid without the helpers, and the sheet quietly shows nonsense. A single spilled formula cannot be half-copied. The trade is that it only works for people on Microsoft 365, so decide who will open the file.

If building the grid each month from several statements is what takes the time, that is the part a statement-importing tracker handles; the summaries on top are a nice formula exercise either way.

SPENDIXAI JOURNAL

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

Explore your reports