nc-state logo
Excel
Christopher G. Healey

Introduction

This module will introduce you to basic chart visualization in Excel, Microsoft's long-standing spreadsheet program. We will cover the basics of creating a chart, modifying it, appending new data to it, and varying how different data series are displayed. We will also briefly introduce pivot tables and pivot charts.

Unfortunately, Excel changes their UI with each major release. Our instruction assume Office 365 ProPlus version 15.0.4787.1002 for Windows 8.1 or higher, although any Office 365 version will probably work. For earlier versions of Excel, although most or all of the options may exist in the software, their placement and methods to expose them are likely to be different from our instructions.

Basic Chart Example

To begin, we will construct a basic Excel chart containing two side-by-side bar graphs representing historical maximum and minimum temperatures for Raleigh, NC. We will also overlay a line chart representing historical minimum rainfall for the same Raleigh geographic region.

The instructions below detail, step-by-step, exactly how to build the basic chart, then modify its components to produce a high-quality, perceptually accurate result.

  1. To load the data to chart.
  2. To build a basic side-by-side bar chart.
  3. To offset the bars from one another.
  4. To correct for overlapping negative bars and month labels.
  5. To set the chart title.
  6. To move and format the chart legend.
  7. To add a legend to the vertical axis.
  8. To format the temperature tick labels.
  9. To add labels to the bars.
  10. To correct for overlapping negative bar labels and month labels.
  11. To add Prcp Min to the chart.
  12. To convert Prcp Min to a line chart.
  13. To add a legend to the secondary vertical axis.
  14. To format the secondary vertical axis.
  15. To set the colour of the series bars and line.
  16. To update the titles in the legend.

Pivot Chart Example

As a follow-on, we will briefly introduce pivot tables and pivot charts. Pivot tables are a construct in Excel that allows you to aggregate values over one or more categorical data attributes. A very common way to use pivot tables is to aggregate by date, but you are not restricted to using only dates as the aggregation variable. Pivot charts visualize values in a pivot table. As the pivot table's aggregation changes, the chart automatically updates to present the new values.

  1. To load the data to chart.
  2. To build a basic line chart of temperatures.

    This chart does a good job of visualizing the overall trend of maximum, mean, and minimum daily temperatures, showing the expected downward trend as we move from August to December, as well as a number of peaks and valleys. These may be noise, or temperatures that relate to specific events, and may be worth investigating further.

    Suppose, however, we wanted to look at the data from a less granular level, for example, by month or by quarter. Pivot tables and pivot charts offer exactly this functionality.

  3. To build a pivot chart of temperatures.
  4. To convert from Sum to Average temperatures.
  5. To format the average temperatures.
  6. To build a PivotChart for the PivotTable values.
  7. To aggregate by Month and Quarter.

PivotTables have functionality beyond aggregation. They can be used to filter data (for example, clicking on any of the "buttons" in the PivotChart will bring up a dialog that allows you to choose which data to display). They can also "pivot" the data by swapping rows and columns to explore the data in different ways. For more information on PivotTable, you can read Microsoft's introduction to PivotTables or Google search for PivotTable tutorials.

Updated 01-Jan-01