Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it.
It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8).
But today I want to show you a technique I have been using to dynamically extract and sort all unique items from a last list of values using Pivot Tables & OFFSET formula.
This is how it goes…,
Step 1: Select your data & Create a pivot table
Just select any cell and insert a pivot table. Very simple right?
Step 2: Drag the field(s) to row label area of pivot
Like this.
Make sure you have turned off grand totals and sub-totals as we just need the names. And sort the pivot table.
Step 3: Create a named range that refers to the pivot table values
Using OFFSET formula, we can create a named range that refers to pivot table values and grows or shrinks as the pivot is refreshed. Assuming the pivot table row values start in cell F6, write a formula like,
=OFFSET($F$6, 0,0,COUNTA($F:$F)-1,1) and map it to a name like lstProducts.
The formula gives us all the values in column F, starting F6. The COUNTA($F:$F)-1 ensures that we get only row labels and not the title (in this case Product Names).
Step 4: Use the named range in formulas etc. as you see fit
That is all. Nothing else.
Just make sure that you refresh the pivot table whenever source data changes.
Download example file with this technique
Click here to download an example file and play with it to understand how this works.
How do you deal with duplicate data?
In my work, I come across duplicate data all the time. I have been using pivot table based technique with great success. It is fast, reliable and easy to setup. The only glitch is that you need to refresh the pivot tables whenever source data changes. However, you can automate this by writing a simple macro.
What about you? How do you deal with duplicate data? Share your techniques, tips & ideas using comments.
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.