Since its initial release in 1985, Microsoft Excel has become ubiquitous in the business world as the go-to software package for just about everything to do with numbers. The program can be used for compiling lists, complex calculations and financial modeling, reporting and even as an ad-hoc database platform for smaller data applications. Last year at the Build-2016 conference, Microsoft announced that its Office suite of programs had reached over 1 billion active users worldwide! Since most of these user subscriptions likely include the Excel program, it is probably safe to say that LOTS of us are using Excel on a regular basis, in our daily lives and/or at work. In my professional life, I have encountered almost no one who hasn’t used Excel at one point or another, and many are using the program pretty much daily in their current jobs. Given that so many of us spend so much time in this wonderful program, any efficiencies that we can add to our daily operations within the program would equate to a large number of man/woman-hours saved every day – work smarter, not harder!
Without further ado, I present 10 awesome keyboard shortcuts in Excel that will have your desk neighbors thinking you’re some kind of Excel ninja! I’m guessing most of you have not been using these and I think you will find yourself using them often instead of reaching for the mouse.
(In this section, the abbreviation “Ctrl” stands for the Control button on your keyboard – you can use whichever one is more convenient. All of the keys in the keystroke within the brackets  should be held down together or sequentially, then released to execute the command. This article relates to Excel for Windows – Mac users may need to refer to the program’s help file to translate these keystrokes.)
Top 10 Excel Tips
- [ Ctrl + ; ] – Enter today’s date in active cell – Pressing Control and semi-colon will input the computer’s current date into the selected cell. This is very handy for filling out forms or signing off on a report.
- [ Alt + = ] – Auto-Sum – Instead of manually entering the sum() formula, or clicking on the autosum button in Excel’s ribbon, you can automatically enter a summation formula in the selected cell which will add all adjacent cells above the selected cell. Be mindful that the formula will stop once it reaches a blank cell, so you may need to reselect the correct range with your mouse in certain cases before accepting the formula with Enter.
- [ Ctrl + Home ] and [ Ctrl + End ] – Pressing Control + Home will move the cell selection cursor to the top left cell in the worksheet (A1). Conversely, Control + End will move the cell selection cursor to the last cell used in the worksheet – note that this is the last cell that ever had data or formatting applied in that sheet, regardless of whether that data or formatting has been deleted. You can also modify this keyboard shortcut by holding down the shift button as well – this will extend the current selection of cells to include all cells between the current selection and either the first cell (Ctrl+Shift+Home) or the last cell used on the sheet (Ctrl+Shift+End).
- [ Ctrl + up/down/left/right arrows ] – Similar to the previous hot-key, this will jump the cell selection cursor to either the next non-empty cell in the direction specified by the arrow you pressed, or it will jump to the end of a row or column of data if you press it while the active cell is already in the middle of a dataset. This one can also be modified with the Shift button to extend the active selection in the same manner.
- [ Ctrl + Page Up] and [ Ctrl + Page Down ] – These awesome hot-keys will cycle through the tabs (worksheets) in the active Excel workbook. This might sound trivial, but this one will really speeds up navigating larger workbooks that have many sheets.
- [ Ctrl + Space ] and [ Shift + Space ] – Pressing Control + Space will selected the entire column containing the currently selected cell. Similarly, pressing Shift + Space will select the entire row containing the currently selected cell. If you do these two hot-keys one after the other, you will have selected all cells in the entire worksheet. Neat!
- [ Ctrl + +/- (on the 10-key)] – If your keyboard has a 10-key, this is a great trick. While having an entire single row or column selected in Excel, (as can be achieved by hot-key #6 previously) pressing Ctrl + + (plus) or Ctrl + – (minus) on the 10-key will either add or delete a row or column. Added rows will be added above the currently selected row and added columns will be added to the left of the currently selected column. Using these hot-keys without first selecting a row or column will bring up a dialogue box which will ask you which operation you would like to perform.
- [ Alt + Enter ] (when in cell-edit mode) – Have you ever wanted to type multiple lines of text within one cell? Guess what… you can! Pressing Alt + Enter while editing a cell will enter a line feed character which moves the text cursor down to a new line within the cell. This way, you can put multi-line text within one cell!
- [ F4 ] – This one is easy! This is a button you want to get familiar with on your keyboard. Depending on the context, this button does two amazing things in Excel. If you are not in cell-edit mode (i.e.: you are not currently typing a formula or text into a cell), pressing F4 will repeat the last formatting change that you made and apply it to the active cell. For example, if you are going through a list of names and need to highlight several, after highlighting the first cell by clicking the Fill Color button in Excel’s ribbon interface, you can highlight all subsequent cells by simply clicking F4 after selecting the cell you wish to highlight. The other amazing thing that F4 can do is lock your cell references while entering a formula. This is a more advanced concept, but essentially, if you are entering a formula that you are going to copy to other locations within a worksheet, you need to lock any cell references that you don’t want to be changed when copying the formula down rows or across columns. For example, if you have a number in cell A1 that you are referencing in your formula, pressing F4 after referencing A1 will lock this reference by adding $ (dollar signs) around the reference ($A$1). This means that when you now copy/paste this formula to another location in the workbook, the formula will still reference A1 in that part of the formula. All other cells in the formula that are not surrounded by $ signs, will now reference different cells that are equidistant from the pasted cell as they were from the cell referenced in the original copied cell. Pressing F4 repeatedly while editing a formula will cycle through the variations of cell-locking modes (i.e.: lock row only, column only, both or no locking).
- [ Ctrl + f ] or [ Ctrl + h] – Find / Replace – Many of you may already know that pressing Ctrl + f, as in many programs besides Excel, will yield a prompt with which you can perform a search for certain text within the document (f is for “find”). In addition to performing a search, you can also replace text within the document using the find and replace dialogue. This is actually just another tab on the find dialogue box, but it can be called directly by pressing Control + h. I want to point out an often overlooked aspect of this feature – you can search, not only by text, but also by cell or font format. You can also change the scope of the search to be limited to the current sheet, or to the entire workbook. The find/replace feature can then be used to replace just the formatting of certain cells based on their current format, for example.
So now you can whiz around the Excel program, adding dates, columns, rows, summation formulas and navigating through the tabs of your workbooks without ever reaching over to the mouse, leaving your friends’ and colleagues’ eyes wide and mouths agape. More importantly, I hope these little tricks will help you work more efficiently and get you out of the office a little quicker!
I will leave you with one last keyboard shortcut as a bonus and a complement to all the tricks presented in this article – F1 (The “panic” button) – Pressing this key anywhere in Excel will bring up the in-program help feature where you can find detailed information on all of these features and the thousands of others built into this amazing program.
Written by Dan Langlois, CPA, MSA, Senior Accountant