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!