Protect Your Work!

Well, you probably don’t have a Doberman Pincher to Protect your Excel work, but you have easy-to-access tools that will help you out with this important task. Maybe you are asking “Why is this necessary?” Maybe you have never done this, and never had a problem. Trust me, however, if others are viewing/using your workbooks, there will come a time when they will want to “Experiment” with your formulas and format. You don’t want this to happen!

Let’s take Excel 2007 for a How-To Example:

Protecting and Unprotecting a Worksheet with a Password

1. If there are specific cells that you wish to enable users to modify (such as DropDown Boxes in a report), go to the Review tab and select the Allow Users to Edit Ranges in the Changes group and select the range you wish to keep accessible.

2. Next, click the Protect Sheet button in the same group. Excel opens a dialog box, where you can Assign a Password, and select the Permissions you wish to be available to the users.

3. Click OK

You can easily Unprotect the worksheet with the password anytime you wish to make changes. And, of course, as this can cause a business disaster (people have been fired for losing this), Be Sure to Keep Track of the Password. I know, it barely warrants mentioning, but it does happen.

This is a good habit and certainly a Best Practice for any Excel practitioner. Give it a shot if you haven’t already!

Solving Power!

Microsoft Solver is one of the most Overlooked tools in Excel, and bears occasional review. Although you may not have an immediate need for it, knowing that it exists, and how this Free Add-in works, can result in a future Brainstorm!

"Solver" is an example of how computing power can save you from a plethora of second-guessing. Let's take a look at what it can do and how you can leverage this Powerful Analytic Tool:

Where is it?

To find it in Excel 2010, click on the File Tab, and click the Excel Options button at the bottom of the dropdown. Then choose Add-Ins and select Solver Add-in.

To find it in Excel 2007, simply click on the Microsoft Button, and click the Excel Options button at the bottom of the dropdown. Then choose Add-Ins and select Solver Add-in.

After you load the Solver add-in, the Solver command is available in the Analysis group on the Data tab.

What it can Do for You!

My Classic Example is one where you have several shifts of call center employees that overlap, and you are trying to optimize the scheduling to best handle the projected incoming calls. By using MS Solver, you can quite quickly find the most Favorable Balance for the schedule.

The trick is set your Target Cell (this may be a cell in which you are trying to find the Best Sum, Average, or Standard Deviation) in the Solver Parameters, and make it subject to various cells that you wish to change (in this case, it would be the totals for each shift). You can also make it subject to Constraints such as Whole numbers (good when counting people...).

MS Solver can be effectively used to maximize Sales/Profit Plans, Strategic Planning, Optimizing a Product Mix, and even picking a Winning Team! There are Countless Applications that are only limited by your imagination.

It can take a bit of effort setting up your worksheet, but the results are Outstanding! Demonstrate once again that you truly are a Genius!   Give Microsoft Solver a try!

Match and Index Revisited

Back in 2010, we took a look at the Power of using a table or database combined with the MATCH and INDEX tools in Excel. Due to the lack of use of these Fabulous Functions, (VLOOKUP is more popular, but less robust), today we will Reexamine how and why you should keep this technique in mind.

If you want something more versatile than VLOOKUP, MATCH and INDEX can serve as your humble servants. Let’s take a look at a simple example using the Illustration above.

For instance, MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0))

Using the MATCH and INDEX functions together is truly a Powerful way of extracting data when you need it. Once you try it, you will be Amazed!

Power to the People!

Happy New Year, Everyone! Being the first week of the year, we are going back to some basics with a look at using the Power and Square Root Functions in Excel. These functions can be found in the Math & Trig grouping on your Excel 2007 or 2010 ribbons.

Power Function
Although you can use the caret symbol (^) to raise a value to a power, the Power Function adds an alternative and a bit more flexibility.

The syntax of the Power function is uncomplicated. If you wish to raise 25 to the third power (resulting in 15,625), you would use the following:

=POWER( 25, 3 )

The POWER function also allows you to raise a value to a Fractional power. Let’s say you with to raise 25 to the power of 1/3:

=POWER( 25, 1/3 ) This would give you a result of roughly 2.92

Square Roots
The syntax of the Square Root function is equally uncomplicated. If you wish to find the square root of 25, you would use the following:

=SQRT( 25 ) This, of course, produces a value of 5.

Since SQRT cannot handle negative numbers, you can work around that annoyance by using the Absolute function as follows:

=SQRT( ABS( -25 )) Once again, the result is a value of 5.

The functions of Power and Square Root; Definitely not for Squares, as they give Power to the People!

Excel Quick Tricks!

Season Greetings All! I hope everyone is having a safe and enjoyable holiday season.

I have always appreciated brief “Aha!” tricks that can reveal a Quick Easy Way to accomplish a task in Excel. Here are four little known or used tricks, plus the results of a survey:

1. Select Noncontiguous Cells
Selecting noncontiguous cells in a worksheet is simple by holding down the CTRL key and click on the cells you want.

2. Format Individual Characters
Click the F2 key and use your cursor to highlight the character to want to format. Right-click and the Format drop down menu is at your command!

3. Align Text Your Way!
Once again, Right-Click and use access the Alignment tab on Format Cells. It’s a Snap Aligning your text in any orientation you want.

4. Save Your Chart as a Picture
Do you want to Use Your Chart in Another App or location? Copy / Paste as a Picture, and feel confident it will stay true to the original (and take up less space…).

Extra: Survey Results
I set up a survey of More than 3,000 business folks recently. Over 82% of the responders voted that being efficient in Excel can help you in your job “A Great Deal” or “To a Large Extent”.

That survey philosophy can make you glad you are an Excel Enthusiast!

Keeping Things Proper

Having a list of names imported from a source other than Excel can result in all upper-case, all lower-case, or even a mixture of both! Whereas this may not affect recordkeeping or data calculations, it certainly doesn’t look very professional.

So what do you do when you download 5,000 names that are in something other than proper case? (You sure don’t want to change everything manually…). The answer is, of course, use the PROPER function!

It is so simple, you will laugh:

Let’s say that you list of names runs from A2 to A5002. In cell B2, insert the following formula:

     =PROPER(A2)

Then simply copy this formula down to the bottom of your data with a quick double-click. Voila! Proper Names!

One final note of caution, if your database list contains names such as McCarthy or MacNamara in it, you will probably need to change those manually. And if you have any really Odd Names like DeLaMartre, then you will for sure need to make some adjustments by hand…

Cheers for your holidays!

Bob DeLaMartre

A Micro-Graph for All Versions

This is so cool. Here is a way to make a simple Micro-Graph that resides in your table and Works in Any Version of Excel!

This is really easy. Let’s say you have your Products (or sales reps) in Column A as illustrated above, and in Column B you have the Units Sold. Here is the formula you should put in Cell C2:

  = REPT( “l” , B2/10) and then copy it to C7

For Each Approximate Count of Ten, the formula puts a Hash Mark, (using an Arial font works well), in Column C. The result is a simple, easily read, Micro-Chart!

Try it out in the office, and wait for the Kudos to Roll In!