Have you ever had a project where you had to create a series of charts with identical formatting? For example, a 1-page summary of every program you operate, county you serve, or district in your region? Creating a chart template in Excel will save you so much time.
The Original Chart
Imagine that you have to create a visual overview of key data for 10 districts you serve. You start with your first district's data set, sort the data, and spend 10-15 minutes reformatting Excel’s default chart using all the best practices around chart readability.
When you're done, you have a beautifully chart formatted with branded colors and a descriptive title, which might look like this:
The original federal grant allocation chart for District A with final formatting.
Then you realize you have to do it 9 more times for the remaining districts. Does that thought make your head hurt? Instead of starting from Excel’s default formatting each time, you can turn your chart into a chart template. I estimate this will cut your formatting time for each chart by 90%.
Not only will creating a template save time and headaches, it will ensure every formatting detail is exactly the same across charts.
Turning the Chart into a Template
With the chart open in Excel, I right click on it and select “Save as template.” I give it a name to distinguish it from other chart types. For this chart type I chose “District Federal Allocation Chart.” Then press Save.
Inserting the Chart Template
Now, after I set up the data for my next district in the same structure as my first district, all I have to do is highlight the data, go to the Insert tab, and click the small arrow in the bottom right-hand corner of the "Charts" section.
Insert the chart template by going to the Insert tab.
An "Insert Chart" box pops up. In the "All Charts" tab, click on the Templates folder in the left-hand column.
Your chart template will be visible in the Templates folder.
There I see the template I just saved, so I click on that and press OK.
This is what the new chart looks like:
District B's data formatted with the chart template I created.
The new chart retained the exact title from the original chart, so I need to update that and adjust the sizing so that the subtitle is visible.
Here is the final version:
District B's final federal grant allocation chart with the updated title.
And with that 30 seconds of editing complete, I’m ready to move on to my next district!
This trick has saved me so much time and I hope it saves you time too.