This video is part 7 in a series of videos on graphing in excel 2010.
This part I will be going over how to make a surface plot or a 3-D plot.
On my spreadsheet I do have an example assignment question.
This question states to create a surface plot for the equation z = 5 * x^2 - 4 * y^4 where
"x" and "y" are both between -2 and 2.
To create a 3-D plot you need to have a 3-D range of data.
Normal plots have a 2-D range, the "x" and the "y" in two columns.
Now however you have an "x", "y" and "z".
Your "x" and "y" are both independent variables so they need to be set up separately.
Here I am going to put "x" which is going in this direction and "y" which is going in
this direction.
That means the "z" is going to be left going in this direction.
Now we need to input the information.
The "x" goes from -2 to 2.
I am going to do it in a range of 0.5.
I have -2 and -1.5 then I highlight both of these cells and use the fill handle to take
it all the way out to 2.
Then I do the same here.
Fill in -2 and -1.5 and use the fill handle to fill in the rest.
That means the equation has to go in this area.
The equation is 5 * x^2 - 4 *y^4.
I enter the equation as "= 5 * C10^2 - 4 * B11^4".
For these two values that is a -44.
I can go through and manually type that in for every single one of these cells but there
is an easier way to do this.
Here since I need this to remain row 10 as we go through all of the "x" values I need
to put a dollar sign in front of the 10.
This means that when I use the fill handle to change things the "C" or the column can
change but the 10 which is the row cannot.
I need to do the opposite for my "y".
I need to make sure that the column "B" does not change even though the row 11 is allowed
to.
I press enter again and the value is still -44.
Now when I use the fill handle and drag it down it fills in the rest of the way here.
If I select this one for example "C10" is still the correct location for "x" and now
"B16" is the "y" which is the "y" that is currently next to this selected cell.
If I highlight that range again and do the fill column over one and select this one my
row is still 10 but my column has changed to "D" for my "x".
My "y" is still "B13" so "B" remains the column but 13 is the new row.
If I highlight this again and drag it all the way over it fills in the rest of this.
To make this a little bit easier to read I am simply going to format the cells and put
a border around these answers.
Then now I am going to put a border around the "x" and one "y" values.
This way I can see that here are my "y" values, here are my "x" values and these are my "z"
values.
To plot this I highlight my range of "z" values and go to insert, other charts and I select
a surface chart.
This is my chart here.
I right click and I am going to move it to its own sheet.
I am going to name it surface.
You can see that the values are all very small and hard to read so I am going to change the
font up to 16.
The way surface charts work is by color coding.
This blue range here you can see is where the chart is in the range of -70 to -60.
The next color up is the range of -60 to -50 and so on.
All the way up to here which is 10 to 20 at only a few locations.
Changing surface charts to make them more easy to read or view is a bit different from
using normal 2-D charts.
If I right click and go to format chart area.
I have lots of options here.
3-D format is where I can change the 3-D view of it.
If I want to have beveled edges it adds that to the outside edge here.
If I change the bevel edge to this you can see that the edge changed here.
I do not really care so I am going to turn that off.
3-D rotation is really important.
If your graph is set in a location where it is hard to see what it is trying to show you
then you need to change the 3-D rotation.
Here I am going to change the perspective and increase it to 40.
If I move this out of the way you can see here that it is actually a lot more difficult
to view.
I am taking this back down.
This is actually pretty good for viewing this.
I can change other properties of this but I think that is all that I want to alter here.
I close that and go back here.
Now what I am going to do is label these because I do not know if this is the "x" or this is
the "x" although I am pretty sure that this one is the "z".
As you can tell even though this could be the "x" or the "y" it goes from 1 to 9 since
there where 9 values instead of -2 to 2.
To change this I go to select data and I pick a series.
The series is what is listed on here which is the y-axis.
This here is my y-axis.
I edit it and my first value goes from "C11" to "K11" so "B11" is my series name.
I have to go through and edit every one of these series that way.
Now I have all of these now entered as the series name or the "y" value that is associated
with that location.
If I look back here I can see the numbers are now shown where they are suppose to be
for the y-axis.
The x-axis still needs to be changed.
I go to my horizontal axis label and click edit and go to sheet one and highlight the
range.
It changes it for all of them.
Now it is labeled -2 to 2.
I am going to expand the ribbon and go to layout.
I need to add a chart title but I am just going to name it "Assignment Question" since
there is no specific example associated with this but I need to add axis to the title.
The depth axis title which is the y-axis on the right I want to have a horizontal title
shown there.
I am going to name it "Y-axis".
The vertical which is my "z" axis is rotated over here and I will name that "Z-axis".
My horizontal is simply "X-axis".
Even though this particular example does not have anything associated with those axis it
is still important to label which one is which.
That way I can see that there seems to be a 0 here when this is about 0 and 0 although
it is still very difficult to view this sort of thing in excel and it would just be better
to go to the sheet and find out where it is 0.
It is 0 where everything else is 0.
Surface charts can be extremely useful in excel but they are also very difficult to
use and in that case they are not used very often.