Excel pivot table – unable to get graph to display correctly

chartsmicrosoft excelpivot table

I have a pivot table (Excel 2013) of results for a helpdesk. In column A it lists the agents, grouped by year (this divides up into months). In column 2 it lists the number of tickets closed by each of those agents.

I can't seem to create a graph of these numbers correctly. I want a line chart of number of tickets closed on the vertical axis, month on the horizontal axis and a line for each agent so I can compare them to each other.

Any idea how I might do this please?

Best Answer

For your Pivot Chart to work correctly, you'll need to setup your Pivot Table like this:

  • Legend (Series) Agent
  • Axis (Categories) Date
  • Values Sum of Closed Tickets

Your chart will then reflect the changes. This assumes your initial data set has at least those three columns (agent, date, closed ticket count). Once setup this way, you can group your date data by whatever interval you like (e.g. month, quarter, year).

This example uses a Pivot Chart (but you have more formatting flexibility with a standard chart):

Pivot Chart layout

Related Question