One of the great things about Excel is that it allows you store multiple rows and columns of data with many different relationships. Take your classic customer information database as an example. You can store all of your customer’s information and the relationships between different pieces of this information (such as all the customers who ship to a certain city, order a certain item, etc.). However, even though the relationships are stored, it may not be readily apparent what they are. For example, say you want to see all of the customers who are based in Denver. If your data has more than a few rows, it could take a lot of time to do this manually. This is obviously not practical. What’s the solution? The answer is simple: The AutoFilter.

The AutoFilter option is quick and simple to use. It allows you to sort through your data by applying drop-down lists to your columns. These drop-down lists are populated with the unique values for that column. Let’s take a look at the following example:

*Note: If this was a real life example, filtering would be overkill as the data set is so small.

Using the above data, how would we filter on the City field to get only the companies that are based in Denver? Follow the steps below:

1. Select the column that contains the City field.
2. Click on the Data menu.
3. Select Filter.
4. Select AutoFilter.

The City field should now have a drop-down arrow on the right hand side of the cell. If you click on this arrow, the following should be displayed:

This drop-down list will be populated with all of the unique values from the selected column. As shown in the above screenshot, the two values displayed are “Denver” and “Miami”, even though there are three rows with data. This is because two of these rows contain “Denver” in the City field. These unique values are the values that you can filter on.

Finally, to show only those companies based in Denver, click on “Denver” in the drop-down list. The following is the result:

As you can see, only the rows that have a value of “Denver” in the City field are now displayed. To undo this, select the All option from the drop down list. All of the rows will now be displayed.

To remove the filter, just click off the AutoFilter option from the Filter menu (basically the same way you turn the AutoFilter on as described above). This will also undo any filter selections made.

You can also use this same technique to filter on multiple rows All you have to do is select the columns you want to filter on when applying the AutoFilter. Using our example, if I wanted to filter on both the City field and the Phone Number field, I would just select both columns before turning on the AutoFilter.

*Note: In the drop-down, there are also options for “Top 10″ and “Custom”. These are a little more advanced than your basic filter, play around with them to see what works for you. I also plan on writing another article on using these options, as this article is just for the basic use of the AutoFilter.

The AutoFilter is a great option because it is so simple to use, and it allows you to sort through large amounts of data very quickly. I use this feature often, especially when I need to get results fast on a large set of Excel data.

While this is a basic Excel function, I haven’t come across a lot of people who know about it. It’s a shame, because it’s very useful. Basically, the COUNTA function will return the number of non-blank cells in a list or array that you pass into it.

Let’s use the following sample data:

Using the above data, here’s the usage for this function:

• COUNTA(A1,A2) - This will return the count of the non-blank cells when looking at A1 and A2. In the above example, this would return two.

• COUNTA(A1:A3) - This will return the count of all the non-blank cells between and including A1 and A3. In the above example, this would return two.

• COUNTA(A1:A5,”Seven”) - This will return the count of all the non-blank cells between and including A1 and A5, and then will add one to this count for the value “Seven”. In the above example, this would return five. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A5,”Seven”).

• COUNTA(A1:A3,2) - This is very similar to the example usage above. It will return the count of all the non-blank cells between and including A1 and A3, and then will add one to this count for the value “2″. In the above example, this would return three. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A2,2).

I’ve found COUNTA to be a very useful function, especially when you have a lot of data where not all the cells are necessarily populated, and you only care about how many populated ones there are. For example, if you are calculating the average score for each question on a survey, but only want to divide by the number of surveys where this question was answered, then you would use this function to get the number to divide by.

This is the second article in what has become my “Basic Excel Functions” series, where I provide a quick how-to on how to use some simple but useful functions in Excel.

For this article, I’ll be taking a look at the MIN and MAX functions. They can be defined as the following:

• MIN - returns the minimum value in the range of cells you specify.
• MAX - returns the maximum value in the range of cells you specify.

Usage: MIN

Here are a couple of ways to use this function:

• MIN(cell1,cell2,…) - This will return the minimum value between cell1, cell2, and any other cell you specify.
• MIN(cell1:cell4) - This will return the minimum value of the cells between cell1 and cell4, including cell1 and cell4.
• MIN(cell1,cell2,0), MIN(cell1:cell4,0) - This works like the two examples above, however it also includes the number 0 as a value for comparison as well.

Usage: MAX

The usage for this function is pretty much the same as the MIN function, so I won’t go into details for each usage. Here’s a quick usage list for this function:

• MAX(cell1,cell2,..)
• MAX(cell1:cell4)
• MAX(cell1,cell2,15), MAX(cell1:cell4,15)

That’s it for these functions, it really is just that simple to use them. Here’s a link to the previous article in this series on using the SUM function.

If you have any experience working with Microsoft Excel formulas, then you already know that they can be tricky beasts.  I find this to be especially a problem when I cut and paste formulas.  While this is good most of the time, as the formula will change based on the location of where you pasted it to relative to it’s original location, what if you want to only have some of the references change?

This is where Absolute Cell References come into play.  Here is an example situation:  You have created a workbook to track the results of a survey that is being given out.  You have a spreadsheet (let’s call it “backend”) to hold the questions themselves and the associated point value for each answer.  The other spreadsheet (”frontend”) has the results for each survey filled out, showing the answer and the associated point value.  This is done through a formula similar to the one shown below:

=IF(a1=”a”,backend!c1,IF(a1=”b”,backend!c2,”Not Valid”));

This will auto-populate a cell based on the value of a1.  If “a” is entered, the value will be the value of cell c1 in the “backend” spreadsheet.  If it is “b”, the value will be the value of cell c2 in the “backend” spreadsheet.  Otherwise, the answer is marked “Not Valid”.  This seems simple enough and works for one survey result.  However, what happens when you want to fill out the results of another survey?  You can cut and paste the formula, however the cell references for the answer point values (in “backend”) will change as well as the new input cells.  What we want here is the “frontend” reference to change based on location (so “a1″ would change) but the “backend” values to stay the same (so we score each survey the same for each question).

To accomplish this, all we have to do is make the “backend” reference an Absolute Cell Reference.  This is easily accomplished below:

=IF(a1=”a”,backend!$c$1,IF(a1=”b”,backend!$c$2,”Not Valid”));

By adding the dollar signs to the backend cell references, these cell references will not change when the formula is cut and pasted to another row.  Only the “frontend” references will change.  This allows multiple survey results to be inputted into “frontend” without having to change the formulas each time to account for the new location.