Present excel data into a "Open, Closed, and On Hold" tickets into a bar graph

Question

I need to present the amount of "Open, Closed, and On Hold" tickets I have into a bar graph. But I need the three bar graph to be organized by months. For example the month of January had "8" Open, "55" Closed, "2" On Hold. And repeat this grouping for every month of the year I have data collected.

Open: 8 tickets

Closed: 55 tickets

On Hold: 2 tickets

Answer 1

Hi Tarik Moore, 

Good day to you! Thank you for reaching out to Microsoft Community. 

Regarding to your request, in order to present the ticket data in a bar chart in Excel, I would recommend trying the following steps: 

1. Select your raw data.
2. Go to Insert > PivotTable.
3. In the PivotTable:  
   • Drag Date Opened to the Rows area.
   • Drag Request Type (or Category) to the Columns area.
   • Drag Request Number to the Values area and set it to Count.
   • Group the dates by Month (Right-click a date > Group > select "Months").

1. Then go to Insert > PivotChart and choose Clustered Column.

Here is the final result of the steps above: 

If you have any further questions and concerns, please don’t hesitate to ask, I'm here to help. Looking forward to your response. 

Best regards, 

Jay-Tr - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Tarik Moore, 

It has been a while and I am writing to see how things are going with this issue. Have you had a chance to check the replies provided? Any update would be appreciated.

Best regards, 

Jay-Tr– MSFT | Microsoft Community Support Specialist 

 ------------------------------------------------

⚠️ This forum has moved! 

Microsoft 365 and Office is now supported on Microsoft Q&A. 

Starting July 16, new questions must be posted there.

Existing discussions on Microsoft Answers will remain accessible through July 21, after which customers will be redirected to Q&A.

Answer 3

Thanks for this...we're closer to what I need. How do I show the following in the graph? I need to quantify the work being done/completed in comparison to the request received in each month. For example January 8 tickets received and 5 of those tickets have been closed:

Need graph to show

• how many tickets opened each month
• how many tickets closed each month

This is what I have thus far

Answer 4

Hi Tarik Moore, 

Thank you for the clarification, to display the number of tickets by category (e.g., Open, Closed, On Hold) for each month, please follow these steps in your PivotTable: 

1. In the PivotTable Fields pane:  
   • Drag Months (Date Opened) into the Rows area. This will group your data by the month each ticket was opened.
   • Drag Category into the Columns area. This will create separate columns for each ticket status.
   • Drag Category again into the Values area. Excel will automatically count the number of entries for each category per month.
   
2. If the months are not grouped automatically:  
   • Right-click any date in the PivotTable → choose Group → select Months (and optionally Years).
   • This is the final result:

 

This setup will give you a clear view of how many tickets were opened, closed, or put on hold each month. 

If you have any further questions regarding to pivot chart and pivot table, please don’t hesitate to ask. Looking forward to your response. 

Best regards,  

Jay-Tr– MSFT | Microsoft Community Support Specialist  

 ------------------------------------------------

⚠️ This forum has moved! 

Microsoft 365 and Office is now supported on Microsoft Q&A. 

Starting July 16, new questions must be posted there.

Existing discussions on Microsoft Answers will remain accessible through July 21, after which customers will be redirected to Q&A.

Answer 5

Hi Tarik Moore, 

It has been a while and I am writing to see how things are going with this issue. Have you had a chance to check the replies provided? Any update would be appreciated.

Best regards, 

Jay-Tr– MSFT | Microsoft Community Support Specialist 

 ------------------------------------------------

⚠️ This forum has moved! 

Microsoft 365 and Office is now supported on Microsoft Q&A. 

Starting July 16, new questions must be posted there.

Existing discussions on Microsoft Answers will remain accessible through July 21, after which customers will be redirected to Q&A.