How to Autosum Time in Excel (4 Easy Methods)

Microsoft Excel has multiple ways of performing autosum in spreadsheets. In this article, we will show how to autosum time in excel. The methods described in the article might be helpful in calculating the total working hours of employees in a month, the total time taken to complete several tasks, and so many other situations. So, let’s begin today’s journey.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Useful Methods to Autosum Time in Excel

This section will demonstrate 4 effective methods for Autosum in excel. For illustration, let’s assume that we have a data table where weekly working hours for four employees of a company are given. ( See the figure below)

Now, we want to autosome the total working hours for each employee in column G. To accomplish that, let’s check our first method.


1. Utilize Autosum Option to Autosum Time in Excel

This is a very straightforward method. In this method, we will use the Autosum option. Follow the steps given below to know how this method works.

Steps:

  • First, select all the cells of the column named Total.

Utilize Autosum Option to Autosum Time in Excel

  • Now, go to the Editing group in the Home tab and click on the Autosum Option icon (Shown in the figure below)

Utilize Autosum Option to Autosum Time in Excel

  • Consequently, you will get the Sum of times for each row.

Utilize Autosum Option to Autosum Time in Excel

Read More: How to Use AutoSum In Excel (4 Easy Ways)


2. Apply SUM Function to Autosum Time in Excel

We can also use the SUM function in addition to the Autosum option to autosum time in excel. To do that, follow the steps below.

Steps:

  • First, click on cell G5 and write down the following formula.
=SUM(C5:F5)

Apply SUM function to Autosum Time in Excel

  • Now, press Enter, and you will see the Sum of times of the first row.

Apply SUM function to Autosum Time in Excel

  • Then, we can use the Fill Handler to copy the formula to other cells as well.

  • As a result, we will get the Sum of times of all rows in the table.

Apply SUM function to Autosum Time in Excel


3. Use a Shortcut Key to Autosum Time in Excel

This method is not so popular but useful when we need to autosome data in a column. Hence to illustrate this method, we have to transpose the timetable like this.

Now in this arrangement,  we have to autosum time in column-wise. Follow the steps below to learn how we can use a shortcut key to autosum time in excel.

Steps:

  • First, select cell C9.
  • Then press  Alt += 
  • And now click Enter You will have the following result.

Use a Shortcut Key to Autosum Time in Excel

  • Now, do the same thing in cells D9 to F9. The final result will be the same as the previous two methods.

Use a Shortcut Key to Autosum Time in Excel

Read More: [Fixed!] Excel AutoSum Not Working (4 Possible Solutions)


4. Apply Total Row Option to Autosum Time in Excel

This method uses the Total Row option in the excel table to autosum time in excel. Similar to 3rd method, the time is summed column-wise. Hence, we need to organize the dataset as shown in the figure below.

Figure 12

Now, to do autosum, follow the steps below.

Steps:

  • First, we need to convert the data set into an excel table. Select the entire data set and click on Format as Table in the Styles group. From the available table design, click on any suitable one.

Apply Total Row Option to Autosum Time in Excel

  • Now in the Create Table dialogue box, click on My Table has headers and then click OK.

Apply Total Row Option to Autosum Time in Excel

  • Now that we have created a table, we need to add Total Row. To do that, click on any table cell, go to the Table Design tab and check the Total Row Consequently, a new row will be created. (Shown in the figure below)

  • Now, click on cell Then from the dropdown menu, choose Sum. You should see the Sum of times in the first column.

  • Do the same tasks again to get all the desired Sum of times for the rest of the columns.


Sum Time Over 24 Hours in Excel

If we have a set of times whose Sum is more than 24 hours, we need to modify the format to display accurate results. ( See the figure below)

Here we can see that the cells G5 and G6, the displayed results are not what we expected. In G5 and G6, we should have got 24:30 and 27:30, respectively. This anomaly is due to the formatting of the cells. To solve the issue, follow the steps below:

Steps:

  • Select the last column and go to the Number group and click on the down arrow (see the figure below)

  • Then, in the Type box, write the following text [h]:mm;@

  • Now, click OK. As a result, the sums will be shown in the desired format.

Sum Time in Excel Over 24 Hours


Things to Remember

  • The first two methods are applicable for both row-wise and column-wise summing.
  • The third and fourth methods are only applicable for column-wise summing.

Conclusion

That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo