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!