Formatting percentages

Time and again, I receive spreadsheets from people multiplying numbers by 100 to show them as percentages. Here's a brief overview of how percentages work, both generally and specifically in Excel.

A percentage is merely an expression of a ratio. Thirty percent of the class was wearing a tie. This means that of the 30 people in the class, nine were wearing a tie. Nine out of 30 = 0.3

Year-on-year profits increased by 120%. This means that if the profit was £2.0m in 2010, it increased to £4.4m in 2011. 4.4/2.0 = 1.2.

But percentages are always displayed multipled by 100. So the 0.3 above is written as 30%. And the 1.2 is written as 120%.

In Excel, once you've calculated your ratios, you should never multiply them by 100 to display them correctly. Instead, there is a handy percentage format built in. Simply click the % button in the Home ribbon, and your 0.3 will show as 30%. And the number of decimal places showing can be increased or decreased using the appropriate buttons, or in the Formats dialog box.

Multiplying your percentages by 100 creates inefficient formulae, and can cause all sorts of issues when you start using them downstream in further formulae.

Usually, if you see a 100 in a formula, it doesn't need to be there.

• Tweet