How do I show time in the Y axis in Excel?

I am currently trying to do a graph reflecting the time spent per phone call. I need to be specific in the amount of minutes each call is but can't seem to get the y-axis to reflect the values correctly. I have tried using seconds and breaking it down to minutes:seconds but can't get it right.

DURATION Duration (Minutes)
3222 53 Min 42 Sec
2833 47 Min 13 Sec
2753 45 Min 53 Sec
2220 37 Min 0 Sec
2175 36 Min 15 Sec
2154 35 Min 54 Sec
1953 32 Min 33 Sec
1905 31 Min 45 Sec
1845 30 Min 45 Sec
1803 30 Min 3 Sec
1795 29 Min 55 Sec
1764 29 Min 24 Sec
1635 27 Min 15 Sec
1607 26 Min 47 Sec
1589 26 Min 29 Sec
1582 26 Min 22 Sec
1509 25 Min 9 Sec
1505 25 Min 5 Sec
1504 25 Min 4 Sec
1478 24 Min 38 Sec

2 Answers

In column A, have the duration in seconds.

In column B, use this formula to convert it to fractions of a day: =B2/3600/24

Now format column B using this custom format, so it displays as desired: m" min" s" sec"

Be sure to include the double quotes as shown in my string; they mark the parts of the format string that should be shown literally.

Now you can use the formatted column in your graph.

1. Convert your data to an Excel Table (it's a good practice).
2. Add a helper column (or replace your Duration(min) column) with the following formula
   =TIME(,,[@Duration])
3. Format your Time column as Time (cumulative hh:mm:ss, represented in the Type list as 37:30:55)
4. Create a bar chart with the Time column as your data series.
5. Format your horizontal axis for the time intervals you like. For example, you could set up the following values: Min=0 Max=0.0416666 (one hour) Major=0.0069444 (ten minutes) Minor=0.0006944 (one minute)
6. Format everything else to taste.

Since Excel uses serial time/date where 1=1 day=24 hours, divide as appropriate for the increments you'd like.