• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to generate 60+ charts automatically

soderlloyd

New Member
Hello all!

Thank you in advance for your help on this.

I have been tasked with making 60+ excel charts (1 per clinician) that will be updated each month. They are very simple charts, just showing the average number of patients seen each month. I have attached an modified version of the excel file I will receive each month. I have made phony clinician names, but they come to me as a last, first name and there are around 65. It is important to note that I need to account for the fact that the clinician names will change from month to month (some will go on leave, new ones added, some leave their jobs, etc.).

The columns of interest are A and C. Just a simple bar or line graph for each clinician that maps average patients per day over time. So, this file is for September, but I will receive one for each month and need to have a summary chart for each clinician that shows their numbers over time (example in Sheet 2).

I have two questions for you:

1. How can I make a chart for each clinician without having to manually make each one?
2. Is there a way I can easily update the graphs accounting for the names changing?

Thank you so much! Please let me know if you have questions!
 

Attachments

GraH - Guido

Well-Known Member
Or use a pivot chart and a slicer to allow selecting the clinician's name? Rough example attached.

Or using CTRL and left mouse click to copy the chart and then move the highlighted data series to the next name with the mouse (selecting the blue border and just drag down).
63361

or ask for a macro that does that for you on the macro forum.
 

Attachments

soderlloyd

New Member
Or use a pivot chart and a slicer to allow selecting the clinician's name? Rough example attached.

Or using CTRL and left mouse click to copy the chart and then move the highlighted data series to the next name with the mouse (selecting the blue border and just drag down).
View attachment 63361

or ask for a macro that does that for you on the macro forum.
Thank you! I will give that a try.
 

soderlloyd

New Member
Or use a pivot chart and a slicer to allow selecting the clinician's name? Rough example attached.

Or using CTRL and left mouse click to copy the chart and then move the highlighted data series to the next name with the mouse (selecting the blue border and just drag down).
View attachment 63361

or ask for a macro that does that for you on the macro forum.
I looked at the file that you attached. How did you make the green table on Sheet3?
 

vletm

Excel Ninja
soderlloyd
You will need one chart, which can use with those 60+ clinician.
Means - You could select needed clinician's data to see it.
Many things depends of Your data.
 

Chihiro

Excel Ninja
Here's how it's set up.

1. Select H4, insert column sparkline. Set B4:F4 as range. Copy down.
2. While having all sparkline selected, go to Sparkline ribbon tool.
3. In "Show" section, tick "High point", "Low point"
4. Then go to "Group" section, Axis -> Vertical Minimum -> Custom 0.0
5. For Vertical Maximum - > Same for all Sparklines.
6. In "Style" section, Marker Color -> High Point to Green
7. Set Row height for entire range to 25 and widened H column to 9.14

See attached sample.

Note: Numbers were generated using...
=RANDBETWEEN(1, 9) + Rand()
Format set as 0.0. Then copied and pasted back in as values.
 

Attachments

soderlloyd

New Member
Here's how it's set up.

1. Select H4, insert column sparkline. Set B4:F4 as range. Copy down.
2. While having all sparkline selected, go to Sparkline ribbon tool.
3. In "Show" section, tick "High point", "Low point"
4. Then go to "Group" section, Axis -> Vertical Minimum -> Custom 0.0
5. For Vertical Maximum - > Same for all Sparklines.
6. In "Style" section, Marker Color -> High Point to Green
7. Set Row height for entire range to 25 and widened H column to 9.14

See attached sample.

Note: Numbers were generated using...
=RANDBETWEEN(1, 9) + Rand()
Format set as 0.0. Then copied and pasted back in as values.
Thank you! That is really helpful.
 
Top