Create a Summary Report in Excel with Data Consolidation
In this article, we’ll teach you how to create a summary report in Excel with data consolidation to make your information more readable and easier to digest. You’ll also learn how to export your report into PDF format, so you can share it with colleagues and clients without any formatting issues. Let’s get started!
How to consolidate the data
Here are 5 steps for how to consolidate data:
First, copy and paste the raw data into an Excel file. Once you’ve made your copies, organize them by type or by company. This will make it easier to find the information that you need when you’re done consolidating data. If you want to see the totals of all columns at once, click on Data in the top left corner of your screen. Then select Data Tools from the drop-down menu.
Check if there are missing records
Now that we have successfully queried for all the transactions made by each of our four store locations, let’s merge these tables together to create a summary report. Before we do this, let’s just double-check and make sure that our data is complete. One way to do this is by selecting the All Transactions column on the tab labeled Store 1, sorting the table on descending order and scrolling through the list until you see an instance where there are two blank records next to each other.
The reporting section
When you need to provide management or clients with an overview of the effectiveness of your marketing efforts, an example report would include charts for total sales and average sales per month. These charts can be combined into one summary report that can be accessed by clicking on the Get Report button from the main reporting page. This report is created by consolidating sales data from multiple reports and then summarizing it into one chart.
Step-by-step guide
- When opening an Excel file, start by clicking the Data tab and then selecting data consolidation.
- From here, click on the box titled Data Source. This will bring up a window to show all of your data tables that are currently loaded into your current workbook. Click the Add button in this area to select new data to include in your report.
