Sometimes we get values in our Excel sheets in such a way that the % sign is omitted. So instead of the value being 23%, it is 23. Now, you can very easily correct this by editing the cell and adding a % sign at the end. But what if you have 100s of rows of data. You can’t do this to every cell. (You can not just format the cells to % format either, excel shows 23 as 2300% then). There must be some simple and intuitive solution for this … umm.
Of course there is..
To clean up incorrect percentages just follow these 4 steps.
- Type 100 in an empty cell. Now copy the cell by pressing CTRL+C
- Select cells with incorrect percentages, and press ALT+E S (alternatively right click and select Paste Special)
- Now, Select “Divide” from operation area. See it aside.
- That is all. We have divided all the values in incorrect percentage cells by 100. Make sure the cells are formatted in % style to show 0.23 as 23%.
Bonus: You can remove % signs by multiplying cell values with 100.
Bonus*Bonus: You can convert a bunch of hours to days by dividing them with 24, minutes to hours by dividing with 60 etc.
Bonus Bonus: 15 more ways to use Excel’s paste special. Do you know all of them?
2 Responses to “Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org”
How did you arrive or formula when you ask me who made more sales Jackie or Jamie?? I could not figure the formula out yet??
@Frank
You need to sum up the sales for Jackie and compare them to the sales of Jamie over the same period.
The practical parts depends on how and what data you have and how it is arranged.
But it will either involve a Sum, Sumif, Sumifs or Sumproduct to accummulate the sales for each within a specified date range.