A Developer’s Excel: A Couple Of Tips

As a BI developer I regularly use Excel to analyze my data on correctness.  In Excel it’s easy to filter out unrelated records, as you’ll probably already know.

How do I get my data in Excel?

With a simple copy/paste.  Since SQL Server 2008, you can just right-click on the results in the Management Studio and select Copy with Headers.  This will put the selected data into memory, including the column headers.

You can even make a selection in the Results pane and only copy that, as shown in the screenshot above.  Here’s what it looks like when pasted into Excel:

How do I make the columns wider?

Excel will not expand the column widths by default, shown in that last screenshot.  You could manually enlarge them one by one, but that’s not convenient when you last filled column is called “DI”.  Instead, I use the following trick.  First I select the whole sheet by clicking on the grey square in the top-left corner:

Then I double-click on the vertical divider in between columns A and B.  This will adjust all column widths so that the data fits nicely.

Does Excel know SELECT DISTINCT?

The other day I needed to filter out all duplicate records out of a really large dataset.  I first tried by activating the regular filter.  This is an Excel feature that I use a lot.  Just clicking the Filter button on the Data ribbon will add the small dropdowns next to each column’s header:

But in those dropdowns I couldn’t find an option which would get me to the required result.

(Note that I added a couple of duplicate records in my dataset.)

After a little search, I found the magic checkbox!  I didn’t know that it could be so easy.

To find it, click the Advanced button in the Data ribbon.

In the Advanced Filter window that appears you see a checkbox called Unique records only.

There’s your SELECT DISTINCT!

And here’s the sheet with the duplicates filtered out:

Do you also see that the filter dropdowns are gone, although I did not remove them?  The Advanced Filter cannot be used in combination with the regular filter.  Once you activate the regular filter by clicking the Filter button, your duplicates will re-appear.

If you want to further filter your data after filtering out all duplicate records, you should select the Copy to another location option in the Advanced Filter window.  Copy it below your data on the same sheet and apply the regular filter on that new set of data.

Copying to another sheet is not supported.  Apparently you can only copy to the active sheet.  Should you try it out anyway, you’ll get this message:

Happy filtering!

Valentino.