Excel Dashboards and Reports, 2nd Edition

Chapter 6: Working with Chart Series

In This Chapter

• Adding and removing series from a chart

• Finding various ways to change the data used in a chart

• Using noncontiguous ranges for a chart

• Charting data from different worksheets or workbooks

• Dealing with missing data

• Controlling a data series by hiding data

• Unlinking a chart from its data

• Using secondary axes

Every chart consists of at least one series, and the data used in that series is (normally) stored in a worksheet. This chapter provides an in-depth discussion of data series for charts and presents lots of tips to help you select and modify the data used in your charts.

All workbook examples in this book are available on the companion website for this book at www.wiley.com/go/exceldr.

Specifying the Data for Your Chart

When you create a chart, you almost always start by selecting the worksheet data to be plotted. Normally, you select the numeric data as well as the category labels and series names, if they exist.

When creating a chart, a key consideration is the orientation of your data: by rows or by columns. In other words, is the data for each series in a single row or in a single column?

Excel attempts to guess the data orientation by applying a simple rule: If the data rows outnumber the data columns, each series is assumed to occupy a column. If the number of data columns is greater than or equal to ...