fbpx
Search
Close this search box.

Fix Incorrect Percentages with this Paste-Special Trick

Share

Facebook
Twitter
LinkedIn

Fix Incorrect Percentages - Excel Howtos
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..

Paste-Special - Microsoft ExcelTo 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?

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

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

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

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.

Advanced Pivot Table tricks

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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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.

Leave a Reply