Excel

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.

Bookmark/Search this post with:

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

I read bunches of excel tips and hints from other websites and every now and again I post links and details of the best ones. Here's a few good ones I have picked up recently.

Counting number of unique entries in a specified range From ExcelTip
Counting number of unique entries in a specified range is simple by using the SUMPRODUCT and COUNT functions.
The range can be all in one column or row, or may span multiple (adjoining) columns/rows.
The formula would be written as: =SUMPRODUCT((1/COUNTIF(A1:C5,A1:C5&""))) where A1:C5 is replaced with the range you want to apply the count to.

Bookmark/Search this post with:

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

MS Office Outlook Team Blog - Managing Automatic Meeting Responses. This is a good article on keeping your inbox free from meeting responses - great for cubicle warriors.

How to Search your PSTs with Vista's built-in Search. I have a large amount (several Gigabytes) of archived email stored in PSTs. This tip means that content is now searched by Vista without me having to have these PST files open in Outlook. - tip to Sarah from Channel 10.

EqualsSolved - putting me out of business! Microsoft Excel and web queries for the non IT pro.

Excel has made numerous changes in its conditional formatting between 2003 and 2007. Most of the new features I think are great, though there are a lot of problems with backward compatibility.

One thing I discovered this week is a trap for the unwary.

In Excel 2003 it was easy to create a conditional format for a cell, and then copy that cell or even just the formatting for that cell to other cells, and the conditional formatting would be copied also. Of course it pays to make sure that you correctly apply absolute and relative cell references in the conditional formatting if using formulas. And using paste special or the format painter you could copy just the formatting from once cell to another - including the conditional formatting.

Excel uses a powerful vector system for managing formulas. Each cell can be linked to many others using formulas. In a simple way of explaining this, 2 cells can be added together in a third cell using a formula that references them. The columns are named alphabetically and the rows numerically.

Thus the first cell is known as A1.

Here are two numbers in cells.

It is easy to create a formula that adds 2 cells, E.g., =A1+A2. Entering this formula in cell A3 effectively sums the first 2 cells into the 3rd cell.

Just kidding I don't have a crystal ball, and it should be SpyJournal - but I thought the accidental typo made a cool pun!

However what this post is about is revealing a plan to write a number of Back To Basics series. These will be written from two points of views, mine and Amanda's.

The reasons for the series.