Question on how to chose data for bar chart

[captainmcfunk]

Hey all,

This seems so elementary to me, but I can't for the life of me figure it out in Excel 2013. I have a chart that is organized like the below:

Date Patient Name PCP PCP City Referred By Orders

date 1 Patient 1 PCP 1 PCP City 1 Referrer 1 Product 1
date 2 Patient 2 PCP 2 PCP City 2 Referrer 2 Product 2


I'm simply trying to build a bar chart that is organized so that the y-axis is "count of patients", x-axis is "month" from 2013-2015, and each bar is an individual PCP so i can see the new patient referral trends by month by PCP, compare them against each other, etc. I'm unsure how the select data function works under chart tools. I'm using Excel 2013. I'd greatly appreciate the help, as I can apply the methodology to several other tables I need to build.

Thanks!

Captain

[MrShorty]

Correct me if I'm wrong, but I don't see anywhere in your data where you have "number of patients" as a data column. Charts don't analyze data, they present data that has been analyzed in the spreadsheet. Before you can create a chart showing "count of patients" vs. "month", you will need to have the spreadsheet compute the number of patients per month.

Easiest approach might be a pivot table and pivot chart (except for me, because I am not familiar with pivot tables).

If you decide not to use a pivot table/chart, you should be able to achieve a similar thing using COUNTIF() or COUNTIFS() functions.

Let us know if you need help creating a pivot table/chart or using these worksheet functions.

[captainmcfunk]

okay, i can create a pivot table that shows:

Row Label Count of Patient Name
PCP 1 7
PCP 2 10


How would i get the date into this pivot table accurately? Right now, it's full dates (1/1/2013, 7/14/2014, etc.) -- I just need those by month if possible.

Thanks so much for the help!

[simarui]

easiest solution is to add another column to your source data for month

Please Login or Register  to view this content.

also within a pivot table you can group your date ranges, but i'm not sure of a quick way to do so off the top of my head... chandoo is though!!
http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/

[Kyle123]

Right click on the date field in the pivot table, then group > select month