Microsoft Excel has enabled users to deal with large amounts of data input with ease on a daily basis. When it comes to essential practices like accounting, the software program further assists with the process of comparing and analysing data with essential functions like Microsoft Excel data consolidation. Here are 5 data consolidation tips to consider:
- Basics
Data consolidation can be performed in the single workbook file using the ‘Data’ tab. Locate the data tools group and choose the ‘Consolidate’ option. Once selected, the consolidate dialog box should appear. Microsoft Excel allows the user to select the type of function, input reference, use labels and more! The user can select cells to reference in the workbook file or between multiple workbook files and the rest will be performed by Microsoft Excel. Keep in mind to open up a blank workbook for data consolidation so as not to affect the data of the original workbook files on accident.
- Identical workbook files
Another alternative way to use Microsoft Excel data consolidation would be to create 3D references or use external references when consolidating. 3D references would be recommended for single workbook files where identical data are present, while external references work better with multiple workbook files. For multiple workbook files, recreate the steps mentioned in the basic portion of this article and select the ‘Create links to source data’ option. Excel will create a link to the source data and update it accordingly if there are changes in the consolidated data.
- Formulas
It is possible to make use of formulas for Excel data consolidation as well. Begin by identifying the rows and column labels that are to be consolidated in the Master worksheet, then select to designate the cell for consolidated data. In order to consolidate the data from all cells, insert the formula in the first cell: =SUM(Department __ : Department ___!__ ).
- PivotTable
Excel data consolidation can also be achieved via the creation of a PivotTable report, which enables the user to consolidate data from different ranges and reorganise categories. Locate PivotTable and PivotChart by selecting Alt + D + P on the keyboard and select multiple consolidation ranges when prompted. Once done, choose the command ‘I will create page fields’ and proceed. Select the relevant cell ranges and add them into the dialog box and key in page field options (default 0) before choosing another location to generate the report in and selecting Finish.
- Identifying categories
Repeat the steps included in the basic section. Make sure to select the upper left cell of your Master worksheet to place your consolidated data. Select the data tab and click consolidate before choosing to use the summary function. Once done, identify your category ranges and then click add and proceed with the rest of the steps.
These are 5 Microsoft Excel data consolidation tips to practice and try out. While different methods all lead to the same outcome, it could take some time with discovering which method fits different users. The important factor is that there is minimal struggle when the user is attempting to consolidate data.
