Formatting Charts

Quick reference

• 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.