13 3 Advanced – Advanced Formulas, Functions and Macros (4/10) -- Beginning to Intermediate Excel

13 3 Advanced – Advanced Formulas, Functions and Macros Pivot tables and macros Outcomes: Analyze worksheet data using a trendline •Create a PivotTable report •Format a PivotTable report •Apply filters to a PivotTable report •Create a PivotChart report •Format a PivotChart report •Apply filters to a PivotChart report •Analyze worksheet data using PivotTable and PivotChart reports •Create calculated fields •Create slicers to filter PivotTable and PivotChart reports •Format slicers •Examine other statistical and process charts •Create a Box and Whisker Chart Introduction In both academic and business environments, people are presented with large amounts of data that need to be analyzed and interpreted. Data are increasingly available from a wide variety of sources and gathered with ease. Analysis of data and interpretation of the results are important skills to acquire. Learning how to ask questions that identify patterns in data is a skill that can provide businesses and individuals with information that can be used to make decisions about business situations. Below is a video about creating Pivot tables. Below is a video about creating Excel slicers. A trendline is a line that represents the general direction in a series of data. Trendlines often are used to represent changes in one set of data over time. Excel can overlay a trendline on certain types of charts, allowing you to compare changes in one set of data with overall trends. In addition to trendlines, PivotTable reports and PivotChart reports provide methods to manipulate and visualize data. As an interactive view of worksheet data, a PivotTable report lets users summarize data by selecting and grouping categories. When using a PivotTable report, you can change, or pivot, selected categories quickly without needing to manipulate the worksheet itself. You can examine and analyze several complex arrangements of the data and may spot relationships you might not otherwise see. For example, you can look at years of experience for each employee, broken down by type of accounting service, and then look at the yearly revenue for certain subgroupings without having to reorganize your worksheet. A PivotChart report is an Excel feature that lets you summarize worksheet data in the form of a chart, and rearrange parts of the chart structure to explore new data relationships. Also called simply Pivot Charts, these reports are visual representations of PivotTables. For example, if a company wanted to view a pie chart showing percentages of total revenue for each service type, a PivotChart could show that percentage categorized by city without having to rebuild the chart from scratch for each view. When you create a PivotChart report, Excel creates and associates a PivotTable with that PivotChart. Slicers are graphic objects that you click to filter the data in PivotTables and Pivot Charts. Each slicer button clearly identifies its purpose (the applied filter), making it easy to interpret the data disp