IF and ISBLANK: Controlling Whether a Calculation is Made or Not

There's a way to tell Excel to only perform a calculation if there are actually values for it to work with.

As an example, someone once sent me their invoice template in Excel to fix up. The total column was multiplying the number of items purchased times the price of that item. With nothing filled into the invoice yet, the totals column was just a line of zeros going down the 15 rows where you could enter items. That means that if you only entered two items, you'd have to delete all those extra zeros or just leave them there (which is sloppy). Some people work around that by using conditional formatting to turn zero value white (on a white page, the white font is invisible). I'd suggest using IF and ISBLANK together to only perform the calculation if there are actually values to work with. Otherwise, have the cell remain empty.

Using this same example, assuming your totals are in column A, the price is in column B, and your totals are in column C, enter this formula:

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",A2*B2)

It's basically saying: if A2 is blank or if B2 is blank, put nothing (""). Otherwise, multiply A2 by B2.

This ensures that the calculation only takes place once there is both a number of items and a price. Unless you really need all those zeros, this is a great way to help clean up your spreadsheet.

Read More

Change Underline Colour in Word

(Yes, I'm Canadian and I spell it colour, not color!)

Today I've got a nifty underlining trick to share - did you know you can change the colour of the underline, without changing the colour of the font? You could have black text with a purple line underneath it, for example. And it's easy!

Just highlight the text you'd like underlined, go up to your 'Home' tab on the ribbon, and in the 'Font' group you'll see the underline button (the one with an underlined U). If you click the little down-pointing arrow right next to it, you'll see at the bottom an option for 'Underline color'. You can go ahead and choose any colour you like, and you'll see the underline colour has changed but your font colour has stayed the same.

In that same menu you can also change the style of the underline... maybe you'd like it dashed, or thick? Anything you like - it can all be customized right there.

Read More

Word: Inserting Comments Shortcut

Today's question: "Is there a shortcut to insert comments into a Word document?"

There's a shortcut for just about anything imagineable - to put a comment into your Word document without using the ribbon (or menus in previous versions), just type:

Alt + Ctrl + M

It'll automatically start a new comment for you, wherever your cursor is currently positioned.

Read More

Excel: Data Validation List Source in Other Worksheet

Ever tried to limit what users can enter into a cell by making a drop-down list using Excel's Data Validation feature? You might have had a hard time selecting a range from another worksheet to use as the list source. Thankfully, there is a way to make it work. If you name the range in the other worksheet that you'd like to use as your list source, and enter that name range as the source in the Data Validation dialog box, it'll work just fine!

(Use "=TheRangeName" as opposed to something like "=Sheet2!$A$1:$B$4").

Read More