Consolidate Reports
"I usually get summaries of quarterly sales of more than 12 different commercial Is there any way that excel" gather all information into a table without me having to walk by copying / pasting?
Yes, there is: the tool Consolidate. We start as always with an example. Suppose we have 4 trade, namely, Carlos, Javier, Jose and Santiago, and reminds us that each excel file where it says its quarterly net sales of individual products that comprise its portfolio. The following images show this information:
We want to consolidate all this information in a document called Total. To do this we must follow the following steps:
1. From the file menu go to TOTAL Data / Consolidate . The following window appears:
2. We chose the function that we use in our building (Sum, Count, Average, etc). In our case we use the SUM.
3. In the section we select the first reference ranges of interest. To do this click the icon with the red arrow, we go to the worksheet named Sales Carlos and select the range A6: E12. The display shows the full path:
4. press Enter and see the following:
5. Press the button Add . Once this is done the model appear in Table All references .
6. Return to press the icon to select a new range in the field Reference. This time we went to the sheet named Javier Sales and select the range A5: E8. As in the previous case Excel will display the full path. press Enter and then the Add button . At this point we can see on the screen as follows:
7. We repeat the steps above to add the sale of Joseph leaves and Santi:
8. Keep options marked Use labels in / Top row and Left column a. This is because we have labels on the top row (the quarters) and signage in the left column (the products). Finally we decided if we checked the Create links to source data. If you uncheck excel will not save the links and is limited to performing consolidation "sticking values." On the contrary, if we keep this option selected, Excel will generate a schema with information about where each piece of data and also from TOTAL keep you tied the sheet with the rest of the leaves, so that if you modify any data on these pages (within the range considered for consolidation) will automatically update the sheet completely. Press the OK button and the result obt enid is this:
As you can see excel has created a scheme with the consolidated information. Press the Level 2 information and get the most detail:
