• 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

S

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

  • Charges by Cost Center Day and Employee.xlsx
    18.5 KB · Views: 12
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

  • clinicians.xlsx
    40.1 KB · Views: 8
GraH - Guido said:
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.
Click to expand...
Thank you! I will give that a try.
 
GraH - Guido said:
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.
Click to expand...
I looked at the file that you attached. How did you make the green table on Sheet3?
 
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.
 
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

  • Charges by Cost Center Day and Employee.xlsx
    18.1 KB · Views: 7
Chihiro said:
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.
Click to expand...
Thank you! That is really helpful.
 
You must log in or register to reply here.
Back
Top