Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting

See how to create a basic Attendance Sheet for a classroom in Excel. See THESE TRICKS:
1)See how to Freeze Panes for Large Spreadsheet
2)Ctrl + 1 to open Format Cells Dialog box
3)Create A Custom Date Number format: ddd, m/d/y
4)Copy Dates and use Smart Tag to fill weekdays only
5)Ctrl + Shift + Arrow selection trick
6)IF function to show blank when no student name is showing
7)SUM function to add attendance score.

Vhmrz18 from YouTube

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Attendance
• Sheet
• for
• classroom
• in
• Excel.
• See
• some
• copying
• tricks
• and
• the
• SUM
• function
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[msashikin85]

i have multiple worksheet with the same template, how im gona freeze panes all the worksheets at the same time??

[ExcelIsFun]

@msashikin85 , I do not know how to do that because if you highlight all sheets many features such as Freeze Pane, Conditional Formatting, etc. do not work. I would just do it on each sheet but use the keyboard (Excel 2007 and 2010): Alt + W + F + F

[Facebookawya]

Thanks a lot it helped me pretty much. I guess it is much easier than doing it with Access!

[ExcelIsFun]

@Facebookawya , You are welcome!

[TheTeacherJulio]

Thank you very much for sharing your tips with us. Julio, from Brazil!

[ExcelIsFun]

@TheTeacherJulio , you are welcome!

[adrian73341]

Question: lets say that for every absent i want to add an "a" and at the end of each row i want to have the total of "a"s for each student

[huliaajuliaa]

Thank you! what do you do if the class is only on monday's? just enter the dates in manually? or is there an easier way?

[ExcelIsFun]

I am glad that you lik eit!

[verifierdata]

excellent tutorial ......... specially freeze the columns, formula, auto formula..... this tutorial is verrrrrrrrrrrrrrrrrry helpful. thanks