Three Quick Tricks!

This week we are going to take a look at a medley of tricks that do not necessarily fall into any neat category. All they do is Save You Time and Make Your Excel Life a Little Easier.

1. Lock Your Formatting
Using the Format Painter is a terrific way to save time adding a professional look to your worksheets. When you format and select a cell, you can click Format Painter and paste the formatting into any single cell.

The Trick that many Excel users do not know is that if you Double-Click the Format Painter, it will Lock the formatting so you can apply it to as many cells as you like! Piece of Cake!

2. Move the Quick Access Toolbar
Okay, you've customized the Quick Access Toolbar, but you don’t like having it way up in the upper-left corner (after all, why stretch all the distance with your mouse!).

You can opt to have it displayed at the bottom of the ribbon by simply clicking on Show Quick Access Toolbar below the Ribbon in the dropdown menu of the Quick Access Toolbar. Easy as Pie!

3. Prevent Error Checking
The Error Checking function in Excel is highly useful for pointing out Inconsistent Formulas in a particular range. There are times, of course, when you may need to omit a range’s formula in a few cells. Excel will then Flag what it perceives as an Error!

To avoid this annoyance, go to Excel Options and choose the Formulas group. Simply clear any of the error-checking rules that are bugging you. Piece of Cake!

Three Quick Tricks. It’s all Cake and Pie!

Intro to MS Query

As has been often discussed in this blog, there are an abundance of powerful tools in Excel that are often overlooked or ignored. This has been a recent theme, so today I want to gently introduce you to MS Query in Excel.

Microsoft Query is, frankly, an Advanced Tool for mining external data. Once this data is acquired from the external source, you are then free to work your Excel magic and manipulate it as you wish. Along with other potential sources, you can secure your information from the following databases/applications:

• Microsoft SQL Server Analysis Services
• Microsoft Access
• dBASE
• Microsoft Excel
• Text file databases

To fire up MS Query, first go to the Data tab and click on "From Other Sources." Select the option "From Microsoft Query."
 

Then choose a data source, (MS Access Database for instance), and provide the required information when prompted. You can also click Browse to find the source on your hard drive or network.

From there, it is a relatively straightforward matter of following the prompts of the Query Wizard. With a small bit of experience, you will be able to extract the Data you need, and be on your way to developing the Information you want in Excel.

If your job involves analysis of information stored in other formats and sources, you owe it to yourself to take a spin with MS Query.

All the best…

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!