Example of consolidating excel workbooks
To understand them, say that you have three data sets.
Each has names down the left side and months across the top.
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.
Alright hey, I want to thank for stopping by, we'll see you next time for another netcast from Mr Excel!
I got a call from a friend who wanted to combine multiple Excel files into one Excel workbook.
Today, an ancient technique called Data Consolidation.
If you have to combine data from multiple worksheets or multiple workbooks, Consolidation can handle the job if your data is in a specific format. There are two ancient consolidation tools in Excel.
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.
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!
He had a lot of files in a folder and he wanted to get all the worksheets from all the workbooks into one single workbook.
While this can be done manually, it would be time-consuming and error-prone.