Trying to consolidate data in Excel for the first time can frustrate many FP&A professionals. Mistakes can be costly when you’re handling a lot of your company’s financial data, and you’d have to repeat the process a couple of times to get a hang of it. But there are no hard-and-fast rules for the consolidating process, contrary to what you may assume.
In this guide, we’ll be demystifying the Excel process for finance professionals. Let’s get started.
What is Data Consolidation in Excel?
Data Consolidation is an Excel feature that enables you to collect data from different worksheets and compile it in one, centralized worksheet. In simple terms, it creates for you a ‘master’ table where you can access data summarized from other sheets. The result? Your information becomes much easier to read and understand in its new aggregate form.
Excel makes this possible through the ‘Consolidate’ tool. A handy function for FP&A analysts, it has been around for over two decades. Its main function is simple: combining information from several worksheets or workbooks and displaying it in a neat summary. Located under the “Data”’ tab, it helps keep things organized for future analysis.
But it only works under one condition. The data to consolidate needs to have a single column and a single row of labels and headings, respectively. So if your table has multiple labels, the tool may not work for you. While this can seem like a major drawback, most of the sets of data you’ll come across will follow this format.
The function also gives you an option to link to your source worksheets (the sheets you’re copying data from). Enabling this option allows the summary table to refresh once you change the information in the source worksheets.
Why Consolidate Data in Excel?
The consolidation function in Excel can help just about anyone organizes their data. Financial analysts often spend large parts of their day collecting information from multiple sources. One of their duties may be coming up with an organization’s budget by combining accounts from several different departments. Excel can help them develop an error-free budget, allowing them to avoid the hassle of calculating manually.
The consolidation function’s true potential, however, is maximized when you have to combine large data sets. Let’s say you have to get comprehensive summaries for annual budgets for your business, for a time-period spanning decades.
You can copy and paste this information, and you’d probably arrive at accurate results. The downside, though, is that you’d have to put in a lot of effort and the process would consume too much time. Also, if information changes in any of your source worksheets, you have to adjust the summaries manually.
The copy-and-paste approach works well only when you have a few cells to fill. As data becomes more extensive, workbooks pile, and cells multiply, the approach turns into a chore. Excel’s combining functionality can take this load off your back, allowing you to manage your tasks more efficiently.
How to Consolidate Data in Excel
Excel allows you to centralize data in several ways. As we mentioned earlier, there’s no one-size-fits approach to the process. There are several methods available that you can use depending on the values you have. That said, here are the three common ones:
1. By Position
You only use this technique when your worksheets follow a similar layout, particularly if they’re from a single template. The information in these sheets should have a similar order and arrangement, with details being in their exact location. With this method, Excel only moves the values from the source worksheets to the target summary and forgoes the labels.
2. By Category
This technique comes into play when your worksheets share similar headings and labels, but the cell coordinates vary. The worksheets can also follow a different order. In such a case, Excel copies the headings and labels to ensure the source data matches the information in the master worksheet. Unlike the previous approach, you have to include the labels of the source data when referencing.
Spelling or capitalization mistakes in the labels can prevent you from getting accurate results. Before completing this method, you’ll have to specify the data to combine, by choosing between ‘Top Right’ and “Left Column’’ labels, as we’ll see later.
3. By Formula
To consolidate using this method, you need to use a 3-D reference. In Excel terms, a reference refers to the range from worksheets that you want to include in your consolidated summary. And 3-Ds are the types whose range covers multiple worksheets — different from normal ones whose range covers one worksheet at a time.
This technique is most suitable when you’re consolidating several sheets in one Excel file. It includes a formula that references parts of a different worksheet that you’re combining, making it ideal for working with sheets with different layouts. And if your sheets have an identical layout, then you can include their names in the consolidation formula.
Consolidate Data From Multiple Worksheets in a Single Workbook
Let’s say you have data for the earnings of employees Joy, David, Hannah, Alex, and Andrew — 5 workers whose remuneration changes from month to month. Your data shows the commissions they each received in different months from February to May.
As a financial analyst, your goal may be to come up with a summarized version of your data. The version should give a clear picture of what they earned in several years working for your company. Using Excel’s Consolidate functionality is the most ideal way of achieving your desired result. You first need to compile the data in different worksheets and follow these steps:
Consolidation Steps:
Here are the worksheets we’ve come up with for three years, and we’ve named them: Year 2016, Year 2017, and Year 2018, respectively.
As you can see, we have missing values in B5, B4, and E6 in all three worksheets. This means that David never received any commissions in 2016 March; Hannah in 2017 April; and Joy in 2018 May. Joy’s row in Year 2017 is unaccounted for and Alex’s too in Year 2018. There’s also a missing column in 2018, meaning no employee received commissions for April.
The sheets are barely identical. They each have different sizes, and their rows and columns vary from worksheet to worksheet. Also, they have multiple empty cells. Despite this, however, Excel can still consolidate this information for you. It can create a summary by only following the labels you’ve created. This feature makes consolidating by category much easier than using formulas.
But before we dive into the nuts and bolts of creating consolidated summaries, there are few things you need to note:
- The worksheet tables need to follow a similar layout, where the headings and labels are placed in the same positions.
- You can rearrange the columns and rows, but you need to make sure they have identical names. This means that all labels should have the same spelling and wording.
- Delete any empty rows or columns
Step 1:
You need to find a separate sheet where the summary table will be located. You can add a new worksheet or rename an existing one. We’ve named ours ‘‘Summary’’:
Step 2:
Make sure your sheet is empty. Choose the region you want your master sheet to feature. Click on the upper-left, empty cell where your summary table will start to cover.
Step 3:
On Excel’s ribbon, go to ‘Data’ then navigate to ‘Consolidate’:
Step 4:
Click the drop-down bar under ‘Function’. You’ll get various choices such as Sum, Min, Count, Max, among others. We’ve selected ‘Sum’ because we need to add the values of all three worksheets together.
If we wanted an average amount of commissions received for the employees over the three years, we’d choose ‘Average’.
Step 5:
Choose a source for Excel to reference. You have to select the ‘Collapse Dialog’ button. Since our worksheets are on the same file, we navigate to ‘Year 2016’, our first worksheet. We’ll then click and drag, so that we can highlight the information we’re consolidating. In this case, we’ll select A3 to E8.
At the reference section, a display will pop up showing your worksheet’s name with an exclamation mark beside it, while also detailing your chosen row and column range. For instance, we get ‘’Year 2016 !$A$3:$E$5”.
If you’re looking to combine data from different files, navigate to ‘Browse’, and select your file. Then click ‘Open’.
Step 6:
Find the ‘Add’ button that’s right next to ‘All References’ then click it. By doing so, you’ll include the Year 2016 range in your combined references list.
Step 7:
Repeat steps 5, 6, and 7, for the remaining worksheets.
Ensure you add each table as it is, without leaving any labels or headings. You’ll get similar recordings in the ‘All References’ section.
Step 8:
Check all the boxes under ‘Use Labels In’.
When we check the ‘Top Row’, we enable the consolidation tool to include our labels in the highest rows: February, March, April, and May Earnings. Likewise, when we check ‘Left Column’, we allow the functionality to include our furthest-left labels: Joy, Hannah, Andrew, Alex, and David.
Selecting the “Create Links’ option enables the platform to update the summary table whenever we change the source information. If you leave it unchecked, you’ll have to update the master worksheet manually, whenever you see any changes.
Step 9:
Press ‘Ok’ to authorize Excel to consolidate your information in the target worksheet.
Clicking the ‘+’ buttons next to the cell numbers allows you to view the original information from the new master worksheet.
After completing these steps, you’ll get a final product that looks similar to this:
You have to note a few things, though:
- You can only create links when your source information and summary table are in different worksheets. When they’re on the same worksheet, the consolidated information can’t be updated automatically.
- To change the range of your reference, you first have to delete the outline reference in the ‘List’ section, before creating one. After deleting it, return it to your sheet and drag it again to get a suitable range.
- If you leave ‘Top Row’ and ‘Left Column’ unchecked, then the platform combines all the cells positioned similarly such as: cells C5 (Year 2016) + C5 (Year 2017) + C5 (Year 2018). Avoid this by clicking on the boxes.
Datarails is the Excel Solution You Need
Working manually on most spreadsheet tasks can be overwhelming, tiring, and repetitive. Datarails offers technology that automates taxing Excel work, enabling you to save time and deliver error-free work. The best part? You don’t have to change your processes; you can keep doing what you’ve been doing, but now better.
The software encourages financial analysts to work independently with data and provide data-driven insights. Streamline the connection between your company’s finance and operations and encourage better organization decisions with Datarails FP&A Software.
