Quickest access to your chart templates

Piquet00

New Member
Joined
Nov 6, 2017
Messages
4
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,532
Messages
6,172,878
Members
452,486
Latest member
standw01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top