Transcript Day 8:MICROSOFT EXCEL – CHAPTER 5

DAY 8:
MICROSOFT EXCEL – CHAPTER 5
Aliya Farheen
[email protected]
February 5, 2015.
REMINDERS
• The First Assignment will be due on
2/6/2015 by 11.59pm.
• MyITLab Lesson B is due on 2/9/2015 by
11.59pm.
• The Second Assignment is due on
2/13/2015 by 11.59pm.
MICROSOFT EXCEL –
CHAPTER 5.
CHART
• Chart gives visual representation of Excel
sheet workbook
• They allow us to interpret large amount of
data easily and quickly.
CHART COMPONENTS
• Data Points - A data point is a numeric value that
describes a single value on a chart.
• Data Series - A data series is a group of related
data points.
• Category Labels - A category label is text that
describes a collection of data points in a chart.
• The chart area contains the entire chart and all of
its elements. The plot area contains the graphical
representation of values in the data series. The Xaxis (Category axis) is a horizontal line. The Y-axis
(Value axis) is a vertical line.
CHART COMPONENTS
BASIC CHART TYPES
• Pie – Compares parts with the whole
• Column – Shows/compares values across
different categories
• Line charts – Shows trends over time
• X-Y Scatter chart – compares pair of
values, usually looking for a trend
• Other types: Area chart, Stock chart,
Surface chart, Doughnut chart, Bubble
chart, Radar chart
WHAT THEY LOOK LIKE
Pie
Column chart
WHAT THEY LOOK LIKE..
Line Chart
X-Y Scatter plot
Instrument Sales 2005
Growth
60
45000
40000
50
35000
30000
Drum Set Sales
30
Guitar Sales
Quantity
Total Sold
40
25000
20000
15000
20
10000
10
5000
0
1
2
3
Quarter
4
0
1965
1970
1975
1980
1985
Year
1990
1995
2000
2005
CHOOSING THE CORRECT
CHART TYPE
• Choose the chart type that best fits the data
• For comparing values from different
categories, use
– Column, Bar, or Line Chart
• For comparing individual values to a whole
collection, use
– Pie or Doughnut Chart
CREATING A CHART
• Three main steps to create a chart
– Select the Data Source that is the range of
cells to chart
– Select the chart type
– Position and size the chart
• You can change the properties of chart
using several options available in the
ribbon.
COLUMN CHART
• A column chart displays data vertically,
with each data series forming a column.
• Example:‘majors’ Excel file and open it.
• Select the cells A4:A11 and E4:E11
• Insert Tab -> Column chart in Charts
section
CLUSTERED COLUMN CHART
• Groups or clusters similar data in columns
to compare values across categories.
• Select the cells A4:E11
• Insert Tab -> Column chart in Charts
section (Clustered column chart)
STACKED COLUMN CHART
• Places stacks of data in segments on top
of each other in one column, with each
category in the data series represented by
a different color.
• Select the cells A4:E11
• Insert Tab -> Column chart in Charts
section (Stacked column chart)
• Right click on the chart and select “Select
Data” and click “ Switch row/columns ”
100% STACKED COLUMN CHART
• Compares the percentage that each data
point contributes to the total for each
category.
• Select the cells A4:E11
• Insert Tab -> Column chart in Charts
section (100% Stacked column chart)
• Right click on the chart and select “Select
Data” and click “ Switch row/columns ”
BAR CHART
• Bar chart compares values across
categories using horizontal bars.
• Width of the bar indicates its value.
• Create a Bar Chart for the given Majors
work sheet
LINE CHART
• Line chart uses a line to connect data
points in order to show trends over a
period of time.
• Different color for each data series.
• Create a Line Chart for the given Majors
work sheet.
PIE CHART
• Pie chart shows each data point in
proportion to the whole data series as a
slice in a circular pie.
• To create a pie chart for 2012, Select the
cells A4:A11 and E4:E11
• Insert Tab -> Pie chart in Charts section
SEPARATING PARTS OF PIE
(EXPLODING PIE)
• Single Part Separation/Explosion
– Make two slow clicks on one of the pieces
– Left click and drag it outward
• Full chart explosion/Separation
– Make sure nothing is selected
– Hold down the left button on one of the pieces
and drag it outward
CHANGING THE CHART TYPE
– Click the Design tab
– Click Change Chart Type in the Type section
– Select the desired chart type and click OK
– For example you can change a stacked
column chart to 100% stacked column chart
CHANGING THE DATA SOURCE
MOVING A CHART
• To move a chart:
– Click the Design tab
– Click Move Chart under the Location group
PRINTING A CHART
• To print an embedded chart:
– Select the chart
– Click the File tab
– Click Print to display the Backstage view
– Use the Print Selected Chart option
• To print a chart sheet:
– Use the Print Active Sheet option
CREATING A SPARKLINE
• A Sparkline is a miniature chart displayed in a
single cell
• To create a sparkline
―Click the Insert Tab
―Click Line, Column, or Win/Loss in the
Sparklines group.
FORMATTING CHART
ELEMENTS
• Formatting a chart element:
– Right-click the element and select the Format
element command
– The element portion will change depending on
the selection
CUSTOMIZING CHART LABELS
• A chart title is the label that describes the
entire chart
• An axis title is a label that describes either
the category or value axis
• A legend is used to distinguish data points in
a pie chart or data series in a multiple series
chart
• A data label is the value or name of a data
point
• A data table is a grid that contains the data
source values and labels.
AXES AND GRIDLINES
• Based on the data source values, Excel
computes starting, ending, and
incremental values for display on the value
axis.
• A gridline is a horizontal or vertical line
through the plot area.
ADDING A TRENDLINE
• A trendline is a line used to depict trends
and forecast future data.
TRENDLINES
• Trend Types
– Linear
– Exponential
– Logarithmic
– Polynomial
– Power etc.,.
• Forecast
• R-Squared Value