Absolute Cell References Shortcut

Ever use absolute cell references? If you're like me, you find it a pain to go in and type the little dollar signs everywhere. Next time - try this handy trick:

Click in the formula so that your cursor is in the cell reference that you'd like to make absolute. Now press F4. Excel automatically puts in the dollar signs for you. Cool, eh?

Read More

Minimize the Ribbon

Here's a neat trick - you can actually minimize the ribbon in Office 2007. Just double-click on any of the tabs, and you'll see it neatly tuck itself away into the top of your screen. Clicking once on any tab will bring it back to full size, and then it will automatically hide itself again when you click away from it. Double-click on any tab again to completely unhide the ribbon again.

Read More

E-mail Addresses from Excel Spreadsheet to New Outlook Message

Have you ever wanted to send an e-mail to all the addresses you have in an Excel spreadsheet, without importing them into your address book? It's actually pretty simple: you can highlight all the cells that contain the addresses and just copy and paste them into the 'To' field in your Outlook message.

Read More

Excel: Colour Striping Rows Using Conditional Formatting

Have you ever wanted to colour in certain rows in a worksheet to make it easier to read, but found it irritating to have to update the fill colours across the whole sheet if you ever added or deleted rows?

You can actually use conditional formatting to do it automatically for you. Here's how:

1. Click and drag to select the area you'd like striped. Go to Conditional Formatting, it's on the ribbon in the 'Home' tab, in the 'Styles' group. Choose "New Rule...".

2. Under 'Select a Rule Type:', pick 'Use a formula to determine which cells to format'.

3. Type in the following equation (substituting your own values for the variables, of course):

=MOD(ROW()-x,y*2)+1<=y

'x' is the number of the row that you want the striping to start on. 'y' is the number of rows you'd like in each stripe. For example, if you wanted the stripes to start on row 4, and each stripe should be 5 rows tall, you'll want to type in:

=MOD(ROW()-4,5*2)+1<5

4. Click 'Format', go to the 'Fill' tab, and choose how you'd like the stripes to look (colours, patterns...). Click 'OK', then 'OK' again... and tada! You'll have even sets of stripes that are based on the row numbers and the formula you entered, as opposed to manual formatting.

Read More

"Ditto" - A Shortcut to Copying the Cell Above

Here's a handy shortcut not a lot of people know about - if you want the current cell you have selected to have the same thing in it as the one directly above it, you can just press control and quotation marks:

Ctrl + "

Whatever is in the cell above it will automatically be copied to the current cell.

Read More