how to consolidate data in excel pivot table

#How to consolidate data in excel pivot table how to#
#How to consolidate data in excel pivot table series#

=INDEX() returns the value of a cell in a table based on the column and row number.The INDEX MATCH formula is the combination of two functions in Excel: INDEX and MATCH. Using INDEX / MATCH to create custom reports.Cross-reference data from multiple reports using Excel.

#How to consolidate data in excel pivot table how to#

I want to wish you all a great Holiday season! We will begin 2021 with two articles that show you how to utilize Excel’s great financial functions. Selecting the Funnel icon in the top right of any Slicer clears the Filter for that slicer.įigure 6: Sales of Cars and Planes in Asia during Q1 and Q2

We can see that during Q1 and Q2, a total of 140 Cars were sold in Asia. For example, selecting Cars and Planes, Asia and Items 1 and 2 yields total sales of Cars and Planes in Asia during Q1 and Q2 (see Figure 5). Hold down Shift to select multiple items within one slicer. Using the Shift and Control keys, you may choose any subset of product, locations and worksheets (Item 1 = Q1, Item 2 = Q2, etc.) to govern the calculations in the Pivot Table. After selecting Row, Column and, you will see the Slicers shown in Figure 5. With your cursor inside the Pivot Table, navigate to the Insert tab. Slicers enable the user to easily slice and dice their data in many ways. We find, for example, that 280 Cars were sold in Asia and 136 Trains were sold in the USA.

Continue in this fashion until the data in all four worksheets has been selected (see Figure 3.)įigure 2: In Step 2b, we can add Multiple Ranges as the source data for a Pivot Tableįigure 3: All Source Data for PivotTable has been selectedĪfter clicking Finish you will see in the worksheet Q1 the Pivot Table shown in Figure 4. We first select the range D4:G7 in worksheet Q1 and select Add – you can do this by manually typing or by selecting within the Excel sheet! Click Next, and select the range D4:G7 in worksheet Q2. To begin, use the keystroke combination ALT+D, release D and press P while still holding down ALT to bring up the PivotTable and PivotChart Wizard dialog box shown in Figure 1.įigure 1: PivotTable and PivotChart Wizard Dialog BoxĪfter checking “Multiple consolidation range,” “Pivot Table,” and selecting Next, check “Create a Single Page Field” on the Step 2a Dialog Box, and the Step 2b Dialog Box shown in Figure 2 appears. We want to create a Pivot Table that summarizes the sales data from all quarters. The workbook Beckerdecember2020.xlsx contains quarterly sales of Planes, Trains, and Cars in Asia, Europe and the USA. In our example, the data sources will reside in different worksheets, but our data sources could easily be in different workbooks. For example, sales data for each month of the year may be located in individual worksheets, and you may want to create a Pivot Table combining sales data from all months. In this article, I will show you how to create Pivot Tables based on multiple data ranges. Most of the time, you probably put your cursor in the data and Excel miraculously recognizes the source data for the Pivot Table.

#How to consolidate data in excel pivot table series#

Welcome to another series of my best Microsoft Excel tips and tricks! I am sure that most of you use Pivot Tables regularly – in the accounting world, Pivot Tables can help organize data for everyday tasks immensely.

Excel Fundamentals + Data Analytics Certificate.

Data and Analytics for Business Professionals Certificate.

Cultivating a Diverse and Inclusive Workplace Certificate.

Leading Change for a Diverse and Inclusive Workplace Certificate.