Thursday, April 29, 2010

Good Databases (Part 1)

It has recently occurred to me that the design, construction, maintenance, and information-mining of Good Databases are the quintessential keystones of knowledge for any advanced Excel user. Today will be the beginning of a Three-Part Series on this vital topic.

Creating a Database in an ongoing Excel workbook can save you time, money, and frustration. By creating a database for information that is routinely updated, you can automate your reports and simplify your users’ interface.

Today we will look at what constitutes a Good Database, and what pitfalls to watch out for.

First of all, a database should contain data, and that is all! No formulas should exist in a database, just pure Data waiting to be turned into Information on a separate worksheet.

Secondly, there should be no blank rows (called “Records” in a database) and no blank columns (called “Fields” in a database).

Thirdly, put only one piece of data in each field. This will eliminate the need for repeating fields, and make your information-mining much easier.

Lastly, make sure the information is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

Next week, we will look at some phenomenal ways of deriving information from your new, improved database. You can use one of your own, or create a database using the example below.

Fantastic stuff coming! Cheers!

Thursday, April 22, 2010

AutoFilter and Beyond!

AutoFilter is a quick and easy tool that allows you to display only the desired rows of data that fit into your defined grouping. By filtering out the rows that do not interest you, your view of your data is instantly enhanced.

To access AutoFilter, go to Data, choose the Filter menu, and click on AutoFilter. AutoFilter Arrows will appear at the top of each column, allowing you to filter on whichever column you choose. Filtering by multiple choices in the columns can be done to narrow your focus on the data.

Now, this is terrific, but wouldn’t it be great to be able to use the functionality of AutoFilter whereby you merely select the value in an active cell for your filter criteria?

Although it is easy to do this in Excel 2007 and the upcoming 2010 versions, (improved functionality), you can use this cool feature in prior versions of Excel versions of Excel as well (if you know where to look).

For instance, in Excel 2003, you should:

1. Click on Tools and go to Customize
2. Choose Data under the Categories and find the “AutoFilter” in Commands
3. Drag the AutoFilter icon to your toolbar

What is confusing is that this flavor of “AutoFilter” is that it works differently than the one you most familiar with! Bear with me, though, as it is worth the bit of extra effort in getting it set up.

Here is what you can do with it:

1. Point to any cell in your table
2. Click on your new “AutoFilter” icon
3. The table will automatically filter according to your selection
4. Cumulative filters can be added by selecting another cell and clicking on the icon again

How absolutely Cool is that?!? Try it out; you will find how truly Amazing AutoFilter can be!

Thursday, April 15, 2010

Top Ten Excel Shortcuts!

I was just sitting here thinking about what my own Top Ten Best Excel Shortcuts would look like, so I have decided to create it and post it in this week’s Excel Enthusiast Blog Here they are in reverse order (ala Letterman’s Top Ten…):

10. Ctrl + ; (Enter the current date )
9. Ctrl + Arrow key (Move to next section of text –Quick!)
8. Shift + F3 (Open the Excel formula wizard)
7. Ctrl + P (Bring up the print dialog box)
6. Ctrl + Shift + ! (Format number in comma format – Nice!)
5. Ctrl + K (Insert a hyperlink – Wow!)
4. Ctrl + Tab (Move between open Excel files – Handy!)
3. F11 (Create a quick chart – Cool!)
2. Ctrl+C (Where would we be without the quick copy combo)

And the Number One Best Shortcut Is (Drum roll, please)...

1. Ctrl+Z (Undo of previous action – Super useful!)

There you have it! Depending on the details of your own work, you may sort them differently, but they are all very useful. Happy Excelling!

Thursday, April 8, 2010

Hyperlinks in Excel

Being able to jump from one location to another online is entirely commonplace when you log onto a website. The ability to do this within an Excel workbook can add both Functionality and Coolness!

By using hyperlinks in a worksheet, the user can instantly access another area in the workbook, another relevant workbook or application, or a place on the web. You can insert a hyperlink into a cell or a shape in any version of Excel.

So how do you insert a hyperlink into your worksheet? It is, as I have said about other techniques in Excel, so Easy you will laugh!

In either Excel 2003 or Excel 2007, simply choose the cell that you want to put the link into, and go to Insert / Hyperlink. Then click on the appropriate Link to area in the left-hand column, and complete the address information. Bamm! You have your Link!

If you would like to make your worksheet even cooler, however, you can quite easily draw a shape like a Button for your link. For instance, you can choose the Rounded Rectangle in the Shapes to draw your own custom buttons in your worksheet, (you can even mimic a website design). From there it is an easy matter of selecting the button (shape) and creating the hyperlink like you do for a cell.

Hyperlinks can add convenient navigation options and Pizzazz to your Excel sheets! Give it a try!

Thursday, April 1, 2010

Microsoft Solver

Have you ever used (or even heard of) Microsoft Solver? If you answered “No”, I am not surprised. MS Solver is a very powerful, but little known free Add-In tool.

To find it in Excel 2007, 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.

To find it in Excel 2003, simply go to Tools / Add-Ins, put a check mark next to Solver Add-in and click OK.

What it Do for You!

Let’s say that 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 (the totals for each shift in this case). 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 other applications that are only limited by your imagination.

Now it does takes a bit of effort setting up your worksheet, but the results are quite remarkable! Show them that you truly are a Genius; give Microsoft Solver a try!