Chapter 3: Charts: Depicting Data Visually

Gridlines

Horizontal or vertical lines that span across the plot area to help people identify values plotted by visual elements. -use either Chart Elements or Add Chart Elements in the Chart Layout group on the design tab to add these. -format by double clicking to open the Major Gridlines Task pane.

Column Chart

Compares values vertically in columns where the height represents the value; the taller the column, the larger the value (most effective when limited to 7 or less values). -vertical axis displays values. -horizontal axis displays categories.

Distribute a chart

Ensure that the worksheets are set up so that the recipients can easily print the chart if needed. Guidelines to print a chart: -if the data and chart are in the same sheet but you want to print only the data or chart, select the data or chart and then display Print Preview windows. Select the Print Selection setting to print the spreadsheet data or select Print Selected Chart to print the chart. -to print both, display the Print Preview window to ensure the data and chart fit on the page (adjust margins/scaling if needed). -if the chart is on a chart sheet, when you display print preview the chart displays itself as a full page within the margins.

Create a chart using quick analysis

Steps to create a chart using Quick Analysis: Step 1: Select the Data Range and click Quick Analysis. Step 2: click charts in the gallery. Step 3: point to the recommended chart thumbnail to preview the type of chart. Step 4: click the thumbnail of the chart you want to create.

Other chart types

The attached image lists and describes some of the other chart types you can create using excel.

Formatting Chart Elements

The chart elements menu includes a More option, select that option or double click the chart element to display the Task Pane. -the Task Pane name and elements change depending on the Chart element selected. After selecting an element, you can format it using commands in the Font group on the Home tab.

Add chart elements

To add chart elements: - select Chart and then select top icon, Chart Elements. - click check box next to element to add. - submenus specific to the element are available by clicking the triangle that appears when the pointer is over the element. OR -use Add Chart Elements command in the Chart Layouts group on the Chart Design Tab.

Chart titles

To edit, position, and format the Chart Titles: -select the default chart title and change to a more descriptive title and press enter. -default alignment is centered, can change this by using the Chart Elements icon

Format task pane

consolidates shape, fill, and color options and provides custom commands for the element. -apply multiple settings at once for an element using this. -to open, double click the element.

Axis titles

identify the values on each axis. -primary horizontal -primary vertical -secondary horizontal -secondary vertical Format the axes: -double click the axis to open Format Axis Task Pane. OR -use Home Tab to format the axis. Axis options: Bounds: specifies the min and max values displayed on the value axis. Units: controls the intervals of values listed in the value axis. Display Units: specifies any conversions of values on the value axis. Tick marks: specifies the locations of the tick marks, which are tiny extensions of the value axis intervals. Labels: specifies the location of the interval numbers on the value axis. Number: formats the values with a number format, the number of decimal places displayed, the format for displaying negative values, and other number formats.

Quick Layout

predefined arrangement of chart elements, use this to apply a layout to add several chart elements conveniently at 1 time. Using Quick Layout: -select Quick Layout in the Chart Layouts group on the design tab.

Combo chart

A chart that combines 2 chart types, such as column and line, to plot different types of data, such as quantities and percentages. -useful to show 2 different but related data types. -when it has 2 axis, the primary displays on the left and the secondary displays on the right. To create a custom combo chart: -click Combo in the Charts group on the insert tab. -select Create Custom Combo chart. -insert chart dialog box opens with Combo selected on the left side and the specific options for selecting the chart type and secondary axis on the right side.

Exploded Pie chart

A chart type in which one or more pie slices are separated from the rest of the pie chart for emphasis. To format: -click within the pie chart, pause, and then select the slice you want to format. -right click the selected pie slice and select Format Data Point to open the Task Pane. -Click Fill and Line Icon and click the option (such as solid fill line) in the Fill Category. -click the Color Arrow and select a color for a solid fill or insert a picture/select a texture. -click the Series Options icon and drag the Point Explosion to the right to explode the selected pie slice.

Bar chart

A chart type that compares values across categories using horizontal bars where the length represents value (the longer the bar, the larger the value). -horizontal axis: displays values. -vertical axis: displays categories. Types: similar to a Column Chart, this chart has clustered, stacked, and 100% stacked bar charts.

Line Chart

A chart type that displays lines connecting data points to show trends over equal time periods such as months, quarters, decades, or years. -each data set displayed with a different color. -horizontal axis: displays time. -vertical axis: displays values.

XY scatter chart

A chart type that shows a relationship between two variables using their X and Y coordinates. -excel plots one coordinate on the horizontal axis and the other variable on the vertical axis. -scatter charts are often used to represent data in education, scientific, and medical experiments.

Pie chart

A chart type that shows each data point in proportion to the whole data series as a slice of the circle. A pie chart depicts only one data series. -use this chart when you want to display percentages for up to 7 data points.

Stock chart

A chart type that shows fluctuations in the stock prices. 4 subtypes: 1. High-Low-Close 2. Open-High-Low-Close 3. Volume-High-Low-Close. 4. Volume-Open-High-Low-Close

Stacked column chart

A chart type that shows the relationship of individual data points to the whole category by stacking data segments in one column with each segment represented by a different color. -when creating this column type, make sure the data is additive (each column represents a sum of the data for each segment).

Chart style

A collection of formatting that controls the color of the chart area, plot area, and data series, as well as the font and font size of the titles. -options are shown when you click Chart Styles to the right of the chart. -options can also be shown in the Chart Styles gallery that displays from Chart Styles on the Design Tab. -change colors in the chart style group on the design tab allows you to select different predefined colors from the workbook theme. Save chart as template: -you can save a chart as a template to be able to create others with those elements, formats, and colors. -do this by right clicking the chart and selecting Save Chart As Template.

Chart elements

A component of the chart that helps complete or clarify the chart. -some are required (ex. Chart Title) -some are optional. Elements: -Axis Title: label that describes the category/value axes to clarify it. -chart area: container for the entire chart and it's elements. -chart title: heading that describes chart. -data label: descriptive label that shows the exact value or name of a data point. -data table: grid that contains the data source values/labels; useful when the chart is on a different sheet from the data source. -error bar: visuals that indicate the standard error amount, a percentage, or a standard deviation from a data point or marker. -gridlines: horizontal or vertical lines that extend from the tick marks on the value axis across the plot area to guide the readers eyes across the chart to identify values. -legend: box that contains a key to identify the color or pattern assigned to each data series. -plot area: region containing the graphical representation of the values in a data series; surrounded by 2 axes. -trend line: line that depicts trends/helps forecast future data, such as estimating future sales or number of births. Add this to column, bar, line, stock, scatter, and bubble charts.

Data series

A group of related data points that displays in rows or columns in a worksheet. -each data series is represented by a different color in the chart (blue and orange in attached image).

Legend

A list that identifies patterns, symbols, or colors used in a chart. -format this similarly to choosing the position of the Chart Titles using the chart elements. -use format legend task pane to customize and format. -use home tab to format.

Chart filter

A setting that controls what data series and categories are displayed or hidden in a chart. -by default, all data selected is included but you can apply chart filters to focus on particular data. To do so: -use Chart Filters to the right of the chart to display options. -check the box next to the one you want.

Sparkline

A small line, column, or win/loss chart contained in a single cell to provide a simple visual illustrating one data series. -does not include any standard chart labels. To insert: -click insert tab. -click line, column, or win/loss in the Sparklines group (create sparklines dialog box opens). -type in the data range box or select the range of cell references containing the values. -enter or select the range where you want the sparkling to display in the location range box and click OK.

Clustered column chart

A type of chart that groups, or clusters, columns set side by side to compare several data points among categories.

Data point

A value that originates in a worksheet cell and that is represented in a chart by a data marker -you can select a single data point on a chart and format it differently than the rest of the data series. Format: select the data point, display format data point task pane, and make the changes you want.

Chart

A visual representation of numeric data. -compared data and reveals trends/patterns. Common Chart Types: -Column Chart -Bar Chart -Pie Chart -Line Chart -Combo Descriptions attached.

Source Data

After reviewing the data and deciding what story to represent, it is time to create the chart. Select a Data Source: -identify the range that contains the data you want included in the chart. *** include column/row headers when multiple data series are included.

Customize sparklines

After you insert a sparkline, The Sparkline Tools Design tab or Sparkline tab displays with options to customize the Sparkline. Groups are listed and described in the attached image.

Alt Text

Also known as alternative text, an accessibility compliance feature where you can enter text and a description for an objective, such as a table or chart. A special reader can read the alt text to a user. -add this in addition to a meaningful title in a chart. Adding alt text: -Click Alt Text in the Accessibility group on the Format Tab. OR -right click the chart and select edit alt text. Both of these will open the Alt Text task pane will appear, enter a description of the chart and then close the pane.

Data point

An individual value in a cell that is plotted in a chart.

Create a chart with ideas

Ideas feature: a task pane that provides intelligent analysis of a data set to recommend potentially useful charts (locate by clicking Ideas group in the Home tab). -analyzes data and provides a visualization in the Task Pane. AKA: Analyze Data in the Analysis Group. Task Pane: a window of options specific to a feature in an office application. The task pane name and options change based on the selected range, object, or feature.

Change the Chart Type

If you decide you want to change the chart type after creating a chart: Step 1: Select the chart and click the Chart Design Tab. Step 2: Click Change Chart Type in the Type group to open the Change Chart Type dialog box. Step 3: click the chart type on the left side of the Change Chart Dialog Box. Step 4: Click the chart subtype on the right side of the dialog box and click OK.

Data labels

Labels that identify each value in the data series. -add by clicking Data labels check box, click the arrow on right to select position. -to select for a specific data series, select data series before selecting data labels. Formatting: -use the Format Data Labels task pane. -use home tab to apply font formatting.

Move a chart

Move within worksheet: -cut and paste commands. Or -drag the chart (locate the Chart Area ScreenTip, when pointer is a white arrowhead and 4 headed arrow, drag the chart). Move to new sheet: (Chart Sheet, contains no data. Only the chart). Step 1: select the chart. Step 2: Click the Chart Design Tab and click Move Chart in the Location Group (or right click the chart and select Move Chart dialog box). Step 3: Select New Sheet and type the name for the chart sheet. Step 4: click OK.

Create a Recommended Chart

Use the Recommended Charts command in the Charts Group on the Insert Tab to display the insert chart dialog box. Dialog Box contains 2 tabs: Recommended Charts and All Charts.

Change data source

Use the Select Data command in the Data Group on the Design Tab to open the Select Data dialog box. -this dialog box allows you to filter which categories and data series are visible in your chart. -also enables you to change the source data range

Insert Tab

Use the ____ ___ to locate the Charts Group, which contains commands for creating a variety of charts.

Axis

X-axis: Horizontal axis -the horizontal border that provides a frame of reference for measuring data left to right on a chart. Y-axis: Vertical Axis -the vertical border that provides a frame of reference for measuring data up and down a chart. Category Axis: -displays descriptive labels for data points plotted in a chart (labels are typically text contained in the first column of the worksheet data). Value Axis: -displays incremental numbers to identify approximate values, such as dollars or units, of data points in a chart.

Size a chart

You can adjust the chart size to fit a range or to ensure the chart is appropriately sized. Select the chart then use Sizing Handle, which is one of the 8 circles that displays around the four corners/outside middle edges of the chart. -middle sizing handle: this handle allows you to change the height or width. -cornerizing handle: allows you to change the height and width at the same time. Without using Sizing Handle: using the sizing handle can cause data to become distorted when you adjust row/columns. -Use the Shape Height and Shape Width settings in the Size Group on the Format Tab.

Switch Row and Column Data

You can switch data used to create the horizontal axis and the legend to different perspectives and to change the focus of the data. Steps to do so: 1. Select the data 2. Click Switch Row/Column in the Data group on the Design Tab

100% stacked column chart

a chart type that places (stacks) data in one column per category, with each column the same height of 100%. -converts data into percentages of total whole. -each data series is a different color of the stack.