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!