How to fix incorrect percentages in Excel
Number formats are a way of telling Excel how to output data.
Although they don't affect the underlying values, they improve readability, consistency and make analysis easier.
One of the 11 built-in choices is Percentage. It's designed for cells that contain percentages. However, it can easily be misused due to how it behaves.
Firstly, it's important to understand that Excel stores percentage values as decimals 'under the hood'. That means 25% is 0.25, 50% is 0.5, and 100% is 1. This isn't very reader-friendly, though, which is why Percentage exists.
Assuming General, the default number format is the starting point; applying Percentage will multiply a value by 100, which means two extra zeros are added, along with the same number of decimal places. For example, 75 and 67 become 7500.00% and 6700.00%, respectively.
Not very sightly, eh?
This problem stems from:
➤ not setting the number format as Percentage from the start
➤ not inputting percentages in decimal format
➤ working with datasets that don't store percentages in decimal format
Also, as the per cent sign is often only included in headings and omitted from values, Excel does not get the trigger it needs.
Thankfully, there are a couple of solutions, which both divide each value by 100.
As shown in the video, you can perform this calculation directly on your data using the Paste Special window. Or, you can construct a formula in a separate column.
Has the Percentage number format ever caused you headaches?
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest virtual gathering of Microsoft Excel users and experts.
Learn more at https://globalexcelsummit.com/.
Find us on:
👔 LinkedIn: / global-excel-summit
🐤 Twitter: / excelsummit
🟦 Facebook: / globalexcelsummit
📸 Instagram: / globalexcelsummit