DAY 7:MICROSOFT EXCEL – CHAPTER 4

Akhila Kondaiakhila.kondai@mail.wvu.eduSeptember 09, 2013

ANNOUNCEMENTS

• The First Assignment will be due by 09/13/2013 by 11.59pm

• MyITLab Lesson B is due on 09/16/2013 by 11.59pm

• The Second Assignment is also posted and is due on 09/20/2013 by 11.59pm

MICROSOFT EXCEL – CHAPTER 4.

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 X-axis (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..

0

10

20

30

40

50

60

1 2 3 4

Tota

l Sol

d

Quarter

Instrument Sales 2005

Drum Set Sales

Guitar Sales

Line ChartGrowth

0

5000

10000

15000

20000

25000

30000

35000

40000

45000

1965 1970 1975 1980 1985 1990 1995 2000 2005

Year

Qua

ntity

X-Y Scatter plot

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 LAYOUT ribbon.

COLUMN CHART

• A column chart displays data vertically, with each data series forming a column

• Go to my lecture notes page, download the ‘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)• Legend is a key that identifies color,

gradient, picture, texture, or pattern fill assigned to each data series in a 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.

CHART LAYOUT

• The Layout tab offers many ways to enhance a chart visually by selecting specific chart elements, inserting objects, displaying or removing chart elements, customizing the axes and formatting background.

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

NEXT CLASS

• Working with Tables and Worksheets.• Homework # 1 overview

Questions ?