How to Update an Excel Dashboard from Monthly Tabs Using Pivot Tables
Question details
The user needs to update an Excel dashboard to display employee data for the current month while excluding previous months, but is finding it difficult to manage automatic summaries across separate monthly tabs.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Maintaining a dynamic employee data dashboard that needs to pull and filter current monthly data from a workbook.
- Observed behavior
- Keeping data separated into individual monthly tabs prevents the dashboard from updating automatically and complicates data summarization.
Before merging your data, ensure that all your monthly tabs share the exact same column headers and structure to prevent mismatched data during consolidation.
Consolidate Data and Use a Pivot Table with Slicers
Combining all monthly tabs into a single structured table allows you to use a Pivot Table and slicers to dynamically filter and update your dashboard without complex formulas.
Maintaining separate tabs for each month restricts Excel's ability to seamlessly aggregate data. By consolidating your records into one master table and using Pivot Tables, you can easily control which month's data is displayed on the dashboard.
Insert a new worksheet and create a single master table with standardized columns such as Employee Name, Date, Month, Year, Type, and Time.
Copy and paste the data from your individual monthly tabs into this new master table, ensuring all rows align correctly under the respective headers.
Highlight your master table, navigate to the Insert tab on the ribbon, and click PivotTable. Place this PivotTable on your dashboard sheet to serve as the data engine.
Click anywhere inside your new Pivot Table. Go to the PivotTable Analyze tab, select Insert Slicer, and check the boxes for 'Month' and 'Year'. You can now click these slicers to instantly filter your dashboard.
Whenever you append new monthly data to the bottom of your master table, right-click the Pivot Table on your dashboard and select Refresh to update all numbers.
Build Dynamic Dashboards Easily in WPS Spreadsheet
WPS Spreadsheet provides robust data consolidation tools, intuitive Pivot Tables, and interactive slicers, making it incredibly easy to manage monthly data and update your dashboards seamlessly.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the monthly tabs.
- 2. Consolidate the data: Copy your monthly records into a single, unified worksheet to streamline your data management.
- 3. Create your Pivot Table: Go to the Insert tab, click PivotTable, and drag your desired fields to structure your dashboard summary.
- 4. Insert Slicers: Under the Options tab in PivotTable Tools, click Insert Slicer to create clickable buttons for filtering by month and year.
Frequently Asked Questions
How can I link a single slicer to multiple charts on my dashboard?
Right-click the slicer on your dashboard and select 'Report Connections' (or 'PivotTable Connections'). Check the boxes next to all the Pivot Tables that power your charts. Now, clicking one month on the slicer will update the entire dashboard simultaneously.
Is it better to keep raw data in one sheet or multiple monthly tabs?
It is highly recommended to keep raw data in a single, well-structured table. While monthly tabs might seem visually organized for manual data entry, they make automated reporting, formula creation, and dashboard updates significantly more difficult and prone to errors.
Can I automate the consolidation of my monthly tabs?
Yes, if you use advanced features like Power Query, you can use the 'Get Data' function to combine multiple worksheets or an entire folder of monthly files into one master table automatically, which then feeds directly into your dashboard's Pivot Table.
Why isn't my Pivot Table updating when I add a new month's data?
If your Pivot Table source data is tied to a specific range (e.g., A1:F500) rather than a dynamic Excel Table, new rows added below row 500 will not be included. Convert your raw data range to a Table (Insert > Table), ensure the Pivot Table source is set to the Table name, and click Refresh.
