Method 1. Using formulas
We have several tables of the same type on different sheets of the same book. For example, these are:
It is necessary to combine them all into one common table, summing up the matching values by quarters and names.
The easiest way to solve the problem “on the forehead” is to enter a formula of the form
=’2001′!B3+’2002′!B3+’2003′!B3
which will sum the contents of cells B2 from each of the specified sheets, and then copy it to the remaining cells down and to the right.
If there are a lot of sheets, then it will be easier to expand them all in a row and use a slightly different formula:
=SUM(‘2001:2003’!B3)
In fact, this is the summation of all B3 cells on sheets from 2001 to 2003, i.e. the number of sheets, in fact, can be anything. Also in the future it is possible to place additional sheets with data between the start and final sheets, which will also be automatically taken into account when summing up.
Method 2. If the tables are not the same or in different files
If the source tables are not absolutely identical, i.e. have a different number of rows, columns, or repeated data, or are in different files, then the summation using conventional formulas will have to be done for each cell individually, which is terribly time consuming. It is better to use a fundamentally different tool.
Consider the following example. We have three different files (Ivan.xlsx, Rita.xlsx и Fedor. Xlsx) with three tables:
It is clearly seen that the tables are not the same – they have different sizes and semantic filling. However, they can be compiled into a single report in less than a minute. The only condition for successful merging (consolidation) of tables in such a case is the coincidence of column and row headings. It is on the first row and left column of each table that Excel will look for matches and summarize our data.
To do this consolidation:
- Open source files first
- Create a new blank workbook (Ctrl + N)
- Set the active cell in it and select on the tab (in the menu) Data – Consolidation (Data — Consolidate). The corresponding window will open:
- Set cursor to line Link (Reference) and, switching to the Ivan.xlsx file, select the table with the data (together with the header). Then press the button Add (Add) in the consolidation window to add the selected range to the list of ranges to merge.
- Repeat the same steps for Rita and Fedor’s files. As a result, all three ranges should appear in the list:
Please note that in this case, Excel remembers, in fact, the position of the file on the disk, prescribing the full path for each of them (disk-folder-file-list-cell addresses). In order for the summation to take place taking into account the headings of columns and rows, you must enable both checkboxes Use as names(Use labels). Flag Create links to source data (Create links to source data) will allow in the future (when changing the data in the source files) to recalculate the consolidated report automatically.
After clicking on OK we see the result of our work:
Our files were summed up by matching names from the leftmost column and the top row of selected areas in each file. Moreover, if you expand the groups (with plus icons to the left of the table), you can see from which file what data was included in the report and links to the source files:
- Macro for automatic collection of data from different sheets into one table
- Macro for assembling sheets from several files