A Great Reporting Tool For Excel Power-Users

toggle-button

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: 

Your rating: None
4.07143
Average: 4.1 (14 votes)
toggle-button

Comments

@Neifer: I am not sure that getting aggressive or pointing at so called "lack of knowledge/experience" lead to any positive thinking. Of course we agree that we can disagree i.e differences of opinion should be encouraged, this is not my point. Moreover, I am not moderator in here and I do not want to be.
Anyhow, this being said, allow me to suggest friendly that statements like "You have any idea what you're talking about?" and others on alike tone, do not bring anything positive. In addition your English is good enough to understand the fact that questioning competence of anyone can not be considered quality arguments.

To rob.schifreen

I will not discuss ExMerg, but I want to say this:

1. Do you really think that VLOOKUP is so "notoriously complicated"? Strange opinion for the person who writes articles about Excel.

2. Few minutes ago, I read your another article - http://techsupportalert.com/content/best-photo-viewer-ive-seen-years-and...
Really and truly, is Diffractor the best viewer you've seen? In years. Then maybe it's the only viewer you've seen.

You have any idea what you're talking about? Sorry for my English, but I think it's much better than your knowledge of Excel and image viewers.

I assume ExMerg must have access to your data for the commands to work. Doesn't this present some risk to the safety of your data, whether personal or business related?

Hello,

ExMerg author here. Your assumption regarding the commands is correct - let me explain. To provide functionalities like data merging, data are being processed on the server. Personally, I would also preffer client-side processing, but we're not yet there - web applications still have its limits. This is of course improving over time, and I really hope that future version of ExMerg will not require workbook upload.

Meanwhile, let me assure you that security is and will be top priority at ExMerg. We have no intention of threatinng the reputation of this service (which we love to develop) by any means - and as a Java developer with background from banking and corporate environments, I personally take care of the security related code.

If you would have any questions regarding the security or any other topic, feel free to contact us via our website.

Thank you for your feedback and best regards,
Ondrej Sevce

I have to say that I'm a little surprised a website like this is being touted on Gizmo's. There is no privacy policy to speak of and yes, your data is being uploaded to a complete strangers website based out of Slovakia. They also state, "We carefully use sample data from our users to test algorithm improvements." Tread very carefully with this site..

Hello,

ExMerg author here. Thank you for your feedback.

The development of ExMerg is being done in Slovakia, however the service is hosted at Amazon AWS cloud. Anyway, I think that this fact should not matter from the data-safety perspective - services are being developed and hosted all round the globe, and it's really not about the location, but rather about the people behind the service.

Regarding the sample data - these are being irreversibly obfuscated before any usage in our development environment.

Best regards,
Ondrej Sevce

Pivot tables are another great database like in Excel. PowerPivot is a free add-on from Microsoft that takes PivotTables to the next level. I think PowerPivot can combine tables but I've not tried as VLOOKUP and the occasional custom function have always worked for me.
PowerPivot at http://www.microsoft.com/en-us/download/details.aspx?id=102