Tips for an Easier Life in Excel, Part #1: Shortcuts

Ugh, Excel. It’s one of the most widely-used software applications. Whether you’re a Finance, Administrative or Sales professional, employers today are hiring those who can produce deliverables quickly, often in Excel.

As of earlier this month, our community had answered more than 1,000,000 questions in the Smarterer Excel test. Yet for most people, Excel still looks intimidating with its seemingly complex formulas and a big learning curve.

We want to help you become an Excel ninja with a series of posts in the next few weeks, where we will cut through the clutter and drill down on the most practical techniques.

For Part 1 of this series, here are 3 Excel tips for any beginners:

#1. Use Excel for something fun.

It may seem like a spreadsheet is just for school projects or boring accounting ledgers, but it could actually be a fabulous organization tool for anything fun in your life. Divya Bahl, blogger for HerCampus.com, used Excel to keep track of her adventures in trying different restaurants when she first moved to NYC. By categorizing her list the way she envisioned her journey, the experience was less overwhelming than searching on Yelp and much more personalized.

Excel is a prerequisite for most professions today, so why wait to learn it over something not as fun? Practice makes perfect.

#2. Unplug your mouse to learn Excel shortcuts.

We know this sounds crazy, but imagine having to click through the ribbon a hundred thousand times over your career. That could add up to thousands of hours which you could’ve spent on a vacation or pursuing a new interest. For many competitive entry level jobs such as Investment Banking, productivity in Excel plays a large part in the hiring decision. @IBDBlogger, a blogger who specializes in career advice for bankers, drew from his experience that forcing your hand muscles to practice the shortcuts was a worthwhile investment.

It’s common knowledge that ctrl+c and ctrl+v are shortcuts for copying and pasting. Beyond that, which keystrokes are actually used often out of the 200+ that exist? We recommend learning the following shortcuts first, in order to get comfortable with maneuvering and performing basic operations (Windows, Excel 2007 only):

  • F5: go directly to a specific cell. Nothing wastes more time than scrolling down or to the right to look for a specific cell.
  • Ctrl + arrow (up, down, left or right): go directly to the end of a block of data. Many times you need to get to the bottom of a data set. This saves time from scrolling and clicking.
  • Ctrl + Shift + Arrow (up, down, left or right): select an entire block of data instantly. No need to drag the mouse and miss a cell.
  • F2: edit content in cell. Get in the habit of using this instead of double clicking. Imagine not having to move your hand to the mouse and risk clicking the wrong cells.
  • Alt + = : auto create sum formula and add all values on top. Most of the time you have a simple table that sums a list of numbers. Not having to type the formula can save you a lifetime.
  • Shift + spacebar: select entire row. You may often want to clean out an entire row, copy the row or insert a new row on top. Now you don’t have to scroll all the way to the left of the screen to do it.
  • Ctrl + spacebar: select entire column. Similar to selecting an entire row; with this, you don’t have to scroll to the top.
  • Ctrl + z: undo. It’s easy to mess something up. Undo lets you go back easily.
  • Alt -> E -> S -> V: Paste Special Value. To keep things simple in the beginning, you may like to play with a copy of some data without tangling with the existing formulas or formats. This will provide you the value of the cell with a fresh start. When maintaining advanced data analysis between multiple workbooks, this will become very handy.
  • Ctrl + Page Up or Page Down: Move to the next or previous worksheet easily without moving your hand to the mouse.

#3. Keep everything neat—with more Excel shortcuts!

Nothing hurts the eye more than a messy spreadsheet, where you can’t immediately decipher the categories or the content of individual cells. Formatting a sheet, however, is very time consuming without the practical shortcuts. Here, we recommend:

  • Ctrl + B: bold fonts. Simplest way to create headers for your data block.
  • Ctrl + Shift + &: adds border around the cell. For the most basic spreadsheets, adding border to highlight a summary value is often enough.
  • (select entire column) Alt -> O -> C -> A: auto-adjust column width based on content. No need to drag the tiny line of a column trying to fit the content!
  • (select entire row) Alt -> O -> R -> A: auto-adjust row height based on content. No need to drag the row.
  • Ctrl + 0: hide the entire column. No need to select entire column and right-click for the menu in order to hide it.
  • Ctrl + 9: hide the entire row. Same as above.
  • Ctrl + !: format as Number with 2 decimals
  • Ctrl + $: format as Currency
  • Ctrl + %: format as Percentage
  • Ctrl + /: format as Scientific

If you tackled all of these and are looking for more shortcuts, Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, offers a video showing many keystroke tricks.

Follow us on Twitter @smarterer to stay tuned for our Part 2, Powerful and Essential Formulas. In the meantime, practice your skills by taking our Excel test:

Excel