If you want to prevent accidental changes to a pivot table’s layout, you can hide the pivot table buttons and labels. There are a few benefits from doing that. Continue reading “Hide Excel Pivot Table Buttons and Labels”
Category: Formatting
Copy Pivot Table Values and Formatting
In some situations, you might want to send someone a copy of a pivot table, but as values only, not an interactive pivot table. That would give them a summary of the data, but without access to the underlying details, which might be confidential. If you want to copy the pivot table formatting too, it requires extra effort, and the manual steps are shown below, as well as a macro which makes it easier to copy pivot table values and formatting.
Show Numbers as Text in Pivot Table Values
In an Excel pivot table, you can add text fields to the Row and Column areas, and in the Report Filters. However, pivot table Values area will only show numbers, so if you add a text field there, Excel shows a count of those text items. The technique shown below lets you show number fields as text Values, so you can display the names (East, West), instead of ID numbers (1, 2), for a small group of items.
Continue reading “Show Numbers as Text in Pivot Table Values”
List All Pivot Tables and Their Styles
In a big workbook, you might have lots of pivot tables, and each of those pivot tables is formatted with a PivotTable Style. To quickly see what’s in a workbook, use a macro to list all Pivot Tables and their styles.
Copy Pivot Table Custom Style to Different File
If you create a pivot table custom style, how can you copy that custom style to a different workbook? Excel doesn’t have a built-in command to do that, but you can copy it yourself, with a few simple steps. These instructions work in Excel 2016, and earlier versions too (way back to Excel 2007).
Continue reading “Copy Pivot Table Custom Style to Different File”
Show Tenths of Second in Pivot Table Times
If you try to show hundredths of second, or tenths of second in pivot table, you might have trouble. Usually, the times are rounded, and there is a zero, instead of numbers, for the tenths and hundredths.
See how to fix that pivot table time problem, so that the numbers are displayed correctly.
Continue reading “Show Tenths of Second in Pivot Table Times”
Copy Pivot Table Source Number Formatting
When you add numeric fields to a pivot table, Excel applies General format, in most cases. It doesn’t matter if you’ve spent hours applying fancy custom formatting to the numbers in the source data – all of that is ignored.
After the numeric data has been added to the pivot table, you can manually change the formatting of each field, in the Field Setting window. It’s not too painful in a small pivot table, but can take a while if there are several Values fields.
Continue reading “Copy Pivot Table Source Number Formatting”
Better Format for Pivot Table Headings
When you create a pivot table, Excel applies a default pivot table style. If there are two or more fields in the Row Labels area, you might see dividing lines, below the item headings.
Hide Pivot Table Errors
When there are errors in the pivot table source data, you might see errors in the pivot table Values area. In the screen shot below, a VLOOKUP formula in column E has returned an #N/A error, because the product wasn’t found in the lookup table. That also creates an error in column G – Total Sales.
Format a Pivot Table in Excel 2003 Classic Style
Last week, someone asked me how to make an Excel 2010 pivot table have the same look as the default settings in Excel 2003.
You might have forgotten what those pivot tables looked like, or maybe you never used that version of Excel. In the screen shot below, you can see the pivot table that I created, with Region and City in the Row area, Order Yr in the Column area, and Quantity in the Data area.
Continue reading “Format a Pivot Table in Excel 2003 Classic Style”