Microsoft Excel —

Eliminate Blank Cells in the Data Area

People started complaining about the blank cells immediately when pivot tables were first introduced. Anyone using Excel 97 or later can easily replace blank cells with zeroes. In the user interface, the setting can be found in the PivotTable Options dialog box. Choose the For Empty Cells, Show option and type 0 in the box.

The equivalent operation in VBA is to set the NullString property for the pivot table to "0".

NOTE

Although the proper code is to set this value to a text zero, Excel actually puts a real zero in the empty cells.



Control the Sort Order with AutoSort

The Excel user interface offers an AutoSort option that enables you to show markets in descending order based on revenue. The equivalent code in VBA to sort the customer field by descending revenue uses the AutoSort method:

PT.PivotFields("Line of Business").AutoSort Order:=xlDescending, _
    Field:="Sum of Revenue"

Default Number Format

To change the number format in the user interface, double-click the Sum of Revenue title, click the Number button, and set an appropriate number format.

When you have large numbers, it helps to have the thousands separator displayed. To set this up in VBA code, use the following:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0"

Some companies often have customers who typically buy thousands or millions of dollars of goods. You can display numbers in thousands by using a single comma after the number format. Of course, you will want to include a "K" abbreviation to indicate that the numbers are in thousands:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"

Of course, local custom dictates the thousands abbreviation. If you are working for a relatively young computer company where everyone uses "K" for the thousands separator, you're in luck because Microsoft makes it easy to use the K abbreviation. However, if you work at a 100+ year-old soap company where you use "M" for thousands and "MM" for millions, you have a few more hurdles to jump. You are required to prefix the M character with a backslash to have it work:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,\M"

Alternatively, you can surround the M character with a double quote. To put a double quote inside a quoted string in VBA, you must put two sequential quotes. To set up a format in tenths of millions that uses the #,##0.0,,"MM" format, you would use this line of code:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0.0,,""M"""

In case it is hard to read, the format is quote, pound, comma, pound, pound, zero, period, zero, comma, comma, quote, quote, M, quote, quote, quote. The three quotes at the end are correct. Use two quotes to simulate typing one quote in the custom number format box and a final quote to close the string in VBA.