Excel and Access (incl. VBA) Cheatsheet
Cheatsheets for any subject are a good reference tool when working in a detail-rich environment. They can also be an excellent learning tool if you want to quickly get to grips with the shortcut tools of a pro.
The best cheatsheet I have learned from in the past is Soren Laueson's VBA reference (by the way, thanks for that one Soren!) Another one by David Franklin that helped me learn SAS is here. A couple of quick searches reveal others for SQL and for Excel.
If you find a shortcut you like it is a good idea to list it on a sticky note and stick at the side of your monitor--until you do it without thinking.
My Top 10 Excel Cheatsheet Shortcuts
I recently created my own Excel and Access cheatsheet with some of the better shortcuts I reach for. Below are my current top 10 techniques from the cheatsheet.
1. Add current range to selection: Shift+F8
So, I recently started really using this and that is the reason it is my current favourite shortcut. It basically allows you to quickly select multiple regions without using a mouse. Goodbye endless scrolling to copy congruent ranges into one: phenomenally useful shortcut!
2. Jump to cursor (VBA): Ctrl+F9
Although learned years ago, I had to rank this at #2 since it is one of the most powerful I know. It enables you to jump to a later or earlier line in your code when in break mode: invaluable when debugging or testing bits of a VBA subroutine.
3. Autofit selection col widths: Alt+HOI
Another one I currently rank highly since it is one I have recently started using. This lets you auto-fit columns to the current selection (not the whole sheet--just the range you select!) Loving it just now.
4. MaxIf: =MAX(IF(A1=C1:C100,D1:D100))
This is an array formula form. Thank goodness for these--it allows me look take two lists and pull back the maximum (or minimum) associated duration in the first list for each correlated reference item in the second list... ask me for more detail! It is a great thing to be able to do.
5. New pivot table: Alt+DP
I can't believe I am putting this in at #5! It is one of my most frequently used and rightly so since pivot tables are the bread and butter of inquisitive Excel data analysis.
6. Week Commencing: =INT((A1-2)/7)*7+2
How many times have I had to quickly look this quick formula up. Basically it converts a date to the Monday of the week in which that date falls. You can mess with the 2 and 7 to modify the start day or change to fortnights, etc. You can also adapt it to convert times to hourly or 10 minute increments.
7. Enter current time: Ctrl+:
Use this often for timesheet tracking. Saves a lot of time, ironically.
8. Get last row (VBA): Range(“A64000”).end(xlup).row
The old End(xlup).row trick will pull back the current last row in a column and is indispensable for a number of VBA applications like caching several workbook datasets into one, etc.
9. Format a number with 2dp + ‘000,: Ctrl+!
It is one of the best--ranked low here since I have done for years and do without even thinking now. But it will format any number as a fixed number with two decimal places, commas after each thousand increment. Again, insanely useful shortcut.
10. Jump to formula precendents: Ctrl+[
Last in at #10, press Ctrl+[ while on any formula cell and the cursor will jump to one of those inputs and highlight the rest so you can quickly jump to what is feeding the formula. Not perfect but can be really useful.
Download my PDF cheatsheet packed with over 100 quick-reference techniques like the ones above. Or alternatively just download the cheatsheet.
