Microsoft Excel 2013: Charts
June 2014
Description
We will focus on Excel features for graphs and charts. We will discuss multiple axes, formatting data, choosing chart type, adding notes and images, and customizing your charts. We will discuss how to export charts to other formats including Word, PowerPoint and PDF.
Email for follow-up questions: [email protected]
Learning Objectives
Choose a chart type
Format chart data
Add chart elements
Create a secondary axis
Customize layouts
Add notes and images
Export charts to Word, PowerPoint and PDF
Web Resources
Microsoft Office Online Training
(http://office.microsoft.com/en-us/training/).
Safari Tech Books Online – electronic books. Type “Safari” in Penn Libraries FindIt box to get the link.
Lynda.com video tutorials – Weigle information Commons and the Vitale Digital Media Lab have a site license to Lynda.com
(http://wic.library.upenn.edu/wicideas/lynda.html).
Google!
Chart Types
There are 11 major chart types in Microsoft Excel 2013, each with its own set of chart subtypes. Chart selection is dependent on the data and what the chart aims to reveal.
Main Types Descriptions
Column Used to show the changes in data over a period of time or illustrate comparisons among items
Line Mainly used to plot changes in data over time
Bar Emphasizes the comparison between items at a fixed period of time
Pie Emphasizes a significant element in the data. It represents data in the form of a pie.
Scatter Useful for showing a correlation among the data points that may not be easy to see from data alone
Area Displays the magnitude of change over time
1. Create a chart in Excel 2013
Open the Excel Charts Workbook
Click on the first tab labeled Gradebook
Fill in numerical grades (any number from 60‐100), for the five students listed, for all four quarters. When finished, you should have 20 values, 4 grades for each student.
Use the mouse to select the data ‐ click and drag mouse to highlight cells, including row and column headers.
Click on the Insert tab on the ribbon.
Click on the clustered column chart type.
Note: To select non‐contiguous data in a spreadsheet, hold down Ctrl button while dragging mouse to select cells.
Caution: Excel remembers the order in which you select data. As a rule of thumb, when entering data, leave the top‐left cell blank.
2. Chart Tools
When a chart is created and/or selected, the Chart Tools appear in the Excel Ribbon (highlighted in green). The following tabs are located under Excel’s Chart Tools:
Design Tab: Contains buttons to change the chart type and style, add chart element
Format Tab: Contains buttons to customize data markers and shapes, add WordArt, and resize chart
3. Resizing and Moving a Chart
Once you create a chart, it appears as an embedded object on the active worksheet. The chart can be moved to different places on the worksheet, as an embedded object in another sheet, or the chart can be made into an entire sheet.
Click once on your gradebook chart to select it ‐ the chart borders become highlighted to indicate that the chart is selected.
Click and drag your mouse at the corners and sides of the chart to resize it.
Click and drag in the chart’s white space (the chart area) to move the entire chart within the worksheet (the pointer will be 4 crossed arrows).
Make sure your chart is selected, and your chart tools are visible on the Excel ribbon.
Click on the Design tab (if not already selected).
Click on Move Chart button.
Select “New sheet” (Chart takes up entire sheet) and title the Chart “Gradebook Chart”
Click OK.
Click on the tab labeled “Gradebook Chart” to view the newly‐created sheet. Click on the gradebook tab to see the data table and change a few of the grades. Then go back to the Gradebook Chart, and notice that the chart reflects the changes you made in the table.
4. Changing a Chart Type
Click on the Design tab.
Click on Change Chart Type.
Select the first line chart from the Chart Type dialog box that appears and click OK.
Click the Switch Row/Column button so that each student is represented by a different colored line.
5. Reorder legend entries (Series)
Click the Select Data button.
Located under the Legend entries (series) column, use the up and down arrow buttons to alphabetize the students’ names. The entry (student name) must first be selected (highlighted) to move it.
Adding Chart Elements
There are 15 chart elements that may be added to a chart. For deciding which elements appear on a chart, you can select one of Excel’s predefined chart layouts located in the Design tab. You can also manually select individual chart elements with options located in the Layout tab.
Click on the Design tab and locate the Chart Layouts.
Click on the down arrow for the Add Chart Element button.
Click on the down arrow for the Axis Titles button and select a vertical axis for the chart.
Click on the down arrow for the Legend button and select “None.”
Click on the down arrow for the Data Labels button and select “Inside End.”
Click on the down arrow for the Data Table button and select “With Legend Keys.”
7. Formatting Titles
Click on the Chart Title placeholder and use the mouse to highlight the text.
Type “Mr. Smith’s Gradebook, 2008‐2009” in the chart title.
Use the Mini toolbar to change font color, size and type, right‐click on the title (you can also change font attributes from the Home tab).
Type “Class Average” for the vertical axis title in the same manner.
8. Formatting Axes
Click on the chart’s Y (vertical) axis.
Click on the Format tab.
Click the Format Selection button under Current Selection group.
Under Axis Options – Minimum > choose Fixed and type in the number 60.
Note: To format any component on the chart (except an individual data point), you select the element with your mouse and click Format Selection (located under the Format tab). You may also format a chart element by right‐clicking on the element in the chart and choosing the last option listed.
9. Exporting charts: PDF, Word, and PowerPoint
To save a chart as a PDF, click the Office button (Top left, above Ribbon).
Select Print.
Click on the down arrow on the Name field and select Adobe PDF (first option in list).
Open a blank Word document.
In Excel, copy the chart (right‐click and select copy OR use Ctrl+C).
In Word, paste the chart (right‐click and select paste OR use Ctrl+V).
Click on the Clipboard icon located near the bottom‐right corner of the chart to view paste options.
Follow the steps above to paste a chart in a PowerPoint slide.
10. Chart elements: Adding a secondary axis
Click on the Workshops tab.
Create a line chart of the data.
Move the chart to a new sheet.
Click on the workshops series (blue line) to select it.
Click on the Format tab.
Click the Format Selection button.
In the data series dialog box, select Secondary Axis and click Close.
11. Inserting text boxes and shapes
You can draw attention to an entry on a chart by inserting an Autoshape or a text box to a chart. You can also insert images saved on your computer (file) or available in Microsoft’s online clipart gallery. Images may be inserted anywhere on the chart. Images may even be used in place of data markers (legend entries).
Click on the Format tab.
Click on the down arrow located in the Insert group.
Select the “Left Arrow Callout” under the Block Arrows category.
Inside the chart, click and drag your mouse to “draw” the shape.
Right‐click on the shape and select “Edit Text.”
Type “Winter Break” in the shape.
Position the shape so that it is pointing to the January dip in the chart.
12. Inserting images
Click on the Pie tab.
Create a pie chart of the data and move it to a new sheet.
Click on the chart’s pieces one time > all the pieces are selected.
Click again and only the piece your pointer is on will be selected.
Select the “Apple” section of your pie. Click Format Selection.
Select the Fill option and then click the Picture or Texture Fill option
Click the File button under “Insert from.”
Select the apples image file located on the computer.
Repeat these steps for all the pieces of the pie chart, inserting all the fruit images.
