Consolidate Data By Position

Danny August 23, 2010 6 Comments

Summarize Multiple Excel Worksheets – Consolidate Data By Position

There are many ways to Summarize the data that is stored in multiple Excel Worksheets or Workbooks. Pivot Tables are great for producing summaries. However, many people do not use – or do not know how to use – Pivot Tables, so let me demonstrate how to use Excel’s Consolidate Data Tool to get the job done.

In this scenario, I will take the data from four identical worksheets and consolidate the sales numbers in a new worksheet. First, without using a “Link” to keep the data in the consolidated worksheet current and then I show you how to create a link to the Source Data.

But… there is a “Got’cha Step” when you link sources. It is possible to “double your sales numbers” without realizing it! This might make you feel good when you first see this. However, this is not good – when you are found out. And, trust me on this, someone will definitely find this error!

SUM Across Group of Excel Worksheets

As a bonus, I include another technique to SUM cells from multiple worksheets. Watch as I show you this “trick” – how to use the SUM() Function to total data across a contiguous group of Excel worksheets. It really is a great tip to learn!

Consolidate Data from Multiple Excel Worksheets By Position [ 6:53 ] Play Now | Play in Popup | Download (1591)

Filed Under: Consolidate Excel Data, Excel Functions, Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, Video Training Courses Tagged: Consolidate data by Position, Consolidate Ex, Excel Subtotals, Excel Video Training, Subtotal Function, Summarize Excel Data, Video lessons for Excel

Danny October 27, 2008 Leave a Comment

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:

Select an empty cell. Choose Data – Consolidate.
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.
Be sure to check the Use Labels in “Top Row” and “Left Column.” Click OK
A quirk – The Label for the Category does not appear when you Consolidate Data – Simply copy & paste it from cell A1.
Repeat these steps to add an additional Function – e.g. AVERAGE to your Consolidation Report.
Copy and paste the Consolidated Data to another workbook or to another application – e.g. MS Word.
Contrast this quick alternative to creating Subtotals.
The real “Time Saver” comes from the ability to quickly copy the Consolidated Summary.
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!”

Filed Under: Consolidate Excel Data, Excel 2003 Video Lessons, Excel Training, Tips and Time Savers Tagged: Consolidate data by Position, Consolidate Excel data, Create Subtotals in Excel, Excel 2003 Training Video, Tips and Time Savers in Excel 2003

Danny August 7, 2008 3 Comments

Consolidate Excel data by position

Most companies use Excel to produce budget reports. In this video lesson you will learn how to consolidate data from January, February and March to produce a 1st Quarter Consolidated Budget report – based off of an Excel Template.

Here are the steps to follow in this video lesson:

Start with a Template – either on your computer or download one from the Microsoft website. Modify as necessary and then make multiple copies of it. e.g. 1 ea. for Jan, Feb, Mar and 1st Qtr, etc.
Select the 1st cell to receive consolidated data in your 1st Quarter Consolidated report. Then, choose Data, Consolidate.
Point to the January worksheet and select the range of cells that you want in the 1st Qtr consolidated report. Then click ADD.
Repeat this process pointing to the February and March worksheets. Because we based our monthly reports on a Template, the range of cells in each worksheet is exactly the same – i.e. “By Position.”
If you do not check the “Create links to source data” box, you will simply add (SUM) the data from the selected ranges into the Consolidation worksheet. SUM is the most common function but you are free to choose others.
If you did check the “Create links to source data” box your consolidation worksheet will be outlined and each cell will be linked back to its source cell.

Danny Rocks “Training You to Excel”

Summarize Multiple Excel Worksheets – Consolidate Data By Position