• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Chart Formatting Tips

You are here: Home / Excel Charts / Excel Chart Formatting Tips
December 14, 2017 by Mynda Treacy

Format your dashboards and reports fast with these pro Excel chart formatting tips.

Watch the Video

Subscribe YouTube

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:

format shape

Pro Tip 3 – Snap to Grid: Hold down the ALT key while resizing and moving to snap to the grid:

alt key

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:

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):

shift key

Pro Tip 6 – Repeat Formatting: Let’s say you decide that you want to remove the chart border from all your charts.

repeat formatting

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:

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:

copy chart

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:

move chart

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:

prevent charts resizing

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:

built-in chart styles

Pro Tip 13 – Select Chart Elements: Sometimes selecting the element you want can be tricky, like the ‘Small Values’ series in the chart below:

select chart elements

There are two options for selecting those teeny tiny chart elements:

  1. 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.
  2. Select the chart > Chart Tools: Format tab > choose the element from the drop down:
  3. Chart Tools

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:

Copy All Formatting

Note: Paste Special doesn't work with Pivot Charts. Instead, you can simply copy and paste, which will paste the formatting.

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.




Category: Excel Charts
Previous Post:Excel dot plot chartExcel Dot Plot Charts
Next Post:23 Essential Excel Keyboard Shortcutsessential excel shortcuts

Reader Interactions

Comments

  1. Keith Mitchell

    January 29, 2021 at 10:45 pm

    Hi Mynda

    I have developed a “Combo” chart with stacked Columns and Stacked Area and have linked the underlying pivot tables to slicers. Now on changing the slicer selection these charts reformat themselves and all series are displayed as Stacked area.

    How can we stop this? I have tried changing the Advances Options not to change “Properties follow chart data point for current workbook” as well as for all new workbooks – as some websites propose.

    Reply
    • Keith Mitchell

      January 29, 2021 at 10:49 pm

      PS I have also tried creating a Chart template, as you propose further down in this chat, but this does not help. One needs to change the chart type each time the format goes awry.

      Reply
    • Mynda Treacy

      January 30, 2021 at 2:24 pm

      Hi Keith,

      You can try applying the chart settings with no filters/items selected in the Slicers, i.e. in a completely unfiltered state. If that doesn’t work then this is the result of a known bug with Pivot charts. The solution is to insert a regular chart based on the PivotTable data.

      Mynda

      Reply
  2. Richard Copeland

    October 12, 2020 at 6:32 am

    Hi, Mynda Treacy; Nice job on these tips! I copied some website info (a mix of text, pictures and graphics) into Excel-2016. I have everything the way I want it, but cannot delete the graphics. When they are clicked-on, they don’t have/display resizing dots at their edges and when I right-click on them, the context (or, right-click) menu doesn’t display at all. The graphics are small rectangular boxes that had embedded links in them in the website; in effect, they were “Click Here to Do This or That” buttons.

    Do you have any suggestions to delete these annoying objects? They make what is otherwise a well-organized spreadsheet, quite messy looking. I can’t be the only one with this problem. Thanks for any assistance.

    Reply
    • Mynda Treacy

      October 12, 2020 at 8:43 am

      Hi Richard,

      Maybe these objects are actually inside the cells. Have you tried deleting the cells or the contents of the cells? A better way is to use Power Query to get data from the web. If you’re still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  3. Anna

    July 29, 2020 at 1:36 am

    Tip 14 does not work? I cannot paste special after I copy a chart.

    Reply
    • Mynda Treacy

      July 29, 2020 at 9:32 am

      Hi Anna, It looks like something is preventing the paste special working with Pivot Charts in the latest version of Excel as it works with earlier versions of Excel and also works with regular charts, as you can see from the screenshot. I’ll raise a bug with Microsoft. In the meantime you can simply copy the chart and paste, which will paste the formatting. Mynda

      Reply
  4. Gulliver

    August 29, 2019 at 2:03 pm

    Does not answer the question of keeping format (series names, colors, date range, etc,) from one graph to next

    Reply
    • Mynda Treacy

      August 29, 2019 at 2:11 pm

      Series names and date ranges are not formats. These are dictated by the data your chart is based on.

      Reply
  5. Musadaq

    August 23, 2018 at 5:46 pm

    Thank you so much for the valuable stuff.

    Reply
    • Philip Treacy

      August 23, 2018 at 7:47 pm

      You’re welcome

      Reply
  6. Tom DeVan

    May 24, 2018 at 6:54 am

    hi Mynda, With the help of your dashboard course I have been able to make myself a very usable Dashboard c/w 3 main chart areas and 3 slicers controlling them. It displays my sales dollars per year, per territory etc. The issue I am having is after I format the line colors to match in the 3 charts e.g. 2016 = red, 2017 = blue, 2018 = green, the line colors randomly change and when I refresh the data, I’ll end up with Chart 1 & 2 as Red, Blue Green but Chart # 3 may end up with Red, Blue Purple…… My numbers come up accurately based on slicer selections …so everything appears connected…. I’m not sure how to phrase the question – why do my line colors seem to have a mind of their own? Thx Tom D. Ontario Canada

    Reply
    • Mynda Treacy

      May 24, 2018 at 1:18 pm

      Hi Tom,

      This is a common issue with Pivot Charts resetting formatting upon filtering/Slicing. You could try creating a custom color theme for the chart and applying that, otherwise the colors are likely to reset each time a series is filtered out and back in again.

      Mynda

      Reply
      • Steve B

        March 10, 2021 at 12:34 am

        Hello Mynda, hello Tom,

        almost three years since the original post.
        I have a similar problem with keeping line colors.
        Is MS refusing to accept this as a mistake/error?
        It is really frustrating that lines loose their color when refreshed / changed by slicer choice.
        Or is there, three years after, now a solution?

        Y’all have a great day
        Steve

        Reply
        • Mynda Treacy

          March 10, 2021 at 8:56 am

          Hi Steve,

          The problem is caused when the PivotTable is filtered to a point where the series is no longer present in the PivotTable. The Chart can’t remember the formatting for a series that is no longer there. So, if your filters completely filter out a series the best option is to create a regular chart from a PivotTable where you can ensure the cells referenced by the chart for each series are never removed. Instead they simply contain errors or empty cells.

          Mynda

          Reply
  7. Fran Cammock

    January 16, 2018 at 12:48 pm

    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!

    Reply
    • Catalin Bombea

      January 16, 2018 at 1:57 pm

      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.

      Reply
      • Fran Cammock

        January 16, 2018 at 4:03 pm

        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…

        Reply
        • Catalin Bombea

          January 16, 2018 at 4:11 pm

          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.

          Reply
          • Fran Cammock

            January 16, 2018 at 5:56 pm

            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

            January 16, 2018 at 10:57 pm

            You’re welcome, glad to hear you managed to make it work!

  8. Peter Bartholomew

    December 17, 2017 at 8:47 pm

    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..

    Reply
    • Mynda Treacy

      December 17, 2017 at 9:55 pm

      Great points, Peter. Maybe I should write a design tips post next.

      Reply
  9. SunnyKow

    December 16, 2017 at 10:31 am

    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.

    Reply
    • Mynda Treacy

      December 16, 2017 at 10:57 am

      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

      Reply
  10. Allan

    December 16, 2017 at 3:37 am

    You put the ‘Joy’ in the holiday season!

    Reply
    • Mynda Treacy

      December 16, 2017 at 7:31 am

      🙂 thanks, Allan!

      Reply
  11. Abayomi

    December 14, 2017 at 6:41 pm

    this is very great and helpful. thank you

    Reply
    • Mynda Treacy

      December 14, 2017 at 7:08 pm

      Thanks! Glad you like it, Abayomi 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.