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?
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.