ALTOM: Excel can be used as database in a pinch

I’ve been helping an administrative assistant (who also happens to be a close relative—see what comes of having a reputation for using technology?) who needs a database on her job to track employee data, but can’t afford a real database. There are good, pre-built database applications for this purpose, but her employer won’t sign that check. And learning to use Microsoft Access to build her own is a major task that’s just not going to happen. So the office soldiers on with paper records and the occasional sparse spreadsheet.

That didn’t leave many options. The lone remaining realistic possibility was Microsoft Excel. It has features that are reminiscent of a database, although it’s not a database application and never will be. As I never tire of reminding people, databases are not optimized for storing data, but for entering and retrieving it, and it’s here that Excel falls short.

It’s easy to enter data in Excel, but getting it back out again the way you want it is a chore. For example, let’s say you want to know how many employees have not taken any vacation time so far this year and have more than two weeks coming to them. A database designed for employee vacation tracking can tell you within seconds, but Excel likely can’t. You have to piece that information together.

Despite its many drawbacks as a database, Excel can at least bridge the gap between file cabinet and full database. But you need to know how to set up a spreadsheet to mimic a database, and there are two different approaches to this. The first is what we might call “LookBack,” which is where you import data from the past to analyze it. Such data is usually not in any sort of easily readable form and won’t be updated during the analysis. It’s kind of a crude form of data warehouse. You analyze this sort of data using pivot tables and the like.

The other kind of Excel database we might term “LookNow,” where you’re constantly updating data in worksheets. You don’t do a lot of analysis of it, but mostly use it for tracking. The designs for LookBack and LookNow are different. I’ll be dealing here mostly with LookNow, but if there’s enough reader interest, I can cover LookBack as well. Or you can pick up any good book on pivot tables, which will show you how to arrange data properly for analysis.

Whenever I design an Excel spreadsheet for LookNow, I start with a single page just for the names of the people or things whose characteristics I’ll be working with. Let’s say that on worksheet “Names” I start with Adams, Mary in cell A1 (I don’t need a heading on this column). When I want Mary’s name on any subsequent sheets in the same workbook, I can simply put “=Names!A1” in that cell, and Mary’s name appears. That way I can change the names in “Names” whenever I want without also changing them on every sheet.

Then, construct the columns for the data. Each column is a characteristic. It might be department, pay level, title, or nearly anything about the objects or people at hand. This can be a bit trickier than it sounds. Much of the skill in building these simple databases is in the data structure. Remember you’re not designing to store data, but to retrieve it.

For example, your company may have mandatory training. If you have a single column labeled “Training Classes,” it may be necessary to type in as many class names as you offer into one cell, something that makes it hard to sort or filter later. It’s usually better to have a separate sheet with nothing but training data, so you can make a heading for each training course. Each employee is then a row, and the training courses are in their own columns, one per column. The actual cell would contain the date the training took place, so you can identify when an employee needs a refresher.

After you have the sheet set up, the major things you’ll probably want to do are to sort and filter. For instance, you can sort in the training sheet by date, so you can see which employees are due for more training. Or in a customer sheet you can filter for only those customers who live in a given zip code or on a particular street.

Later you can get much fancier. Excel has features like AutoFilter, conditional formatting, and data validation that greatly expand its usefulness. You can build a truly remarkable database using complex formulas, but by the time you do that, you’d be better off just building a database in Access.•

__________

Altom is an independent local technology consultant. His column appears every other week. He can be reached at taltom@ibj.com.