CHARTS: How to plot nice chart for any formula / equation

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.