4 IFs

Happy Boxing Day All! 

“IF” Functions are some of the most useful and easy to use tools in Excel. There are several flavors of IF to choose from, and each of them can aid you in making sense of your data. Here are my 4 Favorite Ifs:

 1.  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. Alternatively, you can refer to a cell that contains your criteria number (No quotation marks needed with that approach).

 2.  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.

 3.  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. 

4.   IFERROR: =IFERROR(Value, Value If Error) 
Introduced for the first time in Excel 2007, this combines the IF and ISERROR functions, and essentially gives you greater control with potential Error Messages. Rather than just accepting the sometimes cryptic and less-than-helpful default error messages that Excel dishes out, you can Customize your error messages for more clarity.

 No IFs about it, IF functions are another great set of tools for Excel arsenal. Happy Holidays, All!

Layout Best Practices

Back to basics today, and a look at some Leading Best Practices for organizing your data and laying out your Excel workbooks. Although I hesitate to say that any of these guidelines are Absolute Rules, if you consistently follow these practices, you will find you can Do More with Less Effort.

Data on One Worksheet; Information on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports in another worksheet. The fewer the worksheets you have, the easier it will be for your users (including yourself, of course) to navigate and glean the information you are creating.

No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity. 

Deconstruct Your Data 
Always try to divide your data down to its minimum components. You will be able to use more powerful functions and search your data much more effectively. For instance, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later if you find it necessary).

Format Your Headings 
Use a bold font when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.

Sort Your Data 
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.

Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.

 If you follow these guidelines, you will easily be able to use the powerful built-in features that require organized data and a logical layout. Organization = Simplification = Information.

 I wish you the best of times during the holiday season! ~Bob

Your New Default Template

As most all of know, the Default number format in Excel is General. There is nothing wrong with that, of course, but if you find that you a regularly doing work which requires a different format, you can Save Time (and a little frustration, perhaps) by changing the Number Format in the Normal style.

Let’s say you work in an accounting department and, for the sake of formality, you prefer to use the Currency option for formatting your worksheets. You can set this up manually each time, or you can utilize the convenient Templates feature found in Excel, as well as other MS Office applications.

Here is the Suggested Approach for Our Accounting Professional:

1.   Open a blank worksheet in Excel

2.  Go to the Styles gallery on you Home ribbon

3.  Right-click the Normal style and choose Modify to display the Style options

4.  Click the large Format button, and make the new formatting choices you desire

Save as a Reusable Template

1.  Once your number (as well as any other changes) format changes are made, simply click on the File tab and choose Save As.

2.  Type in the File name of your choice (e.g. AccountingTemplate1) and for the Save as type, choose Excel Template (*.xltx)

Now whenever you wish to create a new workbook, your new AccountingTemplate1 will be readily available under PERSONAL templates when you open Excel.

You will be Saving Time every time you set up a new workbook of this type; and How Cool is That!

Staying Current with Currency

It can be interesting to see which Excel topics are the most popular over a span of years. Perhaps ever-increasing globalization accounts for the attention paid to downloading Currency Rates into Excel. This topic alone has gotten over 2,000 hits on this humble site in the last two years.

Excel 2013 Update:

Somewhat curiously, however, it is not quite as easy to access and download the MSN Money Central currency data with Excel 2013 as it was with earlier versions. Although that is the case, a Few Easy Steps is all it takes to import this information into the current Excel version. Here is

What You Do in Excel 2013:

1.   Open a new workbook and select cell A1

2.  Access the Data ribbon, go to Get External Data, and click on From Web

3.   Paste the following link into the Address textbox: http://moneycentral.msn.com/investor/external/excel/rates.asp

4.  When you click on the Import button in the lower right, you get the following dialogue box:

5.   By clicking on the Properties… button in the lower-left, you can choose to Save Query Definition and Refresh every 60 minutes (or however long you choose…)

6.  Click OK

Now you will get the Current Currency Rates whenever you open this workbook. Pretty Cool, eh? Let’s see, as of this writing, you can get approximately 62 Indian Rupees for 1 US Dollar.

  Sounds like it is time to do a little traveling!

A Shortcut to Your Shortcuts

Even if you read this blog only occasionally, you know that I am a Huge Fan of Keyboard Shortcuts. There is no doubt that using keyboard shortcuts makes you more Efficient and Productive (particularly true on larger displays). An additional benefits is that these shortcuts also save stress on your hand and wrist.

The Problem is that Keyboard Shortcuts are hard to remember. Of course you can use a Cheat Sheet, run drills, or regularly devote a small amount of time to learning these wonderful tools. This will be time well spent, as the efficiently gained will pay dividends for years to come.

An alternative way to use these Efficiency Gems is to use a Special Trick that most Excel users are not aware of. Here is What You Do:

1.  Simply Select any Cell or Range on a worksheet without activating the cursor

2.  Press the Forward Slash key on your keyboard or number pad, and Presto…

3.  Letter Labels will magically appear across your toolbar (e.g. ‘F’ for File, ‘H’ for Home, ‘N’ for Insert, etc)

4.  Type the letter that corresponds to the Ribbon you wish to view and Bamm, you will be taken to the ribbon of your choice which will also have Labels that you can choose with your keyboard!

This is a remarkably easy and effective way to get in step with using Keyboard Shortcuts. It is, in effect, a Shortcut to Your Shortcuts!

I hope you have a Safe and Truly Wonderful Thanksgiving!  ~Bob

Customizing the Quick Access Toolbar

Like many useful features in Excel, the Quick Access Toolbar is often overlooked by even the savviest Excel users. This handy feature appears above the ribbon on the left and by default includes the commands, Save, Undo, and Redo.

While that is nice, So Much More can be added to this convenient locale.

By clicking the dropdown arrow on the Quick Access Toolbar, you can easily add commonly used commands such as Sort, Quick Print, Email, etc.

Don’t Stop There, however! After accessing the dropdown menu, click on More Commands (near the bottom). Excel will then present an Excel Options dialogue box that gives you hundreds of choices that you can add (Caution: It is easy to get carried away…).

1.   Choose the type of command you want to add from the Choose Commands From list. Popular Commands is the default, but you can also have other options.

2.  Select the command you want to add to your newly enhanced toolbar and click the Add button. The command button will then appear on the list on the right.

3.  Then use the Move Up and Move Down arrows to reorder buttons on the Quick Access Toolbar.

4.   Click OK

The result will be an Extremely Convenient customized toolbar that you will likely find to be one of your Favorite Excel features!

Double-Click: The Big List

Although I am a fan of using Keyboard Shortcuts, the Mouse also offers unique opportunities to augment your life on your worksheets. One way to enhance your maneuverability in Excel is to master the art of Double-Clicking. 

 Here are 10 Amazing Ways to Use Double-Click: 

 1. Perfectly Adjust Column Widths – Just select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too.

 2. Auto-Fill a Series of Cells with Data or Formulas - 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.

 3. Rename a Worksheet Tab: Double-clicking the tab allows you to immediately edit the name.

 4. Insert a Split - Double-Click just above scroll-bar to include a horizontal split; Works for a vertical split too, by clicking on the little bar shape next to the right of horizontal scroll-bar.

 5. Edit a Cell Formula: Double-click any cell to edit its contents in place (rather than using the formula bar.)

 6. Close Excel 2007 (only) – Simply Double-Click the Office Button.

 7. Collapse Ribbon to Get More Space – I like this one. Just Double-Click on ribbon Menu Names.

 8. Lock Format Painter – Save a Ton of Time by Double-Clicking on the Format Painter icon, making it Reusable. (So Cool!..)

 9. Jump to Last Row / Column in Table – Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

 10. Pivot Table Drill-Down: Double-click on any data value within a pivot table to instantly create a new worksheet which will show the Underlying Records that comprise that value.

 Double-Clicking: Demonstrate your Mastery of Excel with this Important Tool!