how do i create a pivot table for this MOM by agent AHT

Question

how do i create a pivot table for this MOM by agent AHT

Carrie Ward 80

User's image

Accepted answer

3 additional answers

Your answer

Answer 1

Hello @Carrie Ward,

Thank you for reaching out to Microsoft Q&A.

According to your request, I will guide you to create a pivot table based on the data you shared:

1-Reorganize data

Your current layout has agents and AHT values spread across months in separate columns. For a pivot table, I recommend normalizing the data into a single table: User's image

2-Convert data

If AHT is in time format, Excel will treat it as text. Therefore, you need to convert it to general numerical format (hours, minutes, seconds) for calculations. For example, I convert it to minute format

User's image

3-Insert and configure pivot table

Now, select the entire table (including headers) > Insert > PivotTable > from table/range > choose place for pivot table. You can configure the Pivot Table area based on your need. I will demonstrate with agent as rows, month as columns and AHT as values

User's image I hope this information is helpful. If there's anything else I can help you with, please feel free to reach out again.

Hope to hear from you soon.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Carrie Ward 80 Reputation points

2025-08-26T16:23:16.94+00:00

Very helpful answer! Question: why when I convert time stamp to time in minutes am I getting 00:00 on some like 13:35 is one

Answer 2

Carrie Ward 80

User's image

Carrie Ward 80 Reputation points

2025-08-26T16:24:05.43+00:00

??
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T16:28:42.3+00:00

Hi @Carrie Ward,
The format number must be set in General to show the results as number of minutes. The result after calculation is set automatically in Custom and it shows as you experienced.
Carrie Ward 80 Reputation points

2025-08-26T16:38:40.36+00:00

I am so sorry I need further assistance. How do I correct?
Carrie Ward 80 Reputation points

2025-08-26T16:40:21.6033333+00:00

Here is my formatted number in min when I changed column to "general"
Carrie Ward 80 Reputation points

2025-08-26T16:46:29.6166667+00:00

Is this right?
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T16:49:28.03+00:00

I will guide you the steps to make it correct:
1-Calculate in the AHT number by AHT*1440

2-Click on the first cell, in my file it's M3. Then in Number > choose General.
The you can copy the M3 cell and paste to all the below, the results will show correctly

Please feel free to ask if you need help, I'm here to support you

Answer 3

Carrie Ward 80

Step 1 is not looking like yours User's image

Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T17:20:03.9066667+00:00

It's no problem. In my file all the original results are 0:00.
When you change it to General, it will show right number (as 819 for the first cell) so it's okay.
Carrie Ward 80 Reputation points

2025-08-26T17:26:36.31+00:00

I do not think the 819 in K3 and following down is correct as "minutes". AHT cannot be 819 minutes. Please clarify
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T18:05:08.5633333+00:00

Yes let me explain.

In Excel, time is stored using the exact same serial numbering format as with dates. Each day is represented as 1, each hour is represented as decimal value of 1/24 = 0.0416666... and each minute is the value of 1/1440. That's why when you want to convert time to minute you need to multiply by 1440 (24 hours * 60 mins per day).
Then when you multiply the first AHT 13:39 (if you change to general it will show 0.56875) with 1440 the result will the 819 and it will be in the custom format h:mm. However, 819 is represent for a date (in date time it will be 3/29/1902) and there's no number represent for minute since 819 equals 819.0000 (no number for hours and minutes). That's why automatically in h:mm format makes Excel doesn't know how to show results in hour and minute since there's no number for that. That's why it shows the results as multiplies of 12 (likely 0 or 12 if in am/pm settings).

You can check if the result show 819 or not by clicking the cell > More number format > Custom > scroll up to find general result.
Carrie Ward 80 Reputation points

2025-08-26T18:24:35.2133333+00:00

I see where my issue is right below marked in yellow. When I multiply 13:39 time stamp in F2 by 1440, I get 36:00 in cell D2 and when I change 36:00 to "general" I get 13:65???
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T18:32:52.27+00:00

Please can you click in and show me the value in your F2 cell? The results in column C you're having are not wrong but are in hours number which should be calculated by multiplying 24.
Carrie Ward 80 Reputation points

2025-08-26T18:35:01.7+00:00

I do get zero if I add this formula as suggested by a peer of mine in the call center business
Carrie Ward 80 Reputation points

2025-08-26T18:35:21.8333333+00:00

F2 Cell as requested
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T18:51:51.3866667+00:00

Thank you Carrie, I think I find the problem now.
The data in your F2 cell is 12:13:39 in h:mm:ss but the cell shows only 13:39 which only mm:ss. With your screenshot I thought it's 13:39 in h:mm. The result 13.65 minutes is correct equal to 13 minutes and 65 seconds in your data and because the time in AM, 12 is considered as 0.
Click on the cell and go to More number format > Custom > choose the h:mm:ss or h:mm to show the full time and share it again to me. Also check for me that if all the data are in AM or PM. Thank you for your cooperation and understanding.
Carrie Ward 80 Reputation points

2025-08-26T19:00:04.1366667+00:00

All AHT times are in "AM". See below h:mm:ss for
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T19:15:26.5266667+00:00

Thanks Carrie. May i ask for clarification of your request. Based on your data, the time is 12:13:39 AM so when calculating in minutes it will result only 13.65 mins as you calculated before. Do you want to calculate the amount of 12-hour time too?
Carrie Ward 80 Reputation points

2025-08-26T20:13:52.7733333+00:00

No I think I got it now! I really appreciate you working with me so methodically!
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-26T20:23:16.7766667+00:00

Hi Carrie Ward,
You're welcome.
Please feel free to reach out to Microsoft Q&A if you have concerns or need any further support.
Carrie Ward 80 Reputation points

2025-08-27T12:08:54.62+00:00

How do I delete dup names in a large data column set with other columns included

Example: I want to delete "all" of Agent As in a column set that have AHT and Month in the columns next to name

Agent A AHT Month
Hendrix-C 2,050 Reputation points Microsoft External Staff Moderator

2025-08-27T16:58:28.6733333+00:00

Hi Carrie,
May I ask for some screenshots about your request so it will help me have a clear understanding and provide you the most appropriate guideline. With the old data, if you delete Agent A in a column set, then when creating the pivot table Excel cannot identify which Agent that the AHT data belongs to. For example, it will be like this:

Answer 4

Hi,

This M code in Power Query will transform your data into a 3 column structure.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Extractheaders = Table.AddColumn(Source, "Months", each List.Transform(List.Select(Record.FieldNames(_), each not Text.Contains(_,"AH")), each {_})),
    Groupsof2 = Table.AddColumn(Extractheaders, "Custom", each List.Split(List.RemoveLastN(Record.ToList(_),1),2)),
    #"Added Custom" = Table.AddColumn(Groupsof2, "Custom.1", each Table.FromRows(List.Transform(List.Zip({[Months],[Custom]}), each List.Combine(_)),{"Month","Agent","AHT"}))[[Custom.1]],
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom", "Custom.1", {"Month", "Agent", "AHT"}, {"Month", "Agent", "AHT"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom.2",{{"Month", type text}, {"Agent", type text}, {"AHT", type time}})
in
    #"Changed Type"

User's image

Share via

how do i create a pivot table for this MOM by agent AHT

3 additional answers

Your answer