Power Pivot Column Formatting: Set Formats That Stick

In Excel for Microsoft 365 on Windows, open the Power Pivot window, select the column, then choose a number format from Home > Formatting. This sets the column’s display format in the Data Model; it changes how values appear, not the underlying data or its type.

What Power Pivot column formatting controls

Power Pivot stores tables and their columns in Excel’s Data Model. A column’s format is presentation metadata: it tells Excel how to display a stored value, such as showing 0.25 as 25%, 1234.5 as $1,234.50, or a date serial as a calendar date. The numeric value remains unchanged, so calculations and sorting continue to use the actual stored value.

This distinction matters when the same field appears in several reports. Setting a sensible format in the model gives PivotTables a useful starting point and helps keep a field consistent. It does not replace the column’s data type, clean malformed source data, or determine whether a field can be summed. Those are separate model and PivotTable decisions.

Column formatting applies to the whole model column. It is not a way to format selected cells differently according to their values. For conditional colors or cell-level visual distinctions, use worksheet formatting or conditional formatting on the PivotTable instead.

Where to find the formatting controls

Power Pivot is an Excel add-in for Windows desktop. If its tab is not visible, check whether the add-in is enabled: in Excel, select File > Options > Add-ins. At the bottom, set Manage to COM Add-ins, select Go, and look for Microsoft Power Pivot for Excel. Availability and exact add-in names can depend on the Excel edition and installation.

Once the Power Pivot tab is available, select Power Pivot > Manage to open the model window. In that window, select a table tab, then click the column heading you want to format. The Home tab’s Formatting group contains the format controls. This is a different ribbon context from the regular Excel worksheet, so first confirm that the Power Pivot window is active.

How to format a column step by step

In the Excel workbook, choose Power Pivot > Manage.
In the Power Pivot window, select the table containing the field.
Click the column heading. Check that the intended column, rather than a neighboring one, is selected.
On the Home tab, use the Formatting group’s format control to choose an appropriate format, such as Currency, Percentage, or a date format. If the format includes decimal-place controls, set the precision that makes the values readable.
Return to Excel and inspect a PivotTable that uses the field. Refresh the PivotTable if needed, then check both the displayed values and totals.

For example, if a model column contains decimal fractions such as 0.08 and 0.125 that represent rates, choose Percentage. The values display as 8% and 12.5% (depending on the selected decimal places), while their stored values remain 0.08 and 0.125. Do not multiply the source data by 100 just to make the percentage sign appear; that would change the values used in calculations.

Choose a format that matches the data

Pick a format based on what a value means to the people reading the report, not just on how the source happens to look. Common choices include:

Whole numbers: Use a number format with zero decimal places for counts, units, or quantities that cannot be fractional. Keep a decimal format if fractions are meaningful.
Currency: Use for monetary amounts, and choose the appropriate currency symbol and decimal precision. A currency format displays a symbol; it does not convert between currencies.
Percentage: Use when a stored decimal fraction represents a proportion or rate. Confirm the source scale first: 0.2 normally means 20%, while a stored value of 20 would display as 2,000%.
Date or time: Use when the column contains date/time values. A date-like number is not necessarily a valid date; verify the column’s type and the source values.
Decimal number: Use when fractional precision is important, such as a unit price or measurement. Avoid showing unnecessary digits if they obscure comparisons.

Formatting can also affect what readers infer. Showing a monetary amount with no decimals may make two slightly different amounts appear identical, even though their underlying values differ. Choose precision appropriate to the report, and retain enough detail when rounding could mislead.

Format and data type are different settings

A format changes appearance; a data type determines how the model interprets a column. A numeric column displayed with a currency symbol is still numeric. Conversely, text that looks like a date or amount does not become a usable date or number merely because a display format is selected.

If a format choice is unavailable or values appear unexpectedly, inspect the column’s data type in Power Pivot before changing the source format. Text values containing symbols, inconsistent date strings, or mixed values may need cleaning or conversion before loading into the model. Also check for blanks and errors. A format cannot repair inconsistent source data, and converting a text column incorrectly can produce errors or unwanted values.

Identifiers such as postal codes, product codes, and account numbers are often best treated as text, even if they contain only digits. They are labels rather than quantities: adding them or displaying them with decimal places would not make sense. A numeric format may also remove leading zeros, so preserve those zeros in the source or store the identifier as text when appropriate.

Formatting columns versus measures

A calculated measure is not a model column. Measures calculate results in response to the PivotTable’s filters and layout, while a column contains a value for each row in its table. A measure that returns revenue, a ratio, or a count may need its own format even if the source columns are already formatted.

In the Power Pivot window, select the measure in the calculation area or the measure list, then use the formatting controls on the Home tab to set its display format. The precise selection experience can vary with the model view. If a measure is selected, confirm that the format is being applied to that measure rather than to a table column.

For example, a revenue column may be formatted as currency, while a measure calculating revenue divided by units should usually be formatted as a currency amount per unit. A measure returning revenue divided by total revenue is generally a percentage. Formatting the source fields does not automatically determine the correct format for every calculation made from them.

How model formatting relates to PivotTables

Model-level formatting is useful because it can provide a default when a field is used in a PivotTable. However, the worksheet PivotTable has its own formatting controls, and an individual report can display a field differently from the model’s default. If the number format in one PivotTable does not match the model, inspect that PivotTable’s value-field settings rather than repeatedly changing the column in Power Pivot.

To set a format for a value field in a worksheet PivotTable, right-click a value in that field and choose Value Field Settings, then choose Number Format. Set the format in the resulting dialog and confirm the changes. Using Number Format within Value Field Settings is generally preferable to formatting the visible cells directly: ordinary cell formatting can be lost or rearranged when a PivotTable refreshes or changes shape.

One model field can be used in more than one report, and those reports may have different needs. A management summary might show whole currency units, while a reconciliation report needs cents. In that case, keep the model’s format sensible as a default and use the PivotTable’s value-field format for the report-specific presentation.

Common formatting problems and fixes

The format does not appear in the PivotTable: Confirm that the correct model column was selected, then check the PivotTable’s Value Field Settings for a report-level number format. Refresh if the model or source data has changed.
A percentage looks 100 times too large: Check the stored scale. Applying Percentage to 20 displays 2,000%, while applying it to 0.2 displays 20%. Correct the source or calculation if the stored scale is wrong; do not hide the problem with a different display format.
A date displays as a number: Verify that the column contains date values and has a suitable date data type and display format. A numeric serial without valid date interpretation will not become a meaningful date by appearance alone.
Leading zeros disappear: Codes that require leading zeros should usually be stored as text. Numeric formatting is not a safe substitute for preserving identifier values as text.
Totals seem inconsistent with displayed values: Number formats can round what is shown without rounding the underlying data. For instance, several values displayed as 1.2 may add to a total that does not equal the sum of the displayed rounded figures.
Formatting changes after refresh: Determine whether the change is in the model column, the PivotTable value field, or ordinary worksheet cell formatting. Apply the format at the level that should own it, and test a refresh before distributing the report.

Custom formats and practical conventions

Built-in formats cover many everyday cases. If a specific display is required, such as showing negatives in parentheses or suppressing a zero, check which format choices are offered in the Power Pivot window and in the worksheet PivotTable’s Number Format dialog. Custom-format support and the available controls can differ by context, so do not assume that a format entered for worksheet cells will automatically become model metadata.

Keep formatting conventions consistent across related fields. Use the same currency and precision for amounts that readers compare, and use a consistent percentage precision for rates. When two columns represent different currencies, a shared currency symbol can be misleading; make the currency clear in the field name, report heading, or other documentation. A number format is not a substitute for describing units or business meaning.

Windows, Mac, and web availability

These Power Pivot authoring steps apply to Excel for Microsoft 365 on Windows with the Power Pivot add-in available. Power Pivot’s model-management window and its column-formatting workflow are not available in Excel for Mac in the same way, so a Mac user may need to open the workbook in Excel for Windows to edit the model’s formatting. Excel for the web also does not provide the full Power Pivot authoring interface.

If you can only work in Excel for Mac or the browser, you may still be able to adjust formatting in a regular worksheet PivotTable, depending on the workbook and available features. That is a report-level workaround, not the same as setting the column’s model format. Check the workbook in the environment where it will be maintained and refreshed, particularly if multiple people use different Excel platforms.

Frequently Asked Questions

Does Power Pivot formatting change the actual value?
No. A number format changes how a value is displayed, not the stored value. Calculations continue to use the underlying value and data type.

Can I format only some cells in a Power Pivot column?
Not with the model column’s format setting; it applies to the column. For value-dependent colors or selective emphasis, use conditional formatting in the worksheet PivotTable where appropriate.

Why is my Power Pivot column format not showing in the PivotTable?
The PivotTable may have its own number format for that value field. Check Value Field Settings > Number Format, and confirm that the model column itself has the intended format.

Should I format a percentage in the source data or in Power Pivot?
Store the correct numeric proportion in the source, such as 0.15 for 15%, then apply Percentage formatting in the model or report. Do not multiply the value by 100 solely to display a percent sign.

Can I use Power Pivot column formatting on a Mac?
The Power Pivot model-management window is a Windows Excel feature. For model-level formatting, use Excel for Windows; a PivotTable’s worksheet formatting may be an option on Mac, but it is not the same setting.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.