Skip main navigation

New offer! Get 30% off your first 2 months of Unlimited Monthly. Start your subscription for just £29.99 £19.99. New subscribers only. T&Cs apply

Find out more

Chart templates

Creating and using chart templates can be a great time saver. Watch now as Paula demonstrates how to create a chart template.
In this video, we’re going to look at creating a chart template and then applying this chart template to another chart. So I have two sample sets of data. The first set of data is sales rep, with total sales and percentage of total sales. And in the second set of data is sales rep, with total cost to good sales and percentage total cost of good sales. So we will select our first lot of data here and I’m going to insert a combo chart. I’m going to bring this up here and I am just going to slightly change the size of it here so that there is room for a second chart in a couple of minutes.
We’re going to go ahead now and we are going to do some formatting to this chart. So first of all, I am going to take my axis, and from my axis, I’m going to make them values bold and just that tiny little bit bigger. I’m also going to make the secondary axis bold and a tiny bit bigger. And I’m going to make the label names on the bottom bold and a tiny little bit bigger as well. Then I’m going to go into the chart itself and the grid lines that are available. I’ve selected all of the grid lines and we can see the selected with the dots.
I’m going to select delete to remove those grid lines from the actual chart. Next, I’m going to format the line. So I’ve selected the line and I’m going to select format data series to bring up my formatting box. And then here, I am going to change the fill in here or I’m going to change the color of the line, the fill of the line, and I’m going to change this to a dark green color. I’m also then going to go into our shadow and I’m going to add a little shadow to that line as well. Now, you’ll see it a little bit clearer when we moved through once we move away from the line.
You’ll see that formatting a little bit clearer. I’m also going to come down here and I’m going to make it a smooth line and I’m going to reduce the width of the line as well. Now, I’m going to select all of the columns and we’ll see that the next data… the format data box is updated to be in formatting this particular data series. Now I’m going to change the color in here to a lighter green color. And I’m also going to slightly increase the transparency on that. I’m going to go in and I’m going to add a shadow to it as well. And then I am going to go into the options and I’m going to reduce the gap width.
So by reducing the gap width, you’ll notice the size in between the columns actually get a little bit smaller. Now that chart is looking a lot nicer. We can add other details to the chart. But when you’re creating a chart template, only formatting options carry across. So if you add chart elements such as titles and titles on your axis, these are not going to carry across in a chart template. So let me just show you that. I am going to add in some axis titles and this axis is going to be equal to our sales so we can type in what we want our axis to be.
Or if we’re referencing somewhere else on a spreadsheet, a cell in a spreadsheet, we need to press the equals in the formula bar. If we were to press equals just in here, it’s not going to allow us reference in actual cells. We have to do it in the formula bar, and I’m going to just delete this one down here by deleting that. And I’m going to change our chart title as well to sales by rep. And now we have a chart that we are happy with the formatting of and we wish to copy this formatting across other charts that we’re going to create on a dashboard or a report.
So to save the chart template, click on the chart and then select save as template. Now, charts that are saved as templates, the file type that it will be saved as is a dot (.) TX and it will go through and it’ll find your templates folder that’s created in your Microsoft pack. So I’m going to save this as our course template. And as I already had one saved, I’m just going to overwrite it. Now, I’m going to go to my second lot of data. Again, I am going to insert a column chart and I move this up, make it a little bit bigger as well, and now I’m going to say change chart type.
And in here, I’m going to go to template. I’m going to select my template and say okay, and you see all of the formatting has now carried across from the first chart to the second chart. But you’ll note what hasn’t carried across. We haven’t carried across any title. So the chart title and the axis titles, neither of these carried across but the rest of the formatting from the chart… [END]

In the workbook you downloaded for this week, you will find a worksheet named Chart template data. You can use the data in this worksheet to create and save a chart template.

Comment below.

What are your main takeaways from this video?

This article is from the free online

Introduction to Excel Charts for Data Visualisation

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now