Excel- Chapter 12: Creating Charts

Purpose of PivotTable reports and PivotCharts

-Analyze large amounts of data -subtotal and gather numeric data, summarize data by categories and subcategories, and create custom calculations and formulas -expand and collapse levels of data to filter 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

Modifying a Chart's legends

-Modify the content of the legend -change the position of the legend relative to the chart -expand or collapse the legend box -Edit the text that is displayed -change character attributes

To create a basic chart

-enter the data for the chart on a worksheet -select that data and choose a chart type to graphically display the data

to identify data for your chart

1. select multiple ranges at one time that will become different chart elements 2. identify the chart type and then select the data for each chart element

amount of chart types

16 with numerous subtypes and combocharts

legend

A box that identifies the patterns or colors that are assigned to the data series or categories in a chart

Combined chart

A chart with multiple data series can have a different chart type for any single data series

Axis

A line bordering the chart plot area used as a frame of reference for measurement

If the element isn't visible

Add it by checking the chart elements option or inserting a text box

Modifying a Chart

Add or delete individual elements or by moving or resizing the chart Change the chart type without deleting existing chart or change how Excel selects data as its data elements by changing rows to columns

Formatting tab of QuickAnalysis Gallery

Allows you to format the cell data in different ways such as by color or etc.

PivotChart

An essential tool to help organize and arrange large amounts of data from worksheets. Can also visualize information in a simple chart

Quick Analysis tool

Appears when a data range is selected Allows you to quickly create charts, add spark lines, work with totals, format data with conditional formatting, create PivotTables

2-D and 3-D charts

Can't be combined.

To select elements you want to format

Click the arrow next to the Chart Elements box in the Current Selection group on the Format tab

To format a chart element

Click the chart element you want to change and then use the appropriate commands from the format tab

To edit existing titles or labels

Click the label, select the text, type the new text

title

Descriptive text that is aligned to an axis or at the top of a chart

PivotTable Fields pane of PivotTable Tools Tab

Display when you select any cell in the PivotTable and include options to perform additional tasks as you work with and improve a PivotTable report

Adding Data Labels

Helps make a chart more understandable

Chart elements that can be manually formatted

Legend, data labels, chart title, chart area, vertical/horizontal axis, plot area, data series

Chart's border line

Outline around a chart element

To resize a chart

Point to a corner of a chart or the midpoint of any side to display sizing handles, which are two-sided arrows Use the size handles to change the chart height or width Use the corner sizing handles to change both height and width

format tab

Provides a variety of ways to format chart elements

Adding elements to a chart

Provides more information to a chart

Quick Analysis tool with Totals

Quickly adds SUM, AVERAGE, and COUNT functions and % of Totals and Running Totals to bottom row or to the right of the data

PivotTable

Report that analyzes and displays the numerical data in detail and to answer unforeseen questions about data

data series

Row or column of data represented by a line, set of columns, bars or other chart type

To delete elements from a chart

Select an element on the chart and press the delete key Or select an element in the Chart Elements drop-down in the Current Selection group and press Delete

To apply a border around the entire chart

Select an element or the chart and use the colored outlines in the Shape Styles group on the Format tab, or click Shape outline and choose a color

To create an outline around a chart element

Select the element and apply one of the predefined outlines or click Shape Outline to format the shape of a selected chart element

To format a data series

Select the element to format, click on one of the buttons on the ribbon or display the Format pane to add fill color or a pattern to the selected chart element

Data labels

Text that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.

plot area

The area bounded by the axes

When you use the mouse to point to an element in the chart

The element name appears in a ScreenTip

chart area

The entire chart and all its elements.

When you click the arrow

The list includes all elements that you have included in the displayed chart

Commands on the Format tab

To add or change fill colors or patterns applied to chart elements

PivotTable report and PivotCharts

Ways to quickly condense and rearrange large amounts of data

data marker

a bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell

chart

a graphical representation of numeric data in a worksheet

chart sheet

a sheet that contains only a chart

design buttons on the right of a selected chart

allow you to change the style and color and select which elements appear on the chart

chart styles

allows you to choose which style and color you want for your chart

to quickly change a chart's appearance

apply a predefined style or layout

surface chart usual data arrangement

both categories and values are numeric values

create Excel's common chart types

by clicking its image on the Insert tab of the ribbon

when you insert a chart

by default it is embedded in the worksheet

data arranged in columns or rows on a worksheet

can by plotted in a bar chart

doughnut chart usual data arrangement

categories are colors of circular bands and the size of the bands are the values of each band

bar chart usual data arrangement

categories or time are along the vertical axis and values are along the horizontal axis

area chart usual data arrangement

categories or time are on the horizontal axis and values are on the vertical axis

Column chart usual data arrangement

categories or time are usually on the horizontal axis and values are on the vertical axis

sizing handles

change size of a chart

embedded chart

charts placed on the worksheet rather than on a separate chart sheet

to move the chart

click in the white space and drag, using the four-headed black mouse pointer

clustered bar charts

compare values across categories

charts group on the insert tab

contains nine buttons leading to multiple chart types

insert tab

contains the command groups needed to create charts in Excel

stacked column

data markers are stacked so that the top of the column is the total of the same category from each data series

chart elements button

displays which items appear on the chart

2-D or 3-D column charts

each data marker is represented by a column

chart filters

enables you to filter your chart data to see only a portion of the source data charted

when you create a chart, the chart tools tab becomes available and the Design and Format tabs and Quick Layout button appear on the ribbon

enabling you to format a chart with a quick style or layout

column charts

facilitate comparisons among items and over time periods

radar chart usual data arrangement

first column is label of spike. First row is label of units. Values for each unit go down each column starting in the second column after the row labels.

bubble chart usual data arrangement

first value is horizontal distance, second value is vertical distance, and third value is the size of bubble

stock chart usual data arrangement

for each time period, there are three to five numbers

recommended charts button

helps narrow the choices of picking a chart depending on the data that you select

side-by-side bar charts

illustrate two views of the same data

data series

made up of related data markers

chart's data selection

must contain sufficient information to interpret the data at a glance

pie chart usual data arrangement

only one data series and none of the values are negative or are zero

data values

represented by graphs with combinations of lines, vertical or horizontal rectangles (columns and bars), points, and other shapes

click the charts dialog box launcher to open the Insert Chart dialog box

select from any chart type

stacked bar charts

show the relationship of individual items to the whole of that item

line charts

show trends over time

line chart

shows points connected by a line for each value

pie chart

shows values as part of the whole

bar charts

similar to column charts and can be used to illustrate comparisons among individual items

data in charts

some charts require a specific data arrangement

when you select data and create a pie chart

the chart is placed on the worksheet

when you click a chart type in the left pane

the first of that type is selected in the right pane

XY (Scatter chart) usual data arrangement

the independent variable is usually on the horizontal axis and the dependent variable is on the vertical axis

when you expand the selection to include row labels

then numeric data will be displayed as a label in a chart, not data

line chart usual data arrangement

time in equal units on horizontal axis and values on vertical axis

Sparklines

tiny miniature graphs

click a corner of a chart or the midpoint of any side

to display sizing handles, which are two-sided vertical, horizontal, or diagonal white arrows

all charts tab in Insert Chart dialog box

to see sample of all charts and subtypes

combo chart

two or more chart types, such as line and column, depicted in a single graphic

pie chart

useful for comparing the size of items in one data series and how each slice compares with the whole. Data points are displayed as a percentage of a circular pie.

bubble chart

useful for comparing three sets of values

column chart

useful for comparing values across categories or a time period. Data points are vertical rectangles

doughnut chart

useful for displaying the relationship of parts to a whole. Can contain more than one data series. Values are represented as sections of a circular band.

area chart

useful for emphasizing magnitude of change over time. Shows relationships of parts to the whole. Values represented as shaded areas.

surface chart

useful for finding optimum combinations between two sets of data. The resulting plot looks similar to a topographic map or piece of cloth draped over points

bar chart

useful for illustrating comparisons among individual items when axis labels are long. Values are represented as horizontal rectangles

stock chart

useful for illustrating the fluctuation of stock prices or scientific data when there is a start, end, high, and low value during each period

radar chart

useful for showing multiple variables for each subject, standardized to the same scale. Represents values as points that radiate on spikes from the center

XY (Scatter) Chart

useful for showing relationships of one numeric set of data against another numeric set of data to see whether there is a correlation between two variables

line chart

useful for showing trends in data at equal intervals. displays continuous data over time set against a common scale. Values are represented as points along a line.