facebook google plus twitter
Webucator's Free Intermediate Microsoft Excel 2013 Tutorial

Lesson: Visualizing Your Data

Welcome to our free Intermediate Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2013 Training course.

In this lesson, you will learn techniques for visualizing your data.

Lesson Goals

  • Create charts that enable you to visualize your data.
  • Choose what data is displayed in your charts.
  • Show and hide data labels.
  • Show and hide the legend.
  • Show and hide the chart title.
  • Add a picture or shape to a chart.
  • Change the way text displays in a chart.
  • Change the fill color of a chart.
  • Add and format objects in a worksheet.

Visualizing Your Data

It is often useful to visualize data, and it's easy to do so in Excel using charts. The most commonly used chart types are:

  1. Column. Column charts are commonly used to compare values of different items.
  2. Line. Line charts are commonly used to show trends.
  3. Pie. Pie charts are useful when you have only one set of data and want to show how much of the whole each item accounts for.
  4. Bar. Bar charts are similar to column charts except that the bars are displayed horizontally instead of vertically.
  5. Area. Area charts are similar to line trends in that they show trends, but the data is stacked, making it easy to compare different values over time.
  6. Scatter. Scatter charts are used to display two variables, one along the X-axis and one on the Y-axis, for a single set of data.

In the activities that follow, you will learn more about the charts available in Excel.

Inserting Charts

To insert a chart into an Excel workbook:

  1. Select the data you wish to display. If Excel recognizes your data as being a list, you can select any cell within the list.
  2. On the INSERT tab, in the Charts group, select the type of chart you wish to insert by clicking on it:Selected Chart
  3. Select a specific chart. The following image shows the charts available if you click the Column command:Available Charts

Resizing Charts

You can easily resize charts, as well as graphs, by clicking and dragging one of the resize handles at the edges of the chart or graph. Hover the cursor over the handle until it turns into a two-sided arrow:

Two-Sided Arrow

Inserting Charts

Duration: 15 to 25 minutes.

In this exercise, you will practice inserting charts in Microsoft Excel.

  1. Open Charts.xlsx from your Excel2013.2/Exercises folder.
  2. In Sheet1, insert a pie chart that looks like the one below:Pie Chart
  3. In Sheet2, insert a line chart that looks like the one below:Line Chart
  4. In Sheet3, insert a column chart that looks like the one below:Column Chart

Solution:

  1. To insert a pie chart in Sheet1:
    1. Select cells A1:B35 (do not remove subtotals or display all the data) or select any cell in the range.
    2. On the INSERT tab, in the Charts group, click the Pie chart command:Pie chart Command
    3. Select the -D Pie (Pie in 3-D in Excel 2007/2010) chart by clicking on it:D Pie Option
  2. To insert a line chart in Sheet2:
    1. Select cells A1:H8 or select any cell in the range.
    2. On the INSERT tab, in the Charts group, click the Line chart command:Line chart Command
    3. Select the Line chart by clicking on it:Line Chart
  3. To insert a column chart in Sheet3:
    1. Select cells A1:E8 or select any cell in the range.
    2. On the INSERT tab, in the Charts group, click the Column chart command:Column chart Command
    3. Select the Clustered Column chart by clicking on it:Clustered Column Chart

Editing Charts

When you select a chart in Excel, three CHART TOOLS tabs appear:

  1. Design. Use the CHART TOOLS DESIGN tab to select or change data, change the layout of the chart, or change the style of the chart.
  2. Format. Use the CHART TOOLS FORMAT tab to change the shape styles, chart fill colors, and text formatting in the chart.

In Excel 2007 and 2010, there is a third Chart Tools tab, called Chart Tools Layout. In Excel 2013, the options available on this tab are on the DESIGN and FORMAT tabs, or are no longer available.

Changing the Layout of a Chart

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS DESIGN tab, in the Chart Layouts group, click Quick Layout (click More in Excel 2007/2010) to see all the layouts available:Quick Layouts
  3. Select an alternative layout by clicking on it. The following image shows the layout options for pie charts. The same options are available for other chart types, though they obviously look a little different:Chart Options

Changing the Style of a Chart

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS DESIGN tab, in the Chart Styles group, click the More button to see all the styles available:Available Styles
  3. Select an alternative style by clicking on it. The following image shows the style options available for pie charts:Pie Chart Options

Adding a Shape to a Chart

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS FORMAT tab (Chart Tools Layout tab in Excel 2007/2010), in the Insert Shapes group (Insert group in Excel 2007/2010), click the More (Shapes in Excel 2007/2010):More Option
  3. Navigate to and select a shape by clicking on it.
  4. Your cursor will change to a plus sign. Click in your chart where you want to place the shape, drag your mouse until the shape is the size you want it to be, and then release your mouse.

Adding a Trendline to a Chart

A visual way to show trends in charts is to add trendlines.

To add a trendline to a chart:

  1. In the chart, select the data series to which you want to add the trendline.
  2. Select the Chart Elements option that appears next to the chart. Chart Elements Options
  3. Check the Trendline check box. Trendline Check Box
  4. Click the arrow next to Trendline to change trendline options. Trendline Arrow

Trendline Options

Adding a Secondary Axis to a Chart

You can create a chart with dual axes; that is, add a secondary vertical axis to a chart. This is useful when you have two different types of data you want to display.

To add a secondary axis to a chart:

  1. Click in the chart to display the CHART TOOLS DESIGN tab. CHART TOOLS DESIGN Tab
  2. Select the DESIGN tab, and then from the Type group, select Change Chart Type. Change Chart Type Option
  3. On the left select Combo. Combo Option
  4. Under Choose the chart type and axis for your data series, check the check box for the data you want to plot on the secondary axis, and then select Line for the Chart Type. All other data should be Clustered Column. Click OK. Selected Options

Adding Additional Data Series to a Chart

Add the data to the cells that you selected for the chart, and the chart automatically updates with the new data series.

New Data Series

You can also use the CHART TOOLS DESIGN tab. In the Data group, select Select Data and then use the options in the Select Data Source dialog box to select the new series.

Select Data Source Dialog Box

Switch between Rows and Columns in a Chart

To switch between rows and columns in a chart, follow these steps:

  1. With your chart selected, from the CHART TOOLS DESIGN tab, in the Data group, select Switch Row/Column. SwitchRow/Column Option
  2. The row/column display in the chart will change. To revert back, select this option again.

Positioning a Chart

Once the chart is inserted, it is easy to change its position.

To change the position of a chart:

  1. Hover your cursor over the chart until it turns into a four-headed arrow. Four-Headed Arrow
  2. Click and drag the chart to the appropriate position.

Modifying Chart and Graph Parameters

To make changes to your chart, use the Chart Elements, Chart Styles, and Chart Filters options. To access these options, click the chart. They will appear next to the chart:

Chart Filters Options

Some of the things you can use Chart Elements to do include:

  1. Add or remove gridlines.
  2. Add or remove titles.
  3. Display table data.
  4. Add error bars.

Some of the things you can use Chart Styles to do include:

  1. Change the color of the chart.
  2. Change the style of the chart.

Some of the things you can use Chart Filters to do include:

  1. Filter your data by categories.
  2. Filter your data by series.

Watching Animation in a Chart

In Excel 2013, once you have added a chart, if you change the source data for that chart, you can watch the chart automatically update. Below is a pie chart reflecting original data, and then source data that has changed.

Original Data in Pie Chart

Changed Source Data

Showing, Hiding, or Changing the Location of the Legend in a Chart

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS DESIGN tab (Chart Tools Layout tab in Excel 2007/2010), in the Chart Layouts group (Labels group in Excel 2007/2010), click Add Chart Element and select Legend (Legend command in Excel 2007/2010):Legend Command
  3. Select one of the legend options by clicking on it:Legend Options

Show or Hiding the Title of a Chart

To show or hide the title of a chart, follow these steps:

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS DESIGN tab (Chart Tools Layout tab in Excel 2007/2010), in the Chart Layouts group (Labels group in Excel 2007/2010), click Add Chart Element and select Chart Title (Chart Title command in Excel 2007/2010):Chart Title Option
  3. Select None if you do not wish to display the title, or one of the other options if you do wish to display it:None

Changing the Title of a Chart

To change the title of a chart:

  1. Select the title by clicking on it within the chart:Chart Title
  2. Double-click the title to select the title text:Selected Title Text
  3. Type in the new title for your chart.

Show, Hiding, or Changing the Location of Data Labels in a Chart

To show, hide, or change the location of data labels in a chart, follow these steps:

  1. Select the chart by clicking on it.
  2. On the CHART TOOLS DESIGN tab (Chart Tools Layout tab in Excel 2007/2010), in the Chart Layouts group (Labels group in Excel 2007/2010), click Add Chart Element and select Data Labels (Data Labels command in Excel 2007/2010)Data Labels Option
  3. Select None if you do not wish to display data labels, or one of the other options if you do wish to display them. Note that the options change based on the chart type. The options shown below are for a pie chart:Pie Chart Options
  4. If you aren't sure which to choose, just select each one individually to see what it looks like until you find something you like.

Changing the Style of Pieces of a Chart

To change the style of pieces of a chart:

  1. Select any piece of the chart (chart image itself, title, legend, data labels, etc.).
  2. On the CHART TOOLS FORMAT tab, in the Shape Styles group or WordArt Styles group, move your mouse over the styles available to see what your chart will look like if you select that style:Chart with Style
  3. As desired, click the drop-down arrows to see and mouse over the many style options available.
  4. Select a style by clicking on it.

Editing Charts

Duration: 15 to 25 minutes.

In this exercise, you will edit the charts you inserted in the last exercise.

  1. Open Editing Charts.xlsx from your Excel2013.2/Exercises folder.
  2. On Sheet1, change the layout and style of the chart so that it looks like the image below. Each section of the pie should pastel (or if you are using Excel 2007/2010, it should be a different shade of maroon):Chart
  3. On Sheet2, move the legend, move the title, and add a title to the chart ("Grades by Student") as shown in the image below:Updated Chart
  4. On Sheet3, add styles to the chart such that it has an orange outline, blue fill, and all text is outlined in red:Styles Added

Solution:

  1. On Sheet1, change the layout and style of the chart:
    1. Select the chart by clicking on it.
    2. On the CHART TOOLS DESIGN tab, in the Chart Layouts group, click Quick Layout and select Layout 1 (in Excel 2007/2010, just select Layout 1):Layout 1
    3. On the CHART TOOLS DESIGN tab, in the Styles group, click Style 4:Style 4
  2. On Sheet2, move the legend and the title and change the title of the chart:
    1. Select the chart by clicking on it.
    2. On the CHART TOOLS DESIGN tab (Chart Tools Layout tab in Excel 2007/2010), in the Chart Layouts group (Labels group in Excel 2007/2010), click Add Chart Element and select Legend and select Right (Data Labels command in Excel 2007/2010 and select Show Legend at Right)Legend
    3. On the CHART TOOLS DESIGN tab (Chart Tools Layout tab in Excel 2007/2010), in the Chart Layouts group (Labels group in Excel 2007/2010), click Add Chart Element and select Chart Title and then select Centered Overlay (in Excel 2007/2010, select Chart Title and select Centered Overlay Title):Centered Overlay
    4. Click in the Chart Title box, select the text, and type "Grades by Student".
  3. On Sheet3, add styles to the chart so that it looks like the image shown:
    1. Select the chart by clicking on it.
    2. On the CHART TOOLS FORMAT tab, in the Shape Styles group, click the orange Colored Outline (you may have to click the More button to see the orange outline):Colored Outline Option
    3. On the CHART TOOLS FORMAT tab, in the Shape Styles group, click Shape Fill and then select a color:Shape Fill Colors
    4. On the CHART TOOLS FORMAT tab, in the WordArt Styles group, click Text Outline and then select Red:Red Text Outline

Add and Format Objects

You can add objects to your worksheets, such as pictures, ClipArt, shapes, and screenshots.

To add an object to your worksheet:

  1. From the INSERT tab, in the Illustrations group, select an option to add an object. Object Options
  2. You can change the position of the inserted object by clicking and dragging it within the worksheet. First hover your cursor over it until it becomes a four-headed arrow. Four-Headed Arrow
  3. You can add a border to your object by right-clicking the object and selecting Outline. Outline Option
  4. Use the options in the drop-down list to add a border. Border Options

Insert a Text Box

You can insert a text box in a worksheet.

To insert a text box:

  1. On the INSERT tab, in the Text group, select Text Box. Text Box Option
  2. Your cursor will change to look like an upside-down cross. Click and drag to insert the text box where you want it in your worksheet. Inserted Text Box
  3. Now you can type within the text box. Typing in Text Box

Create a Custom Chart Template

If you have a chart you would like to reuse in the future, you can save it as a custom chart template.

To save a chart as a custom template:

  1. Right-click the chart and select Save as Template. Save as Template Option
  2. In the Save Chart Template dialog box, select a name for the template and click Save. Save
  3. The chart now appears as an option in the Templates section when you are inserting a chart. Chart in Templates Section