Power Query Problems

How to Retrieve Monthly Actual Sales by Salesperson in Excel

Huda Qurayshi Sep 28, 2026 869 views

Question details

The user needs a way to extract and report monthly 'Actual' sales values for specific salespeople from a complex dataset containing both forecast (CW) and actual columns for each month.

How to Retrieve Monthly Actual Sales by Salesperson in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an accurate monthly sales performance report to isolate actual figures from forecasted data for individual sales representatives.
Observed behavior: The source workbook has a wide structure where each month contains multiple columns (such as various CW forecasts and Actuals), making standard formulas difficult to use for dynamic salesperson filtering.

Before you start

Ensure your source data is formatted as an official Excel Table or a Named Range, and that your column headers clearly distinguish between forecast and actual data points.

Solution 1Recommended

Use Power Query to Normalize Data and Create a Pivot Table

Transform the complex multi-column structure into a flat, normalized dataset using Power Query, then analyze it effortlessly with a Pivot Table and slicers.

Because the initial workbook structure includes multiple columns per month for forecasts and actuals, it requires transformation. Normalizing the data into a tabular format allows Excel's Pivot Table feature to correctly aggregate and filter the information.

Load data into Power Query

Select any cell within your data range, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Unpivot columns and filter for actuals

Select the Salesperson column, right-click its header, and choose 'Unpivot Other Columns'. Filter the new Attribute column to keep only the 'Actual' values, effectively removing the forecast (CW) columns from this specific report.

Load transformed data to Excel

Add a proper Date column if necessary for chronological sorting. Then, click 'Close & Load To...' from the Home tab and select 'PivotTable Report'.

Configure the Pivot Table with Slicers

In the PivotTable Fields pane, drag 'Salesperson' and 'Month' to the Filters area (or insert them as Slicers for better visual filtering), and drag your 'Actual' values to the Values area.

Use Power Query to Normalize Data and Create a Pivot Table

Automated Refreshing: Once configured, you can simply append new rows to your original source data, navigate to Data > Refresh All, and your Pivot Table will automatically update with the latest sales figures.

Solution 2

Load Transformed Data to the Data Model for Performance

If you are working with a massive dataset, loading the Power Query output directly into the Excel Data Model reduces file size and speeds up report processing.

Analyze Complex Sales Data Easily with WPS Spreadsheet

WPS Office Spreadsheet provides robust data transformation tools and an intuitive Pivot Table interface, allowing you to easily consolidate multi-column data and filter actual sales by salesperson without complicated configurations.

1. Open your sales workbook: Launch WPS Spreadsheet and open your existing .xlsx sales data file containing the forecast and actual columns.
2. Insert a Pivot Table: Highlight your data range, navigate to the 'Insert' tab, and click 'PivotTable'. Choose to place it on a new worksheet.
3. Build your report: In the PivotTable fields pane, drag 'Salesperson' to the Filters area, 'Month' to Rows, and the 'Actual' column to Values.
4. Add Slicers for interactivity: Go to the 'Options' or 'Analyze' tab while clicking on the Pivot Table, select 'Insert Slicer', and check 'Salesperson' to quickly toggle between different representatives' actual sales.

Free, lightweight, and fast-performing office suiteFully compatible with Microsoft Excel (.xlsx) formats and legacy data structuresIntuitive Pivot Table and Slicer interface for rapid data analysisBuilt-in robust data consolidation and filtering tools to handle complex reports

Frequently Asked Questions

Why is it necessary to unpivot columns for monthly sales reports?

Unpivoting columns converts a wide dataset (where each month's actuals and forecasts have their own columns) into a flat, tabular format. This normalization is essential for tools like Pivot Tables to correctly filter, group, and aggregate data by Salesperson and Month.

How do I update my actual sales report when new data is added?

Once your Power Query connection and Pivot Table are established, simply add the new sales data to your original source table. Then, navigate to the Data tab on the ribbon and click 'Refresh All' to instantly update your reports.

What is the benefit of adding the sales data to the Data Model?

Loading transformed Power Query data into the Data Model compresses the information. This significantly reduces the overall Excel file size and improves calculation performance, which is especially noticeable when working with large historical sales datasets.

Can I filter between Actual and Forecast values simultaneously in the same Pivot Table?

Yes. If you choose not to filter out the forecast (CW) values during the Power Query transformation step, you can include the 'Attribute' or 'Type' field as a Slicer or Column in your Pivot Table to dynamically compare actuals versus forecasts.

Excel & Data Problems