Excel Tips

I recently had a request to write about any good tips I had for working with Microsoft Excel, so below are a few tips and tricks you might not come across easily when using Excel. This is not intended to be a comprehensive list of every cool feature in Excel, but I want to share a few tips that I use on a regular basis.

First, keep in mind that every version of Excel is different, and the newest versions can have quite different interfaces from the older versions. The below tips will work with Excel 2010, but may not work with older versions. If you’re curious as to how to do the same technique with an older version, I recommend Mr. Google, or leave a comment below and I’ll see if I remember how to do the same thing with older versions (which I used up until recently).

The majority of these tips orient around using the keyboard as much as possible, which shouldn’t surprise you if you’ve read my previous article about maximizing keyboard use. In general, you can do most functions with only the keyboard by selecting alt and then following the yellow letters that pop up on the tabs and then the functions within each tab. This is the same with all recent Microsoft Office products.

To quickly insert or delete a row, first select the current row by hitting Shift-Space. Then you can insert by hitting the menu keyboard button (should be near the bottom right), and selecting insert (usually has the keyboard value i). You can delete that row by selecting delete (keyboard value d). And there are a lot of other features you can perform from that same menu, such as clearing all the values in that row, etc.

To set a value in a field to always equal the value of a particular field in the row above it (regardless of rows inserted or deleted), you can use something like this:

=INDIRECT(“B”&(ROW(B73)-1))

In this example, I have a field in L73, and I want it to equal the field in the B column and the row above the field in L73. You might think, why not just enter =B72 ? Yes, that would work if you know you will never insert a row between row 72 and row 73. BUT, if you do insert a row, that field will then continue to refer to B72, even though I now want it to refer to B73 (because my destination field is B74).

So, the ROW command returns the value of the row the current field is on (which is 73 in this example). Then I subtract 1 from this value (so now it’s 72). Then I tack on a B to the front of that (to form B72), and I use the INDIRECT command to tell excel I’m interested in the value from that B72 field.

If you have the row number you are interested in referring to in another field, you can use that information to call the field.

=INDIRECT(“L”&N73)

For example, let’s say that in N73 I have the value 66. That is the row that I want to refer to. By calling =INDIRECT(“L”&N73), it is calling L66. The advantage of this is that I can use other functions to determine what row I want to refer to, and have that row number in a different field.

I also highly recommend using Macros within Excel to automate tasks you do on a regular basis and that don’t require new information (like a formatting change, etc.)

Some other tips:

To start a new line in a particular cell while in edit mode (so you should see a cursor blinking), hit alt-enter. To select the entire range of cells that have content, hit Ctrl+Shift+*. To insert the current date, hit Ctrl+Semicolon, or enter “=today()”. And to insert the current time, hit Ctrl+Shift+Semicolon. To insert both the current date and time, enter into the field “=now()”. To have all columns sufficiently wide to see the content for all entries, select all columns with data and double-click one of the sides of the column header. Same with rows.

Happy spreadsheeting!