Consolidate monthly sheets
Upload your monthly sheets and download a single table with the values summed by product, customer or account.
The year-end case
You have twelve files — or twelve sheets inside one workbook — with the same structure: one row per product, customer or account, and the same value columns in all of them. You need the year's total in a single table.
This tool does exactly that: it groups by one or more key columns and sums the numeric columns, so the twelve "Product A" rows spread across the twelve months become a single row with the accumulated total. You can upload up to 20 files at once and, if each workbook has several sheets, it processes them all.
It resolves columns by name
The detail that saves headaches: columns are matched by their name, not their position. If March's file has the columns in a different order than January's, it still consolidates correctly as long as the titles match. The first sheet sets the reference structure and the rest is lined up against it.
What gets summed and what does not
- Key columns: the ones that define each group — the product, the customer, the month. They are required and are not summed: they group.
- Value columns: the ones that accumulate. If you name none, every numeric column that is not a key gets summed, which is what you normally expect from "consolidate". Only cells that are numbers are added; text is ignored.
- Record count: an optional column that counts how many original rows fell into each group, handy to check nothing is missing or double-counted.
Do not confuse it with stacking or counting
- If you only want to bring the rows together from several files without summing anything, use merge by stacking rows.
- If you work with a single file and want to group and total, it is more direct to group and count.
Consolidate is what you need when it is several monthly sheets with the same shape and you want the grand total.
Frequently asked questions
Which columns should I mark as keys?
The ones that identify each group you want to keep. For a total per product, the product column; for a total per customer and month, both. Everything else numeric is summed within those groups.
What if my files have the columns in a different order?
No problem. Consolidation matches columns by their name, so a file with reordered columns is summed correctly as long as the titles are the same.
How many files can I upload?
Up to 20 at once. And if each workbook has several sheets with the same structure, they are all included in the total.
What happens to text columns?
If they are among the keys, they group. Otherwise they are ignored: only numeric columns are summed. A text cell inside a value column does not break the sum, it simply adds nothing.
Can I tell how many rows were summed in each group?
Yes, leave the "Record count" column on. It tells you how many original rows made up each total, which is the best way to reconcile the result.