Power Query Problems

How to Create an Excel Dashboard for Employee Hours Using Power Query

Ayan Masood Oct 19, 2026 868 views

Question details

The user needs to consolidate employee working hours from multiple weekly Excel sheets and build an interactive reporting dashboard.

How to Create an Excel Dashboard for Employee Hours Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating fragmented weekly timesheet data into a single structured table to analyze hours logged by month, week, project, and activity.
Observed behavior: The raw data is currently scattered across separate weekly worksheets and must be appended together before an interactive dashboard can be created.

Before you start

Ensure all your weekly timesheet worksheets have identical column headers and formatting, as inconsistencies will cause errors when appending the data.

Solution 1Recommended

Use Power Query to Append Data and Build the Dashboard

Power Query automates the consolidation of multiple worksheets into a single master table, allowing you to seamlessly generate a dashboard using PivotTables and PivotCharts.

Instead of manually copying and pasting weekly timesheets, Power Query can dynamically link and stack all your worksheets. Once the data is unified and standardized, Excel's PivotTables can be used to slice and visualize the hours.

Import Data with Power Query

Open Excel, navigate to the 'Data' tab, click 'Get Data', select 'From File', and then 'From Workbook' (or 'From Folder' if your weekly sheets are saved as separate files) to import your timesheet data.

Append the Worksheets

In the Power Query Editor, use the 'Append Queries' feature from the Home tab. Select all your weekly worksheet queries to stack them vertically into one comprehensive master table.

Standardize Data Types and Load

Verify that all column headers align correctly. Change the data types to ensure accuracy (e.g., format 'Date' columns as Date, and 'Hours' columns as Decimal Numbers). Click 'Close & Load' to bring the combined table into Excel.

Insert PivotTables

Select a cell in your newly loaded master table, go to the 'Insert' tab, and click 'PivotTable'. Drag 'Employee', 'Project', or 'Activity' into the Rows or Columns fields, and 'Hours' into the Values field.

Add Slicers and Charts

Click inside your PivotTable, go to 'PivotTable Analyze', and choose 'Insert Slicer' to add clickable filters for Months, Weeks, and Projects. Finally, insert 'PivotCharts' to create visual representations of your data for the final dashboard layout.

Use Power Query to Append Data and Build the Dashboard

Easy Updates: By using Power Query, your dashboard is fully automated. When you add a new weekly worksheet, simply click 'Refresh All' on the Data tab to update your combined table and dashboard charts instantly.

Build Powerful Dashboards with WPS Spreadsheet

WPS Spreadsheet provides powerful data consolidation tools and robust PivotTable features, allowing you to easily summarize weekly timesheets and craft interactive dashboards without relying on complex external queries.

1. Combine Weekly Sheets: Use the 'Merge Worksheets' feature in WPS Spreadsheet's Data or Enterprise tools to automatically consolidate all weekly timesheet tabs into a single master sheet.
2. Create a PivotTable: Highlight your combined data range, navigate to the 'Insert' tab, and click 'PivotTable' to begin structuring your dashboard data.
3. Configure the Layout: In the PivotTable pane, drag 'Project' and 'Employee' to the Rows area, and 'Hours' to the Values area to calculate total logged time.
4. Add Interactive Filters: Select the PivotTable, go to the PivotTable Options, and choose 'Insert Slicer' to enable fast, clickable filtering by Month or Week.
5. Insert Visual Charts: Go to the Insert tab and add PivotCharts connected to your summarized data to finalize your visual employee hours dashboard.

Highly compatible with Microsoft Excel (.xlsx) formats and formulasBuilt-in 'Merge Worksheets' tool for rapid data consolidationAdvanced PivotTables and PivotCharts for instant dashboard creationFree to download and exceptionally lightweight on system resources

microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically update my Excel dashboard when new weekly timesheets are added?

Yes. If your dashboard is built using Power Query to pull from a specific folder or workbook, you simply need to place the new weekly sheet in that source location, open your dashboard, and click 'Refresh All' on the Data tab.

Why are my dashboard PivotTables not summing the employee hours correctly?

This typically occurs if the data types in your combined table are incorrect. Ensure that the 'Hours' column was explicitly formatted as a Number or Decimal in the Power Query Editor before loading the data into your PivotTable.

How do I group my dashboard data by month and week?

In your PivotTable, right-click any date value in the Rows area and select 'Group'. You can then choose to group dates by Months. To group by weeks, select 'Days' and set the 'Number of days' to 7.

What should I do if my column headers differ across the weekly sheets?

Power Query requires identical column headers to append columns correctly. If headers differ (e.g., 'Emp ID' vs 'Employee ID'), you must either standardize them in the source files first, or add a 'Rename Columns' step for each sheet in Power Query before performing the Append step.

Excel & Data Problems