Microsoft Excel Pivot Table Basics PDF

CST8118 Intro. to Comp. Prog. Pivot Table Basics  Week 10 Pivot Table Basics What is a Pivot Table? What is meant by Pivot? What are pivot tables used for? What is required of the data to use a pivot table? Not normalized vs normalized data Ensure...

CST8118 Intro. to Comp. Prog. Pivot Table Basics  Week 10 Pivot Table Basics What is a Pivot Table? What is meant by Pivot? What are pivot tables used for? What is required of the data to use a pivot table? Not normalized vs normalized data Ensure that the data is normalized Before creating a PivotTable, Create a Table Creating a Pivot Table Automatically Creating a Pivot Table Manually 2  Welcome This presentation introduces the basics of using the Pivot Table feature of Microsoft Excel. The slides in this presentation are based on. Additionally, the spread sheet files utilized come from chapter 26 of the textbook via the publisher's website and are used with permission under educational fair use. (Personal Communication Sara Veltkamp Oct 19, 2023). The original textbook files can be downloaded from this website: www.wiley.com/go/excel365bible download the Chapter 26 zip file normalized data.xlsx bank accounts.xlsx 3  What is a PivotTable? According to a PivotTable is a summary report dynamically generated from a database. The database can be data within a worksheet table or an external data file. A key benefit of a PivotTable is the ability to re-arrange information in an interactive manner to see different summary viewpoints of the data. A drawback is that if the underlying data changes, e.g., adding, removing, updating records the PivotTable will not update automatically. (however, you can request the PivotTable update itself) (Connecting to external databases (e.g., relational) data sources is outside the scope of our course, see for more information. ) 4  What is meant by Pivot? explains the word Pivot as a verb to rotate or revolve. When you work with a new physical object you likely pick it up in your hands and reposition it so you can view it from multiple angles. A pivot table lets you look at a summary of the data, and dynamically change the viewpoints to help gain a deeper understanding of what the data may represent, and to solve problems. 5  What are pivot tables used for? Pivot tables are a problem-solving tool, that help answer questions about a collection of data. For example, given a table of banking information with account type, date the account was opened, and so on, a bank might want to answer the following questions (taken from ): What is the daily total new deposit amount for each branch? Which day of the week accounts for the most deposits? How much money was used to open the accounts? What types of accounts do tellers open most often? And more 6  What is required of the data to use a pivot table? The data used as the basis for the pivot table should be formatted as a normalized list. There should be one or more columns of values that can be summarized, as well as additional columns that describe the data. Data Field A column of values that can be summed. Category Field A column that describes the data field. 7  Not normalized vs normalized data uses the Microsoft Excel file named “normalized data.xlsx” to illustrate the difference between normalized and not normalized data. The table below shows data in a cross-table format, with categories labeled from left to right in the first row across the columns, and top down in the first column. This is not normalized. State Jan Feb Mar Qtr-1 Apr May Jun Qtr-2 Total California 1,118 1,960 1,252 4,330 1,271 1,557 1,679 4,507 8,837 Washington 1,247 1,238 1,028 3,513 1,345 1,784 1,574 4,703 8,216 Oregon 1,460 1,954 1,726 5,140 1,461 1,764 1,144 4,369 9,509 Arizona 1,345 1,375 1,075 3,795 1,736 1,555 1,372 4,663 8,458 West Total 5,170 6,527 5,081 16,778 5,813 6,660 5,769 18,242 35,020 New York 1,429 1,316 1,993 4,738 1,832 1,740 1,191 4,763 9,501 New Jersey 1,735 1,406 1,224 4,365 1,706 1,320 1,290 4,316 8,681 Massachusetts 1,099 1,233 1,110 3,442 1,637 1,512 1,006 4,155 7,597 (Not all the data from the original spreadsheet is shown here) 8  Not normalized vs normalized data uses the Microsoft Excel file named “normalized data.xlsx” to illustrate the difference between normalized and not normalized data. The table below shows how the state, region, month, quarter, and sales were moved into a normalized list format: State Region Month Qtr Sales California West Jan Qtr-1 1,118 California West Feb Qtr-1 1,960 California West Mar Qtr-1 1,252 California West Apr Qtr-2 1,271 California West May Qtr-2 1,557 California West Jun Qtr-2 1,679 Washington West Jan Qtr-1 1,247 (Not all the data from the original spreadsheet is shown here) 9  Ensure that the data is normalized. Before creating a Pivot Table make sure that your data is in a table format with category and data fields (also called columns). Each row in the table should represent one record, with entries for each field. (This website is a good resource, with more examples, on how to ensure your data is structured correctly : https://www.excelcampus.com/pivot-tables/structure-pivot-table-source-data/ ) 10  Before creating a PivotTable, Create a Table In Microsoft Excel a Table is a way to visually group and analyze data. By creating a table if we delete or add rows, we will not need to manually update the pivot tables range of cells. The pivot table will have the range of cells updated along with the table it is based on. Using the spreadsheet “banking data.xlsx” which is taken after “bank accounts.xlsx” from : 1. Select the worksheet with the data, here the worksheet is named “data”, and select a cell anywhere within the worksheet that has a value. 2. Use the insert menu in the Ribbon and use the Table command. 3. The data in the worksheet will become formatted visually as a MS Excel Table. 11  Before creating a PivotTable, Create a Table 1. Select the worksheet with the data, here the worksheet is named “data”, and select a cell anywhere within the worksheet that has a value. 2. Use the insert menu in the Ribbon and use the Table command. 3. The data in the worksheet will become formatted visually as a MS Excel Table. 12  Before creating a PivotTable, Create a Table Microsoft Excel will automatically name the table, e.g., Table1, Table2 and so on in sequence. To check the name of the Table, or change the name: Select a cell in the table. Select the Table Design tab in the Ribbon Look in the left corner of the Ribbon for the field “Table Name:” 13  Creating a Pivot Table Automatically Microsoft Excel offers the ability to generate a Pivot Table Automatically after examining your data. You will be shown a gallery of possible Pivot Tables to select from, if one is close to what you need you can select it and edit it more as needed. Use the Insert tab in the Ribbon Select “Recommended PivotTables” e.g., “Sum of Amount by AcctType” Click the OK button 14  Creating a Pivot Table Automatically The PivotTable will be created in a new worksheet, in this case “Sheet1”. The Amount and AcctType fields will have been selected and placed in the Values and Rows areas. Try dragging the Amount field to the Columns area to see the PivotTable change, you can also experiment by adding more fields and / or filtering by a field. 15  Creating a Pivot Table Manually Follow these steps to get started. Using the “banking data.xlsx” spreadsheet, which is a normalized list, and has a MS Excel table created in it: 1. Select the worksheet with the data, in this case it is named “data” 2. Select the Insert tab in the Ribbon, then “PivotTable” and “From Table/Range” 3. Keep the name of the table provided, as well as the other default settings. 4. Click: OK 16  Creating a Pivot Table Manually Follow these steps to get started. A PivotTable designer will open in a new worksheet. Start by dragging the Amount field (the data field) into the Values area to create a summary. Then select the other fields you would like to break down the summary by categories. Attempt to answer the question: “Which day of the week accounts for the most deposits?” 17  Creating a Pivot Table Manually Attempt to answer the question: “Which day of the week accounts for the most deposits?” Setting Amount as the Value, and Weekday as a Row, the PivotTable shows that Friday is the day of the week with the most deposits, having 1718148 for that day. You can experiment with the other category fields as columns or rows to further examine the data. 18  Conclusion In this lesson we reviewed: What is a Pivot Table? What is meant by Pivot? What are pivot tables used for? What is required of the data to use a pivot table? Not normalized vs normalized data Ensure that the data is normalized Before creating a PivotTable, Create a Table Creating a Pivot Table Automatically Creating a Pivot Table Manually 19  References Michael Alexander and Dick Kusleika. 2022. Microsoft Excel 365 Bible. 1st Ed. John Wiley & Sons, Inc., Hoboken, New Jersey. Print ISBN: 978-1-119-83510-3. Ch 26 Create a PivotTable with an external data source. support.microsoft.com. https://support.microsoft.com/en-us/office/create-a-pivottable-with-an-external-data -source-db50d01d-2e1c-43bd-bfb5-b76a818a927b (Accessed Oct 22, 2023) Jon Acampora. How to Structure Source Data for a Pivot Table & Unpivot. excelcampus.com. https://www.excelcampus.com/pivot-tables/structure-pivot-table-source-data/ (Accessed Oct 22, 2023) Microsoft Corporation. Create and format tables. Microsoft Excel (365 edition) 20 built-in help system.  Recommended Learning Resources Recommended Course Textbook: Michael Alexander and Dick Kusleika. 2022. Microsoft Excel 365 Bible. 1st Ed. John Wiley & Sons, Inc., Hoboken, New Jersey. Print ISBN: 978-1-119- 83510-3. Ch 26 Via the School Library, in LinkedIn Learning, for free: Dave Ludwig. Excel: PivotTables for Beginners: Excel PivotTables made easy. https://www.linkedin.com/learning/excel-pivottables-for-beginners/excel-pivo ttables-made-easy Dennis Taylor. Excel Essential Training (Microsoft 365): Getting started with Excel for Microsoft 365 https://www.linkedin.com/learning/excel-essential-training-microsoft-365-17 21 231101/getting-started-with-excel-for-microsoft-365