In this post you can find tips on number formatting in Excel, also usable in PivotTable reports. In iqbs, numbers in PivotTables come pre-formatted (based on what is defined in the cube). In Excel 2002 and 2003, there is no pre-formatting at all because the PivotTables in these versions do not support pre-formatting.
The general pre-formatting in iqbs is done like this:
- Amount and price fields: thousands punctuation and two decimals
- Quantity fields: fields: thousands punctuation and one decimal
- Days fields (lead time, deviations): thousands punctuation and zero decimals
- Percentages fields: thousands punctuation and one decimal
- Counts (orders, order lines): thousands punctuation and zero decimals
For example, when you choose the field ‘Net Amount’ in a Sales cube, the number would be: 1.000.250,95 (using the Dutch regional settings).
Tip #1: format to thousands
To format a number to the 1000s, go to Format Cells, Number, Custom and use: #.##0.
(or #,##0, if your 1000-separator is a comma).
The number 1.000.250,95 will show as 1.000 then.
Tip #2: format to millions
To format a number to the 1000s, go to Format Cells, Number, Custom and use: #.##0..
(or #,##0,, if your 1000-separator is a comma).
The number 1.000.250,95 will show as 1 then.
Tip #3: Format positive numbers > 100 with 2 decimals, < 100 with currency Euro, zero as 0
Formatting can be done with filters: [ > =100]0.00;[ > 0]€#;0
(or [ > =100]0,00;[ > 0]€#;0 if your 1000-separator is a comma).
More tips will follow!
