Excel is really an useful tool and I think I’m just getting started to learn some of its most useful features. Most people (included myself) always thought that complex data are best stored and processed through relational database such as MySQL, Oracle, SQL Server, DB2, Firebird etc. But apparently you can still run relational queries from data which are stored on excel workbooks and fetch required data using MS Query. This feature itself is pretty useful for me when I’m analyzing large datafeeds from affiliate networks/merchants.
For people who are still wondering what this is all about, I recommend that you read this tutorial. The demo was done on a “sales and customers” xls but the same steps to getting the dta through MS Query can be applied to any kind of data you have (for me I have datafeeds).
It was really an eye opener for me and I’m sure it will improve your productivity while dealing with data and reporting in excel. BTW, here are a list of excel shortcuts.
RickG says
I didn’t notice it in your tutorial and I don’t have the referenced book, but one can extend the functionality of MSQuery using the “SQL” button. Clicking this will show the SQL code behind the current MS Query. There are limitations, but one can replace the SQL in this window with more a more complex query than one can build through the MS Query GUI. I use this when I need to populate a PivotTable directly with data, e.g., when there are too many records to return to an Excel table and then build a pivot. When I do this, I usually build the query through another tool, like MySql Query Browser or MSSQL Server Mgmt Studio, and copy the SQL from there to the SQL window in MS Query.
David says
Thanks for sharing, Rick.