I am obsessed with unique values, I guess it has something to do the quality data of I get to work with. So much so that it is the first excel post I have written on this blog, way back in 2006. Today I want to share with you all one more way (Not again man, you did that 2 weeks back) to extract unique values. I promise that I wont propose another unique idea to extract unique values (remember, I can still write about ways to remove duplicates… 😛 )
Ok, enough introduction already. Here goes another unique items extraction technique, this time using Pivot Tables.
1. Just select the data from which you need to extract unique items
2. Create a Pivot Table (in Excel 2003 menu > data > pivot table and in Excel 2007 Insert > Pivot Table)
3. Drag and drop a column in the pivot table and you will instantly see all unique values for that column.
4. Copy the values and use paste special to extract the values.
Why I love this technique:
It is scalable and works really well with large data. If your data changes just refresh the pivot. Also, when you need to extract unique values for multiple columns this technique is really good, for eg. unique customer names, product names, cities from monthly sales report. Despite the manual step of creating the pivot tables, this method is highly reliable.
Also checkout other ways to filter unique items or eliminate duplicates:
> Getting unique items using data filter and formulas
> Use advanced data filters to find unique items
> Eliminate Duplicate Entries in a List using Formulas
> Get Unique items using Excel 2007 built in features
What is your favorite way of extracting unique items ? Or you are happy with with few duplicates here and and there ?
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.