Summarizing Employee Hours Worked and Vacation Time over 52 Weeks in Excel

Sum Across Worksheets

Sum Across Worksheets

This is the third episode in my series of Excel Tutorials where I share my best practices for creating an Excel Workbook that contains 52 weeks of Employee Time Cards. In this lesson, I focus on techniques to Summarize – or Consolidate – the hours worked or taken as vacation, by each employee, over a period of several weeks.

Sum Across Worksheets

This is a great tip to learn! In my experience, I find that very few experienced users of Excel know that you can do this! This will eliminate the hassle of linking to a cell in individual worksheets to create your formula. There is, after all, a limit to the number of characters that you are allowed in a formula!

Consolidate Data By Position

Since each worksheet in this workbook is identical in structure, Data Consolidation is simple. Provided, that is, that you remember to “Add” each worksheet range reference in the Dialog Box. In this example, I have “created a link” to each worksheet so that the data can be easily updated.

Links to Other Tutorials in this Series

Shop for Additional Resources

I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com – where you can preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Rename and group worksheets in Excel 2003

Here are the “tips and time-savers” in today’s lesson:

  1. Rename your worksheets – give them a more descriptive name
  2. Insert a new worksheet in your workbook
  3. Use “Tab Color” to better organize your worksheets
  4. Group your worksheets to improve efficiency & accuracy of data entry & formatting
  5. Use “AutoFill” to speed up data entry

Find the video lesson that you want – Index to all Excel Topics

New! My DVD, “The 50 Best Tips for Excel 2007,” is now available for purchase. I invite you to visit my online bookstore for more information.

Click here to see a listing of “The 50 Best Tips for Excel 2007.”