Excel Chapter 3: Creating and Editing Charts

Trendline

- A chart element that plots patterns using a moving average of the current data. - It uses a straight or curved line and can extend past the data to predict future averages. -Some charts cannot display a trendline, such as a stacked chart, a pie chart, or a 3D chart. - A basic linear trendline is appropriate for values that tend to increase or decrease as time passes. - A trendline traces data for one data series.

Data Table

- A columnar display of the values for each data series in a chart, located just below the chart. - When your readers do not have access to the source data for a chart, a data table can supply valuable information.

Waterfall Chart

- A financial chart that displays a moving total for positive and negative values. - Graphs how each expense or outlay affects the account. - Plots one data series and has a legend that clarifies the increase, decrease, and total colors. - Can also include axes, titles, gridlines, and data labels.

Chart Style

- A preset combination of colors and effects for a chart, its background, and its elements. - Are available for a chart are based on the current workbook theme.

Filter

- A requirement or condition that identifies which data is shown and which is hidden. - Do not change the underlying cell range for a chart, but they enable you to focus on particular data.

Chart Object

- A selectable item surrounded by a square border that is separate from worksheet data. - Contains chart elements such as titles, axes, and gridlines, and each element is selectable, too.

Chart Element

- A separate, clickable, editable object. - The chart layout and style affects which elements are initially displayed, but you can add, remove, format, size, and position elements as you design a chart.

Chart Layout

- A set of elements and the location of those elements. - Elements are individual parts of a chart such as a main title, a legend, and axis titles.

Chart

- A visual representation of numeric data in a worksheet. - Helps you identify trends, make comparisons, and recognize patterns in the numbers. - Dynamic and linked to the data, so when values in the worksheet change, the chart is automatically redrawn.

Shape Fill

- Color that fills a shape or graphic object. - The background color.

Chart Sheet

- Excel chart that is displayed on its own sheet in the workbook. - Does not have rows, columns, and cells, but the chart is linked to its data on the source worksheet.

Combination Chart

- Has at least two data series, each graphed in its own chart type. - Keep this type of chart relatively simple, because its purpose is to compare unlike items and too many data series complicate what viewers see. -You can display values on two vertical axes. - The axis on the left is the primary axis; the one on the right is secondary. - This option is best when the values are very different or use a different scale. - Includes at least two chart types such as a line chart and a column chart.

Sparklines

- Miniature charts in a cell or cell range. - They can be used to illustrate trends and patterns without adding a separate chart object or sheet. - They do not have the same characteristics as an Excel chart. - Are created from a selected data range and placed in a location range, usually next to the data. - They are embedded in the cell, almost like a background. - If you enter a label or value in a cell with a sparkline, that data appears on top of the sparkline.

Excel Chart Types: Histogram

- Purpose: Column-style chart that shows frequencies within a distribution. - Data Example: Number of students in each of five grade categories for an exam - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Waterfall

- Purpose: Plots each element in a running total and displays negative and positive effects of each on the total. - Data Example: Banking or savings account register - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Funnel

- Purpose: Plots values that progressively decrease from one stage or process to the next. - Data Example: Job applications received, applications selected, initial interviews, team interviews, HR interview, job offer. - Categories (Labels): Vertical axis - Values (Numbers): Horizontal axis

Excel Chart Types: Combo Chart

- Purpose: Uses two types of charts to graph values that are widely different. - Data Example: Line chart for number of monthly web site visits and a column chart for monthly sales of online golf gloves - Categories (Labels): Either - Values (Numbers): Either

Excel Chart Element Types: Chart Area

Background for the chart; can be filled with a color, gradient, or pattern.

Excel Chart Element Types: Chart Floor

Base or bottom for a 3D chart.

Shape Effects

Command used to effect options which include shadows, glows, bevels, or soft edges.

Marker

Data point in a sparkline.

Excel Chart Element Types: Data Marker

Element that represents individual values. The marker is a bar, a column, a slice, or a point on a line.

Selection Handle

Four-pointed arrow that selects and moves objects.

Excel Chart Types: Sunburst

- Purpose: Displays a hierarchical view of data with concentric rings. The top hierarchy is the inner ring and each outer ring is related to its adjacent inner ring. - Data Example: Tablet sales by screen size, continent, country, and city - Categories (Labels): NA - Values (Numbers): NA

Excel Chart Types: TreeMap

- Purpose: Displays a hierarchical view of data with different sized and colored rectangles and sub-rectangles to compare the sizes of groups. - Data Example: Soda sales by product name, continent, country, and city. - Categories (Labels): NA - Values (Numbers): NA

Excel Chart Types: Bar

- Purpose: Displays comparisons among individual items or values at a specific period of time - Data Example: Number of pairs sold for eight shoe styles - Categories (Labels): Vertical axis - Values (Numbers): Horizontal axis

Excel Chart Types: Surface

- Purpose: Displays optimum combinations of two sets of data on a surface. - Data Example: Perceived outside temperature at ten temperature and ten humidity levels. - Categories (Labels): Horizontal axis (value 1-x) - Values (Numbers): Horizontal axis (value 1-x)

Excel Chart Types: XY (Scatter) or Bubble

- Purpose: Displays relationships among numeric values in two or more data series; these charts do not have a category. - Data Example: Number of times a patient visits a doctor, amount billed to insurance, and cost billed to patient - Categories (Labels): Horizontal axis (value 1-x) - Values (Numbers): Vertical axis (value 2-y)

Excel Chart Types: Box and Whisker

- Purpose: Displays the distribution of data with minimum, mean, maximum, and outlier values. - Data Example: Sales prices of home in five suburbs during a three-week period. - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Radar

- Purpose: Displays the frequency of multiple data series relative to a center point. There is an axis for each category. - Data Example: Style, comfort, and value ratings for three snow boot styles - Categories (Labels): NA - Values (Numbers): NA

Excel Chart Types: Area

- Purpose: Displays the magnitude of change over time and shows the rate of change - Data Example: Yearly consumption of apples, bananas, and pears over a ten-year period - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Stock

- Purpose: Displays three series of data to show fluctuations in stock prices from high to low to close - Data Example: Opening, closing, and high price for Microsoft stock each day for 30 days - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Line

- Purpose: Displays trends in data over time, emphasizing the rate of change - Data Example: Number of weekly web site views over a ten-week period. - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Column

- Purpose: Illustrates data changes over a period of time or shows comparisons among items. - Data Example: Monthly sales data for three automobile models. - Categories (Labels): Horizontal axis - Values (Numbers): Vertical axis

Excel Chart Types: Pie

- Purpose: Uses one data series to display each value as a percentage of the whole - Data Example: Expenses or revenue by department for one quarter - Values (Numbers): One data series shown by slice size

Source Data

- The cells that contain values and labels to be graphed in a chart. - When you select data for a chart, you usually do not include sums, averages, or similar calculations.

Hierarchy

A division of data that identifies a top group followed by lesser groups.

Data Series

A group of related values graphed by columns, bars, slices, or other objects in a chart.

Sunburst Chart

A hierarchy chart that illustrates the relationship among categories and subcategories of data. It resembles a doughnut chart with concentric rings for each layer of data

Shape Style

A predesigned set of borders, fill colors, and effects for a chart element.

Data Point

A single value graphed in a chart.

Excel Chart Element Types: Data Point

A single value or piece of data from a data series.

Excel Chart Element Types: Horizontal (category) Axis

Describes what is shown in the chart and is created from row or column headings. In a bar chart, the category axis is the vertical axis; the category axis is the horizontal axis in a column chart.

Excel Chart Element Types: Legend

Element that explains symbols, textures, or colors used to differentiate data series.

Excel Chart Element Types: Data Series

Group of related values that are in the same column or row and translate into the columns, lines, pie slices, and other markers.

Excel Chart Elements Types: Axis

Horizontal or vertical boundary that identifies what is plotted.

Excel Chart Element Types: Gridline

Horizontal or vertical line that extends across the plot area to help in identifying values.

Excel Chart Element Types: Trendline

Line or curve that displays averages in the data and can be used to forecast future averages.

Excel Chart Element Types: Axis Title

Optional description for the categories or values.

Excel Chart Element Types: Chart Title

Optional description or name for the chart.

Excel Chart Element Types: Data Label

Optional element that displays values with the marker for each data series.

Excel Chart Element Types: Plot Area

Rectangular area bounded by the horizontal and vertical axes.

Excel Chart Element Types: Vertical (value) Axis

Shows the numbers on the chart. In a bar chart, the vertical axis is along the bottom; in a column chart, the vertical axis is along the side.

Excel Chart Element Types: Tick Mark

Small line or marker on an axis to guide in reading values.

Shape Outline

The border around the element or graphic object.

Excel Chart Element Types: Chart Wall

Vertical background for a 3D chart.

Alt+F1

You can create a default chart object by selecting the data.

F11 (FN+F11)

You can create an automatic column chart sheet by selecting the data and pressing.