How To Create Pivot Table From Multiple Sheets In Excel

To create a Pivot table from Multiple Sheets in Excel, you can use the data consolidation feature in Excel. It is important that these sheets have the data setup in the same manner i.e. they have the same column headings – e.g. sales data from different regions, or inventory data across stores.

Consider the following data from a store showing the quarterly sales of different products across two stores. We now want to create a report that shows the sales across each store by the category. We can achieve this by creating a Pivot Table for data across these multiple sheets.

Launch the Pivot Table and Chart Wizard using the keyboard shortcut – Alt+D, P and select ‘Multiple Consolidation Ranges’

Pivot Table and Chart Wizard

Select -> I will create the Page Fields option and press Next

Create Page Fields

Now select the range for the data on which you wish to create a Pivot table – select the column headings as well.

 Select data from both the sheets and create one Page Field for each sheet. Label the Page field appropriately.

Create one Page Field

Select to create the Pivot table in a new Worksheet and click on Finish. If you wish to create the pivot table in same sheet, input the desired cell information from where the pivot table should start.

Next modify the fields in the newly created Pivot table -

  • Rename the Page Field to something more appropriate to reflect the data in the Pivot table, for example we are consolidating data across Stores, so rename it to Stores

  • Filter the Column Labels, to only show the Sales data
  • Modify the Values column to calculate the sum, instead of count and select the appropriate number format.
    • Click on any field in the Pivot table, the Pivot Table Field list will appear
    • Go to the “∑  Values” column, click on the field that says “Count of Value”
    • Select “Value Field Settings”
    • In the “Summarize by” field, select Sum. You can also set the appropriate number format by clicking on the “Number Format” button

  • Modify the Pivot Table Options to calculate the grand total only across columns.

To see data only, across a particular store, filter on the Stores Field, for example to see only data across Store 1

However, there are limitations to such a pivot table and it is best that you consolidate the data into a single work sheet. You can then create a Pivot table for that sheet and create reports across any/all fields.

Note: These steps work for Excel 2003 as well. However, the screenshots shown here are for Excel 2007.

Hope you find the information presented here useful. Feel free to share your feedback or suggestions in the comments section below.

 

  1. Hi Team,

    I am in need of some data crunching methodology. My scenario is this,

    I have to test 9 devices each with 3 temperatures(hot, room, cold) so totally 9*7=27 data.
    In one sheet I ll test for some 200 tests.

    Eg:
    sheet1,
    test 1 : 1st devices, hot temperature

    test 2 : 1st devices, hot temperature

    test 3 : 1st devices, hot temperature

    test 4 : 1st devices, hot temperature

    test 5 : 1st devices, hot temperature

    test 6 : 1st devices, hot temperature
    .
    .
    .
    test 200 : 1st devices, hot temperature

    sheet2,
    test 1 : 2nd devices, hot temperature

    test 2 : 2nd devices, hot temperature

    test 3 : 2nd devices, hot temperature

    test 4 : 2nd devices, hot temperature

    test 5 : 2nd devices, hot temperature

    test 6 : 2nd devices, hot temperature
    .
    .
    .
    test 200 : 2nd devices, hot temperature
    ..
    ..
    ..
    .
    . so on for 27 sheets..

    I want to crunch all data into one sheet such that I should do minimum enteries and maximum output.
    In output sheet I should get max, min, avg values of the data entered.

    For eg. Max(sheet 1 , hot temp, 1st device: sheet2, hot temp, 2nd device…. Upto 27 devices)

    I need a format how to put this data into excel and get these kind of maximum outputs and easy for a reader.

    Thanks in advance. Hoping for your favorable reply.

    Regards,
    Ralins A
    +91-9003043088

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">