How to Create a Dynamic Excel Dashboard Table without a PivotTable
Question details
The user wants to create a dynamic, interactive dashboard view for borrowed-item details based on date and event criteria without using a traditional PivotTable.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an interactive dashboard to track and filter borrowed items by date and event.
- Observed behavior
- A need to display cleaned data dynamically using formulas and tables rather than relying on PivotTables, ensuring a cleaner layout controlled by dropdowns or slicers.
Ensure your raw data is organized in a proper tabular format without blank rows, and verify that your spreadsheet software supports dynamic array functions like FILTER.
Build a Dynamic Dashboard using an Excel Table and FILTER Function
Using dynamic array formulas like FILTER combined with an Excel Table is the most efficient way to build a real-time, customizable dashboard without needing to refresh a PivotTable.
The FILTER function returns an array of values that meet one or more criteria. By linking this function to dropdown cells, your dashboard table will automatically update when the user changes the selection.
Select your raw data range, navigate to the Insert tab, and click Table. In the Table Design tab, give it a recognizable name such as 'BorrowData'.
On your dashboard sheet, select the cells where users will input criteria (e.g., FromDate, ToDate, SelectedEvent). Go to Data > Data Validation and set up lists for these criteria to act as interactive dropdowns.
In the cell where you want your dashboard table to start, enter the formula: =FILTER(BorrowData, (BorrowData[Event Start Date]>=FromDate) * (BorrowData[Event End Date]<=ToDate), "No records found"). Make sure to adjust the column names to match your table.
Format the spill range of the formula to match your dashboard aesthetics, ensuring date columns are formatted as Short Dates and currency or numbers are styled appropriately.
Control Source Table Views using Slicers
If dynamic arrays are not supported or you prefer a click-based visual interface, inserting slicers directly into your Excel Table provides a quick, interactive filtering mechanism.
Create Dynamic Dashboards effortlessly with WPS Office
WPS Spreadsheet fully supports dynamic array functions like FILTER, along with data validation and slicers, making it the perfect tool to build interactive dashboards without complex PivotTables.
- 1. Format Raw Data: Open WPS Spreadsheet, select your data, and press Ctrl+T to format it as a Table.
- 2. Create Input Cells: Use the Data Validation feature under the Data tab to create dropdowns for your dashboard criteria.
- 3. Use FILTER Function: Apply the FILTER function on your dashboard sheet to dynamically display the records based on the dropdown selections.
Frequently Asked Questions
Why use the FILTER function instead of a PivotTable for a dashboard?
The FILTER function automatically updates as source data or criteria change, whereas PivotTables require a manual refresh. It also allows for a highly customized layout that integrates seamlessly into a dashboard design without the rigid structure of a PivotTable.
What if the FILTER function returns a #CALC! error?
A #CALC! error usually means the FILTER function found no matching records and the [if_empty] argument was not provided. To fix this, add a fallback text string like "No records found" as the third argument in your formula.
Can I filter by multiple criteria using the FILTER function?
Yes, you can multiply conditions together for an AND logic, such as (Table[Column1]=Criteria1)*(Table[Column2]=Criteria2). For OR logic, use the plus sign (+) between criteria instead of the multiplication asterisk.
Is the FILTER function available in all versions of spreadsheet software?
No, the FILTER function is a modern dynamic array function. It is available in Microsoft 365, Excel 2021, and current versions of WPS Office. Older software versions will need to use complex INDEX/MATCH arrays or traditional PivotTables.
