Add-In Some Power!

It surprises many of my students when their attention is first drawn to the Add-Ins section in Excel. Even many Old-Timers at the application have simply ignored this area, and I can’t say that I blame them – After all, Excel is So Deep these days that no one (unless you are John Walkenbach) can know everything there is to know.

 If you are using Excel 2010 or 2013, you can find the Add-Ins menu by going to File on the toolbar and clicking on the Options button at the bottom of the left column. Then click on Add-Ins and peruse the interesting choices in the right column.

 Here are some of the Highly Useful Tools that you will find available:

• Analysis ToolPak 
• Euro Currency Tools 
• NatSpeak Excel Add-In 
• Solver Add-in 

Of the group, my favorites are the Analysis ToolPak and the Solver Add-in. The Analysis ToolPak contains the following terrific Investigative Tools: 

• Anova 
•  Correlation 
• Covariance 
• Exponential Smoothing (Trust me – Try this!) 
• Histogram 
• Moving Average 
• Regression 
• Much more… 

The Solver Add-in can be effectively used to maximize sales/profit plans, strategic planning, optimizing a product mix, and even picking a winning team! It uses Empirical Data and thousands of calculations to determine the goals you have set.

Excel Add-Ins. Look them up and give them a Quick Try sometime. I think you will be glad you did!

Calculate Payments

The auto industry has been making a significant rebound in recent times due to the improving economy (if you believe that), low interest rates, and cool new features in the newest models. The new high-tech features are enough for many of us geeky types to want to ditch the old minivan, and get something more futuristic. Home buying has also seen a big rise as of late.

  So, how do you know what you can afford? Knowing what the Monthly Payments is, of course, key in making any informed decisions.

Using Excel for Payments:

As long as you have Excel loaded on your favorite laptop, you don’t have to be a financial wizard to quickly calculate what your payments will be. The PMT() function can calculate the monthly repayment using the following syntax:

PMT(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

For example, let’s say you are considering purchasing a new Geekmobile for a bottom-line cost of $32,000. If your annual interest rate is 4%, on a 5-year (60 months) loan of $28,000, your formula would look like this:

=-PMT(0.04%, 60, 28000)

Note: A minus sign (-) was placed in front of the function in order to return a positive value (if you are like me, negative payment amounts just look odd…)

With your formula in place, hit Enter and, Alacazam, you have your monthly payment. If you don’t like the results, play around with the interest rate, the length of the loan, or maybe check out other auto-buying opportunities. PMT is a great little tool – Give it a try!

SmartArt

To many professionals, using SmartArt in Excel may seem to be an unlikely topic. After all, Excel is all about numbers and analysis. Other than Charts, Graphics are seldom thought of, or used in spreadsheets.

SmartArt is, of course, a special group of Graphic Objects that gives the user the ability to create diagrams and illustrations with ease. With the advent of Excel 2010, the selections of SmartArt objects was significantly expanded, giving the Excel Guru more opportunities to make his or her spreadsheet stand out from the masses.

But Why would you want to put graphics in your spreadsheet? Well, consider this example: Let’s say you are a human resources analyst, and you are doing a study regarding the Salaries for the various job levels in your company. You can easily enhance your report to upper management by inserting a well-designed Org Chart into your Excel workbook. Effortlessly done with SmartArt!

 To Insert a SmartArt Object, Simply Do the Following:

1)  Go to the Illustrations group on the Insert tab

2)   Click on SmartArt

3)   Click on the type of object (e.g. Process, Cycle, Hierarchy, etc) you want to insert

4)  Choose the graphic that best suits your purposes and enter your information

5)   Format as desired

Bonus: To quickly insert SmartArt using your keyboard, just press Alt+NM. Bamm!

SmartArt. Another way to make your Excel reports communicate visually and set you apart as the Guru you truly are!

Smile, You’re on Camera!

Charts are wonderful way of presenting data in a visual, easily grasped manner. As the old adage goes, “A picture is worth a thousand words”. The nitty-gritty details can be important at times, of course, and with a little help from a cool little Excel tool, you can have Both!

The Camera Tool
Though not shown by default in Excel, the Camera Tool can be highly useful when you are presenting a chart and would like to include a Resizable Image of the source data included within your graphical exhibit.

Just cruise on over to the Quick Access Toolbar in the upper-left of your worksheet, and from the small drop down button, click More Commands. Then scroll down to Camera and click Add.

Presto! The Camera Tool is on your Quick Access Toolbar.

Now Then, Here is How You Use this Magical Device:

1)  Select the data table of which you wish to "take a picture" and Click the Camera Icon on your toolbar

2)  Then, go to the area on the sheet on which you want the data to appear (probably a blank area on the chart), and Left-Click

3)  Bamm! You have an image of the table (Resize and Reposition if desired).

Now here comes the Really Cool Part! This is Not simply a Static Image of your source table. It is Dynamically Linked to the original table. Any updates or other changes you make in the original table will be instantly reflected in the new table you have placed on your chart.

The Camera Tool. Far Out!

Custom Keyboard Shortcuts

A review of the statistics on this blog shows that Keyboard Shortcuts are one of the favorites of the thousands of Enthusiasts who view this site each month. Everyone knows that keyboard shortcuts improve your productivity and also make you stand out in the crowd of other Excel users.

But what if you would like to create some Custom Keyboard Shortcuts? How can you do this?

With the arrival of Excel 2007, creating new keyboard shortcuts is really a Piece of Cake (or Easy as Pie, depending on your dessert preference…). Let’s say you want to add AutoSum or Sort to your shortcuts. Here is what you do:

• Go to the Excel feature and right-click on it
• Select Add to Quick Access Toolbar from the dropdown menu

Now, when you want to access the Custom Keyboard Shortcut, you simply press the Alt Key on your keyboard. A number corresponding to the feature will appear on the Quick Access Toolbar. Press that number on your keyboard and, Bamm, you have AutoSum, Sort, or whatever you have chosen to place in that toolbar!

This is much Cooler than it may sound, so give it a try. I think you will find it is on your short list of useful Excel features for 2014.

Happy New Year, All!

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