What is a Pivot Table?

A pivot table is a data summarization device that is utilized in the context of data handling. Pivot tables are utilize to summarize, sort, rearrange, group, check, aggregate, or standard record stored in a database. It permits its clients to change columns into rows and rows into segments. It permits grouping by any information field. Pivot tables are the ideal arrangement when we have to summarize and analyze a huge number of data.

Starting with a blank Pivot Table

1. Open the document GL Pivot Table Data.xlsx. The worksheet has a query, including FY11 costs from the Economic Data Warehouse.

2. Spot our cursor on any cell in the information.

Note: Ensure your information is in a tabular format, and there are no clear rows or columns. Additionally, every column must have a different heading that is one row high.

3. Go to the ribbon and open thetab.

• Click on thedown arrow to get a list of choices.

Creating Your First Pivot Table

To make a pivot table:

1. Click on a cell in our table of information

2. Pick “PivotTable” from the Insert label on the ribbon

3. Click on the select table/range button as appeared in the picture above

4. The default is New Worksheet under where to put up the PivotTable

5. Acknowledge the defaults by tapping on thebutton.

6. We will get the following small window.

7. Press in cell address A1

8. Press Ctrl + A on the keyboard to choose all the record cells

9. Your small window demonstrated now occur as follows

10. Press on Close button to return to the options window

11. Press on the OK button.

12. A clear PivotTable and Field List will occur on a new worksheet.

13. When you make a PivotTable, you’ll have to choose which fields to include. Each field is just a column section from the source information. In the PivotTable Field List, check the container for each field you wish to include.

14. The selected fields will be included in one of the four regions below the Field List. In this example, the Order ID field has been added to the Rows zone, while the Amount has been added to the Values zone. On the other hand, you can click, hold, and drag a field to the desired region.

15. The PivotTable will evaluate and outline the selected fields. In this example, the PivotTable displays the amount sold by each customer.

Refreshing the Data

If we transform any of the information in our source worksheet, the PivotTable will not update automatically. To physically update it, choose the PivotTable and afterward Analyze → Refresh.

Refresh Data when opening a file

There is an alternative that can be turned on that will refresh the information when opening the document. To set an option to upgrade PivotTable data when we open our workbook automatically, do the following:

1. From the PivotTable Tools press Options Tab then within Pivot Table Group, Options command.

2. Press on the Data Tab and tick Refresh the information when opening the record.

3. Press on OK.

Pivoting Data

Probably, the best thing about PivotTables is that they can rapidly pivot or rearrange information, permitting us to look at our worksheet data in a various manner.

To change the row

1. Press, hold and drag any current fields out of the rows zone. The field will vanish.

2. Drag the another field from the Field List into the rows region. In this example, we’ll utilize the First Name field.

3. The PivotTable will change, or pivot, to display the new information.

To add columns

PivotTable has only displayed one column of information at a time. To display various columns, we will have to add a field to the Columns region.

1. Drag the field from the Field List into the Columns region. In this example, we’ll utilize the Region field.

2. The PivotTable will contain multiple columns.

Filters

Filters can be utilize to precise down the information in our PivotTable, permitting us to see only the data we need.

To add a filter

1. Drag the field from the Field record to the Filter region. In this example, we’ll utilize the First Name field.

2. The filter will occur raised the PivotTable. Press the drop-down arrow, and then check the container next to Select Multiple Elements.

3. Uncheck the container for any elements you don’t want to contain in the PivotTable. In this example, we’ll uncheck the container for a few different First Names, then Press OK.

4. The PivotTable will conform to mirror the changes.

Grouping Data

In the row and column label region of a PivotTable document, you can group the elements in a field in a custom method. Grouping the information can assist you in isolating a subset of data that fulfils your particular needs, and that cannot be easily grouped in different manners, such as sorting and filtering. You may wish to group utilizing one of the following:

• Group numbers in numeric fields
• Group dates or times
• Group selected elements

To group fields

1. Choose the information in the PivotTable.

2. Right-click on selected information, and press Group.

3. Press OK.

To group by date

1. Press any unit inside the Date segment.

2. Right-click and click on Group.

3. Select Months and press, OK.

4. The PivotTable will conform to mirror the changes.