1

Microsoft Excel 2016

Lecture 7
Charts
Pivot Table

Lecturer: Dr. Huynh Vo Trung Dung

 Building Charts

– A chart is a graphical representation of numeric data in a worksheet.

– Data values are represented by graphs with combinations of lines, vertical or
horizontal rectangles (columns and bars), points, and other shapes.
– When you want to create a chart or change an existing chart, you can choose from
16 chart types with numerous subtypes and combo charts.
– These include five new chart types offered in Excel 2016—Treemap, Sunburst,
Histogram, Box & Whisker, and Waterfall.
– The table on the next slides gives a brief description of the most commonly used
Excel chart types.
2
Building Charts

3
Building Charts

4
 Step by Step: Select Data to
Include in a Chart
– LAUNCH Excel.
1. OPEN the 12 Fourth Coffee Financial History file for this lesson.
2. Select B2:B8 (the 2014 data).
3. Click the Insert tab, and in the Charts group, click the Pie button. Click the first
2-D Pie chart in the drop-down menu. A color-coded pie chart with sections
identified by number is displayed.
4. Move the mouse pointer to the largest slice. The ScreenTip shows Series 1
Point 1 Value: 2014 (39%), as shown in the figure on the next screen. This
corresponds to the label 2014 rather than actual data.

5
Step by Step: Select Data to
Include in a Chart

6
 Step by Step: Select Data to
Include in a Chart

5. Point to the second largest slice and you’ll see that the value is 1575, which is
the amount for the total. Neither the column label (2014) nor the total sales
amount should be included as pie slices.
6. Click in the chart’s white space and press Delete. The chart is now removed from
the worksheet.
7. Select B3:B7. Click the Insert tab and, in the Charts group, click Pie and then click
the first 2-D Pie chart. The correct data is displayed, but the chart is difficult to
interpret with only numbers to identify the parts of the pie.
8. Click in the chart’s white space and press Delete.

7
 Step by Step: Select Data to
Include in a Chart
9. Select A2:B7, click the Insert tab, and click Pie in the Charts group. Click the
first 2-D Pie chart. As illustrated below, the data is clearly identified with a title
and a label for each colored slice of the pie.

8
Step by Step: Select Data
to Include in a Chart

10. Move the mouse pointer to a blank spot within the chart
and drag the chart to move it below the data.
11. Click outside of the chart, click File, and then click Print.
Notice that the Annual Sales data appears with the chart on
the page.
12. Press Esc and then click on the Chart and choose File, Print.
Now notice that the chart appears by itself on the page.
13. CREATE an Excel Lesson 12 folder and then SAVE the
workbook as 12 Charts Solution.
– LEAVE the workbook open for the next exercise.

9
Choosing the Right Chart for Your
Data
– You can create most charts from data that you have arranged in rows or columns
in a worksheet.
– Some charts require a specific data arrangement.
– A single pie chart cannot be used for comparisons across periods of time or for
analyzing trends.
– The column chart works well for comparisons.
– In a 2-D or 3-D column chart, each data marker is represented by a column.
– In a stacked column, data markers are stacked so that the top of the column is
the total of the same category (or time) from each data series.
– A line chart shows points connected by a line for each value.
10
 Step by Step: Choose the Right
Chart for Your Data
– USE the workbook from the previous exercise.
1. Select cells A2:F7.
2. Click the Insert tab, and in the Charts group, click Insert Column or Bar Chart.
In the drop-down list, move to each of the options. When you pause on an
option, Excel shows a preview of the chart on the worksheet and a
description and tips for the selected chart type. Under 3-D Column, move to
the first option. As shown in the figure on the next slide, the ScreenTip shows
that the type of chart is a 3-D Clustered Column. Excel suggests using this
chart type to compare values when the order of categories is not important.

11
Step by Step: Choose the Right
Chart for Your Data

12
Step by Step: Choose the
Right Chart for Your Data

3. In the drop-down list, click 3-D Clustered Column. The column

chart illustrates the sales for each of the revenue categories for the
five-year period. The Chart Tools tab appears with the Design tab
active.
4. Anywhere in a blank area on the chart, click and drag the chart
below the worksheet data and position it at the far left.
5. Click outside the column chart to deselect it. Notice that the Chart
Tools tab disappears.
6. Select A2:F7, click the Insert tab, and in the Charts group, click Line
(first chart in the second row). In the 2-D Line group, click the Line
with Markers option. Position the line chart next to the column
chart. Refer to the figure on the next slide.

13
Step by Step: Choose the
Right Chart for Your Data

14
Step by Step: Choose the
Right Chart for Your Data

7. Click the column chart and click the Design tab.

8. Click the Move Chart button and in the New sheet box,
type Column and then click OK.
9. Click the Sales worksheet tab, select the line chart, click
the Move Chart button, and in the New sheet box, type
Line. Click OK.
10. SAVE the workbook.

15
Creating PivotTables and
PivotCharts

– A PivotTable report and PivotCharts are ways to quickly condense and

rearrange large amounts of data.
– Use a PivotTable report to analyze and display the numerical data in detail and
to answer unforeseen questions about your data.
– A PivotTable report and PivotCharts are especially designed for:
• Analyzing large amounts of data in many different ways.
• Subtotaling and gathering numeric data, summarizing data by categories
and subcategories, and creating custom calculations and formulas.
• Expanding and collapsing levels of data to filter your results, and drilling
down finer points from the summary data for areas of importance.
• Moving rows to columns or columns to rows to examine different
summaries of the data.

16
Step by Step: Create a
Basic PivotTable

– PivotTable reports are used to examine and analyze related

totals. Examples are calculating a long list of figures or
comparing several facts about each piece of numerical
data.
– OPEN 12 School Test Data from the student data files.
1. Click cell A1. Press Ctrl + End and then press the down
arrow. Notice that there are 139,129 rows of data.
2. Press Ctrl+Home to return to the top of the worksheet.
3. Click the Insert tab, and then click the Recommended
PivotTables button, choose the last option!
17
Step by Step: Create a
Basic PivotTable

4. Scroll to the bottom and click Count of ScaleScore by

Proficiency Level (see below).

18
Step by Step: Create a
Basic PivotTable

5. Click OK and name the new sheet Count. The PivotTable

Fields pane opens on the right side of your screen and
the data appears on the worksheet. Notice that the data
for No Score is blank. That is because the count of the
rows is based on the Scale Score, which is empty for
unavailable scores. You will want to change the field to
count to a field that has data. If you look back on the
TestData tab, every row is filled by a grade so you can use
the Grade column so that every row is counted.
6. Return to the Count sheet and drag the Grade field in the
PivotTable Fields pane down to the VALUES section.

19
Step by Step: Create a
Basic PivotTable

7. Drag the Count of ScaleScore from the VALUES section into

the worksheet to remove it. Notice that the No Score row
now counts each missing score.
8. Drag the Grade field to the COLUMNS area. You’ll see each
grade summarized.
9. Drag the Test field to the FILTERS area.
10. Cell B1 currently shows (All). Click the Filter drop-down
arrow, choose Math, and click OK.
11. Click the Filter drop-down arrow again and choose Reading.
Click OK. Your data should look similar to the figure on the
next slide.

20
Step by Step: Create a
Basic PivotTable

21
Step by Step: Create a
Basic PivotTable

12. SAVE the workbook to the Excel Lesson 12 folder as 12

Test PivotTable Solution.
– LEAVE the workbook open for the next exercise.
– You can perform additional tasks as you work with and
improve a PivotTable report.
– Take time to explore these options for PivotTables on your
own.
– Most of these options are available on the PivotTable Fields
pane or the PivotTable Tools tabs that display when you
select any cell in the PivotTable.
22
Step by Step: Add a
PivotChart

– A PivotChart is an essential tool to help organize and arrange large

amounts of data from worksheets. In addition to summarizing a huge
amount of data, you can visualize the information in a simple chart.
– USE the workbook from the previous exercise.
1. On the TestData worksheet, click cell A1.
2. On the Insert tab, click the PivotChart button arrow and then
choose PivotChart. The Create PivotChart dialog box opens and the
range is selected.
3. The default location is for a New Worksheet, so with that option
selected, click OK. Name the new sheet tab PivotChart.
4. In the PivotTable Fields pane on the right side of the worksheet,
drag the Test field to the FILTERS area.

23
Step by Step: Add a
PivotChart

5. Drag Grade to the VALUES area (count number of items).

6. Drag Grade again to the AXIS area.
7. Drag Proficiency Level to the LEGEND area.
8. MOVE the chart to the left of the worksheet, below the data.
9. Click the Test drop-down arrow on the chart, choose Science and
click OK. Notice that only 5th, 8th, and 10th grades are available
because only those grades take the Science test.
10. Click the Format tab, click the Text Box button in the Insert Shapes
group, and then click the top of the chart. Add a label that says
Student Science Test Scores and make this label Bold and 18 points.
Drag a border of the text box to reposition it, as necessary.

24
Step by Step: Add a
PivotChart

11. Click cell A3 and change the label to just say Count.
12. In cell F4, click on the label for Unsatisfactory.
13. Move the mouse pointer to the left edge of the cell until
the mouse pointer changes to a four-headed black arrow
and drag the mouse between columns C and D. Release
the mouse button to drop the data in the new location.
14. Repeat Step 13 and move the Advanced column to
display between the Proficient and Grand Total columns.
15. Move and resize the PivotChart so that it appears within
the range A10:G26.
25
 Step by Step: Add a
PivotChart
16. SAVE the workbook as 12 Test PivotChart Solution. Your
final PivotChart sheet should look like that shown in the
figure below.
– CLOSE the workbook and CLOSE Excel.

26
EX01
28 EX02