Hide and Seek

“Keeping Things Simple” is almost always a good thing. Hiding a worksheet is one way of keeping your workbook tidy, and also can serve to protect sensitive data from prying eyes. As with all things Microsoft, there is more than one way to hide and unhide a worksheet in an Excel, but we are going to look at the most straightforward method of doing this in Excel 2007 and 2010.

To hide a single worksheet in Excel, open the worksheet and then simply Right-Click the tab and choose Hide. To Unhide it, simply right-click any tab and choose the worksheet to Unhide from the dropdown list.

To hide multiple worksheets, just select the first worksheet to hide with your mouse, hold down on the Ctrl button on your keyboard, choose the tabs of other worksheets to hide, and then right-click and Hide.

Please note that if you want to Hide a worksheet so no one else can Unhide it, simply Protect your workbook with a password by going to Review ribbon on the toolbar ribbon and choose Protect Sheet in the Changes grouping.

Easy and effective; always a good combination. Give it a try, and keep things simple and private. Happy Exceling!

MATCH and INDEX = POWER!

You say you like using the VLOOKUP function, but would like something a bit more versatile and powerful? Look no further than the MATCH and INDEX functions!

MATCH returns the row number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Sacramento", $A$2:$A$6,0)

INDEX returns the value that you identify by row number in an array. Using the example above, “Sacramento” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Using the INDEX and MATCH functions In Combination, we can set up a code retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "126"):

=INDEX($C$2:$C$6,MATCH(E2,$A$2:$A$6,0)) 

Using MATCH and INDEX functions together, now that’s POWER!

Three Ifs

“If” functions can really help you make sense of your data. Here are my three favorites:

COUNTIF: =Countif(Range, Criteria)
For the Criteria, you can specify a cell, a simple number such as 10, or use a greater-than or less-than criteria such as “>20”. Be sure to use the quotation marks if doing a great-than or less-than analysis.

SUMIF: =Sumif(Range, Criteria, SumRange)
The Criteria is the same as in Countif, but you have the option of applying it to a “SumRange”. These are the actual cells to add if the cells in the Range match the criteria. If SumRange is omitted, the cells in Range are evaluated by criteria and added accordingly.

IF: =If(Condition, Value If True, Value If False)
This function is extremely valuable as a stand-alone or in conjunction with other “nested” functions. You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. If(A1>10, “Pass”, “Fail”). Just be sure to use quotation marks if inserting actual words.

There you have it! Three terrific “If” functions. Give them a try some time IF you have a chance!

Scrolling, Scrolling, Scrolling!

If you have a spreadsheet that needs incremental updates of the numbers in certain cells, clicking on the cells and typing in the subsequent number can be tedious. Using a Scroll Bar can make it considerably easier to increase (or decrease) a value, such as a number, time, or date by allowing you to Scroll through a range of values when you click the scroll arrows or when you drag the scroll box. Besides that, it makes your Excel worksheet look very cool!

Here is how you do it on Excel 2007 and 2010:

1. Choose the Developer tab and go to Insert on the Controls grouping
2. Click the Scroll Bar under the Forms Controls section
3. Draw the bar next to the cell you want it to control (it can be either vertical or horizontal)
4. Right-click on your new Scroll Bar and choose Format Control
5. Select the Control tab on the Form Control and choose your own custom parameters
6. Insert the cell reference in the Cell link (e.g. $A$1)

Here is how you do it on Excel 2003 and Earlier Versions:

1. Go to the Forms toolbar and click the Scroll Bar
2. Draw the bar next to the cell you want it to control (it can be either vertical or horizontal)
3. Right-click on your new Scroll Bar and choose Format Control
4. Select the Control tab on the Form Control and choose your own custom parameters
5. Insert the cell reference in the Cell link (e.g. $A$1)

Bamm! There you have it! Not only will this save you tedium, it will make you look like the Excel Rock Star that you are!

Double-Click is Our Friend!

Double-clicking is a powerful tool that can save you many hours of work. Try any or all of the following six techniques and see if you do not agree that “Double-Click is Our Friend!”


1. Autofit Column Widths by Selecting Multiple Columns and Double-Clicking Between the Headers
A relatively common, but highly useful move!

2. Double-Click on the Office Button to Close Excel
Works great in Excel 2007! Displays “Do you want to save…” if the workbook is not saved.

3. Auto-Fill a Series of Cells with Data or Formulas by Just Double-Clicking
Just select the formula in first cell, double-click in the “handle” (small black square in bottom-right-corner) and Presto! This works for formulas, auto-fills (of numbers, dates, etc) as long as the adjacent column has data.

4. Double-Click on Ribbon Menu Items to Collapse Ribbon to Get More Space
Another cool Excel 2007 feature. Enables you to collapse the ribbon to one line.


5. Lock Format Painter and Reuse it with Double-Click
Double-clicking the Format Painter locks the format so you can use it repeatedly in your worksheet – Awesome!

6. Jump to Last Row or Column in Table with Double-Click
This is my favorite. Just select any cell in the table and double click on the cell-border in the direction you want to go. Speedy!

There you have it!  Try these simple techniques out and find out why “Double-Click is Our Friend!”