Search

Fix Incorrect Percentages with this Paste-Special Trick

Share

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.

1. Type 100 in an empty cell. Now copy the cell by pressing CTRL+C
2. Select cells with incorrect percentages, and press ALT+E S (alternatively right click and select Paste Special)
3. Now, Select “Divide” from operation area. See it aside.
4. 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?

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”

1. Martin says:

I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.

great thing to know !!!

2. Tony Rose says:

Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!

3. Jody Gates says:

I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!

4. Jon S says:

If it's just appearance you care about, another alternative is to use this custom number format:
0"%"

By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."

• Steven Peters says:

Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.

Thank you.

5. Jon Peltier says:

Here is a quicker protocol.

Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.

6. Chandoo says:

@Martin: That is another very good use of Divide / Multiply operations.

@Tony, @Jody: Thank you ðŸ™‚

@Jon S: Good one...

@Jon... now why didnt I think of that.. Excellent

7. sajith says:

Thank You so much. it is really helped me.

8. Winnie says:

Big help...Thanks

9. Chris Fry says:

Thanks. That really saved me a lot of time!

10. Texas says:

Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.Â