Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In this course, Dennis Taylor shows how to analyze and communicate the value of data with charts in Excel. The course starts with the foundations: what the parts of a chart are, what the different types of charts are, and which charts work best for your data. The course then shows how to create a presentation-ready chart in minutes and offers dozens of in-depth tutorials on formatting and fine-tuning charts so they represent data clearly and accurately.
As you look at the chart on this worksheet, you would recognize almost immediately that the numbers in column B, which are in the millions, are reflected in the chart along the vertical axis on the left-hand side. As a general rule, the numbers on an axis in an Excel chart are in sync with the values, but maybe not quite in the way that you might think. There are times when you want a different format on the chart than you actually see within the data. Now, let me point out if I do change the data, for example if I click column B here right now in this worksheet and on the Home tab if I simply apply the Dollar sign, it's going to change not only the format of the data in column B, but also on the chart, and we see what's happened.
We see Dollar signs and decimal places in column B as well as on the chart. But you can't make the reverse assumption. In other words, we can and we sometimes want to have a different format on a chart than we do in the actual data. Now I'm not a big fan of the formatting in column B and you might not be either, but let's say that we do have that format and we would like to change the actual chart. Let's just click on the chart axis here. On the Layout tab in the Ribbon, if you have chosen it that way or possibly you clicked the drop arrow here to make the choice, the Vertical Axis choice, you can then go right into Format Selection-- this is one of a few ways to get here-- and the Format Axis dialog box pops up.
One of the choices over on left-hand side is Number. Now maybe we're not sure what we want, but let's say that we don't want decimal places. Maybe we'll just stick with Accounting here but not have 2 decimals. How about 0? As I close this, you see what happens. We don't have decimal places, but we still have that in the original data. So you could say in a certain sense this is no longer in sync with the data, but it certainly is reflective of the information at hand. I think you could easily say, "Well yeah, but do we really need to see all the zeros? What if we insert some text eventually that says these are in millions of dollars? Why don't we display this simply as 25, 20, 15, et cetera?" Skip the Dollar sign or not. We will decide on that possibly.
Another way to get into formatting here is simply to right-click the selection. So we have selected the axis already, so right-click > Format Axis, and we are in the same dialog box we saw before. A third way to get here, as I close this, is simply from a selected axis, press Ctrl+1. Now normally within an Excel worksheet that activates the Format dialog box. So it does here too, in a different sense. So here too we want to go to the Number tab within the Format Axis dialog box, and let's choose a custom format.
Now you might or might not know that there is a way to display values in millions. By the way, when you first come in here sometimes you'll get some bizarre temporary format changes that in this case of course we're not going to keep, but we're seeing dates over there that looks strange. But let's choose, for starters here, Number, or possibly Currency or Accounting, and then shift over to Custom. Now we can start almost anywhere here. I'm going to choose one without Dollar signs, say this one. If we are never going to be using negatives, we can simply get rid of this portion of it.
You don't have to be a formatting wiz to necessarily know how to do this, but the next step is certainly an unusual one. To display numbers in thousands, you either end the display in 0 or .0 and then put in one trailing comma. So if we were to add this as a custom format, we will be seeing the numbers on the left-hand side in that vertical axis, 25,000, 20,000, et cetera. That's not what we want. We want millions, so we put in another comma. So each comma suppresses the display of three characters to the left of the decimal.
Now that's, again, a far cry from being intuitive, but this will work. We need to add this, and you see immediately what's happened. Click close. Now you might have wanted the Dollar sign in there. I might put a Dollar sign in front of this right here. You can certainly do that. If that's important, add that. We see what's happening. Click Close. Of course, what you might also want to do is to, by way of Axis Title, put in a title there to accompany that so that would make sense too. But don't forget this axis here as well. Right now, for the moment, we're displaying exactly what we're seeing in column A, and that might be fine, and I'm not suggesting that we always change these, but keep in mind here, too, by simply right- clicking here, that might be as fast as any way as we suggested. Go to Format Axis, Number, and of course this time the focus would be on one of the Date options.
So you pick the one that makes sense to you, and there are quite a few choices there. And here too you might jump into Custom and if you're familiar with how to use Ms and Ds and Ys, there are any number of different kinds of formats, particularly if you start with Date here and then shift over to Custom. You can make up your own if you wanted to, something like this. So maybe we will put in 3 m's, and again I'm not suggesting in this case it would be as necessary as it might have been. If you want to put in the first day of each month, how about mmm-d- and if you want 4-digit years, four y's.
You see what's happening in the display. So quite a few different ways to approach this, but remember, even though in general we think of the values on the axes in a chart to be reflective of the data, and they are, they're not always exactly the same format. And you can change the format of the data on the axes in its chart simply by right-clicking and going to Format Axis and making the change that way, independent of the data in the worksheet.
There are currently no FAQs about Excel 2007: Charts in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.