Share via

Creating Multiple Pivot Tables Quickly

Ms_David 21 Reputation points
2021-01-16T16:21:58.307+00:00

Hello, I have a data set with 60 indicators for 58 companies for each year between 2017 and 2020. I need to analyse the data for each indicator based on 10 different ways of disaggregating the data. This would require about 1,000 pivot tables to present the analysis. How can I create multiple pivot tables with different rows, filters and values automatically or quickly? Or are there any other ways of speeding the process up, other than the slicer or the ‘show report filter pages’ features. Any help would be much appreciated.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erin Ding-MSFT 4,461 Reputation points
    2021-01-18T06:20:06.517+00:00

    @Ms_David

    Based on your description, I think your requirement maybe need VBA code to realize.
    To get more related help, I will add a tag “office-vba-dev” for you.
    Besides, if convenient, you could provide a sample for us to better test for you.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Ms_David 21 Reputation points
    2021-01-18T14:37:04.73+00:00

    Thank you. Here is a google sheet with 3 of the indicators and a few of the companies. For this sample, I would need the results for each indicator summarized according to funding, portfolio, status and country. I would need to create that summary automatically/quite quickly because I would need to do it at least 60 more times for the other 60 indicators.

    https://docs.google.com/spreadsheets/d/18uI7ExqOMdDMwb4m5KO33uknwwAwKLUYHNSG9BI35GA/edit?usp=sharing

  3. Ms_David 21 Reputation points
    2021-01-21T17:54:15.88+00:00

    Hello erinding-msft

    For each indicator I would like to have a pivot table with that indicator as the value (sum or average - both useful), and 'funding' as the row. Then I would like to have the same thing but with 'Country' as the row, and then again with Portfolio as the row. And so on. So multiple tables for each indicator with different variables as the rows.
    The ultimate purpose is to understand the total number of jobs for different funding and then for different countries, and then for different portfolios. And so on. For each indicator
    Thank you so much for your help I really appreciate it.

    0 comments
  4. Ms_David 21 Reputation points
    2021-01-23T16:16:06.007+00:00

    Thank you so much, I really appreciate you taking the time to help me with this. This is such a clever solution. Thank you.

    0 comments