MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Create Perfect One-Click Charts


March 05, 2020 - by Bill Jelen

Create Perfect One-Click Charts. Photo Credit: rawpixel at Unsplash.com

One-click charts are easy: Select the data and press Alt+F1.

Three regions appears in A2:A4. Five months appear in B1:F1. Numbers appear in B2:F4. The top-left corner cell A1 is blank. You have A1:F4 selected.
Press Alt+F1 and you get a default chart: Clustered Columns, with legend at the bottom and a title of Chart Title at the Top.

What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to Alt+F1.


Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions.

Change the Display Units for the chart axis. Choices are None, Hundreds, Thousands, 10000, 100000, Millions, and so on, up to Trillions.

To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top.

Click the Plus icon to the right of the chart. Hover over the entry for Legend and choose Top as the location.

Change the color scheme to something that works with your company colors.

Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.)

The context menu for a chart offers Reset to Match Style, Font, Change Chart Type, Save as Template, Select Data, and Move Chart. Choose Save as Template.

Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that you just created.

After setting up a template, the All Charts tab in the Change Chart Type dialog offers a new category at the top called Templates.

Right-click your template and choose Set As Default Chart.

In the Dialog box with all of the chart types, right-click on any chart tile and choose Set As Default Chart.

The next time you need to create a chart, select the data and press Alt+F1. All your favorite settings appear in the chart.

After customizing the Default Chart, the legends appear at the top, the left axis is in millions. Data labels (also in millions) appear above each column.

Thanks to Areef Ali, Olga Kryuchkova, and Wendy Sprakes for suggesting this feature.

Title Photo: rawpixel at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.