-1

I typically use Google Sheets and am very familiar with how to do this, but I'm forced to use Excel 2013 for a job. Here's what I need to do and I'm hoping someone can help.

I have a worksheet with column "I" with the header "TEAM MEMBER" which gets populated with new members as I add information into the rows below.

I need to create a bar chart which displays each team member on the bottom and shows the number of requests assigned to that person. I know it would be easy to simply create a formula to count the number of times a certain person is listed and chart it that way, but that doesn't allow for me to simply type a new member into the column and the chart automatically start counting the requests.

Here's an example of the Google Sheets chart I very easily made. Google Sheets example

I hope I've made my request clear enough; if not, please let me know and thanks in advance!

Vityata
  • 42,633
  • 8
  • 55
  • 100
Michael
  • 3
  • 1
  • 3

1 Answers1

0

You can use a pivot chart for your graph and use a table for you data. This way the range reference will be the table name and not a range and will adapt when you add a new entry.

In the pivot table you can set your column as Axis and count of that column for values

your table should look like:

enter image description here

and the pivot chart would give you:

enter image description here

which is pretty similar to what you posted Hope this helps

Jomathr
  • 154
  • 1
  • 3
  • 13
  • Not really what I'm trying to do, but I can give it a shot. I'm not super-familiar with pivot tables, though. – Michael Jan 09 '18 at 17:06
  • I edited my post to give you a better image of what I am saying, the count of column will count the number of instance of each different name and adjust the chart accordingly. – Jomathr Jan 09 '18 at 17:54
  • Ahhh, yes, ok, that will work. I set up a pivot table, but I'm not getting the "count of employee" you have listed. I've got the Team Member listed, though. Where/how does that "count of employee" get calculated? Thanks for the clarification! – Michael Jan 09 '18 at 18:20
  • If you drag and drop "Team Member" from the fields to the "values" you should get "Count of Team Member". If not, after you drag and drop it click on the arrow on the right hand side of the field in "Values" and select Value fields then choose count as the summarize option – Jomathr Jan 09 '18 at 18:34
  • Well....it worked perfectly for the items that were already in the list. When I added a new/different person, it didn't update. Do I need to set something to automatically calculate new entries? Thanks. – Michael Jan 09 '18 at 19:05
  • You have 2 choices as far as I know, either you refresh the graph or you can add a piece of vba code that will refresh the data on change in your table. if you go the vba route you can add it in the sheet itself, not a module using : `Private Sub Worksheet_Change(ByVal Target As Range) Worksheets(“SheetName”).PivotTables(“PivotTableName”).PivotCache.Refresh End Sub` – Jomathr Jan 09 '18 at 19:13