A Pivot Table Never Forgets – AKA Renaming Data Items in Pivot Tables

Sometimes, you just have to rename an item in a pivot table, even when you pulled it straight from a data source.  The line item gets renamed in the pivot table, and Excel will remember that name indefinitely now.  Except, you might not remember what it was called before…

One quick way to fix this is use the “Clear All” button for the Pivot Table.  This clears everything, including those renamed items.

This can get a bit complicated with some reports, however.  Let’s say that you’re using grouping, or you’ve filtered and sorted a hunk of stuff in the report, or you have a bunch of conditional formatting set, and using the “Clear All” function for the Pivot Table would result in a lot of rework.

I have a workaround.  There might be a better way, and if so, I’d like to hear it, but this way, at least, will keep your groupings and formats.

It’s pretty simple – just go into your query and find the column in question:

SELECT
ProjectName

FROM

MSP_EpmProject_UserView

and then “add” something to the name, like this:

ProjectName+ ‘Hello’

Now, when you pull all your project names into your Pivot Table, they will look like this:

Project Name AHello

Ugly, but you’ll notice that the part before “hello” actually shows you the original names.

Now go back into your query and remove that + ‘hello’ and refresh.

Bam!  Your original names are back in.

Not a very exciting little piece of info, but helpful to someone, somewhere, someday I hope!

Advertisements

5 thoughts on “A Pivot Table Never Forgets – AKA Renaming Data Items in Pivot Tables

  1. Yes! This is helpful. But can you please clarify: “go into your query and find the column in question.” What query?

    • I suppose I could have been clearer, sorry for that. I’m only talking about situations where your pivot table is built from a query. Did you pull your pivot table data from another sheet in your workbook?

      • I suppose you could use the same basic theory – you could go to the tab you retrieved the data from, select the entire column or row in question, then do a find and replace, refresh your pivot table, then find and replace it back to normal and refresh again. You may end up doing something weird in that case, like turning all spaces into a # or something, but as long as you use a “funky” character you should be able to return it back to the previous state. Does that make any sense at all? Please let me know if that helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s