Category Archives: Formatting

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 … Continue reading

Posted in Formatting | Leave a comment

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. … Continue reading

Posted in Formatting | 1 Comment

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.

Posted in Formatting | Leave a comment

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 … Continue reading

Posted in Formatting | Leave a comment

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 … Continue reading

Posted in Formatting | 2 Comments

Turn Repeating Item Labels On and Off

When you set up a pivot table, the outer field names each appear once, at the top of the group. In the screen shot below, The category names are in the left column, and the products for each category are … Continue reading

Posted in Formatting | Leave a comment

Pivot Table Shows Customers With No Purchases

A pivot table is a great way to see a summary of sales to your customers. You can see how much they bought, and how much they spent, on each product. In the pivot table below, each customer is listed, … Continue reading

Posted in Formatting | Leave a comment

Change All Number Formats in Pivot Table

Last week, I asked for your input in my survey on pivot table number formats. There were 3 questions in the survey: Which number format do you use most often in pivot tables? How many decimal places do you usually … Continue reading

Posted in Formatting | 3 Comments

Show Zero in Empty Pivot Table Cells

When you set up a pivot table, there might be a few blank cells in the Values area. In the example below, no Banana Bars were sold at stores 3062 or 3659. Instead of leaving these cells empty, you can … Continue reading

Posted in Formatting | Leave a comment

Pivot Table Conditional Formatting

You can apply conditional formatting rules to a pivot table, just as you would in other cells on a worksheet. In the screen shot below, cells with amounts above average are filled with light green. However, if you change the … Continue reading

Posted in Formatting | 2 Comments