Create an Excel Dashboard with Pivot Tables, Slicers, and Drop-Downs
Question details
The user needs to create an interactive dashboard showing daily visitors by site and month, with filters for dates and a selected location.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source data contains visitor records with dates and site locations, and the user wants a dashboard that can be filtered interactively.
- Observed behavior
- The dashboard needs grouped date views, PivotTable filters, slicers, and a separate site-selection drop-down.
Make sure the visitor date column contains real date values rather than text, and that every record includes a site or location. Convert the source range to an Excel Table so new visitor records can be included when the dashboard is refreshed.
Build the dashboard with grouped PivotTable fields and slicers
Use this method when you want interactive daily, monthly, yearly, and site-based filtering in one dashboard.
Grouping the date field creates separate day, month, and year levels that can be used in the PivotTable and connected slicers. A Table-based source also makes it easier to extend the dashboard as new records are added.
Select the visitor data range, choose Insert > Table, confirm that My table has headers is enabled, and select OK. Give the Table a clear name from the Table Design tab.
Click inside the Table, choose Insert > PivotTable, select a new worksheet or an existing dashboard sheet, and select OK.
In the PivotTable Fields pane, place the date field in Rows or Columns, place the site field in Rows or Filters, and place the visitor or record field in Values. Set the Values calculation to Count when each row represents one visitor.
Right-click any date inside the PivotTable, choose Group, select Days, Months, and Years, and select OK. Adjust the number of days if you need a daily interval other than one day.
Select the PivotTable, open PivotTable Analyze or Insert, choose Insert Slicer, select the grouped date fields and site field, and select OK. Then choose Insert > PivotChart to display the filtered visitor totals on the dashboard.
Add a separate site selector with Data Validation
Use this option when the dashboard needs a compact location selector in addition to or instead of a site slicer.
Create an interactive visitor dashboard in WPS Spreadsheet
WPS Spreadsheet can build the same style of visitor dashboard with a Table-based data source, grouped PivotTables, slicers, charts, and Data Validation drop-downs. Its familiar spreadsheet interface makes it easy to migrate an Excel dashboard and continue working with compatible files.
- 1. Prepare the visitor data: Open WPS Spreadsheet, select the visitor range, and choose Insert > Table. Confirm that the first row contains headers and verify that the date column uses valid date values.
- 2. Insert and configure a PivotTable: Click in the Table, choose Insert > PivotTable, select the destination sheet, and place the date field in Rows, the site field in Rows or Filters, and the visitor field in Values.
- 3. Group dates and add filters: Right-click a date in the PivotTable and choose Group to create day, month, and year groupings. Use the PivotTable or Insert menus to add available slicers for the grouped date fields and site.
- 4. Create the dashboard view: Choose Insert > Chart or PivotChart to visualize visitor totals, then arrange the chart, PivotTable, and slicers on a dashboard sheet.
- 5. Add a site drop-down and refresh: Select a dashboard cell, open Data > Data Validation, choose a list source containing site names, and use the selected value in formulas or filter controls. Refresh the PivotTable after adding records to the source Table.
Frequently Asked Questions
How do I group Excel PivotTable dates by day, month, and year?
Right-click any date in the PivotTable, choose Group, select Days, Months, and Years, and then select OK. The grouped fields can be used in rows, columns, filters, or slicers.
Why can’t I group the dates in my PivotTable?
Date grouping may fail when the source column contains text, blank cells, or errors. Convert text entries to real dates, remove blank or invalid values, refresh the PivotTable, and try the Group command again.
How can I filter a dashboard by site location?
Add the site field to the PivotTable Filters area or insert a slicer for the site field. For a separate selector cell, use Data > Data Validation > List and connect the selected site to formulas or a dashboard filter.
How do I make the dashboard include new visitor records?
Convert the source range to an Excel Table before creating the PivotTable. Add new records directly below the Table, then right-click the PivotTable and choose Refresh.
Can I show daily visitors by month and site in one chart?
Yes. Group the date field by day, month, and year, place site in the PivotTable rows or filters, and insert a PivotChart. Use slicers to switch between months, dates, and locations.
