I was creating a Pivot Table this week, (one of many!), and it contained negative numbers. Now, this was not the end of the world, but I really only wanted positive numbers to show in my Pivot Table. I did not want the either of the zeros or the negative numbers to be visible. This is a handy little Excel trick, so I thought I would share it with you. Here is a sample of the type of dataset I was working on.
- Click on the Sum of Stock Balance field
- Click on Value Field Settings
- Number Formats | Custom | Special
- Type the following in the dialog box 0;;;
- Hit Ok
How Does The Custom Number Formatting Work In Excel?.
So just how does that customer number formatting work in Excel?. We need to first understand how Excel applies its number formatting.
Number formatting has up to four parts to it, all separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that specific order.
So, in our example, we are leaving the positive numbers visible 0 but hiding the negative numbers by using a semicolon (;) as well as zero and text. You do not need to add the semicolons for all of the custom number formattings. For example, if we only wanted to specify two code sections the first is used for positive numbers and the second for negative numbers. If you only use one customer number format then it is used for all of the numbers.
If you want to use a number formatting for positive, skip negative then use formatting for zeros then you must include the ending semicolon for the section you skip. For example, let’s switch things up a bit and hide positive numbers and zeros, but allow negative numbers and if the cell contains text then display ‘this is text’. The custom formatting would be as below.
;-0;;“this is text”
You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.
Final Formatting Of The Pivot Table.
To finish off the Pivot Table I removed the Grand Totals from the columns, as it is not relevant in or representative of the stock levels.
- Right Click on the Pivot Table
- Pivot Table Options
- Totals & Filters
- Untick Show Grand Totals For Columns
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
Want to Learn More Formulas?
Join the Excel Formulas Crash Course. If you want to learn SUMIFS, SUMPRODUCT, OFFSET and 40 other day to day formulas, then consider Chandoo’s Excel Formula Crash Course.
It has 31 lessons split into 6 modules and makes you awesome in Excel formulas.