How to Preserve Custom Number Formatting in Excel Pivot Tables
Question details
The user needs to display varying custom number formats or units within a single column of an Excel Pivot Table reliably.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing mixed number formatting, such as varying measurement units, in a single Pivot Table column derived from dynamic source data.
- Observed behavior
- Standard Pivot Tables drop custom cell formatting from mixed-format source columns, requiring a robust formula-based workaround to display unit types accurately.
Ensure your source data is organized as a formal Excel Table and that you have access to the Data Model features in your spreadsheet application.
Use the Data Model and DAX to Separate Number Types
The most reliable way to handle multiple number formats is to separate the unit type into its own column in the source data and use a DAX measure in the Data Model.
Standard Pivot Tables aggregate data and strip out individual cell formatting. By separating the formatting category into a distinct column, you avoid losing this information. Using the Data Model allows you to concatenate and display these text types reliably.
Add a new 'Type' column to your source data to contain the specific formatting category or unit (e.g., currency, percentages, or metrics).
Select your source data, insert a Pivot Table, and check the box that says 'Add this data to the Data Model'.
Right-click the table name in the PivotTable Fields pane, select 'Add Measure', and create a DAX formula such as =CALCULATE(CONCATENATEX(DISTINCT(Table[Type]),Table[Type],", ")).
Add this new measure to the Values or Rows area of your Pivot Table to display the formats correctly alongside your numerical data.
Apply Formatting Using a VBA Macro and Lookup List
If you cannot use the Data Model, you can use a lookup list and VBA to apply number formats, though this is more error-prone when data updates.
Manage Complex Data seamlessly with WPS Office
While advanced DAX measures require Microsoft Excel's Data Model, you can effectively manage complex datasets, structure your source data correctly, and create powerful standard Pivot Tables effortlessly using WPS Spreadsheet.
- 1. Download WPS Office: Visit the official WPS Office website to download and install the free software suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook to begin analyzing your data.
- 3. Insert a Pivot Table: Navigate to the Insert tab, select Pivot Table, and customize your data layout effortlessly.
Frequently Asked Questions
Why does a Pivot Table lose custom cell formatting from the source data?
Pivot Tables aggregate values and apply standard formatting to the entire calculated field by default. Mixed custom formatting applied directly to individual cells in the source data is stripped during aggregation to maintain consistent calculations.
Can I apply conditional formatting to a Pivot Table instead?
Yes, conditional formatting can be applied to Pivot Table values. You can set rules based on values or categories to change font colors, apply data bars, or adjust number formats dynamically without altering the source data structure.
Is it better to use VBA or the Data Model to preserve formatting?
Using the Data Model with DAX measures is highly recommended. VBA macros are error-prone, require manual re-execution when data refreshes, and can slow down the workbook, whereas the Data Model dynamically recalculates as your data changes.
