Spy Journal 3.0 Filtering the web for you

Data Filters are a very powerful way of analysing tables of data in Excel. Put simply they are a way of reducing the table down to just the items you want to see based on the selections you choose.

For example a simple table like this one, may have several hundred or thousand rows of data. However there may be only a few lines you are interested in. For example you may be only interested in people under the age of 18 or sales amounts that are negative (indicating a refund) or only see sales of red Ferraris.

Turning on the Filters

In Office 2003 Turn on the filters by selecting any cell in your table (contiguous section of data) and selecting Data, Filter, Auto Filter from the menu. (Keystroke ALT D F F)

The arrows will show up as in the next picture.

Joseph Chirilov has written a series of 3 posts showing exactly how to build a fully functioning media player inside Excel.

If you want to try this then follow his step 1, step 2 and step 3. I would be interested in hearing from anybody who does that.

While we are talking about Joseph's site I want to highlight two other posts that are there.

Andreas from BonaVista Systems has let me know that they are running an Excel Dashboard competition.

You can find all the details of the competition on their website.

Winners will choose from 3 great prizes, an Apple iPhone, a Data Visualisation workshop with Stephen Few, a copy of Stephen Fews book, "Information Dashboard Design".

Some of the rules include:

The solution must be in Excel 2000 or more recent, and not require additional software other than Excel and MicroCharts 3.

If you are not an existing MicroCharts user, you can download your 30 day trial version of MicroCharts at http://www.bonavistasystems.com/DownloadMicroCharts.html

Worth a crack!

Jon Karel Pieterse has updated two of his most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.