Pivot Tables in Excel What is a Pivot
Pivot Tables in Excel
What is a Pivot Table? • A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report. • The tool does not actually change the spreadsheet or database itself, it simply “pivots” or turns the data to view it from different perspectives. • https: //searchsqlserver. techtarget. com/definition/pivot-table
How to make a Pivot Table in Excel • Click somewhere in the data that you want to summarize • Click on the top-level Insert menu • Click on Tables in the ribbon • Excel will show you some suggested Pivot Tables which you can use • Or you can create your own Pivot Table • Excel lets you select the data to use • The location of the Pivot Table – usually a new sheet
Making a Pivot Table • You get the Fields pane next • It lists all the fields in the data and four different areas • The areas are Columns, Rows, Values and Filters • Typically you use the numbers in the data for the Values • The names or ID numbers are the Rows or Columns • You can sort the table by clicking somewhere in the column you want to sort by, then right-clicking and choose Sort from the menu. Lastly choose the order: ascending or descending
Changing the Arithmetic done • By default there will be a Sum column on the right side • If you want to change the operation, • Right click on the Sum column • Click on Value Field Settings • Choose from Sum, Count, Average, Max, Min, etc.
Important Note about Pivot Tables • You save your pivot tables with the sheets in the xlsx file as expected • IF you make changes to your data (back in Sheet 1), the Pivot Table is NOT updated. • You have to explicitly tell Excel to update the Tables • In the Top-Level menu, when a pivot table is active (you’ve clicked on it), choose Analyze • In the ribbon, choose Refresh to make sure your Tables have the upto-date data in them
A simple tutorial on Pivot Tables • https: //www. excel-easy. com/data-analysis/pivot-tables. html
- Slides: 7