I have no illusions that I'll be successful. But here goes nothing.
First, from the Institute of Duh. Microsoft Excel is a spreadsheet program. So here's what Excel is:
- A powerful tool for aggregating numeric data
- An effective way to make low to mid-level financial projections
- A useful tool for scrubbing and analyzing data dumps
Here's what Excel is not:
- A database application. Say it with me - Excel is not a database application. Repeat as often as necessary.
- A project management tool. Don't ask me why anyone would try to make it into a project management tool, a task system, a scheduler, or a calendar. It's beyond me.
Now, about how to succeed with Excel. Just as I previously wrote about with Word, there are some general best practices that I've acquired from some of the greats, and am here now to present in a condensed format:
- Instead of KISS (Keep It Simple, Stupid), it's KIDD. (Keep It Dynamic, Dude.) Instead of putting 2 in A1, 2 in a2, and then "=2+2" in A3, make A3 instead say "=SUM(A1:A2)". That way, you can always change the value of A1 or A2, knowing full well that the value of your total will change instantaneously.
- Give up trying to learn every last function in Excel. Repeat - FORGET IT! The best thing to do is figure out instead what it is you want to do, and then work backwards from the desired result. Believe it or not, with some practice and some creative Googling, you'll get there eventually.
- So what if you don't find a function that does something you're planning on using all the time? OK, write your own. Easier than it sounds. Stay tuned for a future blog on some quick and dirty functions I've used.
- For you macro junkies out there (guilty, Your Honor), try storing your macros in an XLA file rather than in "PERSONAL.XLS." Too much stuff in PERSONAL.XLS can often result in it getting needlessly bogged down.
- In that future blog about macros, I promise also to talk about how to make an Excel add-in. Good way to go if you want to have individual chunks of code for individual applications.
- Fo the record, PERSONAL.XLS is in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\. That's also a handy directory to have if there are any files that you want to have load at startup in Excel.
- Make an absolute religion out of templates. No different than Word in this respect; make sure that your templates have the following properties:
- The cells containing critical formulas are protected
- The cells where you want an end user to input data are not protected
- Any workbooks that will be going to an external client are free of comments
- Don't just protect the cells. Protect the worksheets. If you don't protect the sheets, people can delete those worksheets. That might be bad, what do you think?
- Speaking of templates, the ultimate template secret is in XLSTART.
- Set up a blank workbook with all the parameters you want, from margins to number of worksheets.
- Save the file as "Book.xlt"
- Save the file in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\
- There'll be a "Book.xlt" file there already; when prompted, you can say "Yes" to the replacement
- When you quit and re-open Excel, your workbook becomes the default.
- Good news - the AutoCorrect settings and the Spell Check information that I discussed in the previous blog carries over to Excel. (As well as all the other Office applications. But Excel is the current area of focus here.)
- Useful, quick-and-dirty auditing tool: Hold down the CTRL key and hit the Tilde (`) key. This will allow you to toggle back and forth between formulas and values. (Yes, you can print that information.)
- If you have an enormous sheet, that bogs down Excel every time you open it, try this: Go to Tools->Options->Calculation. Temporarily change it to "Manual." Make sure you remember that you made this change. If not, you may forget later.
- In fact, spend some time and get comfortable with Tools->Options. You can change your default file save location, the default number of worksheets in a book, and default chart colors.
- This is unusual for me to recommend registry hacks, but I have to make an exception in this case. By the way, if you're not comfortable making potentially dangerous modifications to your computer, please skip to the next bullet.
- Go to Start->Run->Regedit.
- Click on the folder that says "HKEY_CURRENT_USER"
- Click on the sub-folder for "Software"
- Click on the sub-folder for "Microsoft"
- Click on the sub-folder for "Office"
- Click on the sub-folder for the highest number. I have Office XP, so that number is 10 for me.
- Click on the sub-folder for "Excel."
- Click on the sub-folder for "Options."
- On the right side, right-click, and choose "New->DWORD Value."
- The name of the value should be "UndoHistory."
- The base should be "Decimal," rather than the default, "Hexadecimal."
- Set the number to anything between 1 and 100.
A couple of final notes:
Excel documents tend to get corrupted when you put them on a network drive, and people are trying to open and re-open them. Watch out for that. If a large number of people are trying to open and change the same book over and over again, you're probably trying to treat Excel like a database. Stop it right now.
Excel is an OK application for forms. Access is way better. Don't forget - Access can do calculations, too. Access is a lot better at storing large amounts of data.
The bottom line, my friends, is that Excel is not really such a great way to store large amounts of data, but it provides some very elegant and graphically appealing ways to display a slice of data. Excel is well-suited to grab a chunk of data from an external source and analyze it, but the external source is probably better suited to store the underlying data. Division of labor, that's what it is.
The next blog will be a little bit of a rest stop for both of us - PowerPoint. Until then, be well.
