How to generate an excel chart that includes aggregating and grouping

Question

My data looks like this

Date    Time    Person Logged in
June 1  2PM     John
June 1  2PM     Chris
June 1  3PM     John
June 2  2PM     Chris
June 2  3PM     Bill
June 2  4PM     Bill

I want to plot a chart where the unique dates on the x axis, and for each day, the number of times each person logged in is quantified.

So you will have June 1 and June 2 as labels on x-axis. for June 1 you will have a bar with Chris (1 unit) and John (2 units) stacked. for June you will have a bar with Bill (2 units) and Chris (1 unit) stacked.

How do we do this in excel?

I am a newbie in excel charts, i tried using charts and pivot table charts but couldn't figure out using it for things involving distinct dates, and count of records. any help is appreciated - thanks.

Answer 1

Follow the below steps to get this done

To begin with, please see the sample of data I have considered for this task

Step-1:
Create a pivot table of your data as shown below:

If you are new to pivot tables, then follow these steps:

• Go to Insert menu and select Pivot table
• In the Pivot table dialog select you table to give the input
• An empty pivot table will be created

• Use the settings shown below to create your pivot

  

Step-2
Once the pivot table is ready, from the Insert menu select 2D bar chart.
Click on the bar chart and select source data as your pivot table area and see the chart as shown below.

Note: I hope I have helped you in better understanding of pivot tables and charts. If you have any queries post comments below the answer