How to Highlight Matching PivotTable Data with Conditional Formatting or VBA
Question details
The user needs to compare specific data criteria (such as State, Loss Month, Loss Day, and Reported COL) across different worksheets and highlight the matching records inside a PivotTable.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing specific column values from a secondary worksheet against a PivotTable and dynamically highlighting the matched data.
- Observed behavior
- The user wants the matching PivotTable records to be highlighted automatically and for the formatting to persist even after the PivotTable is refreshed.
Ensure your PivotTable is fully updated and identify the specific criteria columns (e.g., State, Loss Month) you want to compare before applying any formatting rules or running macros.
Use Conditional Formatting to Highlight PivotTable Data
Conditional formatting is the easiest to maintain and ensures that highlights persist even after the PivotTable is refreshed.
Applying conditional formatting directly to the PivotTable structure rather than specific cell coordinates prevents your rules from breaking or shifting when new data is added and the table is refreshed.
Click inside the data area of your PivotTable where you want the conditional highlighting to be applied.
Navigate to the 'Home' tab on the ribbon menu, click on 'Conditional Formatting', and select 'New Rule' from the dropdown list.
Select the option to use a formula to determine which cells to format. Enter a formula that cross-references the matching criteria (State, Loss Month, etc.) on Sheet2. Choose your desired highlight color under the 'Format' button and click 'OK'.
To ensure the formatting does not disappear after a refresh, click 'Conditional Formatting' > 'Manage Rules'. Edit your new rule and select the option to apply it to 'All cells showing [Field Name] values' rather than a static cell range.
Use a VBA Macro for Complex Data Comparisons
VBA is ideal for complex, repeated cross-worksheet comparisons or when you need to highlight PivotTables across multiple sheets at once.
Highlight PivotTable Data Easily in WPS Office
WPS Spreadsheet provides robust, easy-to-use Conditional Formatting tools and full VBA/Macro support to help you highlight matching PivotTable data without hassle. It is fully compatible with Microsoft Excel formats.
- 1. Open Your Workbook in WPS: Launch WPS Spreadsheet and open your existing Excel workbook containing the PivotTable and your secondary data sheet.
- 2. Select Data and Apply Formatting: Select the target PivotTable cells, navigate to the 'Home' tab, and click 'Conditional Formatting' to set up your cross-sheet matching rules.
- 3. Set Rules or Run Macros: Define your matching criteria via the formatting rules menu, or press 'Alt + F11' to use the built-in VBA editor for running your custom highlighting scripts.
Frequently Asked Questions
Why does my conditional formatting disappear when I refresh the PivotTable?
This happens if the formatting rule is applied to a static range of cells (like A1:D10) instead of the PivotTable's logical structure. To fix this, open the Conditional Formatting Rules Manager, edit your rule, and select the option to apply the rule to 'All cells showing [Field] values'.
Can I highlight data based on multiple conditions across different sheets?
Yes. You can use advanced Conditional Formatting with a custom formula (such as an INDEX/MATCH or COUNTIFS function referring to the secondary sheet), or write a VBA macro to loop through multiple criteria variables simultaneously.
Does WPS Spreadsheet support Excel VBA macros for PivotTables?
Yes, WPS Spreadsheet supports VBA and macros natively. You can open the VBA Editor using Alt + F11 and run your existing Excel VBA scripts to loop through worksheets and highlight PivotTable data just as you would in Microsoft Excel.
