April 2010 Giveaway for Excel Power Users!
Sam Howley, founder of Oak Focus Software has been kind enough to provide me with some licenses for his Excel Add-In, QueryCell. QueryCell was released in June, 2009 by Oak Focus Software. Try it out with a few free queries, running directly in Excel.
Or open it up to unlimited queries with a license key. Just comment on this post (in English please!) with the most wanted feature you are looking for in Excel and your email address in email [at] email domain format. First 3 get a license for QueryCell.
Beware Sam! Third-party vendors of popular Microsoft plugins have the knack of being bought out by the mother ship, either in the monetary way or The Simpsons way. Good luck.
At first glance, the features from this look like a scaled-down version of PowerPivot, applying SQL instead of DAX.
The Install:
- Files are placed in C:\Program Files\QueryCell by default. The build I got seemed to be from March 6, 2010. Quick and simple install.
- Open Excel (I have 2003 on the PC I tested)
Notice the QueryCell button adding to the annoying Office Live button that I never use.
- Time to fire up my sheet of stock ETFs.
Right-clicking to get rid of the Office Live bar, I notice a new CBarSSQL toolbar. The QueryCell add-in.
Clicking on the QueryCell icon hurts my brain at first. What do I do?
Time to check out the tutorials. Lots of info here. Would have liked to see some sort of MS Wizard interface to setting up a data source, with support for embedded, encrypted ole-db data sources, or perhaps connection strings stored on a server. But this is a developer tool, right? Right?!?
Wrong. This is a tool every power Excel user should have.
Select some cells, Right-Click QC-Add Region As Table. Now I get a data source called A and a list of fields to the right.
Ok, type some SQL. Intellisense! SQL Formatting, something Microsoft is still missing! As a SQL DBA, I miss the CTRL-E shortcut to execute SQL. So I click the blatantly obvious Run SQL button.
Run my query and the Query Results fall into the QueryResults worksheet. Cool!
Add another table. Join the two together. Wow.
Click Mark Rows. Run another query. Multi-rule Conditional formatting! In Excel 2003, no less. Complex, repeatable Auto-Filter!
Tried to add the A.Open column. No go, reserved word. Where's my quoted identifiers or square brackets? QueryCell converted the Open column to COLA. Renaming the column should do the trick but if targeting the financial sector with this, a different way of handling reserved words (or at least OPEN) should be on the feature list. No worries!
Clicked the Refresh Regions button in the top left. Automatically detected data sources!
Somehow I feel like I've just scratched the surface on what I can do here. In any case, this is a tool any Power Excel User should check out, even if they don't know SQL.
Some features I would look forward to seeing:
- Improved SQL support and templates
- Access-style Query View designer (for those lazy dba’s)
- Ability to undock the add-in (multiple monitors)
- Update statements to Excel, and back to SQL
- More data sources (Analysis Services cubes, Sharepoint Lists, Web Services)
- Ability to reset defaults
I can see this tool saving hours of time with complex VLOOKUP formulas, conditional formatting, and cut-paste jobs. For repetitive tasks, generating test data, and automated data refreshes from multiple sources, this tool looks a great helper.
