Format your dashboards and reports fast with these pro Excel chart formatting tips.
Pro Tip 1 – Select Multiple: Hold the SHIFT or CTRL key to select/de-select multiple charts or objects.
Pro Tip 2 – Select All: Select one chart then press CTRL+A to select all. Note: This will select all Objects so if you have shapes or images in your worksheet it will select them as well.
With all charts selected you can move, resize, align, group, delete, copy, right-click and set properties including size, locking and more:
Pro Tip 3 – Snap to Grid: Hold down the ALT key while resizing and moving to snap to the grid:
Bonus tip: You can move and resize multiple charts at the same time.
Pro Tip 4 – Distribute Evenly: Select 3 or more charts > Format tab > Align > Distribute Vertically or Horizontally:
While you’re there you can also align them all left or right, or top/bottom.
Bonus tip: This is great for aligning any object, e.g. form controls, shapes etc.
Pro Tip 5 – Lock Alignment while Moving: Hold the SHIFT key while you left-click and drag to keep your chart aligned to its original horizontal or vertical position (useful it you’re not using the grid for alignment):
Pro Tip 6 – Repeat Formatting: Let’s say you decide that you want to remove the chart border from all your charts.
Make the formatting change to one chart, then select the next chart and press F4. Rinse and Repeat for remaining charts. This works for other formatting too.
Pro Tip 7 – Themes: Change all formatting in one go with Excel Themes. Choose from the built-in themes:
Or customize your own including colors, font styles and shape effects. Click here to learn how to use Excel Themes.
Pro Tip 8 – Duplicate/Copy Charts: Copy an existing chart with keyboard shortcut CTRL+D or left-click to select the outer edge of the chart > hold the CTRL key until the mouse pointer displays a + symbol, then left click and drag while holding CTRL.
Bonus tip: hold SHIFT at the same time to keep the new chart aligned to the one you’re copying:
Pro Tip 9 – Chart Templates: Got a chart you’ve spent considerable time formatting to just the way you like it and now use it all the time. Make it a chart template so it’s on call when you need.
Pro Tip 10 – Move Chart with Arrow Keys: Hold CTRL while left clicking the outer edge of your chart. Note: in Excel 2016 you no longer need to press CTRL, just a left click will do. The pull handles will be small dots which indicates that you can move the chart with your arrow keys:
Pro Tip 11 – Prevent Charts Resizing: By default, charts will resize and move when you adjust column width and row height, but you can prevent this in the Properties. Right-click the chart > Format Chart Area > Properties:
Pro Tip 12 – Don’t Use Built in Chart Styles: All but the default, Style 1, is generally a bad idea. They’re full of noise like unnecessary formatting and fill:
Pro Tip 13 – Select Chart Elements: Sometimes selecting the element you want can be tricky, like the ‘Small Values’ series in the chart below:
There are two options for selecting those teeny tiny chart elements:
- Select an element that’s easy to click on, like the ‘Big Values’ column > hold the CTRL key and press the up/down arrows to toggle through the other elements in the chart until you get to the one you want. Note: pre Excel 2016 you don't need to hold the CTRL key.
- Select the chart > Chart Tools: Format tab > choose the element from the drop down:
Pro Tip 14 – Copy All Formatting: If you want to copy the formatting from one chart to others, you can simply select the chart you want to copy formatting from > CTRL+C, then select the chart you want to copy formatting to > go to the Home tab > Paste > Paste Special > Formats:
Please Share
If you liked this please click the buttons below to share.
Fran Cammock says
Hi Mynda, I’m trying to format a pivot chart title to include filtered text from the slicer along with the descriptor (and include a source in tiny font as a separate line below)… is there a way to do this? It only seems to allow me to either insert text – in which case I can format it as wanted – or insert linked cells – in which case it will only allow me to apply one default text to the whole??
here’s my eg as it might be a bit hard to follow: oops – this comment box only allows a default text also… [Actual] from slicer cost change over time 2011 – 2017 $m
(source, FIM)
if you can shed any light, would be greatly appreciated! thanks, Fran
L.E.:
oops, I seem to have worked it out – dah!
oh dear, no I haven’t – that was the unlinked text!
Catalin Bombea says
Hi Fran,
When you use a linked cell for chart title, you can only format the entire text, not just parts of it, as you already noticed. This happens in formulas too, different formats cannot be applied to formula results.
You can try a different method:
prepare your data in 2 or 3 different cells, format each cell as you want, copy those cells and paste them as linked picture from paste special menu, you can place this image on your chart replacing the title, it will update whenever you use the slicer. Calculation should be set to automatic.
Fran Cammock says
thanks, that is helpful, however I do not know how to set calculation to automatic. I’ve searched all of the properties on the linked picture and simply cannot work it out. Help please…
Catalin Bombea says
It’s not a property of Linked Picture, it’s the workbook calculation settings. Look in the Formulas tab in ribbon, Calculation group->Calculation Options. It should be Automatic by default. If this is set to Manual, the linked picture will not update.
Fran Cammock says
my calculation option is set to automatic, however the linked picture is not updating??
L.E.:
oops, sorry to bother you, have just realised that I must have stuffed it up somehow, as I redid it and it now works…
thanks very much for the assistance.
Catalin Bombea says
You’re welcome, glad to hear you managed to make it work!
Peter Bartholomew says
No shorthand tricks but, in keeping with Mynda’s minimalistic style, I find that charts often appear better without any fill or border to the chart area. This allows the plot area to be better integrated with surrounding objects. That, of course, assumes that cell boundaries are not displayed in the vicinity of charts, shapes and controls.
As a personal style note, I would add that all sheets are improved by removing both the grid and the headings but that is more a style of working than a recommendation..
Mynda Treacy says
Great points, Peter. Maybe I should write a design tips post next.
SunnyKow says
Tip 2 and 5 is extremely useful as many are not aware of this trick.
As for tip 13.1, I don’t think it is necessary to press CTRL+arrow key (or am I missing something as I am using Excel 2010).
Just pressing the up/down/left/right arrow key (after selecting the element) should do it.
The up/down key select the series while left/right key will select each point/series.
Mynda Treacy says
Hi Sunny,
In Excel 2016 you now have to press CTRL with the arrow keys and since this also works in earlier versions I left it as is, rather than explaining the differences.
Mynda
Allan says
You put the ‘Joy’ in the holiday season!
Mynda Treacy says
🙂 thanks, Allan!
Abayomi says
this is very great and helpful. thank you
Mynda Treacy says
Thanks! Glad you like it, Abayomi 🙂