Use Paste Special to Speed up Chart Formatting [Quick Tip]

Posted on February 24th, 2010 in Charts and Graphs - 37 comments

Excel Paste Special is one of my favorite features. So I was naturally thrilled when I discovered that you can use paste special to paste formatting from one chart to another.

When we have multiple charts, ensuring consistent color schemes, fonts etc. is very important. Often we individually format the charts because they have different chart types or source data. But instead, you can just use paste special to copy and paste chart formatting (formatting of labels, grid line settings, axis settings, colors, legends, titles) from one chart to another. See this small tutorial.

Use Paste Special to speed up chart formatting

  • To do this, Select the source chart, press Copy (or CTRL+C), now select the destination chart, go to Paste Special (or press ALT+ES). From the dialog select “Formats”.

More quick tips, chart formatting suggestions.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

37 Responses to “Use Paste Special to Speed up Chart Formatting [Quick Tip]”

  1. Martin says:

    Dear Mr Pointy Haired Dilbert,
    You are a genius.
    m

  2. Gerald Higgins says:

    Awesome ! That would have saved me alot of time a month ago 🙂

  3. Klauss says:

    You can also use the shortcut ctrl+alt+V for paste special

  4. Tom says:

    Great practical tip. Thanks!

    Tom

  5. Hats off Chandoo ! Another great tip from a guru!

  6. Ewoud says:

    Great, thanx!

  7. Jon Peltier says:

    Warning: pasting formats sometimes pastes content as well as formatting.

    In Excel 2003 & earlier, the text in axis and chart titles and data labels is copied from the old chart to the new chart. For example, if you had axis titles of "Sales" in the first chart and "Profits" in the second, the title of the second would change to "Sales".

    In 2007, the text of the title of the second chart is not changed, but the text in axis titles and data labels does change to the text in the first chart.

    • Humaira says:

      Please tell me what to do then? I got same problem which you pointed off in excel 2013 that on following the above for formatting axis title also gets copied and paste then... 🙁

  8. Nimesh says:

    Nice find Chandoo.

    Jon - thanks for giving the headsup.
    Will have to keep that in mind when I use this.

  9. bill mcnair says:

    Wow! Thanks for this tip. Excellent for chart maniacs like myself!

  10. Jeff Weir says:

    Great tip, Chandoo. Thanks for the caveats, Jon.

  11. chrisham says:

    Wow! Just a couple of days back Mr Spreadsheet pointed to a wonderful keyboard shortcut in duplicating a charts, and Chandoo's tip here is another great timesaver. Are they more to come?? Thanks Chandoo!

  12. SHEKHAR says:

    Great tip.......Thanks! Be great.

  13. Chandoo says:

    @All.. thank you. I am very happy you all liked this tip.

    @Jon.. very good info. Thanks for bringing this to our notice.

    @Chrisham.. Of course there will be more to come. Just that I dont know what or when. As I learn more, so will you.

  14. Paramdeep says:

    This is very nice! 🙂 A real time saver.. I used to record macros for this purpose (which obviously break pretty easily)... This is good...

    I think the only problem is that it is not able to know the difference between different chart types. For example, if I copy from a bar graph and paste special on a pie chart, it makes the new chart as a bar graph ... If only it just copied the formatting.. it would have been great! 🙂

  15. Ruth says:

    This is a quick and very simple solution - can't believe I have previously overlooked it so thanks for the post!

    My usual solution is to save a frequently used chart format as a user defined custom chart (i.e. right click on the template chart > chart type > user defined > add) which can then be applied when required to new charts.

    The advantage of this is that you can keep lots of templates stored from different spreadsheets / reports, the disadvantage is as per Jon Peltier's post that rather annoyingly Excel retains the original chart title from the template chart.

  16. Gauav says:

    Great help! thanks will save a lot of my time!

  17. HarishG says:

    thanks buddy. it saves a lot of time

  18. Gary says:

    Thanks Chandoo!

    Awesome tip! You've just saved me heaps of time using it.

  19. Sally says:

    Thanks. I'm a big fan of this, but now, when I choose Paste Special for the destination chart, Excel doesn't present the choice of All / Formats / Formulas.
    Anyone know how I can get this back? I'm addicted..
    Thanks loads.

    • Jimo says:

      Thanks for this tip. If only I had known about this years ago, I could have saved hours/days of copy/paste monotony!

      I thought I'd add something I found out on this subject - also in reply to Sally's question (Sept 21st, 2011). The Paste Special option will not work on Pivot Charts. If it's just a normal chart referencing some cells, it works fine. However if it's a Pivot Chart, driven by a Pivot Table, then I can't seem to get it to work. However, I may be wrong, maybe someone knows a way to paste formats onto a Pivot Chart...?

    • Janosh says:

      friend i am not expert in excel but i have a tip about chart formating
      First of All you Save you Chart  ----> Save As Template and when ever you want to apply Saved Chart Format apply another Chart
      simple you change your Chart Type   ----> Template   ----> ok.

  20. Janosh says:

    Assalmu alikum
    Jazak ALLAH Chandoo

  21. Pooyan says:

    Hi there

    This tip was really awesome and saved my time a lot. I would like to do same thing in Microsoft Word on the tables. I really appreciate that if help me with that please.

    Regards

  22. Clark says:

    Thank you so much Chandoo! This is a life-saver!

  23. Megna says:

    Great help! thanks will save a lot of my time!

  24. Freebhie says:

    But I Try using that technique in line graph with more than 10 series or name. But not all colors on line graph is the same. Do you have any solution. On excel 2010 what i did is just simply copy the line graph and paste it, then using Select data i have my new line graph already. I need help please.

  25. Eliud Glz says:

    I belive this can't be done, with pivotchart. any idea?

    thank you so much for your ideas chandoo!

    Regards!

  26. Celina says:

    I love you

  27. Saulo says:

    HI, my name is Saulo. I tried to apply this technique using Excel 2013 and something get wrong. Is it possible to do this using this version of excel?
    Thanks!

  28. Saeh Abdel Qawy says:

    Hi,
    Before, it was a hedious task for tens of charts. But now with your great tip, it becomes a very simple task. Thanks a lot.
    Regards,
    Sameh

Leave a Reply