Consolidate is an ancient feature in Excel
Specify multiple ranges to consolidate
Use labels in Top row & Left column
Annoyances: A1 is always blank, column A is not sorted, blanks in data
Go to Special, Blanks, 0, Ctrl+Enter
Consolidate can point to external workbooks
Specify multiple ranges to consolidate
Use labels in Top row & Left column
Annoyances: A1 is always blank, column A is not sorted, blanks in data
Go to Special, Blanks, 0, Ctrl+Enter
Consolidate can point to external workbooks
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2046 - Consolidate Worksheets!
I'll be podcasting all of my tips from this book, click the “i” on the top-right hand corner to get to the playlist!
Alright, I have an awesome, awesome old ancient trick.
I have three datasets here, Q1, Q2, Q3, they all have a similar shape, and that they have names down the left hand side, months going across the top, but not exactly the same shape.
Q1 has Jan-Feb-Mar, Q2 has 5 months, I guess we got lazy and didn't figure out to do this at the end of June and someone finally did it at the end of August, and then Q4 has 4 months.
Alright so, the 1st one goes A:D, 2nd one A:F, 3rd one A:E. Different names, some of the names are the same, like Michael Seeley is there in all of them, but other names come and go.
Alright, this is an amazing feature, it's been around forever.
I remember doing this back in 1995 even, Data, Consolidates!
Alright, we're going to use the SUM function, I've never used any of the others, but I guess they're there.
SUM function, the first thing we're going to do is go back to Q1 and point to this range, those four columns, click Add, and then go to Q2, select these columns, click Add, and then Q4, select these columns.
Alright, check this box for Use labels in the Top row and the Left column, that Browse button means that these data sets can be in different workbooks!
Create links to source data, we're going to talk about that one at the end.
When I click OK, they're going to get every name that is in any of the 3 lists, the months there in any of the 3 lists, and we now have this amazing superset, alright, annoyances!
This is a great feature, but here's the things that just hacked me off.
They don't give me the label in A1, they don't bother to sort the data going down, and if someone didn't have a record in the first one, they give me blanks instead of zeroes.
Alright, to fill the blanks with zeroes, Home, Find and Select, Go To Special, choose the Blanks, click OK, type a zero, Ctrl+Enter will fill those in.
Easy enough to sort the data, Data, A-Z, and it will sort the data, alright.
Create links, jeez, it never works right, alright, create links, for Create Links to work, it has to be in an external workbook.
Alright, so I'm going to click Browse here, I created a work book called OtherWorkbook, and the data is in A1:D7, click Add, alright, and there's the first one.
The next data is in G1:L8, so I'll Browse OtherWorkbook, G1:L8, click Add, alright.
So now I have two references to other workbooks, Top row, Left column, Create links to source data.
Excel help says that once you use Create links the source data, you'll never be able to edit those ranges, again click OK, and here's what we get.
Alright, first thing, it looks like it gave us the results, it did give us the results, but there's an extra column B here, and we have Group and Outline.
And when we go to the number 2 view, ah.
So ideally, what you would have is, you'd have one workbook called January, and another workbook called February, and it's going to show you here's January, here's February.
Here's the total for Mike Seeley, these are formulas pointing into those sales, and then here's a sum of those two, alright, it's weird.
If you actually use this all the time, I want to hear from you in the YouTube comments, I'm sure there's an easier way to do it for me, never done it in my life, well once before today, and then today, just so that way I could explain it, alright.
Consolidate, though, when we're consolidating sheets from the current workbook, awesome, awesome trick.
Tomorrow we're going to compare the multiple consolidation range, Pivot tables, but all of these tricks are in the book, click the “i” on the top-right hand corner to get to that book.
Consolidate, ancient, ancient feature in Excel, you specify multiple ranges to consolidate, I always check the box for top row and left column.
The results are great, but A1 is blank, column A is not sorted, and there's blanks in the data, use Go To Special Blanks, type a 0, Ctrl+Enter to fill those blanks.
And then that final example there, consolidate can point to external workbooks, might be useful!
Alright hey, I want to thank for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting all of my tips from this book, click the “i” on the top-right hand corner to get to the playlist!
Alright, I have an awesome, awesome old ancient trick.
I have three datasets here, Q1, Q2, Q3, they all have a similar shape, and that they have names down the left hand side, months going across the top, but not exactly the same shape.
Q1 has Jan-Feb-Mar, Q2 has 5 months, I guess we got lazy and didn't figure out to do this at the end of June and someone finally did it at the end of August, and then Q4 has 4 months.
Alright so, the 1st one goes A:D, 2nd one A:F, 3rd one A:E. Different names, some of the names are the same, like Michael Seeley is there in all of them, but other names come and go.
Alright, this is an amazing feature, it's been around forever.
I remember doing this back in 1995 even, Data, Consolidates!
Alright, we're going to use the SUM function, I've never used any of the others, but I guess they're there.
SUM function, the first thing we're going to do is go back to Q1 and point to this range, those four columns, click Add, and then go to Q2, select these columns, click Add, and then Q4, select these columns.
Alright, check this box for Use labels in the Top row and the Left column, that Browse button means that these data sets can be in different workbooks!
Create links to source data, we're going to talk about that one at the end.
When I click OK, they're going to get every name that is in any of the 3 lists, the months there in any of the 3 lists, and we now have this amazing superset, alright, annoyances!
This is a great feature, but here's the things that just hacked me off.
They don't give me the label in A1, they don't bother to sort the data going down, and if someone didn't have a record in the first one, they give me blanks instead of zeroes.
Alright, to fill the blanks with zeroes, Home, Find and Select, Go To Special, choose the Blanks, click OK, type a zero, Ctrl+Enter will fill those in.
Easy enough to sort the data, Data, A-Z, and it will sort the data, alright.
Create links, jeez, it never works right, alright, create links, for Create Links to work, it has to be in an external workbook.
Alright, so I'm going to click Browse here, I created a work book called OtherWorkbook, and the data is in A1:D7, click Add, alright, and there's the first one.
The next data is in G1:L8, so I'll Browse OtherWorkbook, G1:L8, click Add, alright.
So now I have two references to other workbooks, Top row, Left column, Create links to source data.
Excel help says that once you use Create links the source data, you'll never be able to edit those ranges, again click OK, and here's what we get.
Alright, first thing, it looks like it gave us the results, it did give us the results, but there's an extra column B here, and we have Group and Outline.
And when we go to the number 2 view, ah.
So ideally, what you would have is, you'd have one workbook called January, and another workbook called February, and it's going to show you here's January, here's February.
Here's the total for Mike Seeley, these are formulas pointing into those sales, and then here's a sum of those two, alright, it's weird.
If you actually use this all the time, I want to hear from you in the YouTube comments, I'm sure there's an easier way to do it for me, never done it in my life, well once before today, and then today, just so that way I could explain it, alright.
Consolidate, though, when we're consolidating sheets from the current workbook, awesome, awesome trick.
Tomorrow we're going to compare the multiple consolidation range, Pivot tables, but all of these tricks are in the book, click the “i” on the top-right hand corner to get to that book.
Consolidate, ancient, ancient feature in Excel, you specify multiple ranges to consolidate, I always check the box for top row and left column.
The results are great, but A1 is blank, column A is not sorted, and there's blanks in the data, use Go To Special Blanks, type a 0, Ctrl+Enter to fill those blanks.
And then that final example there, consolidate can point to external workbooks, might be useful!
Alright hey, I want to thank for stopping by, we'll see you next time for another netcast from MrExcel!
