INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2010 Pivot Table Drilldown - Update Source table?

Excel 2010 Pivot Table Drilldown - Update Source table?

Excel 2010 Pivot Table Drilldown - Update Source table?

(OP)
Hi,

I've got a Pivot Table based on a Source Table that is 25,000+ Rows by 75 columns.
When I drill down into the Pivot Table, it understandably unearths the odd data error, duplicate, incorrect entry, etc. Correcting these is easy if there's just one or two - each row in the Source Table has a Record Number in column A, so I go to the table and amend the cell directly.

Sometimes though, there are many, many records in the drilldown, and these may have several amendments needed. Is there a way to make changes in the Drilldown output table, then have them 'refresh' or 'flash through' to the Source Table?

At the moment, I use an Advanced Filter on the Source Table, with Criteria based on the Record Number from the Drilldown Table. Is there a 'smarter' way to do it?

Chris

Someday I'll know what I'm donig...damn!

RE: Excel 2010 Pivot Table Drilldown - Update Source table?

There is no way to modify source data via pivot table report.
Microsoft developed Pover Pivot, that is succesively being implemented in excel (2016: native functionality, 2013: free add-in, 2010: add-in that works in professional plus version). It's a powerful tool to manipulate data, either for an input for pivot table or to get final table. A query in PQ is a series of transformations, filters or formulas, including various joins of other tables/queries.
If you have any chance to use it, I highly recommend it.

Pover query is also a part of microsoft's business inteligence suit Power BI (https://powerbi.microsoft.com/en-us/), one of versions is free, I tested it few months ago, it's close to excel's 2016 (professional version) business inteligence package: Get & Transform (Power Query), Pover Pivot and Pover View.

combo

RE: Excel 2010 Pivot Table Drilldown - Update Source table?

(OP)
Thanks combo, that's what I thought, more or less.

No dice on the Power Pivot, the computer and Excel 2010 are the Company's, and downloads are blocked sad

Chris

Someday I'll know what I'm donig...damn!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close