Excel Hacks and Tips - The first step towards becoming a power user

One metric you can use to assess if you are a power user or not is to analyze your dependability on the mouse/touchpad.

If you can get all your work done on an excel spreadsheet using solely the keyboard, you have now learned most of what there is.

There are some basic shortcuts one must be aware of:

#1: Navigation

• The arrow keys (← ↑ → ↓): Navigating one cell at a time
• Ctrl + Arrow keys: Navigating to the next non-blank cell (skipping all intermediate blanks)
• Ctrl + Home / End : Navigating to the first and the last used cell (this could either be a cell with data in it, or a blank cell where there used to be data)
• Ctrl + PgUp/PgDwn: Navigating between multiple worksheets
• Ctrl + Tab: Navigating between multiple workbooks
• Press Alt, release it, and then use the left and right arrow keys: To navigate between the ribbon tabs (home, insert, formulas, data etc)

#2: Selection

• Shift + Arrow keys: Selecting one cell at a time
• Shift + Ctrl + Arrow keys: Selecting entire contiguous blank/non-blank cells
• Shift + Ctrl + Home/End: Select all cells starting from the one where the cursor is to the first/last used cell
• Ctrl + Space: Select entire column
• Shift + Space: Select entire row
• Ctrl + A: Select entire range/table (provided you are already in a cell in the said range/table). A second Ctrl+A will select the entire sheet

#3: Basic operations

• Ctrl + Y/Z/X/C/V: Redo, Undo, Cut, Copy, Paste (in that order)
• Ctrl + D: A combination of Ctrl+C and Ctrl +V (select a range of cells in which the first one has a formula; Ctrl+D will copy the formula to the entire selection)
• Ctrl + Shift + V: Paste special (brings up the menu of paste options - only values, values + formatting , formulas etc; the same can be navigated using the arrow keys)
• Ctrl + Shift + L: Activating/deactivating filters on a range
• Alt + ↓: If used while on the headers of a range with filters active, this will bring up the filter drop-down menu
• Ctrl + F: Find/Replace (use Tab key to toggle between the two options)
• Ctrl + - : Delete the entire row/column (select row/column first)
• Ctrl + Shift + +: Add new row/column above/to the left of the selected row/column
• F4: Repeat last action (could be anything - formula, formatting etc)

#4: Formulae

• Alt + = : Sum all cells above (contiguous)
• Ctrl + ` (the second option on the tilda key below escape): Show all formulae in the worksheet

#5: Basic formatting

• Alt + E + A + A: Clear the contents in all the selected cells
• Ctrl + B/I/U: Bold/Italics/Underline
• Ctrl + 1: Brings up the cell formatting options
• Ctrl + $: Change formatting to currency
• Ctrl + %: Change formatting to percentage
• Ctrl + #: Change formatting to date

#6: File options

• Ctrl + S: Save the file
• Ctrl + O: Open an existing excel file
• Ctrl + N: New workbook
• Alt + Shift + F1: New worksheet

#7: Date and time

• Ctrl+ ; : Insert today’s date
• Ctrl + Shift + ; : Insert current time

And the list goes on.

However, excel has far more utilities than can be tagged to a particular keystroke combination. There are no ready-made shortcuts for stuff like format painter, data validation, tracing precedents and dependents, error checking, charts, etc.

But worry not, there is a master-key to alleviate all those.

ALT key; the excel MVP few people know about.

Try it.

Press the Alt key, and see what happens.

This is what you’ll see.

The numbers are for the Quick-access toolbar. We’ll come to that later.

The letters represent the various ribbon tabs.

Now press any of the letters displayed (F, H, N etc).

This is what you’ll get (when you press P; for Page Layout).

Now to enter any of the commands, you will have to press the displayed key combination.

For example, if you press W, you can access the drop-down menu for page width.

The key here would be practice. You’ll at least need to know which tabs have which commands.

Coming to the Quick-access toolbar; notice how some of these key combinations might be too long. May be there is a command that you frequently use and would prefer a shorter key combination.

Worry not.

Go to the File menu -> Options - > Quick-access toolbar.

You will see this screen.

From the dropdown (2), select “all commands”. The menu below will be populated with all excel commands. Select the one you want, and add it to the right side menu (3).

Now, when you go back to the sheet, you can use Alt + (a number) to access that particular command (the one we discussed in the first image).

P.S. This is, by no means, an exhaustive list. And it only covers the basic commands.

Great excel users use a lot of advanced features like Pivot Tables, Macros etc to automate routine work. But that (at least Macros) would require a fair bit of programming acumen, and lots of practice.

Also, most of these shortcuts (except of course, things specific to excel like formulae, cell commands etc) will work with all Office products. Try them out.

P.P.S The Alt command also works in Windows explorer.