Trend Lines, Pivot Tables, and Pivot Charts Objectives Create a - - PowerPoint PPT Presentation

▶

Oct 07, 2022 155 likes •322 views

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

SLIDE 1

Trend Lines, Pivot Tables, and Pivot Charts

SLIDE 2

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

Objectives

SLIDE 3

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

SLIDE 4

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

SLIDE 5

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES

SLIDE 6

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES

SLIDE 7

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES

Sales totals are sorted by state, then by manager. This is because manager is dragged to above state in the rows area.

State above and Manager below

SLIDE 8

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES

Sales totals are sorted by manager, then by state. This is because manager was merely dragged to above state in the rows area.

Manager is now above and state below

SLIDE 9

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

SLIDE 10

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

SLIDE 11

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

SLIDE 12

Adding Data to the Source and the Effect

n 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

SLIDE 13

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.

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES

Refresh Button used to update a pivottable

SLIDE 14

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

SLIDE 15

ACADEMIC/EXCEL CLASSES/2016/POWERPOINT SLIDES/CHAPTER 8 POWERPOINT SLIDES ON PIVOT TABLES