Would you like to create impressive
charts effortlessly, based just on a single formula? Would you like your
charts look similar to these examples? If so, follow the directions
below.


One of interesting features related to charting in Excel allows you to plot charts based not on actual data ranges/tables but based on formulas.
Here are the basic steps to follow if you'd like to create such nice charts quickly. If you create just one of them, you can save the file as a template for future use.
The first thing to do is to enter/arrange all necessary information you'll need for your chart elements. So, enter the following in your worksheet:
Cell
|
Text entry
|
Cell
|
Data
entry
|
A1
|
“Formula to plot”
|
B1
|
Enter/paste your formula
to be plotted
|
A2
|
“xLeft”
|
B2
|
Enter number for starting
point of X axis
|
A3
|
“xRight”
|
B3
|
Enter number for ending
point of X axis
|
A4
|
“Number of points”
|
B4
|
Enter number of
points to be plotted
|
A5
|
“Chart title”
|
B5
|
Enter “Plott for: “Sheet1!$B$1
|
|
|
|
|
A7
|
“List of formulas”
|
|
|
A8
|
Enter your first
formula, e.g. x^3+20^2-100*x
|
A9
|
and 2nd one, e.g.
(sqrt(cos(x))*cos(500*x)+sqrt(abs(x))-0.4)*(4-x*x) |
|
A10
|
Etc….
|
When you enter all required information your worksheet may look similar to this one:
Remember that you have to follow the format of the formulas exactly as shown here in the examples. No free spaces are allowed within the equation; they would create error message. Remember also that you can change the plot view by changing coordinates and number of points plotted (entries in cells B2:B4).
The second thing to do is to define some Names. On Formulas tab, in "Define Names group, click "Define Name" and define the following Names, one by one:
Name
|
Refers
To:
|
Formula
|
=Sheet1!$B$1
|
xLeft
|
=Sheet1!$B$2
|
xRight
|
=Sheet1!$B$3
|
xNoOfPoints
|
=Sheet1!$B$4
|
xRng
|
=xRight-xLeft
|
x
|
=xLeft+xRng/(xNoOfPoints-1)*(ROW(OFFSET(Sheet1!$A$1,0,0,xNoOfPoints,1))-1)
|
y
|
=EVALUATE(Sheet1!$B$1
& ”+0*x”
|
Now, select any area in your worksheet and, in your the Menu Bar select Insert>Charts>Insert Scatter(X,Y)... Select one of Scatter charts. Place the chart conveniently in your worksheet and right-click on the chart area. In Edit Series window, click on Edit button and in Edit Series window enter:
- under Series name: select range B5 in the worksheet,
- under Series X values: enter "=Shee1!x"
- under Series Y values: enter "=Sheet1!y"
Now you can format your data points in the chart. Right-click in chart area and select Change Chart Type. Select All Charts and click on 3-D Bubble icon. Click OK.
Next, right-click on data points (markers) and select Format Data Series... . Click on Series Options icon (
) , set the Scale bubble size to e.g. 15 (Area of bubbles), then click on Fill & Line icon (
), select Fill>Automatic and mark Vary colors by point.
If you need to edit/add chart title, right-click on the title box and either:
- select Edit Text and edit it, or
- in Formula Bar enter "=" and select cell B5.
Select the chart, click on Chart Elements icon (
) and select those elements which you want to be displayed in the chart (titles, axes, gridlines, data labels, error bars, legend, trendline). Make any changes you want to those elements.
Select the chart and click on Chart Styles icon (
) and select your favorite style and color scheme for your chart.
Have you got any interesting chart? If so, please share it with me.