Sparklines and Combining Data from Several Worksheets

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

This guide covers two skills that help with reports built from many rows or many sheets. Sparklines are tiny charts inside a single cell that show the trend for each row at a glance. Collating means bringing data together from several worksheets into one summary, using 3D formulas, Consolidate or Power Query.

In this guide: What sparklines are · Insert sparklines · Format sparklines · 3D formulas · Consolidate · Power Query

What are sparklines?

Imagine a table with one row per branch and twelve monthly sales columns. A full chart would be crowded, but a sparkline at the end of each row shows that branch's trend in a space the size of a cell. Excel offers three kinds:

Line: shows the trend over time.
Column: compares the size of each period.
Win/Loss: shows only whether each value is positive or negative, ideal for profit and loss by month.

Insert sparklines

Select the empty cells where the sparklines should go, for example N2:N8, one per row.
Go to Insert > Sparklines and choose Line, Column or Win/Loss.
In Data Range, select the numbers, for example B2:M8, and click OK.

Excel creates a group of sparklines, one per row. You can also create one sparkline and drag the fill handle down to copy it, just like a formula.

Format sparklines

Click a sparkline to open the Sparkline tab:

Tick High Point and Low Point to mark the best and worst months, and Negative Points to flag losses.
Change the line colour and weight under Style.
Under Axis, choose Same for All Sparklines for both the minimum and maximum. Otherwise each row is scaled separately and a small branch can look as strong as a large one.
Use Clear to delete sparklines; pressing Delete on the cell does not remove them.

Tip: Make the row taller and the column wider to make sparklines easier to read. Avoid merged cells; sparklines resize with the cell.

Combine sheets with 3D formulas

If every monthly sheet has exactly the same layout, a summary sheet can add them up with a 3D reference. With sheets Jan to Dec next to each other:

=SUM(Jan:Dec!C5)

This adds cell C5 on every sheet from Jan to Dec. AVERAGE, MAX, MIN and COUNT work the same way. A neat trick is to add two blank sheets named Start and End around the monthly sheets and use =SUM(Start:End!C5); any sheet dragged between them is included automatically.

Use Consolidate when layouts differ

When branch sheets list products in different orders, a 3D formula would add the wrong cells. Consolidate matches by labels instead:

On the summary sheet, click the top-left cell for the result.
Choose Data > Consolidate and set Function to Sum.
Click in Reference, select the data (including labels) on the first sheet and click Add. Repeat for each sheet.
Tick Top row and Left column so Excel matches by headings and product names.
Tick Create links to source data if you want the summary to update when the sheets change, then click OK.

Stack many sheets with Power Query

For the most flexible approach, turn each sheet's list into an Excel Table, load each one with Data > From Table/Range and combine them with Home > Append Queries in the Power Query Editor, or use Data > Get Data > From File > From Folder to combine identical files. Power Query stacks the rows into one long table that you can refresh with Data > Refresh All whenever new data arrives. That combined table is perfect for a pivot table.

Summary

Sparklines show a trend for every row in a single cell; set a common axis for fair comparisons.
Use =SUM(Jan:Dec!C5) when sheets share an identical layout.
Use Data > Consolidate when the layout or order differs between sheets.
Use Power Query to stack many sheets or files into one refreshable table.

Next: find answers in your data with Data Analysis tools.