Quickest access to your chart templates

[Piquet00]

Hi

i have a job to do which involves creating a large number of charts from a series of Excel 2016 data sheets and pivot tables built on this data. The process requires judgement on what to chart and so i don’t really want to get into writing macros I don’t think.

I have set set up a series of 12 chart templates in the house style and these work great. The workflow is:

• Pivot table to get required view
• copy and paste to a new tab
• create the required chart
• move the chart to its own tab
• when set completed I have an xlsx containing all the charts and data for future tweaks

I have done hundreds of these this weekend and have many more hundreds to do so any time savings I can make will really help.

the slow bit for me is making the chart. I have an insert chart quick access button but I have to click through recommended charts to templates before I can chose which one I want. Is there a keyboard shortcut or some way of bringing up my templates in one click or press?

thanks for any tips.

Phil

 

[Piquet00]

Anyone?

 

[thisoldman]

There doesn't appear to be a shortcut: I can't find one in Excel 2016. This is what I find when I look in my library:

... There is no icon for Templates or Recent.

To reach the categories of Templates or Recent, you need to open the All Charts tab of the Insert Chart dialog. Even the tiny dialog launcher shown in Figure 1.12 takes you to the Recommended Charts tab of the Insert Chart dialog. It is easier to click the big Recommended Charts icon and then click All Charts.

— Bill Jelen
Excel 2013 Charts and Graphs (MrExcel Library)
​

I've never done repeated charts on the scale you are doing them.

You might find it useful to record some macros to select the proper chart templates.

Ninety-nine percent of the time, I know what chart type I want to use as the base chart. I just go directly to that type using the smaller chart icons on the Insert tab.

My workflow when making multiple charts:

• Create the charts using the defaults.
• Get the formatting correct in Chart 1.
• Copy Chart 1.
• Select Chart 2.
• Using the drop-down menu under the Home tab's Paste icon, select Paste Special >> Formats.
• Select Chart 3.
• Press Ctrl+y (repeat last action).
• Repeat the last two steps for the remaining charts, selecting a chart and pressing Ctrl+y.

I'm very sorry I can't provide an easy solution. If I were in your circumstances, I would consider recorded macros to automate some of the repetitious parts of the work.

 

[Piquet00]

Hi thisoldman,

Thanks for your reply - I am glad in a way that there appears to be no fast way to do this, because it means that I am not going completely mad!

I think I will try and record some macros - should speed things up quite a bit.

Will try your workflow too.

Cheers,

Phil

 

[Piquet00]

You inspired me! Last night I created a macro per chart type, then managed (with my sketchy vba knowledge) to add code necessary to change the specific range the macro recorded to whatever range was selected before the macro was executed. They work like a dream - even moving the chart to its own sheet in the macro saves a few seconds.

I've assigned them to buttons on the quick access toolbar for now, but the range of icons available is not great so it will be a learning process before I get to click the correct one every time.

Thanks again for your input.

Phil

 

[thisoldman]

I'm happy my post nudged you in a good direction. It's a good feeling when you feel you're mastering a new, useful skill. Especially when that new skill reduces drudge-work.

Good luck and best wishes.