Category Archives: technology

What You Need To Know If You Use Excel All Day

Today, I used Excel for almost the entire work day. There were a few trips over to Outlook to reply to emails, but it was basically a solid day spent in the land of spreadsheets. When you use Excel this much, you start to pick up a few things. When you first get started in Excel, you might think of it as nothing more than digital grid paper. Of course, it’s much much more than that. In this post, I’ll share some of the things I’ve learned about Excel that make using this workhorse much faster and pleasant.

I) The Mouse Only Slows You Down (i.e. learn keyboard shortcuts)

The mouse is helpful but it really slows you down when you have a large spreadsheet to work with. Once you learn a few basic keyboard shortcuts, your Excel experience will be much better. Here are some basic shortcuts to get you started:

Select the current row: Shift+Spacebar

Select the current column: Ctrl+Spacebar

Select a contiguous set of cells: Crtl+Arrow Key (e.g. if you want to select a set of 100 cells of data, A1 to A100, then select A1 and then Crtl+Arrow Down to select the whole range. This is a huge time saver if you have hundreds or thousands of rows of data)

F4: cycles through the absolute reference and relative referene options in a formula. This is incredibly helpful if you work with formulas. (If those terms mean nothing to you, here is a short references tutorial).

II) Learn Some Basic Formulas

Learning Excel’s formulas starts to unlock the application’s true powers. There are quite a variety of formulas to choose from including many financial formulas. Here are three simple formulas that tend to come in handy in many situations. Using functions to manipulate

SUM: this function adds a range of cells.

MEDIAN and AVERAGE: it’s a great idea to use both of these functions together especially if you’re working with a complex data set.

VLOOKUP: The VLOOKUP function is considerably more complex than the plain math functions above. It is a great way to hunt for information or even reorganize information. Getting a VLOOKUP to perform reliably often takes some practice though.

III) Elite Excel Users Program – Learn To Use VBA

Visual Basic for Applications (VBA) is a Microsoft programming language that can be used to program Excel (and other Microsoft Office applications) to perform tasks. The major benefit here is Excel can execute a task programmed through VBA much, much faster than you ever could. If you are ever performing a task over and over again in Excel, this may be a good candidate for automating in VBA. The disadvantage is that VBA takes longer to learn (especially if you lack a programming and/or computer science background) and get to work so it is often only worthwhile for larger projects.