How to Build an Excel Dashboard from Multiple SharePoint Workbooks
Question details
The user needs to create an interactive dashboard that consolidates standardized test-result tables from various separate workbooks stored in SharePoint, using a month slicer or timeline to control multiple pass/fail charts simultaneously.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data from distinct SharePoint site workbooks to visualize pass/fail test results on a unified, interactive dashboard.
- Observed behavior
- The goal state involves combining separate data sources into a central model where dynamic PivotCharts and month slicers control the entire dashboard view seamlessly.
Ensure that all source workbooks in the SharePoint folder have identical table structures, column headers, and data types before starting. Verify that you have the necessary access permissions to read files from the target SharePoint site.
Consolidate Data Using Power Query and the Data Model
The most robust and scalable method for combining multiple SharePoint workbooks is using Power Query to merge the tables into a single Data Model, enabling seamless PivotCharts and shared slicers.
By utilizing Power Query's 'Get Data from SharePoint Folder' feature, you can automate the process of appending newly added site workbooks without manual copying.
Loading the consolidated result directly to the Data Model allows you to build complex DAX measures for pass/fail rates and link a single timeline or slicer to every chart on the dashboard.
In Excel, navigate to the Data tab, click 'Get Data', choose 'From File', and select 'From SharePoint Folder'. Enter your SharePoint site URL and click OK.
Click 'Combine & Transform Data'. Select the standardized test-result table format. In the Power Query Editor, clean the data and ensure all columns match.
Click the 'Close & Load' drop-down and choose 'Close & Load To...'. Select 'Only Create Connection' and check the box for 'Add this data to the Data Model'.
Insert a PivotChart using the Data Model as the source. Create DAX measures for your pass/fail rates, ensuring they are connected to a shared date table if needed.
Select one of your PivotCharts, go to the PivotChart Analyze tab, and insert a Slicer or Timeline for the month. Right-click the slicer, select 'Report Connections', and check all related charts to link them.
Build a Formula-Driven Dashboard Using MATCH and OFFSET
If you cannot use Power Query or prefer to keep workbooks strictly separate without a Data Model, you can use lookup formulas to pull data into a central sheet.
Build Dynamic Dashboards Easily with WPS Spreadsheet
WPS Office provides robust data consolidation, advanced Pivot Tables, and interactive slicers, allowing you to combine multiple data sets into professional, interactive dashboards seamlessly.
- 1. Prepare Source Data: Open WPS Spreadsheet and use the Data tab tools to import or consolidate your test results from localized SharePoint synced folders.
- 2. Insert a PivotTable: Select your combined dataset, navigate to the Insert tab, and click 'PivotTable' to begin summarizing your pass/fail metrics.
- 3. Create PivotCharts: From your PivotTable data, generate the necessary pass and fail charts to visualize the performance metrics visually.
- 4. Add a Slicer: Click inside your PivotTable or chart, go to the PivotTable Options tab, and select 'Insert Slicer'. Check the 'Month' field to add dashboard interactivity.
Frequently Asked Questions
Why isn't my slicer controlling all the charts on the dashboard?
For a single slicer to filter multiple charts simultaneously, all those PivotCharts must share the exact same Pivot Cache or Data Model. You must also right-click the slicer, select 'Report Connections', and check the boxes for all the PivotTables or charts you want it to control.
Do all SharePoint source workbooks need the exact same formatting?
Yes, when combining data via Power Query, it is highly recommended that all source tables have identical column headers, table structures, and data types. This prevents errors when appending the data into a single master table.
How do I update the dashboard when new files are added to SharePoint?
If you built the dashboard using Power Query's 'From SharePoint Folder' option, simply navigate to the Data tab and click 'Refresh All'. The query will automatically locate, transform, and append the newly added workbooks to your dashboard.
Can I use a Timeline instead of a standard Slicer?
Yes. If your consolidated data model contains a valid Date column, you can select your PivotChart, go to the PivotChart Analyze tab, and choose 'Insert Timeline'. This adds a specialized, visually intuitive date slider that can also control multiple charts via Report Connections.
