Data Outlines and Conditional Formatting in Excel

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019

Large sheets are hard to read. Two features make them manageable: outlines, which let you collapse detail rows and show only summaries, and conditional formatting, which colours cells automatically based on their values so problems and top results stand out. This guide explains both, with step-by-step examples.

In this guide: Grouping rows and columns · Automatic subtotals · Conditional formatting basics · Data bars, colour scales and icons · Formula-based rules · Managing rules

Group rows and columns into an outline

Suppose a budget has income lines in rows 3–8 with a total in row 9, and expense lines in rows 11–20 with a total in row 21. To let readers hide the detail:

Select rows 3 to 8.
Press Shift + Alt + Right Arrow (or Data > Group).
Repeat for rows 11 to 20.

Minus buttons appear in the margin. Click one to collapse that group to its total; click the plus to expand it. The small numbers 1, 2, 3 at the top of the margin show or hide every level at once. Columns can be grouped in exactly the same way, which is useful for hiding monthly columns and showing only quarterly totals.

To remove a group, select the rows and press Shift + Alt + Left Arrow. If your totals use SUM formulas directly below the detail, Data > Group > Auto Outline can build the groups for you.

Add automatic subtotals

For a list sorted by a category, such as sales sorted by Region, Excel can insert subtotals and an outline in one step:

Sort the list by Region (Data > Sort).
Click inside the list and choose Data > Subtotal.
Set At each change in to Region, Use function to Sum, and tick the Amount column.
Click OK.

Excel adds a subtotal row under each region, a grand total at the bottom and outline buttons on the left. Click level 2 to see one line per region. To undo it, open Data > Subtotal again and click Remove All. The subtotal rows use =SUBTOTAL(9,…), which ignores other subtotals, so the grand total is never double-counted.

Conditional formatting basics

Conditional formatting applies a format only when a rule is true, and updates automatically as values change. To highlight overdue balances above 10,000:

Select the balance column.
Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
Type 10000 and choose Light Red Fill with Dark Red Text.
Click OK.

Other built-in rules in the same menu highlight values between two numbers, text that contains a word, dates in the last 7 days, and Duplicate Values, which is a quick way to find repeated invoice numbers. The Top/Bottom Rules highlight, for example, the top 10 items or values above average.

Data bars, colour scales and icon sets

Data Bars draw a bar inside each cell in proportion to its value, like a mini bar chart.
Color Scales shade cells from one colour to another, for example green for high and red for low, which makes heat maps of sales by month and branch.
Icon Sets add arrows, traffic lights or flags. Edit the rule to set your own thresholds rather than relying on the default percentages.

Tip: For a clean dashboard, edit a data bar rule and tick Show Bar Only in a helper column next to the numbers.

Rules based on a formula

Formula rules can format an entire row based on one cell. To colour every row where the Status in column E is "Unpaid":

Select the data rows, for example A2:F200, starting from row 2.
Choose Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =$E2="Unpaid". The dollar sign locks the column but leaves the row free to change.
Click Format, choose a fill colour and click OK.

More examples: =$D2<TODAY() highlights rows with a past due date, and =MOD(ROW(),2)=0 shades every other row.

Manage and remove rules

Conditional Formatting > Manage Rules lists every rule on the sheet. From there you can edit a rule, change the range it applies to, change the order, and tick Stop If True so later rules are skipped. Too many overlapping rules slow a workbook down, so delete those you no longer need with Clear Rules.

Summary

Group rows or columns with Shift + Alt + Right Arrow to collapse detail.
Use Data > Subtotal on a sorted list for instant category totals.
Use conditional formatting to highlight exceptions, duplicates and trends automatically.
Write formula rules with mixed references like =$E2="Unpaid" to colour whole rows.

Next: present your results visually with Excel Charts.