Contents
There are many ways to summarize data in Microsoft Excel. You can use the tool Tables and related filters to display only the information you need. You can create pivot tableto present information from different angles. In addition, you can combine large amounts of data in a summary that will show as much data as you want to see. In this article, I will introduce you to the tool Consolidation in Excel, with which you can extract data from a number of sheets and even workbooks and summarize them on one sheet.
Working with multiple datasets
Imagine this situation: you have data for twelve months of doing business, information for each month is stored on a separate sheet of an Excel workbook. If each sheet contains information for one month, and if the information on all sheets is organized in the same way, then using the tool Consolidation You can summarize the data on one sheet for the entire year.
To do this, open a blank sheet in an Excel workbook (add a new one if necessary) and click on any cell in it. On the tab Data (data) click Consolidate (Consolidate) to open the dialog box Consolidate (Consolidation). Select a function to analyze the data and reference the ranges to be rolled up. In our case, we want to sum the values, so in the field Function (Function) select Sum (Sum).
You can choose any of 11 operations: Sum (Sum), Count (Quantity), Average (Average), Max (Maximum), Min (Minimum), Product (Work), Count Numbers (Amount of numbers), StdDev (Offset Deviation), StdDevp (Unbiased Deviation), Yes (Shifted variance) and Warp (Unbiased variance).
Click in the field Reference (Link) and click turn to the right of the input field. Select the first data range to consolidate – to do this, open the appropriate sheet by clicking on its tab and select all the necessary data with the mouse, including row and column headers.
Returning to the dialog box Consolidate (Consolidation), click Add (Add) to add the first data set to the list of ranges to consolidate. Follow the same steps to add the second and all other datasets to the range list.
If you have the same ranges of data in the same position on each sheet, then all you have to do is click on the tab of the next sheet, and the range will be automatically selected. So this task is much easier than it seems at first glance.
Pivot ranges that are on different sheets do not have to be the same size. The number of rows and/or columns on each sheet may vary. For example, if we opened a new office in September, then from September the range of cells will have to be expanded to include additional data related to the new office. The same will need to be done for each sheet containing additional rows.
Helpful advice
You can name the ranges before you begin the consolidation process. To do this, select the range and give it a name in the field First name to the left of the formula bar. When you give names to all ranges, then when setting up consolidation, put the cursor in the field Reference (Link), click F3 and in the opened window Paste Name (Insert name) select the desired range. This way you can give each range a meaningful name so you don’t have to remember what data is hidden on the sheet later. Sheet1 in cells A3: F40.
As you can see, in the area All References (Range List) The sheets are arranged in alphabetical order. Before proceeding, make sure you have referenced all required ranges. Check the options Use labels in (Use as names): Top Row (Top line captions) and Left Column (Values of the left column). Please also check the box for Create links to source data (Create Links to Source Data) and click OK.
Consolidated data
When you press OK, Excel will summarize all the selected data in a new sheet. Along the left edge of the screen, you’ll see grouping tools that you can use to show and hide data.
If the item was selected Create links to source data (Create Links to Source Data), then the resulting data refers to the containing source cells. By clicking on a cell with data (not on a cell with an amount), you will see a link to the sheet and the cell containing this data.
If you didn’t check the option Create links to source data (Create links to source data), then the resulting consolidation is just a generalization of the data without any details, without grouping, and containing only the results of the summation.
Helpful advice
Since this data contains links, you can use the tool Trace Precedents (Influencing Cells) to jump to the original cell containing the data. To do this, click on the cell containing the data you are interested in. Open a tab Formulas (Formulas) and find the button Trace Precedents (Influencing cells). Since the original cell is on a different sheet, hover over the black arrow that appears so that the pointer changes to an empty white arrow. Double click to open the dialog Go To (Jump) – a link to the cell will be indicated in this window. Click on the link and then click OKto jump to the desired location.
Formatting data
You can format the summarized data just like you would in a regular Excel file. You will find that the second column contains the name of the workbook. You can optionally hide this column by right-clicking and clicking Hide (Hide) in the context menu. This will hide the column, but the data will remain, i.e. you can use them in the future.
Miscellaneous workbooks
One of the main advantages of the tool Consolidation is that the data can be located in different workbooks. If you have multiple workbooks that contain similarly organized data and span different time periods, you can consolidate them using this tool.
To do this, you will need to follow the same steps as we just did: select an empty sheet or add a new one by clicking Insert Sheet (Insert sheet) tab Insertion (Insert). Push command Consolidate (Consolidation). This time, instead of selecting a sheet in the current workbook, click the button CATEGORIES (Browse) to open another workbook.
Now select the links that will be used for this workbook. Repeat the steps for each workbook that contains the data you are interested in.
It became easier for me to switch between open workbooks when I added a button Switch Windows (Move to another window) on the Quick Access Toolbar.
If you enable the option Create Links to Source Data (Create Links to Source Data), then when the rollup is done, any changes in the source sheets and workbooks will be reflected in the consolidated data. The second column in the summarized data will still display the workbook title, and the command Trace Precedents (Influencing Cells) will quickly take you to linked cells if the corresponding workbook is open, but will fail if it is closed.
Updating the consolidation
If the data on the sheets changes, for example, you add another office after the consolidation, then first of all, add this information to the desired sheet and save the workbook. Then open the sheet containing the consolidated information, select and delete all rows with the summarized data.
On the Advanced tab Data (data) click Consolidate (Consolidation) – You will see that the previously mentioned links are preserved. Make changes by adding or removing ranges or resizing them and click OKto recreate the consolidation.
Caution!
If you enable the option Create Links to Source Data (Create links to source data), then you will not be able to update the consolidated data by clicking on the tab Data (data) command Consolidate (Consolidation) without first deleting the old results. The reason is that in this case, instead of updating, an attempt will be made to insert one consolidation into another, which will lead to complete nonsense as a result. You can update the ranges by manually changing the formulas, but it’s more efficient to recreate the consolidation.
If you have not enabled the Create Links to Source Data (Create links to source data), then your rolled up data is just a generalization, without any details. In such a case, to update the consolidation, click on the top cell containing the results, then click on the tab Data (data) command Consolidate (Consolidate), make any necessary changes and click OK.
The advantage of the tool Consolidation in that it allows you to leave the data where it was originally located (i.e. on different sheets and even in different books), but still combine them for analytical work. Yes, consolidation will not allow you to change the order of data, as you can do in a pivot table. But in the case when such functionality is not required, and you need a simple generalization of information, moreover, presented in the same form as the original data, then consolidation is a simple and quick solution.