Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Sundry Excel Tips

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.

jethro's picture

Microsoft Office Links

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.

jethro's picture

Excel 2007 Conditional Formatting

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.

excel 2007In 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.

jethro's picture

Back To Basics: Using Absolute References in Excel

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.

imageimageIt 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.