Creating and Using Excel Templates

As an Advanced Excel User, you want to avail yourself of every accessible efficiency tool. Excel Templates are files containing common data and formatting options used as Models for other spreadsheets.

A Template is a great shortcut for standardizing the look and functionality of your data. Templates can also provide you with a Standard Framework in which to input and share your data. Reusable formulas and fields enable you to Speed Up your input and get the information that you want in the form you want.

Creating a Template:

1. Open a worksheet you want to use as the basis for the template
2. Choose Save As and enter a name for the template
3. From the Save as Type list, select Template
4. Save the file inside the Templates folder

Using a Template

1. Go to the File menu and choose New
2. Click on Templates and select the general category
3. Double-click on the desired template
4. A new worksheet opens and displays the template
5. Be sure to Name the worksheet when you save it

That’s all there is to it! Try it out – Templates can Save You a Lot of Work, and help you maintain a consistent, Professional Look.

The Power of Solver!

Microsoft Solver is one of the most Overlooked tools in Excel, and bears occasional review. . As an Excel Guru, it deserves to be part of your repertoire. 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 plus Index = Power!

You are an Excel Poweruser, and you say you often use the VLOOKUP function, but would like something a bit more versatile and powerful? Look no further than the MATCH and INDEX functions!

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))

Once you try this, you will be Amazed! Using MATCH and INDEX functions together, now that’s POWER!

Options!

In the old Clint Eastwood movie, Magnum Force, Harry Callahan used the line, “A man’s got to know his limitations”. True as that is, Harry always liked to have Options. For this first post of 2012, I thought it would be interesting to take a look at Option Buttons.

Option Buttons can be very useful in creating Quizzes or Polls in Excel, and are not too difficult to create. There are a few steps, but I guarantee that it will be worth the effort! Here is how you do it:

1. Make sure the Developer tab is visible on your toolbar

2. Under the Developer tab, choose Insert / Option Button (Form Control)

3. Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4. Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box (Form Control)

5. Then draw your Group Box all the way around your Option Buttons

Hooray, We’re Almost There!

6. Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7. Now is where the Fun really begins. Create a Formula that is based on the value that is shown in the Cell Link

8. For example, let’s say you have linked three Option Buttons to cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF(E5=3, "Correct!", "Incorrect"))

Now when the user chooses Option 2 of the three possible, they are rewarded with the “Correct” feedback. Option Buttons, give them a try; “It is Always Good to have Options…”

Happy New Year All!