In Excel for Microsoft 365 on Windows, a slicer has no independent “change data source” command: change the source of the PivotTable or Excel Table it controls, then refresh or resize that source. For a PivotTable, select it and choose PivotTable Analyze > Change Data Source; for a table slicer, select the table and use Table Design > Resize Table.
What an Excel slicer is connected to
A slicer is a visual filter for a PivotTable or an Excel Table. It displays buttons for values in one of the source fields, such as Region or Product, and filtering those buttons changes which records or summaries are shown. The slicer does not store a separate copy of the data or point to a workbook range on its own. Its connection is to the table or PivotTable and field that created it.
This distinction matters when you want to move a slicer to another dataset. Excel changes the underlying object’s source; the slicer continues to filter that object if its field is still available. If you want the slicer to control a completely different PivotTable, you may need to connect it to that PivotTable or create a new slicer. A slicer cannot be retargeted by editing a source address directly on the slicer itself.
Change the source of a PivotTable slicer
Use these steps when the slicer filters a PivotTable built from a worksheet range or Excel Table. Select a cell inside the PivotTable first so the contextual ribbon tab appears.
- Select any cell in the PivotTable that the slicer controls.
- Choose PivotTable Analyze > Change Data Source > Change Data Source.
- In the dialog, select the new worksheet range or enter its reference in the Table/Range box. If the data is in an Excel Table, enter or select the table name.
- Select OK, then choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
- Check the PivotTable fields and slicer. If the new source still contains the slicer’s field, the slicer can filter that field after the PivotTable is refreshed. If the field was renamed or removed, update the PivotTable and add a slicer for a suitable field.
For example, suppose a PivotTable summarizes an Orders range containing Date, Region, and Sales, and a Region slicer filters it. If new monthly records are added below the old range, update the source to include those records and refresh. The Region slicer can still work because Region remains a source field. If the replacement data calls that column Territory instead, the original Region field is no longer available under that name, so the PivotTable and slicer need to be adjusted.
Use an Excel Table to make growing data easier
A fixed reference such as Sheet1!$A$1:$D$500 can leave out records added below row 500. One practical improvement is to convert a well-structured source range into an Excel Table before building the PivotTable. Select the range and choose Insert > Table, confirm whether the table has headers, and give it a descriptive name on the Table Design tab. When rows are added directly below the table, Excel expands the table. Refresh the PivotTable to bring the new records into its results.
To switch an existing PivotTable to a table source, use PivotTable Analyze > Change Data Source and select the table name in the source dialog. For an existing slicer attached directly to an Excel Table, select a cell in that table and choose Table Design > Resize Table, then include the added rows or columns. A table slicer filters the table; it is not the same as a PivotTable slicer, even though both appear as clickable filter panels.
Before expanding a source, check that each column has a single header and that the data is laid out as one continuous rectangle. Blank header cells, merged cells, or totals embedded within the records can prevent Excel from interpreting the source cleanly.
Change the source for an Excel Table slicer
If the slicer was inserted for a regular Excel Table rather than a PivotTable, resize the table instead of looking for PivotTable Analyze commands. Click a cell in the table, choose Table Design > Resize Table, and set the full range that includes the intended data. The slicer remains associated with that table and its column, provided the column still exists. If the slicer’s column is removed or renamed, remove or recreate the slicer for a field that is present.
To create a replacement slicer for a table, select a cell in the table and choose Table Design > Insert Slicer. Choose the column or columns to use, then select OK. The exact tab layout can vary slightly with window size, but the contextual Table Design tab appears when the table is selected. A table slicer filters the table rows; it does not create PivotTable summaries.
Connect one slicer to more than one PivotTable
A common question is whether changing a slicer’s source will make it filter every PivotTable in a report. It will not automatically do that. A PivotTable slicer can control other compatible PivotTables through report connections, but those PivotTables generally need to share the relevant PivotTable cache or compatible data model connection, and they must contain the slicer’s field.
To inspect connections, select the slicer and choose the Slicer tab (sometimes shown as Slicer Tools > Options in older layouts) > Report Connections. In the dialog, select the PivotTables the slicer should control, then choose OK. If a PivotTable is missing or unavailable, it may not share a compatible source or cache, or it may not include the slicer field. In that case, changing the source of one PivotTable does not merge it with another; rebuild the PivotTables from a compatible source or use separate slicers.
For table slicers, the connection is to the Excel Table that owns them. Report Connections is a PivotTable slicer feature, not a way to link a table slicer to arbitrary tables.
When the source is external or uses the Data Model
The Change Data Source command may be unavailable or behave differently when a PivotTable was created from an external connection, Power Query output, or the Excel Data Model. In those cases, the PivotTable may depend on a connection or model rather than a simple worksheet range. Change the query, connection, or model source using the method that created it, then refresh the query or connection and the PivotTable as appropriate.
For a Power Query source, use Data > Queries & Connections to locate the query and edit its source steps in Power Query Editor. For a connection-based PivotTable, inspect Data > Queries & Connections or the workbook’s connection settings. The exact controls depend on the source type, such as a database, text file, or workbook connection. Do not assume that typing a new range into the PivotTable dialog will replace a managed connection.
If you are unsure how the PivotTable was built, select it and check whether Change Data Source is available. You can also inspect Queries & Connections and the PivotTable field list. Keep a copy of the workbook before making substantial changes to a connection or model, especially if the report has several PivotTables that depend on it.
Common problems and how to fix them
- The new rows do not appear. Confirm that the selected range includes them or that they are inside the Excel Table, then refresh the PivotTable. Changing a source range does not itself guarantee that cached results update.
- The slicer shows old or unexpected items. Refresh the PivotTable and check the source data for spelling differences, spaces, or old records. PivotTable options can retain items deleted from the source; select the PivotTable, open PivotTable Analyze > Options, and inspect the Data settings for how many items to retain per field. The exact dialog labels can vary by build.
- The slicer is blank, missing values, or no longer filters. Verify that the replacement source has the field used by the slicer and that the header has not changed. If the field is gone, add a slicer for an available field rather than expecting the old field to map automatically.
- Change Data Source is unavailable. The active cell may not be inside a PivotTable, or the PivotTable may use an external connection or Data Model. Select the PivotTable and identify its source type before choosing the right editing route.
- One slicer controls only one report. Select the slicer and check Report Connections. A missing PivotTable usually indicates a compatibility or field mismatch, not a slicer malfunction.
- The source range is invalid. Make sure the range is on the intended worksheet, forms a single rectangle, and has usable headers. For data that grows regularly, converting it to an Excel Table helps avoid repeated range edits.
Excel for Mac and Excel for the web
The article’s ribbon paths are for Excel for Microsoft 365 on Windows. Excel for Mac also supports PivotTables and slicers, but contextual tab names and dialog layouts can differ by version. Select the PivotTable or table and look for its contextual Analyze or Table controls; if the source-editing option is not available for a connection-based PivotTable, edit the underlying query or connection instead. Keyboard shortcuts are not needed for these steps, and Windows ribbon instructions should not be assumed to match the Mac interface exactly.
Excel for the web can work with many existing workbook features, but editing options for PivotTables, connections, and slicers depend on the feature and current web version. If you cannot change the PivotTable source or its connection in the browser, open the workbook in the desktop Excel app and make the change there. A slicer that is visible and interactive in a workbook is not necessarily editable in every Excel environment.
Frequently Asked Questions
Can I change a slicer’s data source without deleting the slicer?
Usually, change the source of the PivotTable or Excel Table the slicer belongs to and preserve the slicer if its field is still present. If you are switching to a different object or the field has changed, you may need to create a replacement slicer.
How do I change the source range for a PivotTable?
Select a PivotTable cell and choose PivotTable Analyze > Change Data Source > Change Data Source. Set the new range or table name, select OK, and refresh the PivotTable.
Why is my new data not showing in the slicer?
The source may not include the added records, or the PivotTable may not have been refreshed. Check the source range or resize the Excel Table, refresh, and confirm the new records have values in the slicer’s field.
Can one slicer control multiple PivotTables?
Yes, when the PivotTables are compatible and share the required field. Select the slicer, open Slicer > Report Connections, and select the PivotTables to connect. A PivotTable that is unavailable in the dialog may use an incompatible cache or source.
Can a slicer switch between two unrelated data sources?
Not by itself. A slicer filters the table or PivotTable to which it is attached; it is not a general-purpose source selector. Build the report from a suitable common source, use separate slicers, or recreate the report around the new data.
Related
- Excel Power Query Training: Skills, Courses, and Practice
- Excel Power Query Value Replacement: How to Use It
- Excel Power Query Value Filtering Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Styles in Word: What They Do and How to Use ThemYes. Microsoft Word includes styles: named sets of formatting you can apply to headings, paragraphs, lists, tables, and…
- Creating a Booklet in Word: Setup, Pages, and PrintingTo create a booklet in Microsoft Word for Microsoft 365 on Windows, open Layout > Margins > Custom…
- Creating Columns in Word: Layout, Breaks, and FixesTo create columns in Microsoft Word for Microsoft 365 on Windows, select the text you want to format,…
- Excel Keyboard Shortcut to Rename a Sheet in WindowsIn Excel for Microsoft 365 on Windows, press Alt, then H, O, and R to rename the active…
- How to Resize an Image in an Excel Cell and Keep It FittedTo resize an image so it fits an Excel cell, insert it with Insert > Pictures > Place…
- Excel Indian Number Format: Lakhs, Crores, and CommasTo show numbers in Excel’s Indian grouping system, select the cells, press Ctrl+1, choose Custom, and enter #,##,##0…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.