Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Changing the numeric format on labels

From: Excel 2007: Charts in Depth

Video: Changing the numeric format on labels

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.

Changing the numeric format on labels

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.

Show transcript

This video is part of

Image for Excel 2007: Charts in Depth
Excel 2007: Charts in Depth

53 video lessons · 9323 viewers

Dennis Taylor
Author

 
Expand all | Collapse all
  1. 1m 49s
    1. Welcome
      1m 3s
    2. Using the exercise files
      46s
  2. 22m 7s
    1. Identifying chart elements like plot area, chart area, gridlines, and legends
      5m 3s
    2. Selecting the right chart type
      8m 2s
    3. Understanding chart terminology
      6m 31s
    4. Understanding the Ribbon and the Design, Layout, and Format tabs
      2m 31s
  3. 16m 24s
    1. Selecting data to display as a chart
      6m 17s
    2. Creating charts instantly with shortcuts
      4m 33s
    3. Creating charts with standard menu commands
      2m 24s
    4. Creating presentation-ready charts with just a few adjustments
      3m 10s
  4. 28m 8s
    1. Switching rows and columns for a different view of the data
      3m 9s
    2. Setting a default chart type and creating a template
      3m 47s
    3. Dealing with empty and hidden cells
      4m 18s
    4. Choosing a chart layout
      4m 33s
    5. Choosing a chart style from 48 colorful variations
      3m 34s
    6. Changing the location of a chart
      3m 33s
    7. Moving and resizing a chart
      5m 14s
  5. 11m 25s
    1. Using pictures as chart elements
      3m 55s
    2. Adding shapes and arrows
      3m 37s
    3. Adding floating text and text boxes
      3m 53s
  6. 33m 45s
    1. Adding, editing, and removing chart titles
      3m 15s
    2. Adding horizontal and vertical titles
      3m 56s
    3. Linking titles to content
      2m 32s
    4. Showing numbers of different scales
      4m 38s
    5. Specifying the position of tick marks and axis labels
      2m 41s
    6. Changing the numeric format on labels
      5m 34s
    7. Adding, editing, and removing legends
      3m 19s
    8. Adding and editing data labels
      4m 40s
    9. Showing the source of a chart's data
      3m 10s
  7. 8m 13s
    1. Modifying axis scaling
      3m 50s
    2. Working with gridlines
      4m 23s
  8. 12m 53s
    1. Analyzing existing and future data with trendlines
      4m 2s
    2. Adding drop lines
      3m 14s
    3. Adding high-low lines and up-down bars
      1m 39s
    4. Adding error bars
      3m 58s
  9. 9m 13s
    1. Selecting shape fill and outline
      3m 3s
    2. Adding shape effects
      3m 19s
    3. Applying WordArt styles
      2m 51s
  10. 16m 33s
    1. Formatting lines and borders
      4m 24s
    2. Filling an area with a color gradient
      2m 7s
    3. Specifying line style, color, and weight
      2m 46s
    4. Working with chart text
      3m 40s
    5. Changing the rotation of chart text
      3m 36s
  11. 38m 39s
    1. Using column and bar charts
      7m 24s
    2. Using line charts
      5m 46s
    3. Using pie charts
      8m 37s
    4. Using area, stock, and XY charts
      9m 3s
    5. Using doughnut, bubble, and radar charts
      7m 49s
  12. 10m 55s
    1. Pasting new data into a chart
      2m 58s
    2. Creating charts from multiple data sources
      3m 39s
    3. Adding new data using a table
      4m 18s
  13. 6m 19s
    1. Printing charts
      6m 19s
  14. 19s
    1. Next steps
      19s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2007: Charts in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.