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!