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.
