If you need to store simple databases on your computer, you probably use Excel rather than a custom database package. For straightforward lists of items, such as contacts or telephone numbers, it works just fine. However, without using relational database concepts, storing data in spreadsheets can become very difficult to manage.
For example, imagine you want to store a database of all the gifts you've given your family members over the years. The simplest way might be to create a single spreadsheet where each row contains the person's name and address, the date, and the gift. But this involves lots of unnecessary duplication - you have to specify the person's name and address every time you give them a gift. Which is inefficient, can lead to mistakes if you don't spell it precisely the same every time, and makes it difficult to update the database if someone's details change.
A relational database removes the duplication by having multiple spreadsheets. One sheet stores the names and addresses (and any other details you want) of each person. A second sheet stores the dates and the gifts. And alongside each row in the second sheet, you store the row number of the person concerned. Not the person's details, but simply the number of the row in the other sheet that contains their details. Hey presto, you now have what's known in the trade as a relational database.
Now here's the problem. If you use Excel to create relational-type databases, producing useful reports is difficult. To continue the above example, how do you create a report that shows all the gifts given to a particular person? How do you look up between the 2 spreadsheets in order to find that a reference to "row 7" in the first sheet corresponds to Aunt Emma in the second?
Although Excel can do it, using functions such as the notoriously complicated VLOOKUP, there are better ways. One of which is something called ExMerg, which is a system for merging multiple spreadsheets and producing precisely the type of reports I mentioned.
ExMerg is, surprisingly, web-based. There's nothing to download or install. Just head to www.exmerg.com to sign up, then drag your 2 (or more) Excel files into the in-browser workspace to start creating your reports. Once you're done, you can download your results.
This sort of functionality isn't for everyone. But if you store information in Excel and you want to turn it into useful reports, give ExMerg a try. It's currently in final beta, and all features are free to use.
Please rate this article: