In managing a big family weekend, I used Excel to track everything from invitations to RSVPs. I tried to create a macro to do something, but deleted it after figuring out it wouldn’t do what I wanted. Yet, every time I opened the workbook, Excel asked if I wanted to run macros as shown in the below image.

What the hey? I deleted the macro. Why are you asking, Excel? So I thought the macro wasn’t deleted. I opened the Macro dialog box (Tools > Macro > Macros), which showed NOTHING indicating no macros existed in this workbook.

OK, so why are you giving me these macro messages? If I can’t find an answer to a Microsoft problem, I post the problem in the Microsoft forums where MVPs often provide the answer. This time, I lucked out on finding the solution myself (which doesn’t happen often as I guess I find the rare problems). Here are the steps I used since my situation didn’t need to do all the steps shown in the solution — the only folder that appeared as the Microsoft Excel Objects.

1. Save a back-up of the file and work from the back-up. If it works, you can save the back-up over the original file.

2. Right-click any tab and select View Code.

3. Double-click the first item in the Microsoft Excel Objects folder.

4. Press Ctrl+A to select everything (even if nothing shows) and press Delete.

5. Repeat Steps 2 and 3 until completed for all items in the Microsoft Excel Objects folder.

6. Close the Visual Basic Editor.

7. Save the workbook.

Open the workbook again and the Macro message should be gone for good.

If you have a modules folder, expand it (click Modules folder once to expand) and right-click the module. Select Remove Module. Excel asks if you want to export it — up to you. Repeat the steps for all modules. Once done, do Steps 6 and 7.

I’ve bent Excel over backwards doing things that it’s not meant to do. I believe I’m an average user, maybe a notch above. This means knowing some of the lesser known tricks, but not a pro at pivots or creating elaborate financial spreadsheets. I have used pivots, but nothing heavy-duty.

This book is 820 pages, but half of it has room for notes on the left page that has Notes, My Tips/Shortcuts, and Related Tips for finding similar tips like the one on the current page. You can see what these look like in Amazon’s “Search inside” feature. The book has 322 tips and even with as many pages as it has, it’s surprisingly lightweight. I have books that are half its size and weigh more.

I also like the bookmarks on the side of the pages. Finding the part of interest is easy. The first page of a part lists the subjects covered and their bookmarks. These subject bookmarks appear with their related tips for quick referencing. You can use the index to find what you need.

Excel 97, 2000, 2002, and 2003 are covered. Each tip lets you know which versions of Excel can use it. In a majority of the tips, all versions are covered so no one with a specific version of Excel gets fewer tips than others.

Part I addresses the new features in Excel 2002 and 2003. It’s very brief, so those who have it already won’t feel like they’re paying for useless information. The price is appropriate for a book of this size with the tips provided and the Excel versions covered. I won’t rehash what each part covers as Amazon’s “Search inside” also lists them and the complete index.

The writing is formal and stiff, but easy to follow. I had no trouble understanding the directions… most of the time. The screen shots fill in the gap when the tips aren’t clear. Newcomers and seasoned users of Excel will benefit from the book. Those who are pros will need to study the table of contents using “Search inside” and decide whether or not the book meets their needs. The quick referencing guide, the format, and the screen shots provide the extra boost.

Title: F1 Getting the Most out of Excel
Author: Joseph Rubin, CPA
Publisher: Limelight Media
ISBN: 0596005768
Date: August 2004
Format: Paperback
Pages: 820
Cover Price: USD$13.57
CDN: 19.57
UK: 10.49

I have bent Excel over backwards to do stuff it was not meant to do like track training metrics and use it as process bug-tracker (think programming bug tracking system except it’s for processes). These spreadsheets require many workarounds not found in the help file or on the Internet. It just takes a few keyboard banging sections and formula screw ups to make these workarounds work.

Excel also has a few glaring features that are missing and the workarounds are available on Microsoft. One such feature is the lack of totals on a stacked column chart. Excel adds the total for each item in the stacked column, but doesn’t add the value for the whole column.

PivotTables are useful, but when I need them, I have forgotten how to use them to their fullest. My mind goes blank when I face the PivotTable wizard. A whole chapter is devoted to its magic and scanning the hack titles helps you quickly decide what you need and go to work.

The hacks go from easy as pie to tough as nails. Did you know the comments boxes don’t have to be square? It’s a simple hack and the first cool thing I learn from Excel Hacks. The hacks covering XML and macros are for the advanced users, but intermediate users should be able to do them and hard working beginners can get there.

Excel isn’t just for accountants anymore and the book shows how to use it for other things. The book is worth the investment for anyone who uses Excel. Make it break a sweat with these hacks and have fun doing it.

VITAL STATISTICS:
TITLE: Excel Hacks: 100 Industrial-Strength Tips and Tools
AUTHOR: Raina Hawley and David Hawley
PUBLISHER: O’Reilly & Associates
PUBLICATION DATE: March 2004
ISBN: 059600625X
FORMAT: Paperback
PAGES: 304
PRICE: US$24.95

I’ve literally been buried in Excel trying to create tracking metrics for a training program. The problem is that there are too many variables to create a pivot table or other automated function to provide meaningful information. The key is to show how much training has been completed and not completed by month so the bigwigs know when we have to pull their employees for training and for how long.

Shown is an example, but the real thing is more complex as there are 10 classes, 11 departments, and 300+ names. I’ve searched the Internet for Excel ideas and tutorials, but most are focused on financials or creating forms. Nothing like this. We do not have a training program in place that would make it worth it to invest in software or create an Access database to give us what we need.

<edit>Finally making headway, but it is a long, slow method. It will be easy to maintain once I get all the formulas set up.</edit> A fresh night of sleep provides a new perspective and a decent solution to the Excel blackhole. Not the dream solution, but good enough to make bigwiggette a happy gal.

This is one way to get creative with Excel for non-monetary needs. The basic steps:

1. pivot tables based on original data (one for each class with all the departments).
2. create worksheet that connects to pivots so chart is automatically updated when pivots are refreshed. It calculates hours.
3. Put in a purty graphic chart.

The pivot tables (step 1) don’t organize the data in the way that we needed it, hence the reason for the worksheet (step 2). Plus, I added formulas to calculate the hours for the department per month. At the bottom is the total hours added from all the classes combined without the specific class information (just to show you what we’re looking at).

This tells the director the total number of hours that her people are in training by month. Looking at the graph (step 3), shows that most of the training is done in April with the Test team spending a total of 15 hours in April.

The Step 1 usage of Apr-Y, May-Y, and Jun-Y indicates the student takes the class in month and Y indicates the student has completed the class. The reason for combining the month and whether or not the person has attended is to avoid another column — another variable to the mess.

We had to track students required to take the class, but haven’t attended. This is done by using Apr-N, May-N, and Jun-N. Obviously, the ‘N’ represents ‘No.’ Blanks mean the student is not required to take the class and should not contribute to the numbers.

This is a simplified version of the spreadsheet. This concludes our Excel as a training tracking spreadsheet tutorial. Questions?

I’ve literally been buried in Excel trying to create tracking metrics for a training program. The problem is that there are too many variables to create a pivot table or other automated function to provide meaningful information. The key is to show how much training has been completed and not completed by month so the bigwigs know when we have to pull their employees for training and for how long.

I provided an example, but the real thing is more complex as there are 10 classes, 11 departments, and 300 names. I searched the Internet for Excel ideas and tutorials, but most are focused on financials or creating forms. Nothing like this. We do not have a training program in place that would make it worth it to invest in software or create an Access database to give us what we need.

Any geniuses out there with a few hints to help a poor crossed-eyed girl?

<edit>Finally making headway, but it is a long, slow method. It will be easy to maintain once I get all the formulas set up.</edit>