How to Create an Excel Agreement Status List with Pivot Table Slicers
Question details
Organize and filter employee agreement types and signing statuses for a large group of people using an Excel PivotTable and interactive slicers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking and managing HR or administrative records for over 100 employees to monitor who has signed specific agreements.
- Observed behavior
- A dynamic setup is required to easily view and interactively filter large datasets by employee name, agreement type, and completion status.
Ensure your raw data is organized into clear columns with descriptive headings (e.g., Employee Name, Signed Status, Agreement Type) and remove any completely blank rows or columns.
Use a PivotTable with Tabular Layout and Slicers
This method transforms your flat data into a structured report and uses interactive buttons (slicers) for quick, visual filtering.
By setting the PivotTable to a tabular layout, you ensure the data reads like a standard list while maintaining the grouping and summary benefits of a PivotTable.
Select your entire data range, press Ctrl+T to convert it into an official Excel Table, and ensure your columns are named 'Employee Name', 'Signed', and 'Agreement Type'.
Navigate to the 'Insert' tab on the ribbon, click 'PivotTable', and choose to place the new PivotTable on a new worksheet.
In the PivotTable Fields pane on the right, drag all three fields (Employee Name, Signed, Agreement Type) down into the 'Rows' area.
Click anywhere inside the PivotTable, navigate to the 'Design' tab under PivotTable Tools, click 'Report Layout', and select 'Show in Tabular Form'.
With the PivotTable still selected, go to the 'PivotTable Analyze' tab, click 'Insert Slicer', check the boxes for the fields you wish to filter (e.g., Agreement Type, Signed), and click OK.
Add Slicers Directly to an Excel Data Table
If you do not need the data summarization features of a PivotTable, you can attach Slicers directly to your formatted Excel Table.
Effortlessly Manage Employee Records with WPS Spreadsheet
WPS Spreadsheet provides robust PivotTable features and interactive Slicers that work exactly like Microsoft Excel, allowing you to build and share professional HR dashboards for free.
- 1. Open Your Data File: Launch WPS Spreadsheet and open your existing HR or agreement data file.
- 2. Insert PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
- 3. Organize Fields: Drag your Employee, Agreement, and Status fields into the Rows area in the PivotTable settings pane.
- 4. Add Slicers: Go to the 'Analyze' tab on the ribbon and click 'Insert Slicer' to add interactive filter buttons to your sheet.
Frequently Asked Questions
Why is the 'Insert Slicer' option grayed out in my Excel file?
The 'Insert Slicer' option may be disabled if your data is not formatted as a Table or a PivotTable, or if the workbook is saved in an older '.xls' compatibility format. Save the file as '.xlsx' and format your data as a Table to enable slicers.
Can I connect one slicer to multiple PivotTables at once?
Yes. Right-click the slicer, select 'Report Connections' (or 'PivotTable Connections' in older versions), and check the boxes next to all the PivotTables you want the slicer to control. Note that the PivotTables must share the same data source.
How do I clear the filters applied by a slicer?
You can clear a slicer's active filter by clicking the 'Clear Filter' icon (a funnel shape with a red 'X') located in the top-right corner of the slicer window, or simply by pressing Alt+C while the slicer is selected.
What exactly does 'Show in Tabular Form' do to a PivotTable layout?
'Show in Tabular Form' changes the layout so that each row field appears in its own separate column with its respective column header. This makes the PivotTable resemble a traditional flat data table while still maintaining its grouping and subtotaling capabilities.
