Locked lesson.

Upgrade

Quick reference

Advanced Chart Formatting

Learn some advanced chart formatting techniques.

When to use

Use these advanced techniques whenever you want to take your chart formatting to the next level. 

Instructions

When formatting charts, it's good to keep them as clean looking as possible. Formatting should enhance the information not distract from it. 

Let's take a look at a few advanced formatting techniques. In this example, we are using a 2D Column Chart (Pivot Chart). 

Hide Field Buttons

Field buttons are the grey filters that show by default on a chart. We can remove these to give the chart a less cluttered look. 

  • Right-click on any of the field buttons. 
  • Click Hide all field buttons on chart.

Remove Legend

For some charts, we can remove the legend if it's not showing anything useful. For example, if it just says 'Total', we don't really need it as we can see the totals in the chart. 

  • Click on the legend. 
  • Press Delete. 
  • OR, click the + icon to the right of the chart. 
  • Deselect Legend. 

Modify the Gap Width

Gap width relates to the amount of space in between each column in the chart. We can adjust this to make the chart more pleasing to the eye or if we want to add data labels inside the column and need a bit more room. 

  • Click on the chart columns. 
  • Press CTRL+F1 to open the Format Data Series pane. 
  • Reduce the value in the Gap Width field. 

Add Data Labels

Data Labels allow us to display chart information inside or outside the columns. For example, maybe we want to display the value of the column on the column so we can delete the vertical axis. 

  • Click on the columns. 
  • Right-click and select Add Data Labels. 

By default, the labels will show on the outside end. We can change this by pressing CTRL+1 and changing the position. 

Once we have added the data labels we can delete the vertical axis. 

Number Formatting

We can format the numbers that are used in the chart. In this example, we have very long numbers that don't fit well above the columns. We can apply some custom number formatting and display these numbers as millions instead. 

  • Click on the Data Labels. 
  • Press CTRL+1 to open the Format Data Labels pane. 
  • Click Label Options. 
  • Expand the Number group. 
  • In the Category field, choose Custom from the drop-down menu. 
  • In the Format Code field, type #0,,"M" and click Add. 

The numbers are now formatted as millions. 

Add and Position the Chart Title

  • Double-click in the Chart Title area and give your chart a descriptive title. 
  • To move the title to the left or right, hold down SHIFT as you drag the text box to keep it inline. 

Format the Chart Background

Picture Fill

A picture in the background of the chart can look effective. Be careful which picture you use and keep it as plain as possible so it doesn't distract from the chart data. 

  • Click on the chart. 
  • Press CTRL+1 to open the Format Chart Area pane. 
  • Expand Fill. 
  • Select Picture or texture fill. 
  • In Picture Source, click Insert. 
  • Insert a file from your PC, browse online images, or choose a stock image. 

Gradient Fill

Instead of a picture, we could add a gradient fill chart background. 

  • Click on the chart. 
  • Press CTRL+1 to open the Format Chart Area pane. 
  • Expand Fill. 
  • Select Gradient fill. 

We can modify the direction, intensity, and color of the gradient by changing the gradient settings and stops. 

Chart Styles

Chart Styles allow us to apply multiple formatting properties with one click. Excel has several in-built chart styles. If we are going to use a chart style, it is recommended to apply the style first as the style will overwrite any manual formatting applied to the chart. 

  • Click on the Chart. 
  • From the Design tab, in the Chart Styles group, expand the gallery. 
  • Choose a Chart Style. 

Change Colors

Charts use colors based on the theme we have selected in Excel. We can change the colors used in the chart in a couple of ways. 

  • Click on the chart. 
  • From the Design tab, in the Chart Styles group, click Change Colors. 
  • Choose a different palette. 

To change the colors to something outside of our theme colors we can either change the theme in use in Excel (Page Layout > Themes) or we can manually apply formatting. 

  • Select the chart element. 
  • From the Format tab, in the Shape Styles group, click Shape Fill and select a color from the palette. 

Insert a Slicer

Slicers and visual filters. We can insert a slicer based on any field in our data. 

  • Click on the chart. 
  • From the PivotChart Analyze tab, in the Filter group, click Insert Slicer. 
  • Choose the field you want to use as the filter. 

  • Click OK. 

Format the Slicer

Resize Slicer
  • Click on the slicer. 
  • Drag the resize handles. 
Remove the Slicer Header
  • Click on the slicer. 
  • From the Slicer tab, in the Slicer group, click Slicer Settings. 
  • Deselect Display header. 

Change the slicer from vertical to horizontal
  • Click on the slicer. 
  • From the Slicer tab, in the Buttons group, change the number of columns to match the number of items in the slicer. 
Create a Custom Slicer Style
  • Click on the slicer.
  • From the Slicer tab, in the Slicer Styles group, right-click on the current Slicer Style in use. 
  • Choose Duplicate. 
  • Give your slicer style a name. 
  • Format each element of the slicer as desired. 
  • Click on the new style to apply it to the slicer. 

Hints & tips

  • We can format other chart types such as line charts in a similar way. To create a smoothed line in a line chart press CTRL+1 and from the Fill & Line area, select Smoothed line. 
Login to download
  • 00:04 In this lesson, we're going to take a look at a few advanced chart formatting
  • 00:09 techniques to really take our charts from looking fairly standard and
  • 00:14 boring to looking interesting and engaging.
  • 00:17 And we're going to create two pivot charts based off of this pivot table data.
  • 00:21 So let's click in the first pivot table.
  • 00:24 Up to PivotTable Analyze and choose Pivot Chart.
  • 00:28 Now, we're going to start out with the most popular one,
  • 00:31 which is going to be a clustered column chart.
  • 00:33 So let's select this, click on OK, and take a look at some things that we can do
  • 00:38 with this to make it look a little bit more interesting than it currently does.
  • 00:42 It's always a good idea to keep your charts as clean looking as possible.
  • 00:46 So don't use too many different colors, too many background fills, and
  • 00:51 make sure that the data is enhancing the visualization and it's not distracting.
  • 00:56 So the first thing that I always start by doing is just removing anything from this
  • 01:00 chart which isn't really needed.
  • 01:02 So I'm going to get rid of these little gray filter buttons.
  • 01:05 So we can right click and
  • 01:07 choose Hide All Field Buttons on Chart just to get rid of those.
  • 01:11 What else don't I need on this chart?
  • 01:13 Well I can see that over here in the legend area I have total.
  • 01:17 Now, I don't really need this at this time because what I'm going to do is add in
  • 01:21 some data labels and get rid of this vertical axis.
  • 01:24 So, let's click where it says Total, it's just a textbox, so we can simply delete.
  • 01:30 Now the next thing that I always like to do is
  • 01:33 I like to make these bars a little bit wider for a few different reasons.
  • 01:38 Firstly, I just think it looks better, and secondly, if you are going to add data
  • 01:42 labels, it does look a little bit better if the bar is slightly wider so
  • 01:47 it can accommodate the value that it's displaying.
  • 01:50 So let's click on the columns, right click and Format Data Series.
  • 01:55 Now, notice over here, we have a gap width slider.
  • 01:59 So, if we take the gap width down, you can see how that adjusts those bars.
  • 02:03 We get less of a gap in between, which effectively makes these bars a lot wider.
  • 02:09 So, I'm going to take mine out to there, and
  • 02:11 then I'm going to add some data labels.
  • 02:14 So once again, we can right-click on these bars, and we can select Add Data Labels.
  • 02:19 Now, currently, this looks a bit of a mess because these numbers are pretty large.
  • 02:24 But don't worry, we are going to sort that out.
  • 02:27 Now, if you're displaying your actual values above the bars,
  • 02:30 there's not really any need for you to have this vertical axis.
  • 02:34 So I'm going to select it, press Delete, just to get rid of that.
  • 02:37 Now, these numbers currently don't look too great because they are quite long.
  • 02:42 Now, we can reformat these numbers and
  • 02:45 maybe I just want to change them to show as millions.
  • 02:49 So with the data labels selected, let's right click and go to Format Data Labels,
  • 02:55 and we're going to jump straight down to the number section.
  • 02:59 Because what we can do here is we can create ourselves a custom format.
  • 03:04 Now the format that we're going to use to format these as millions is this.
  • 03:09 Now, if you're not too familiar with how custom formatting works,
  • 03:13 we're not going to cover that in this course, but
  • 03:16 just bear in mind that this first number is how we treat positive numbers.
  • 03:20 The next position is how we treat negative numbers, which I've just skipped over by
  • 03:24 putting in a comma because we don't have any negative numbers.
  • 03:28 And then the next position is text, so in there, I have an M to represent millions.
  • 03:34 Now if we click on Add, you can see what that does to our data.
  • 03:38 I can see that the sum of the total cost for baby food is 363 million.
  • 03:43 So it's basically rounding it up.
  • 03:45 So that's just another tip as to how you can use custom formatting to really get
  • 03:49 these numbers to look exactly as you want them to look.
  • 03:52 Now a couple of other things I do in here I would probably add a title, so
  • 03:56 let's double click, total costs by item type, and
  • 03:59 I'm going to move this all the way over to the left hand side of this chart.
  • 04:03 So I'm going to hold down Shift as I drag this across because it keeps it on a nice
  • 04:08 straight line.
  • 04:09 So let's put this over there, and I'm going to apply some bold formatting.
  • 04:15 Now, if I wanted to,
  • 04:16 I could do things like change the background fill off the chart area.
  • 04:19 Now, sometimes this can look pretty good but
  • 04:22 sometimes it can detract from your visualization.
  • 04:24 So I would say proceed with caution,
  • 04:26 particularly if you want to use an image in the background here.
  • 04:29 Now if we click on the chart area and go over to the Format Chart Area pane,
  • 04:34 you can see that we can expand fill and we can choose how we want to fill.
  • 04:38 So we could choose with a picture if we wanted to, and if we select this
  • 04:43 option it's just going to load up the first texture in the gallery.
  • 04:47 But we could insert our own image from a file or from a stock image gallery.
  • 04:52 So let's just add this one as an example.
  • 04:56 Let's click on Insert, and you can see the effect that we have there.
  • 05:00 I could choose a gradient fill for a different effect or
  • 05:04 even a solid fill just here.
  • 05:06 Now, I think gradient fill looks pretty good.
  • 05:08 I could change these colors by changing the gradient stops at the bottom and
  • 05:13 adjusting these accordingly.
  • 05:15 Now if we go up to the Design tab at the top again,
  • 05:18 we have a whole bunch of chart styles that we could choose to use.
  • 05:22 Now, remember if you've done quite a bit of formatting on your chart already, and
  • 05:26 then you choose a chart style it's basically going to knock out all of that
  • 05:30 formatting.
  • 05:31 So you can see all of that number formatting I applied gets overwritten if
  • 05:35 we choose a chart style.
  • 05:36 But if you just want a quick way of formatting your chart,
  • 05:39 these can be really good.
  • 05:40 Of course we have a Change Colors drop down.
  • 05:42 These colors that you see in here are based off of the theme that
  • 05:46 you're currently using in Excel.
  • 05:48 So I'm just using the standard office theme, so
  • 05:51 I'm getting the standard office colors.
  • 05:53 We can choose to remove the grid lines if we want to.
  • 05:56 So if we select the grid lines and press Delete,
  • 05:59 that's going to get rid of those and give us a much cleaner appearance.
  • 06:02 And of course, if I wanted to add something like a slicer,
  • 06:06 I could click on Pivot Chart Analyze, Insert Slicer,
  • 06:10 and then maybe I want to add a slicer for the sales channel.
  • 06:14 So, this is where we can apply a little bit of formatting.
  • 06:16 I'm going to go to the slicer ribbon and put all these on one line.
  • 06:22 And then I could right click and go to Slicer Settings and
  • 06:26 I could choose to not display the header,
  • 06:29 because that then makes this look a little bit cleaner.
  • 06:33 I could then, if I wanted to, move this onto the actual chart.
  • 06:38 Now, it looks a bit funny because it has a border around the outside.
  • 06:42 It doesn't really blend in with the chart.
  • 06:44 But what we could do is create our own slicer style.
  • 06:48 Now to do this, we have to duplicate the current style.
  • 06:51 So if you take a look in the Slicer Styles group,
  • 06:55 you can see currently I have this slicer style selected, but
  • 06:59 I could choose to duplicate it, give it a name, And
  • 07:05 then I can modify my new custom style.
  • 07:08 So what I might want to say here is for the whole slicer, let's click on Format.
  • 07:14 I want to remove the borders, so I don't want an outline.
  • 07:18 Let's click on OK and OK again, and then when I apply my custom style,
  • 07:23 it's going to remove the outline from around this slicer.
  • 07:27 Now, if we had a white chart background, so let's just very quickly change this
  • 07:31 back to solid fill and we'll just fill it with a white background.
  • 07:35 You can see how that slicer then really blends in with the rest of the chart.
  • 07:40 I can then use this to filter my data.
  • 07:44 Now, just a final thing when it comes to formatting charts,
  • 07:47 I'm going to insert one more chart, this time this is going to be a line chart.
  • 07:52 I'm going to do a similar thing here.
  • 07:53 We're going to right click, we're going to hide all fill buttons on the charts.
  • 07:57 But this time we're going to move the legend.
  • 08:00 So let's click on the plus to open up our chart elements area, and
  • 08:04 I'm going to say that I want the legend to be at the top.
  • 08:07 Now, with these lines, what I could do is click on one, and
  • 08:11 then in the Format Data Series, I can choose how I want these to display.
  • 08:16 So do I want any markers?
  • 08:18 Let's take a look at our marker options, I could say automatic, and
  • 08:22 it's going to put little markers to mark those different points on the line.
  • 08:27 And something else I really like to do, is I like to create smoothed lines.
  • 08:32 So if we scroll all the way down to the bottom, notice we have a smooth line
  • 08:37 option, which kind of gives us this really nice curvy line effect.
  • 08:41 Let's click on smooth line again.
  • 08:43 And of course, we can go in and we can change the color of both our lines.
  • 08:48 So I'm going to have this one as dark red, and let's make this one a blue.
  • 08:55 And just like that, I have much more interesting looking charts.

Lesson notes are only available for subscribers.

Gift this course