How to build a scatter chart in excel 2010

#How to build a scatter chart in excel 2010 series

Some fields will overshoot that 5000th row.

I only copied the formulas down to row 5000, and set my named ranges to cap out at row 5000 as well.

You must also perform the happy Excel dance in order to appease the ancient lords of recalc Also note that you must prepend the name of the workbook, then a вЂњ!вЂќ, to your names in orderД¬. Note that IвЂ™ve already done that in the picture aboveД«. Then you edit that formula, replacing any range of cells with your dynamic named ranges, XRange and YRangeДЄ.

#How to build a scatter chart in excel 2010 series

If you do this right, your data points will get selection handles and the SERIES formula will appear in the formula bar, like this:ДЈ. Now, in the created chart, select the data range within the chart.Point it at whatever data you currently have, as if it was going to be a static chart (yes, use the refedit control for now)

What you put in here will NOT adjust, ever.

This is the chart refedit control, for reference. The reason is, that refedit control will eat your dynamic named range and convert it into a fixed range, destroying all of that incredibly obscure work you did above J Second is this: YOU MUST NOT RELY ON THE REFEDIT CONTROL TO POINT YOUR CHART AT YOUR DATA The formula I used for YRange is simply another offset off of that first named range (XRange), using this formula: Here is that formula in case you want to copy paste it: Since each column has a different distinct number of members, youвЂ™ll get behavior that is essentially the same as more/less data after a refresh.ДЇirst is defining named ranges that refer to an OFFSET formula rather than a fixed range: To simulate the effect of getting more/less data, modify the Set in cell A1 to grab any column out of that youвЂ™d like. OK, you have the workbook now, right? Let us continue. It will be easiest if you follow along on your own desktop, so PLEASE GRAB THE WORKBOOK FROM HERE вЂ“ that contains the results of everything I show below. But why complain? Fact is, it CAN be done, and it feels like magic. YouвЂ™re gonna use that CUBESET function, and youвЂ™re gonna like it!Д«ut you might have a love/hate reaction to some of the other Excel acrobatics I am about to share :) For something that Excel actually supports natively, and seems to have gone out of its way to support, this should be easier to do than it is. Intrepid PowerPivot adventurers, however, may want to try out the following technique, by which the data WILL be picked up in your chart. The answer is вЂњno, not even close.вЂќ :) When you convert to formulas, the original list of rows/columns is fixed, by virtue of the CUBEMEMBER function that makes up the axes. But tomorrow, I may have a different number of data points. The next question is usually вЂњOK great I can scatter chart my data points. Once itвЂ™s converted to formulas, you can create any chart type you want against it, no restrictions. Remember the post where I converted a PivotTable to Cube Formulas using that button on the ribbon? This is a wonderful opportunity to use that feature in an unexpected way. Try to use XY (Scatter), Bubble, or Stock as a PivotChart, and youвЂ™ll get the alert below: But not all of them are supported as PivotCharts. This was going around on the MS-internal discussion list this past weekend:ДЄt right is a list of all chart types in Excel.