How To Use The CONSOLIDATE Option In Excel

Here is a simple way to summarize data from a range on a single worksheet, multi sheets or multiple workbooks.  There are many alternatives in Excel to do this such as PivotTables, Sorting, Filtering, Subototals, etc., but CONSOLIDATE is one more alternative you have.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Let’s say you have a list of sales from various salespeople.  In that list are several sales from the same salesperson, and you want to quickly and easily summarize those sales into a table. You can use Consolidate.

Here is an example of units sold by three of your salespeople – Betty, Jack, and Sam:


First, start by putting your cursor in a cell where you want the results to be.  I will choose cell G1.  Then, click on the Consolidate button located in the Data Tools group of the Data tab:


The following pop-up box will appear:


In the Function field you can choose one of 11 operations from Sum, Average, Count, etc.  We’ll go with Sum.  Next, Click on the box at the end of the reference field and highlight your data:


Click on that box again.  When you are back at the pop-up, before anything else, make sure you check the fields where your labels are.  In this case, we have Q1-Q4 labels at the top and Salespeople’s labels on the left. Next, click Add to enter the referenced data into the “All reference:” field:


Finally, click OK, and your data is consolidated into a nice summarized table. With a little formatting, your table can look clean and simple:


Nice!  Now, this data is static.  You can click on any of the data in the consolidated table and you will see no formulas.

Now, in the next example I have 4 tabs in my workbook:


On the Consolidated tab is where we will summarize the data.  On the Year2010 sheet is the following data:


Year2011 has this data:


Year2012 has this data:


You’ll note that they all have the same columns and salespeople, but the number of lines in each data set varies.  As we did with the previous example, we start by putting the cursor where we want the consolidated data to end up.  In this case, that will be cell A1 of the Consolidated tab. Once there, click on the Consolidate button in the Data Tools group of the Data tab and you will get the following pop-up:


As with the previous example, click on the box at the right of the reference field, and highlight the data on the Year2010 worksheet:


Click again on the box at the right. Before you do anything else, make sure you check the labels you want to appear – your row and column headings. Once those are checked, click Add to put your data in the “All reference:” field:


Now, do the same for the other two worksheets.  The Top row and Left column check marks should remain.  You should end up with this:


Click OK, and with a little formatting, you should have a nice consolidated table:


This also does not have any formulas.   It is static data, but very useful!  Try it.

The first few steps of this next example are very similar to the previous .  The main difference between example 2 and 3 is linking the consolidated data table back to the source, thus allowing it to be dynamic with the source changes.  As you select the ranges of data from the three tabs, prior to clicking on “Add”, make sure you check “Top row”, “Left column” AND “Create links to source data”:


Once you have all the ranges in the “All references:” field, click OK.  The data is now summarized at the location you specified:


But, there’s one BIG difference!  Notice the Outline plus buttons in the left column.  Click on any or all of these and you will see the details of the summarized data. Notice also that clicking on any of the data cells shows the source in the formula bar.  Therefore, if the source data is changed, it will also modify the summary data.  In column “B”, the name of the file where the source data resides is indicated:


Play around with changing your data along with expanding and contracting the outlines.  Again, with just a little formatting, the consolidated data can look great!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply