Hello, Excellers welcome back to another #Exceltips blog post in my 2021 series. Today let’s look at using the Paste Special Function in Excel. Have you a worksheet with the % (percentage) formatting missing?. If you ever have tried to correct this then you know you could try to adjust the formatting of the cell. But, that just ends up looking like the screenshot below right?.
Who wants to edit each cell with %?. Well, by using Paste Special we can easily adjust the values to correct them with the simple steps below.
- Type 100 in any cell on your worksheet (this will be used with Paste Special).
- Copy that cell by hitting Ctr+C.
- Next, select the cells that should be formatted as a percentage.
- Hit ALT+E S (alternatively right-click and select Paste Special).
- Select Divide from the Paste Special menu.
- Hit ok, then correct the cell formatting to show %.
That’s it!. So, how about reversing this process?. You can remove % signs by multiplying cell values with 100. Oh Yeah! have you ever used this before?.
Want to read more about Paste Special in Excel?. I have more blog posts you maybe interested in.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips. Click the link below to sign up.