Excel Challenge 57: Consolidate Data from Multiple Files

Have you ever used the Consolidate Data command in Excel? Let's test your skills!

Everything you need to participate in the challenge can be found in the attached workbook. To take part:

• First, watch the challenge video and read the instructions below.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the zipped files you will use to complete the challenge tasks.
  • Right-click the zipped folder, press "Extract all", and follow the instructions.
• Put yourself to the test!

The scenario

Aylin has just started her role as a Sales Analyst at a fast-growing retail company. Every month, the sales team collects transaction data from multiple branches and saves them as Excel files in one shared folder. Each file contains the sales amounts for a single month.

On a busy Friday afternoon, her manager drops by her desk with an urgent request:

Aylin, I need a consolidated sales report covering all months of data in our folder. The report must update automatically whenever a new month’s file is added. This will be presented to the executive board on Monday morning, so accuracy and automation are critical. Can you prepare it?

The dataset

The folder contains eight Excel files, representing sales data from January to August. Each file includes:

• Date
• Product
• Brand
• Price, $
• Quantity
• Sales Amount, $

Every new month, a fresh file is added to the folder. The challenge is to import and consolidate them into one dynamic dataset that always reflects the latest available data.

Questions to be answered

Aylin’s manager wants the following insights:

1. Total Sales Amount – Show monthly totals as well as the overall figure.
2. Monthly Performance – Compare how sales change from month to month.
3. Top Products – Identify the three best-selling products each month and across the entire period.

The importance of consolidating data

Aylin realizes this is not just about preparing numbers — it’s about giving the business a clear, up-to-date view of overall sales performance. Her consolidated report will guide executives in evaluating growth, planning promotions, and identifying which products drive the most revenue.

When her manager reviews the final report, she sees:

• Accurate totals of monthly and overall sales
• A clear month-by-month performance comparison
• A ranking of the top products -- both monthly and across all sales

“Perfect, Aylin,” she says. “This is exactly what the board needs for Monday!"

Now it’s your turn!

Just like Aylin, can you import the folder of monthly sales files, consolidate them, and build a professional sales report in Excel?

Create:

• A summary of total sales amounts by month and overall
• A clear view of monthly performance
• A table or chart showing the top products by month and across all sales

Share your results with our learner community on Slack!

My solution

We hope you enjoyed taking part in this challenge! Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills. 

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you manage data and learn other practical, real-world Excel skills.