Bespoke Microsoft Office Development 
Consultancy    Excel    Word    PowerPoint    Outlook    VBA    C#    Tools    Newsletter 
Excel > Charts > Chart Series Formula  < Previous  Next > 
What is a Data Series ? 
A data series is just a group of related data representing a row or column from the worksheet.  
When you select a particular data series on a chart its corresponding series formula will appear in the formula bar. 
When the source data is changed you will see that the series formula is changed automatically. 
When a series is selected the series formula is displayed in the formula bar at the top. The name of the series is displayed in the “Name box” to the left of the formula bar. 
What is the Series Formula ? 
Excel uses a series formula or function to define a data series for a chart. There is one series formula for each data series.  
Although this resembles a typical worksheet formula there are a few important differences.  
1) The SERIES function cannot be used directly from another cell.  
2) This formula cannot contain any worksheet functions.  
It is possible though to edit this formula and to manually change the arguments. 
Surface charts do not have series formula ?  
chart types  83,84,85,86 
Series Formula Arguments 
[=SERIES(Name, XValues, YValues, PlotOrder, Bubble Sizes) ] 
The data used in each series in a chart is determined by its SERIES formula. Press Enter to apply any changes. 
 
 
 
 

Series Formula Rules and Facts 
Range references in a SERIES formula are always absolute and always contain a worksheet name. If you have defined named ranges then the series formula can contain a named range in place of a range.  
Any range references used are always prefixed with the worksheet name.  
Any range references used are always absolute references (as opposed to relative).  
If you accidently remove the worksheet name or use a relative reference, these will be changed automatically.  
Each data series has a corresponding series formula and the number of arguments will depend on the chart type. 
Examples of Valid Series Formula 
=SERIES(Name, XValues, YValues, PlotOrder, Bubble Sizes)  
=SERIES(Sheet1!$C$2, Sheet1!$B$3:$B$9, Sheet1!$C$3:$C$9, 1)  
=SERIES("North", {"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}, {0.65,0.21,0.86,0.97,0.05,0.34,0.74}, 1)  
=SERIES(Sheet1!$C$2, , Sheet1!$C$3:$C$9, 2)  
=SERIES(Sheet1!$C$2, Sheet1!$B$3:$B$9, Sheet1!$C$3:$C$9, 3, Sheet1!$D$3:$D$9)  
=SERIES(, (Sheet1!$B$3:$B$9, Sheet1!$B$20:$B$30), (Sheet1!$C$3:$C$9, Sheet1!$C$20:$C$30), 4) 
Changing the Arguments 
You can change any of the series formula arguments in four ways you can change the series name of a data series:  
1) Overwriting the cell(s) which the argument refers to.  
2) Using the Automatic Range Finders to move the source data.  
3) Using the (Chart > Source Data)(Series tab).  
4) Editing the Series Formula.  
The only way to change the order of the data series is to either use the (Chart > Source Data)(Series tab) or to edit the series formula directly. 
Things to Remember 
 
 
 
 
 
 
 
 
 
 
 
 

© Better Solutions Limited 04Apr2014  < Previous  Top  Next > 