Aggregate by Colour: sum, average, max, or min cells by their fill colour in Excel

Colour-coded spreadsheets are everywhere in accounting. Categories marked in yellow. Exceptions flagged in red. Reconciling items highlighted by a colleague before the file landed in your inbox. The colour carries meaning — but Excel has no built-in way to act on it.

SUMIF, AVERAGEIF, and their siblings work on values and text. None of them work on cell colour. The result is that aggregating by colour requires either a helper column, a VBA macro, or a lot of manual selection.

The tech+bash Aggregate by Colour app handles it directly, from inside the task pane.

What it does

Aggregate by Colour applies a formula — SUM, AVERAGE, MAX, or MIN — to all cells in a range that share the fill colour of a reference cell you specify. You point it at the range, point it at a cell with the colour you care about, choose the formula, and it returns the result.

No helper columns. No macros. No selecting cells one by one.

Step-by-step

1. Open the task pane

Click the tech+bash button in the Excel ribbon. Select Aggregate by Colour from the Apps section.

2. Select your range

Choose the cell range you want to aggregate across — the full list of transactions, the column of figures, whatever you’re working with. You can type the range directly (e.g. E4:E19) or select the cells on the sheet and use the range picker button to populate the field automatically.

3. Pick a reference colour

Select any cell on the sheet that has the fill colour you want to filter by. If you want to sum the yellow cells, click a yellow cell. The app reads the colour from that cell and uses it to identify matching cells in your range.

4. Choose your formula

Select one of: SUM, AVERAGE, MAX, or MIN.

5. Submit

Click Submit. The result appears immediately. If you want to write it to the spreadsheet rather than just read it, select the destination cell and click Insert Result. The value lands in that cell.

6. Reset

Click Reset to clear the inputs and run again with a different colour or range.

When this comes up in practice

Colour-coded expense categorisation. A common pattern in accounting workbooks: different expense types are highlighted in different colours — travel in blue, software in green, professional services in orange. Aggregate by Colour lets you total each category without restructuring the workbook or building a helper column.

Reviewing someone else’s workbook. Files shared between teams or received from clients often use colour to communicate something — flagged items, disputed amounts, items pending sign-off. If the colour carries a meaning you need to quantify, Aggregate by Colour gets you there without touching the existing formatting.

After running the Reconciler. The Reconciler highlights matching transactions in yellow. Once you’ve found your reconciling subset, Aggregate by Colour lets you total the remaining cells — the unmatched items — to confirm they account for the rest of the balance. Select the same range, pick a cell with no fill colour (or a colour representing unmatched items), and sum.

Reviewing a flagged population. If a colleague has gone through a ledger and coloured exceptions for your attention, Aggregate by Colour gives you a quick total of just those items — useful for understanding the scale of the issue before diving into the detail.

A note on reproducibility

Aggregate by Colour reads fill colour at the time you run it. If the colours in the workbook change later — someone reformats cells, a conditional formatting rule fires — the result from a previous run won’t match a future one.

For recurring calculations or anything that needs to be verifiable later, a text-based category column with SUMIF is more robust. Aggregate by Colour is best suited to one-off analysis, quick checks, and workbooks where the colour scheme is fixed and understood.

Getting started

Aggregate by Colour is included in the free tier of the tech+bash Add-in — no subscription required. Install the Add-in and it’s available immediately from the Apps section of the task pane.