Sunday, July 19, 2009

Do Power Excel with the hot DigDB Add-In for Excel


Sample Data

DigDB is great if you have data in a traditional database-type list. I put together a sample database to use while testing DigDb. This data has sales quantity and revenue for Region, Zone, Channel, and Product.

Filter by Multiple Criteria

Filter by Multiple Criteria...

DigDB offers extremely powerful filtering without having to go through the pain of doing an Advanced Filter. From the DigDB menu, select Filter by Multiple Criteria.

Simple Dialog Box

You are presented with a simple dialog box. You can point and click to add fields to the criteria list. It is easy to join with AND or OR. Here, I've said that I want any Zone from Texas or the Northwest and records where the product is "C". There is no way to select two zones using an AutoFilter. You have unlimited criteria clauses instead of the usual Excel limitation of 3 clauses.

Simple Dialog Box

After you click OK, DigDB tells you the results of the filter. You have an option to see these records, or the records that did not match the filter, or to invert the filter.

Operation Report and Tips

I'll click OK to see the records that matched the filter. DigDB has moved my matching records to the top of the list and hidden the other records with the Group and Outline button.

Result
Copy Visible Rows

Already, DigDB has provided an easier interface to filter just certain records. But, what is the usual next step? You might want to move these records to a new sheet. Usually, this involves the GoTo Special and "Visible Cells Only" trick. You can forget about that with DigDB. Simply go to the DigDB menu and choose to Copy Visible Rows to New Sheet, or select the visible rows or to mark the visible rows.

Multi-Level Sorts

DigDB makes it easy to sort by 4, 5, or more columns in a single sort.

Multi-level Sort

DigDB also offers the ability to randomly sort a range or you can fill a range with random dates or numbers.

Random Values

Deleting Tools

Deleting Tools

You can use DigDB to automatically delete hidden rows or columns.

DigDB also offers the ability to randomly sort a range.

Better Pivot Tables

Pivot Tables are the greatest invention since sliced bread. Lotus introduced Pivot Tables to the world with their Improv tool. Still, Pivot Tables have their limitations. I once had a client who needed the Median via a Pivot Table. It took us MANY lines of code to simulate that with array formulas. Now, DigDB lets you create summaries that can sum, average, median just like a pivot table. DigDB calls this a 1-Step Summary. Here is the dialog box and the result.

Step Summary Sample Data

And More

DigDB lets you match two lists of data. You can easily convert text to dates or vice-versa. You can convert a cross-tab of data to a list or a list of data to a cross-tab.

No comments:

Post a Comment