Locked lesson.
About this lesson
Learn how to create title and various options for your chart, adjust data source and change chart type.
Quick reference
Formatting Charts
Adjust source data and change chart type
When to use
Use when you want to change what is being reported in a chart. Use when you want to change to another type of chart
Instructions
Change source data
- Click on the chart
- Click ‘Select Data’ from the Data command group
- Highlight the new data range directly from the worksheet
Change chart type
- Click on the chart
- Click ‘Change Chart Type’ from the Type command group
- Select the new desired chart type
- 00:05 One of the main reasons for
- 00:06 working with charts is usually because we want to establish a trend or
- 00:10 relationship where words or numbers are less effective or less obvious.
- 00:15 If your chart doesn't do this, you may be using the wrong chart for
- 00:18 that type of data.
- 00:20 And that's why Excel has a variety of chart types to choose from.
- 00:24 Here we have a column chart, which really doesn't add much
- 00:27 value to the data that was all ready recorded on our worksheet.
- 00:31 We already knew the number of each item sold, so
- 00:34 we're not learning anything new by looking at this chart.
- 00:37 Maybe if we changed what was being reported,
- 00:40 our chart would tell a more visually interesting story.
- 00:44 Let's look back at our source data to see what questions these numbers could answer.
- 00:49 Could we find out whether there was any month that was better than the others?
- 00:53 We click on our chart, and we see that our contextual Chart Tools tabs are available.
- 00:59 When we click on the Design tab,
- 01:01 an option to select our data shows up in the data command group.
- 01:05 We click that.
- 01:06 And for the chart data range, instead of using the total items sold,
- 01:11 let's select the three months January, February, and March.
- 01:16 We could manually type the range in, but why do that
- 01:19 when we can easily select the relevant range right here on the worksheet?
- 01:24 And it's entered within the dialogue box.
- 01:27 We noticed before we hit OK that Excel will create a legend for
- 01:30 January, February, and March.
- 01:33 And that the horizontal axis labels will be the items for sale, books,
- 01:38 crayons, erasers, and so on.
- 01:41 If there was any data that we didn't care to see, we'd uncheck them and
- 01:45 they wouldn't show up on our chart.
- 01:47 But we do want to see them all.
- 01:49 So we recheck and click OK.
- 01:52 Now we have our chart but we're not seeing the name of each month.
- 01:56 And we'd much rather see that because we're comparing sales by month.
- 02:00 Did we do something wrong?
- 02:02 Not really.
- 02:03 It's just a matter of the chart layout.
- 02:05 We can go up to the chart layouts command group, and click on the quick
- 02:10 layout drop down to see if any of the prebuilt layouts work for us.
- 02:15 Hovering over each layout gives you a preview of your own chart.
- 02:19 And if you like it, just click and it's updated.
- 02:23 We want our chart to be informative but not cluttered.
- 02:26 So right now let's go back to layout one.
- 02:29 And our chart is created.
- 02:30 We have the legend with our three months here.
- 02:33 One thing that we learned from this chart is that with the exception of marker
- 02:36 sales, February was a good month for
- 02:39 sales, all the other items they're the same or better than January and March.
- 02:44 And maybe we can start asking questions like, what event or
- 02:48 events may have contributed to this?
- 02:51 Is this true of all February's, how did we do last February?
- 02:55 Can we expect the same next February?
- 02:58 Maybe our analysis will reveal that we actually ran out of markers, and so
- 03:03 we should have ordered more.
- 03:04 We could have and still can further customize our chart
- 03:08 by clicking on the Add chart element command within that same command group.
- 03:13 And we see options to add more elements to our chart.
- 03:17 A shortcut to add or
- 03:18 remove chart elements is this green plus sign right next to our chart.
- 03:23 And hovering over each one shows what your chart will look like.
- 03:25 We don't want to add any more elements to our chart.
- 03:29 We're happy with the way it looks now.
- 03:33 We can also change chart colors or styles from the chart styles command group.
- 03:38 And if we decide that this isn't the best type of chart to represent our data,
- 03:43 we can always click the Change Chart Type command.
- 03:46 And we'll be presented with the same list that we got when we created this
- 03:50 chart initially.
- 03:51 But let's say we're satisfied with our chart.
- 03:54 We click OK.
- 03:57 And if we want to move it out of the way and start working on another chart to tell
- 04:01 another story, we can place this chart on its own sheet or in whatever location
- 04:06 we choose by clicking on the move chart command that's located here.
- 04:13 And we can place our chart in its own new sheet, or
- 04:17 as an object within our current sheet.
- 04:20 Let's put it in its own sheet and we'll call it column chart.
- 04:26 We click OK.
- 04:28 And that chart is by itself.
- 04:30 We noticed that it's placed right before the sheet with our source data.
- 04:35 So we go back to our source data to start telling another story.
- 04:40 Another popular chart type is pie charts.
- 04:43 Pie charts are generally used to show proportions or percentages.
- 04:48 We can easily see from our data set that markers accounted for
- 04:51 the bulk of our sales revenue this quarter.
- 04:54 But what percentage was it?
- 04:56 Well, if you do some quick mental math, you'll probably figure that it's about
- 04:59 a third, but it gets trickier for other numbers which are smaller.
- 05:04 And anyway, there's no need to do all that mental math when the Excel charts
- 05:08 can calculate and show us the numbers we want.
- 05:11 So as usual, we select a range for a data, which is going to be the items
- 05:17 we control or command and select the sales totals.
- 05:23 We go to our Insert Tab and we know that we want a pie chart.
- 05:28 So we click on the pie chart drop down.
- 05:31 And we can choose from 2D, 3D, and donut pies.
- 05:37 Let's do a 2D pie, that's the most common one.
- 05:40 We click OK, and this looks pretty basic.
- 05:45 We already know how to add the extra elements we want.
- 05:49 We can also go to the quick layout for prebuilt layouts or
- 05:53 customize by adding the chart elements with the green plus sign.
- 05:57 Let's go to the quick layout and see if there's any that we like.
- 06:01 We hover and we're taking a look at what our data will look like.
- 06:06 We do like layout one because it has a chart title, series names and
- 06:12 the percentages we're looking for.
- 06:14 And if there's anything on this chart that we want to make more visible,
- 06:17 then it's really just a click away.
- 06:19 So let's click on layout number one.
- 06:22 We see that the rulers element
- 06:24 is not that visible because it's almost the same color as the background.
- 06:29 So we can do one of two things.
- 06:30 We can either click on this element and
- 06:35 drag it outside to make it more visible.
- 06:40 Or if we wanted it to stay within the chart,
- 06:44 we could highlight by selecting and changing the color, maybe to white.
- 06:51 So it's obvious, and that's what our chart looks like.
- 06:56 What if we wanted to talk specifically about marker sales,
- 06:59 then we could click on that element and explode it from our chart.
- 07:05 If we want to visually talk about marker sales, we Ctrl+Z to put that back in.
- 07:13 The format tab on the Chart Tools allows multiple changes to the look and
- 07:17 feel of any chart that you create.
- 07:20 If you click on the element that you're looking to restyle, let's say you want to
- 07:24 restyle the title of your chart and you can apply any of the available options.
- 07:30 There are WordArt styles.
- 07:31 So we're hovering over each one.
- 07:34 And if we wanted to change this to this style, then we just click on that.
- 07:42 But if we feel that we want to go back to the overall visual theme and
- 07:48 style of the document, we just click on the reset to
- 07:53 match style command, and we're good as new.
Lesson notes are only available for subscribers.