Consolidation Worksheet: Keep Track of Your Financial Situation

If you have a simple corporate structure like a single corporation or even a sole proprietorship, all you really need to keep track of your finances is a good accounting software package.

However, when you have a complicated corporate structure that includes a group of companies, parent companies, subsidiaries and more, things aren’t that simple. A consolidation worksheet can help you to get the financial data you need quickly, without needing to open and run multiple reports.

‍

How Do I Consolidate Data from Multiple Worksheets in Excel?

If you are creating multiple worksheet reports for all of the different companies in your group, you probably still need to combine sheets in Excel into one so that you can see all the information you need on a single report.

Fortunately, Excel has some quick and easy tools to make this possible.

One option is to name the data you want to copy to your consolidation worksheet, which means that whenever that sheet is updated, your consolidation worksheet will be updated too.

Or, if you want to consolidate data by position in the worksheet or by category, you can use the Consolidate options on the Data menu and select the information you want to transfer to your consolidation worksheet. A word of caution, though – if your data is consolidated based on factors that could change, like the position of a cell, it’s a lot trickier to make changes to the spreadsheet!

There are several consolidation worksheet examples on the internet, so if you are confused about how to set one up, downloading a free sample template is a great way to start.

‍

What Is a Consolidation Worksheet? 

A consolidation worksheet is a financial workbook that contains key data about various companies in a group of companies, including profit, losses, and so on. Each company’s information is usually entered on its own spreadsheet.

All of this data and various calculations are transferred onto the consolidation worksheet, so that you can get an overall report of the financial situation of all of the organizations in the group.

‍

How to Do Consolidation Worksheets 

When you create consolidation worksheets, you need to know what data you need to include. Your accountant can usually advise you which key data is important to each of your companies.

Next, you need to set up a company worksheet template that lists all the relevant data and that has all the formulas you need to calculate important amounts.

It’s helpful if all of the spreadsheets for all of the companies you are working with are in the same format and order so that it’s easier to use and even merge two Excel sheets if you need to.

‍

How to Consolidate Data in Excel from Multiple Worksheets Using VBA

VBA, or Visual Basic Applications, are a kind of advanced coding that you can use in Excel to perform complex functions. If you want to combine sheets in Excel into one consolidation worksheet, this is another way to do that.

You will need to have the Developer tab active on your Excel ribbon, and then you will need to open VBA and create a new module. The exact code you use to create a consolidation worksheet using VBA will depend on the location of your data, the location of the results on the consolidation sheet and more.

The following code is a simple script that will allow you to combine specific data from two sheets from the sheets and cells named below:

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

By modifying this code, you can merge and consolidate different data from different Excel sheets. However, VBA is a little tricky to use, and unless you are an advanced user, you might want to try options before you go this route.

This basic code will probably need to be edited extensively to suit your specific worksheets, and it’s likely to take some time to find the right code for your specific use case.

You could also hire a VBA programmer to create a consolidation worksheet for you, but that can be costly, and if anything changes at any point, you’d have to rehire them to make changes to the code.

There’s also always the chance that a Windows or Office update could make some of your code obsolete, so there’s always the chance that your VBA code will stop working suddenly and without warning. None of that is ideal if you’re not already a VBA wizard!

‍

Use LiveFlow to Create a Consolidation Worksheet

Excel usually feels like a safe, comfortable way to do things like create a consolidation worksheet; however, in this case, it is cumbersome and requires a lot of manual coding and data entry.

That usually means that there’s a lot more chance of human error, and the last thing you want when you’re creating consolidation journal entry information is to make mistakes!

‍

LiveFlow is a valuable platform built for QuickBooks, which is designed to make it easy to create an integrated, real time connection between your QuickBooks data and customized reports and dashboards in Google Sheets. This means that you don’t have to give anyone access to QuickBooks simply to review reports – you can use pre-built templates or bring live data to any customized report you already have.  This way, you only share what departments need to see. The live connection between QuickBooks and Google Sheets means that your reports will always be up to date – without any manual exports, data formatting or hands-on effort.

LiveFlow and the platform’s Google Sheet templates take all the manual labor out of running elimination entries for consolidation, transferring, and updating data and more.

This platform creates a live link between critical data in all your company accounts and maps them to your consolidation workbook. Without all the coding and manual labor, you can get real-time updates about all of your companies every time you open your consolidation worksheet.

Until an accounting package develops a consolidation worksheet report, this is the best and easiest way to stay up to date with everything that’s happening in your group.

If you’d like to find out more about LiveFlow and how it can help you to automate your consolidation worksheet, contact our team. We’re always happy to help with answers, templates or a live demo of our platform and the tools it offers.