facebook twitter
Webucator's Free Intermediate Microsoft Excel Tutorial

Lesson: Visualizing Your Data

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

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

Lesson Goals

  • Learn to create charts that enable you to visualize your data.
  • Learn to choose what data is displayed in your charts.
  • Learn to show and hide data labels.
  • Learn to show and hide the legend.
  • Learn to show and hide the chart title.
  • Learn to add a picture or shape to a chart.
  • Learn to change the way text displays in a chart.
  • Learn to change the fill color of a chart.
  • Learn to add and format objects in a worksheet.
  • Learn to create a custom chart template.

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. XY (Scatter). XY (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.

New Charts

Excel 2016 introduced six new chart types to help you visualize your data. These new charts are:

  • Treemap: The treemap chart can be used to spot patterns; it organizes your data in a hierarchical view. The trees "branches" are rectangles and smaller rectangles. It uses color and proximity to organize data into categories. Treemap
  • Sunburst: This chart is also known as a ring chart. A sunburst chart displays data hierarchically. The innermost rings are the top of the hierarchy. Sunburst
  • Histogram: A histogram chart is used to display frequency data in columns. Histogram
  • Waterfall: The waterfall chart is useful when you are adding and subtracting data. This type of chart keeps a running total of the data, displayed as columns. Waterfall
  • Box & Whisker: This chart type organizes data to show the means and outliers of the data, by arranging it in quartiles. The "whiskers" are lines that indicate variability that is outside of the upper and lower quartiles. Box and Whisker
  • Pareto: This type of chart is a type of histogram that sorts data with columns, in descending order, and then a line showing the cumulative total percentage. Pareto

The treemap and sunburst charts are available from the Insert Hierarchy Chart drop-down from the Insert tab, Charts group. Select Insert Waterfall or Stock Chart for the waterfall chart; select Insert Statistical Chart for the histogram, pareto, or box & whisker chart.

Charts

You can view each chart by selecting Recommended Charts, and then clicking the All Charts tab.

Recommended Charts

All Charts

Excel 2019 includes even more charts: Funnel charts, used to show smaller stages in a process, and Map charts, used to compare values across geographic regions.

Watch and follow along as your instructor shows you some of the many charts available in Excel.

New Charts

Excel 2016 introduced six new chart types to help you visualize your data. These new charts are:

  • Treemap: The treemap chart can be used to spot patterns; it organizes your data in a hierarchical view. The trees "branches" are rectangles and smaller rectangles. It uses color and proximity to organize data into categories. Treemap
  • Sunburst: This chart is also known as a ring chart. A sunburst chart displays data hierarchically. The innermost rings are the top of the hierarchy. Sunburst
  • Histogram: A histogram chart is used to display frequency data in columns. Histogram
  • Waterfall: The waterfall chart is useful when you are adding and subtracting data. This type of chart keeps a running total of the data, displayed as columns. Waterfall
  • Box & Whisker: This chart type organizes data to show the means and outliers of the data, by arranging it in quartiles. The "whiskers" are lines that indicate variability that is outside of the upper and lower quartiles. Box and Whisker
  • Pareto: This type of chart is a type of histogram that sorts data with columns, in descending order, and then a line showing the cumulative total percentage. Pareto

The treemap and sunburst charts are available from the Insert Hierarchy Chart drop-down from the Insert tab, Charts group. Select Insert Waterfall or Stock Chart for the waterfall chart; select Insert Statistical Chart for the histogram, pareto, or box & whisker chart.

Charts

You can view each chart by selecting Recommended Charts, and then clicking the All Charts tab.

Recommended Charts

All Charts

Excel 2019 includes even more charts: Funnel charts, used to show smaller stages in a process, and Map charts, used to compare values across geographic regions.

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:Select Chart
  3. Select a specific chart. The following image shows the charts available if you click the Insert Column or Bar Chart command:Insert Column or Bar Chart Command

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:

Resize

Using the Chart Recommendation Feature

The Chart Recommendation feature can assist you by providing charts that suit your data.

To use the Chart Recommendation feature:

  1. Select the cells of data for which you want to create the chart.
  2. Select the Insert tab, and in the Charts group, click Recommended Charts. Recommended Charts
  3. Excel displays the Insert Chart dialog box, displaying the Recommended Charts tab (as well as the All Charts tab). All Charts
  4. To select a chart, select it and click OK. Chart

Move a Chart to a Chart Sheet

You can move an embedded chart to its own chart worksheet.

Click the chart, and from the Chart Tools Design tab, from the Actions group, select Move Chart.

Move Chart Option

The chart now appears on a standalone worksheet, rather than embedded in the original worksheet.

Inserting Charts

Duration: 15 to 25 minutes.

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

  1. Open Charts.xlsx from your Excel2019.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 Insert Pie or Doughnut Chart (in Excel 2010, this is the Pie chart command) command:Insert Chart
    3. Select the -D Pie (Pie in 3-D in Excel 2010) chart by clicking on it:3D Pie Chart
  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 Insert Line or Area Chart (Line chart command in Excel 2010) command:Insert Line Chart
    3. Select the Line chart by clicking on it:2D 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 Insert Column or Bar Chart (Column chart command in Excel 2010) command:Insert Column Chart
    3. Select the Clustered Column chart by clicking on it:2D Column

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 2010, there is a third Chart Tools tab, called Chart Tools Layout. In Excel 2013 and 2016, 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 2010) to see all the layouts available:Available 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 Types

Using Chart Tools

Charts are easy to format and work with using three chart buttons that appear when you select the chart.

To use the chart tools:

  1. Click the chart you want to work with.
  2. Notice the three buttons that appear next to the chart. Chart Format
  3. Use the Chart Elements button to quickly work with the legend, data labels, axis titles, and more. Chart Elements
  4. Use the Chart Styles button to change the style and color of your chart. Style
  5. Use the Chart Filters button to change which data points and names appear in your chart. Value

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:More Button
  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 2010), in the Insert Shapes group (Insert group in Excel 2010), click the More (Shapes in Excel 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 Option
  3. Check the Trendline check box. Click the arrow next to Trendline to change trendline options.Trendline Option
  4. Your chart may look like this:

Trend Line

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. Chart 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.

Add Data

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.

Chart Tools Design Tab

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. Switch Row/Column Command
  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 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.

Using the Quick Analysis Tool

The Quick Analysis tool is a feature that allows you to convert your data quickly and easily into a chart or table.

To use the Quick Analysis tool:

  1. Select the cells of data that you want to analyze. Select
  2. Notice the Quick Analysis Tool icon that is displayed at the bottom of the data. Click it. Quick Analysis
  3. Roll over each option in the Quick Analysis gallery to see a preview. Choices
  4. To select an option, click it. Results

Watching Animation in a Chart

In Excel 2013, 2016, and 2019, 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 Chart

Updated Chart

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 2010), in the Chart Layouts group (Labels group in Excel 2010), click Add Chart Element and select Legend (Legend command in Excel 2010) - Select one of the legend options by clicking on it:Add Chart Element Command

Showing 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 2010), in the Chart Layouts group (Labels group in Excel 2010), click Add Chart Element and select Chart Title (Chart Title command in Excel 2010) - Select None if you do not wish to display the title, or one of the other options if you do wish to display it:Chart Title

Changing the Title of a Chart

To change the title of a chart:

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

Showing, 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 2010), in the Chart Layouts group (Labels group in Excel 2010), click Add Chart Element and select Data Labels (Data Labels command in Excel 2010) - 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. Data Labels Option
  3. 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:Style Selection
  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 Excel2019.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 2010, it should be a different shade of maroon):Image of 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:Image of Changes
  4. On Sheet3, add styles to the chart such that it has an orange outline, blue fill, and all text is outlined in red:Image of Chart

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 2010, just select Layout 1):Layout Option
    3. On the Chart Tools Design tab, in the Styles group, click Style 4:Style Selection
  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 2010), in the Chart Layouts group (Labels group in Excel 2010), click Add Chart Element and select Legend and select Right (Data Labels command in Excel 2010 and select Show Legend at Right)Show Legend at Right Option
    3. On the Chart Tools Design tab (Chart Tools Layout tab in Excel 2010), in the Chart Layouts group (Labels group in Excel 2010), click Add Chart Element and select Chart Title and then select Centered Overlay (in Excel 2010, select Chart Title and select Centered Overlay Title):Centered Overlay Option
    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):Orange Outline
    3. On the Chart Tools Format tab, in the Shape Styles group, click Shape Fill and then select a color:Blue Fill
    4. On the Chart Tools Format tab, in the WordArt Styles group, click Text Outline and then select 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. Select Object
  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

Add Alternative Text to Objects

You can add alternative text, called alt text, to objects you insert in Excel, to allow screen readers to be able to identify them.

To add alt text to an object:

  1. Right-click the object, and select the Format option. For example, for a shape, select Format Shape.
  2. Select Size Properties, and select Alt Text. Note you may need to Right-click and choose Edit Alt Text on some types of artwork. Enter a title, if desired, and enter a description. Alt Text Option

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.
  3. Now you can type within the text box. Type 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 Template
  3. The chart now appears as an option in the Templates section when you are inserting a chart. Insert Template