Finding Things in Lists of Data in Excel

• This article will help you to locate something in a list of data in Excel

• The example we look at relates to locating a person’s name or people’s names within a list of names

• The techniques we cover are Find / Replace, and counting formulae such as =COUNTIF() and =COUNTA()

Finding and Counting Something – or Some Things – in Excel

You probably know how frustrating it is to be sat in front of a spreadsheet scrolling up and down a list trying to locate a piece of data. It is perhaps more frustrating to know that Excel has the formulae to help you do this kind of thing at the click-of-a-button.

This article will help you speed up this sort of time-consuming task by implementing counting formulae.

Example – Finding A Buyer’s Name in a Prospect List

We were recently asked to do a piece of Excel development work on this theme. The client had two lists of names: one of people who had purchased tickets for an event; the other a list of ‘prospects’, or people who had been contacted about the event. The client wanted to understand if a particular person had purchased a ticket, ie. if he or she appeared in both the prospect and buyer lists. There was also a need to understand how many people had purchased tickets and establish a ‘conversion’ rate (names changed in example).

Technique 1: Use Find and Select to Locate a Name

We recommend that you practise the following techniques yourself.

Download the ‘Finding Things in Lists of Data’ exercise file by clicking here.

The simplest and also most time-consuming way to locate something in a list is to use Excel’s ‘Find and Select’ facility. It is situated under the ‘Home’ tab on the far-right in Excel 2010.

Simply click ‘Find and Select’ and enter the value or word that you are looking for. In our case, we enter the name at the top of the ‘Confirmed Buyers’ list – ‘Beth Baker’. Then click ‘Find Next’ one or more times. Excel will show you each occurrence of the word or value. Cool!

We can see than ‘Beth Baker’ appears in both the prospect and buyer lists. We have ‘converted’ this person from prospect to buyer.

Technique 2: Use the =Countif() Formula to Count Occurrences of Name in a List

We can use Excel’s =Countif() Formula to tell us if a particular word or value appears in a row or column that we specify. So we can set up a formula to ask if a name in the buyer list also appears in the prospect list.

You can practise implementing the =Countif() formulae in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.

If we type “=countif(” into a cell (without the quotation marks), prompts will appear to help us complete the formula. Yes – Excel is trying to be helpful!

But what do these prompts mean? Remember, we want to find out if a name in the buyer list appears in the prospect list. In other words, we want to find out if a certain word or value appears in a certain row or column. In Excel language, we have to specify a ‘range’ and a ‘criteria’. This is what Excel is asking for.

The formula will look something like this: =COUNTIF(D17:D266,B17)

It should return a value of 1. It is telling us that ‘Beth Baker’ appears once in the prospect list. Neat!

Now, what if we were to implement =countif() formulae for all names in the buyer list?

Technique 3: Use Multiple =Countif() Formulae to Count Occurrences of Names in a List

Let’s recap. We have tried the ‘Find and Select’ facility which offers a simple but time-consuming solution.

We have implemented a single countif formula to tell us if a particular name in the buyer list appears in the prospect list; this provides instant feedback. So, let’s try to implement a countif formula for each name in the buyer list to give us the complete picture.

You can practise this approach in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.

Implement a =countif() formula next to one of the names to find if that name appears in the other list. For example, implement a =countif() formula to tell you if Beth Baker appears in the prospect list (above). Alternatively, create a formula to show whether names in the prospect list also appear in the buyer list – this is what we have done in the example in column G. Check it out!

Copy and paste formulae down the list – simply double-click in the bottom-right corner of the cell. Now, let’s try to add up all those ‘1’s to find our prospect-buyer conversion rate…

Technique 4: Add Summary Figures

Now we are in a position to calculate summary figures; in this case – prospect-buyer conversion rate. To calculate this, we need to know:

1. The total number of prospects
2. The number of prospects converted to buyers

You can practise this approach in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.

First, use a =counta() formula to count the number of prospects. This formula counts cells that are not blank (ie. that contain something) in a specified range; we need to remember to subtract unwanted entries (ie. the text in the rows at the top). So the formula will look something like: =COUNTA(D:D)-4

Next, use a simple addition formula to total up names that appear in both lists, or converted prospects. Remember, our =countif() formulae return a value of 1 where names appear in both lists – ie. for prospects who are converted to buyers. Use =sum() to add up those ‘1’s, and remember to select the correct range.

We can now add a simple division formula to divide converted prospects by total number of prospects. Implement % formatting (Home Tab, middle) to create a percentage. This is our conversion rate. Easy!

Summary: What we have learned and Why it is useful

This is an example of an analytical task that Excel can help us with. You have used intermediate formulae including =countif() and =counta() to execute the task and find the prospect-buyer conversion rate. Your boss is happy, your colleagues impressed!

But why is this kind of Excel technique so important to learn?

We believe that progress in Excel is not ‘linear’ – x hours study does not result in x units benefit. Rather, there is a group of techniques that – if you master – can dramatically accelerate your Excel learning, unlocking exponential benefits as you improve. =countif() is one of them, and the others are covered on this website.

Mastering these will allow you to approach data-intensive tasks with confidence.

This is the fundamental idea behind the #ExcelRevolution – that focused learning on certain Excel techniques can transform your analytical ability. This has knock-on effects on a company- or organisation-wide level, and even on the economy as a whole.