So how did your weekend go?
Recently I bought a bicycle. Naturally, outdoor biking has become my primary fitness activity now. So, I went to Google Maps on Sunday morning and found a nice route of 14 kilometers. So I set out on my bike.
Barely a kilometer on that route, I found myself at a cul-de-sac. So I asked a passer by how do I get to “Rampuram“, the place where my route would merge with a state high-way. He vaguely pointed to the left and told me to go right. And right I went, thru what I can call as a walking trial (and what Google tells me as a major road). After biking for a few more minutes, I ended up at an over bridge. And this is where I trusted my gut and took left. And I kept riding on a steep road. I saw 1-2 villages whose names I did not recognize. So I told myself, yeah, this must be the right route. And then, I came to an intersection that looked very familiar. And guess what?, I took a wrong turn and I ended up doing a shorter route. I rode back on the highway to home as it was getting too hot and too late.
But then the weekend ended on a brighter note as we watched a couple of good movies and played with kids.
How was your weekend?
Why did you tell me about the biking story?
Today, we have no excel post. Instead, I am going to ask you to fill-up a survey. And I realized, people always help whenever I lost my way. So…, 😛
Are you interested in online VBA Classes?
Many of you have emailed and asked me, “Chandoo, can you help us learn VBA too? Just like you do with Excel.”
Well, as flattering as those requests were, the fact is, I was lousy at VBA. Well, I used to be very good at Visual Basic, back in the college days. But my programming skills have rusted over the years as I did very little coding. I guess, I have become a business-guy. So it took me time to re-learn VBA. And now, I am happy to announce that We are ready to start our very first batch of VBA Class.
We even have an awesome domain for the classes – VBAClasses.com. (Dont go there yet, it is still blank.)
But, before we start, I need some help from you. I want to know what is it that you want to learn, so that we can design the course curriculum & methodology around your needs.
So please take a few minutes and complete this survey:
(If you cannot see the survey, click here).
Thank you very much.
Thank you so much for your time. I promise that next time, I will tell a more exciting biking story. May be I will find a unicorn or double rainbow. Or, I can just throw in a VBA tip to make it worthwhile.
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
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 !!!
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!
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!
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."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
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.
@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
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
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.