Consolidate Data as a Quick Alternative to Subtotals

I have a great tip for you! When you need to produce a quick summary report of your data, use Consolidate in place of Subtotals. Here’s why:

• You don’t have to sort the data when you consolidate.
• You can use multiple functions (SUM, AVERAGE) in adjacent columns in your report.
• You can easily copy and paste the Consolidated Summary Report to another workbook or into Word.

Watch this short video as I demonstrate how to use Data Consolidate in place of Subtotals.

Steps to follow in this lesson:

1. Select an empty cell. Choose Data – Consolidate.
2. In the dialog box, choose the Function that you want (SUM is the most common) and then select your data range (including the Column Headers) and click ADD.
3. Be sure to check the Use Labels in “Top Row” and “Left Column.” Click OK
4. A quirk – The Label for the Category does not appear when you Consolidate Data – Simply copy & paste it from cell A1.
5. Repeat these steps to add an additional Function – e.g. AVERAGE to your Consolidation Report.
6. Copy and paste the Consolidated Data to another workbook or to another application – e.g. MS Word.
7. Contrast this quick alternative to creating Subtotals.
8. The real “Time Saver” comes from the ability to quickly copy the Consolidated Summary.
9. The “Gotcha” to remember with Subtotals is to choose “Edit – Go To – Special – Visible Cells Only” before you copy the Level 2 Subtotals. If you omit this step, you will discover that you have copied ALL of the data and not just the summary!

Find the Excel Training Video that you want – Click here for the Index to all Excel Topics

Learn how to “Master Excel in Minutes – Not Months!”