Excel Dashboards
Books at Amazon.com |
The Excel Chart Series Formula
Introduction to the Series FormulaAn individual series' data ranges are highlighted when that series is highlighted (see below). The Y values in the series are within the blue outlined range. The Category (X axis) values are outlined in purple, and the Series name (legend entry) is outlined in green. Each outline has a handle -- a small square of the same color -- at its lower right corner. The source data range of the series can be enlarged or contracted by dragging these handles. The source data can be moved by dragging one of the colored outlines. In the screen shot above, note that information about the series is displayed above the worksheet. The Name Box contains the phrase Series "February", while the Formula Bar contains the series definition formula: =SERIES(Sheet1!$B$4,Sheet1!$C$2:$F$2,Sheet1!$C$4:$F$4,2) This formula can be broken up into four elements as follows: =SERIES([Series Name],[X Values],[Y Values],[Plot Order]) Note: Bubble Charts require one additional range of data, for the sizes of the bubbles. This is included in the chart series formula as a fifth argument. This argument can be dealt with in essentially the same manner as the [Y Values], per the discussion below. =SERIES([Series Name],[X Values],[Y Values],[Plot Order],[Bubble Size Values]) In our example: =SERIES(Sheet1!$B$4,Sheet1!$C$2:$F$2,Sheet1!$C$4:$F$4,2) Sheet1!$B$4 contains the Series Name ("February") =SERIES(Sheet1!$B$4,Sheet1!$C$2:$F$2,Sheet1!$C$4:$F$4,2) Sheet1!$C$2:$F$2 contains the Category Labels ("Apples", "Oranges", "Grapes", "Bananas") =SERIES(Sheet1!$B$4,Sheet1!$C$2:$F$2,Sheet1!$C$4:$F$4,2) Sheet1!$C$2:$F$2 contains the Y Values (8, 11, 3 ,7) =SERIES(Sheet1!$B$4,Sheet1!$C$2:$F$2,Sheet1!$C$4:$F$4,2) and the series is plotted second (2) among the chart's series collection. The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can be a whole number between 1 and the number of series in the chart. Editing the Series FormulaEditing RangesThe series formula is an Excel formula like any other. You can click in the formula bar, and edit the formula manually, to change aspects of the charted series. Select part of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). For example, start with the series formula: Select just the "C" in the series X values range to change the first column in this range. Select "Sheet1" if we want to take the X values from another sheet: Select the "2" at the end to rearrange the plot order of the series: If you change this to a lower number, the edited series will move to the lower number, and shift all series between its old and new position one number higher. If you enter zero, Excel will assume you mean 1 and proceed accordingly. If you change this to a higher number, the edited series will move up, and shift all series between its old and new position one number lower. If you enter a number greater than the number of series, the edited series will be given a number equal to the number of series. Select the entire X values element of the series formula to completely change the reference: Hint: When you select the sheet and cell range of one of the series formula elements, you can type in your preferred text for this element, but you can also drag with the mouse to select another range. Click on the sheet tabs below to select a range on another worksheet in this workbook, or select from another open workbook using the Windows menu (below) or Ctrl-Tab to switch to the other workbook. When the series data source has been chanaged to another workbook, the formula includes the name of the other workbook, as shown below: It is easier and more error-proof to use the Source Data dialog to edit the ranges in the series formula. Entering Values Instead of RangesTo enter a series of numbers as an array, you must separate the values with commas and enclose the series in curly braces: {8,11,3,7} To enter a series of text labels as an array (X values only!), you must enclose each value in quotes, separate the values with commas and enclose the series in curly braces: {"Apples","Oranges","Grapes","Bananas"} To enter a text label for the series name, enclose the label within quotes. When entered as arrays, our example series formula is transformed to this: =SERIES("February",{"Apples","Oranges","Grapes","Bananas"},{8,11,3,7},2) It is easier and more error-proof to use the Source Data dialog to enter non-range values into the series formula. ErrorsIf you enter something Excel doesn't like, you will get one of the standard Formula Error dialog boxes, shown below in decreasing order of information content. You can revert to the previous formula by typing Esc.
Add a Series with the Series FormulaA handy technique for adding a series to a chart involves series formula manipulation. The procedure is straightforward:
This technique is particularly suited to adding several series whose formulas differ only slightly from the original series. For example, when all the Y values come from parallel ranges, and all you need to do is change Sheet1!$C$2:$F$2 in the series formula to Sheet1!$C$3:$F$3, then to Sheet1!$C$4:$F$4. For more information, refer to: |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2013. All rights reserved. |