Trend Lines, Pivot Tables, and Pivot Charts
Objectives Create a line chart and trendline Create a pivottable Change the layout and view of a pivottable Filter a pivottable Format a pivottable Create a pivotchart Change a pivotchart view and contents Add slicers ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
What are trendlines ? Trendlines illustrate economic performance in the form of a line chart They allow you to make economic forecasts based on past economic performances They do not take into account earthquakes, hurricanes, or any external factors that would affect your business They show the tendency of one set of data to change in relation to another set of data. Example: temperature and number of people at the beach ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
What are Pivottables? ◦ Pivottables are often referred to as pivottable reports ◦ We will call them pivottables ◦ They allow you to: ◦ summarize your data in different ways ◦ view your data in different ways ◦ filter the data you see ◦ They are usually based on databases, but do not have to be ◦ Much of what you do in pivottables you can already do in databases, but Pivottables are faster ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
State above and Manager below Sales totals are sorted by state, then by manager. This is because manager is dragged to above state in the rows area. ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Manager is now above and state below Sales totals are sorted by manager, then by state. This is because manager was merely dragged to above state in the rows area. ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Pivot tables Pivottable fields are drawn from columns in the worksheet data Fields can be one of two types: ◦ Data ◦ Values that the PivotTable will summarize ◦ Data fields correspond to summary values of original data across categories ◦ Category ◦ Describes the data by categorizing it ◦ Category fields correspond to columns in the original data ◦ Other Information-changing the arrangement ◦ You can pull data from other Excel files or Access databases ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
PivotCharts What are pivotchart reports? They are charts generated from the numbers in a supporting pivottable You cannot create a pivotchart in XY (scatter), bubble, or stock formats Many of the charting tools are available for pivotcharts ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Pivotcharts must be supported by Pivottables How it works . . . Just as you cannot use a side car without a motorcycle, you cannot generate a pivotchart without a pivottable. So . . . If you try to select a range of numbers and tell Excel that you want a pivotchart generated from them, this is what Excel will do: Since Excel cannot build a pivotchart without a supporting pivottable , Excel will ask if you want either: (1) a pivottable and a pivotchart (motorcycle and side car) OR (2) just a pivottable (just a motorcycle) ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Adding Data to the Source and the Effect on the Pivottable Excel will not add the information from new rows to the pivottable unless your source is a database. If your source is a database, THEN you can add records to the source and be assured that they will be included in the pivotable when you refresh . So, build your pivottable from a database to start with. However, remember that even if you use a database as a source, Pivottables still do not automatically update. You will have to refresh to include the new rows in a database ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Refreshing Pivottables Pivottables do not automatically update; when the source data changes, you must manually refresh the pivottable yourself using the Refresh button. For example, a change in the price of an item. Refresh Button used to update a pivottable ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Slicers What is a slicer? A slicer allows you to filter the data – this should remind you of database work that we did in this class. It is very much faster because pivottables require fewer steps You do not have to set up criteria and extraction areas. Slicers make it possible for other people to filter data in your pivottables without knowing how to use pivottables ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Slicers ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES
Recommend
More recommend
