Excel Tip of the Week #266 - Keyboard shortcuts advent calendar

Hello and welcome to the Excel Tip of the Week!  This week, we have a present for you - an advent calendar of Excel keyboard shortcuts, made (how else?) in Excel.

You can download the advent calendar at the bottom of this post.  It should already be displaying the first four keyboard shortcuts automatically - and if you reopen the file each day, then one by one new shortcuts will be revealed!

The rest of this post will focus on how the calendar works - if you just want to get at the shortcuts, then go ahead and download the file!

How the advent calendar works

This is a pretty simple template.  Each of the 'doors' has an Excel shortcut typed into it, but the cell is formatted with a red fill and red font so that the tip isn't readable.  The worksheet is protected (see TOTW #216) so that you can't just select the doors and read ahead in the Formula bar.  A dedicated expert could hack this and eat their chocolate early but that's fine!

The second part is a custom conditional format (see TOTW #73) that looks at the label above the 'door' cell, checks if that date is in the past, and if so changes the background and font colours to make them readable.  There's also a catch-all included so that the doors don't lock up again once January starts.  The formula used for the conditional format is:


The LEFT and LEN functions combine to chop off the last two characters of the label cell, which is then multiplied by 1 to coerce it into number format rather than text.  That result is compared with the result of a DAY function which extracts the day part of the current date (as generated with a TODAY function).  Finally, there's also an OR that ensures that the doors are always open if the current date is on or after 25/12/2018.

You can of course use this template to create your own Excel advent calendars - if you want to do that, you can unprotect the attached workbook with "Hohoho".

Have an excellent festive season and see you next week for more Excel tips!

Previous post - Revisiting DATEDIF
TOTW index 
Next post - Creating dynamic ranges

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.