At the point when the real pivot table was designed is in question. The Excel group authored the timeframe pivot table, which respected in Excel in 1993. Be that as it may, the thought gets not new. Pito Salas and his group at Lotus were taking a shot at the turn work area thought in 1986 and discharged Lotus Improv in 1991. Prior to at that point, Javelin gave usefulness much like that of pivot tables.
The focal thought of driving a pivot table is that the data, formulas, and data views are taken care of autonomously. In this post, we explained how to create a pivot table in excel.
Now that you have a good understanding of the importance of a wellstructured information source, let’s walk through creating a basic pivot table.
To guarantee that the pivot table catches the scope of your information source, of course, click any single cell in your information source. Next, select the Insert tab and discover the Tables gathering.
In the Tables, select PivotTable and afterward pick PivotTable from the dropdown menu. Picture 1.1 shows how to begin a pivot table.
Picture 1.1 Start a pivot table by selecting PivotTable from the Insert tab.
Choosing these options activates the Create PivotTable dialog box, shown in Picture 1.2
Picture 1.2 The Create PivotTable dialog box.
You can also press the shortcut to start a pivot table: Press and release Alt, press, and release N, and then press and release V.
As you can see in Picture 1.2, the Create PivotTable dialog box asks you only two fundamental questions:
- Where’s the data that you want to analyze?
- Where do you want to put the pivot table?
Here’s how you handle these two sections of the dialog box:
Choose The Data That You Want To Analyze—In this section, you tell Excel where your data set is. You can specify a data set that is located within your workbook, or you can tell Excel to look for an external data set. As you can see in Picture 1.2, Excel is smart enough to read your data set and fill in the range for you. However, you always should take note of the range of Excel selects to ensure that you are capturing all your data.
Choose Where You Want The PivotTable Report To Be Placed—In this section, you tell Excel where you want your pivot table to be placed. This is set to New Worksheet by default, meaning that your pivot table will be placed in a new worksheet within the current workbook. You will rarely change this setting because there are relatively few times you’ll need your pivot table to be placed in a specific location.
The presence of another option in the Create PivotTable dialog box shown in Picture 1.2: Add This Data To The Data Model option. You would select this option if you were trying to consolidate multiple data sources into a single pivot table. In this post, we’ll keep it basic by covering the steps to create a pivot table from using a single source, which means you can ignore this particular option.
After you have answered the two questions in the Create PivotTable dialog box, simply click the OK button. At this point, Excel adds a new worksheet that contains an empty pivot table report. Next to that is the PivotTable Fields list, shown in Picture 1.3. This pane helps you build your pivot table.
Picture 1.3 You use the PivotTable Fields list to build a pivot table.
The icons for Columns and Rows in Picture 1.3 are reversed. A summer intern at Microsoft inadvertently reversed the icons several versions ago and no one noticed. After Bill pointed this out to the correct project manager, they pledged to restore the icons to the correct location. The dark gray portion of the icon is supposed to show where the data will be displayed when you drop a field here. Any data dropped in the Columns area will display across the top of the report (as illustrated in the icon currently in the Rows area). It feels sort of goofy to show the wrong icons in this book on pivot tables, but that’s the way it is appearing to us at press time.
Finding the PivotTable Fields List
The PivotTable Fields list is your main work area in Excel 2020. This is the place where you add fields and make changes to a pivot table report. By default, this page pops up when you place your cursor anywhere inside a pivot table.
However, if you explicitly close this pane, you override the default and essentially tell the pane not to activate when you are in the pivot table.
If clicking on the pivot table does not activate the PivotTable Fields list, you can manually activate it by rightclicking anywhere inside the pivot table and selecting the Show Fields list. You can also click anywhere inside the pivot table and then choose the large Fields List icon on the Analyze tab under PivotTable Tools in the ribbon.
Adding fields to a report
You can add the fields you need to a pivot table by using the four “areas” found in the PivotTable Fields list: Filters, Columns, Rows, and Values. These areas, which correspond to the four areas of the pivot table, are used to populate your pivot table with data:
Filters—Adding a field to the Filter area enables you to filter on its unique data items. In previous versions of Excel, this area was known as the Report Filters area.
Columns—Adding a field into the Columns area displays the unique values from that field across the top of the pivot table.
Rows—Adding a field into the Rows area displays the unique values from that field down the left side of the pivot table.
Values—Adding a field into the Values area includes that field in the Values area of your pivot table, allowing you to perform a specified calculation using the values in the field.
Adding layers to a pivot table
Now you can add another layer of analysis to your report. Say that now you want to measure the amount of dollar sales each region earned by product category. Because your pivot table already contains the Region and Sales Amount fields, all you have to do is select the checkbox next to the Product Category field.
As you can see in Picture 1.4, your pivot table automatically added a layer for Product Category and refreshed the calculations to include subtotals for each region. Because the data is stored efficiently in the pivot cache, this change took less than a second.
Picture 1.4 Without pivot tables, adding layers to analyses requires hours of work and complex formulas.
Rearranging a pivot table
Suppose that the view you’ve created doesn’t work for your manager. He wants to see Product Categories across the top of the pivot table report. To make this change, simply drag the Product Category field from the Rows area to the Columns area, as illustrated in Picture 1.5.
Picture 1.5. Rearranging a pivot table is as simple as dragging fields from one area to another.
The report is instantly restructured, as shown in Picture 1.6.
Picture 1.6 Your product categories are now column-oriented.
Creating a report filter
You might be asked to produce different reports for particular regions, markets, or products. Instead of building separate pivot table reports for every possible analysis scenario, you can use the Filter field to create a report filter.
For example, you can create a regionfiltered report by simply dragging the Region field to the Filter area and the Product Category field to the Rows area. This way, you can analyze one particular region at a time. Picture1.7 shows the totals for just the North region.
Picture1.7 With this setup, you not only can see revenues by product clearly but also can click the Region dropdown menu to focus on one region.