(click the images for larger versions)

Excel 2007 is your friend, really. The more I use it, the more I love it. It really IS easier to get things done with it, honest. The ribbon is annoying when you aren’t used to it, but after awhile, you won’t know what you did before. I promise, if you give it a chance, you too can get over the fact that they moved your cheese and see all the neat stuff they added.

Basic Auto-Filter

Make sure your data has headings. Then, on the Data tab, choose Filter in the Sort and Filter section.

The header cells will then have arrows in them. Click the arrow, and a menu will pop up with a variety of choices. To just filter by the basic values, select it from the list.

Clicking OK will hide all the rows that don’t match, as shown here.

Number / Text Filter

You can also use a custom filter to display only rows matching a given criteria. For example, we only want to see products where the price is more than $100. When you click the arrow, you can see that Number Filters is displayed with a small arrow. Hover over it, and options like Equals and Greater Than are available.

Select Greater Than, and enter 100, and click OK.

You can see the custom filter has hidden all non-matching rows, as shown here.

Similarly, you can use custom filters for text fields as well. Text filters support wildcards such as ? and *.  For example, let’s say I only want to see products that have “LL” and “Rim” in the product name. Click the arrow, select Text Filters, select Contains, and enter “LL*Rim” without the quotes.

See Filter Data In A Range Or Table for more.

Filter By Color

Let’s say that you set up conditional formatting so that cells with certain values were highlighted (it’s on the Home tab, in the Styles section). You can now easily filter your data by color, right from the same auto-filter menu. If your cells have colors, the option becomes available.

Don’t use this trick to try to remove duplicates. While removing duplicates was a huge pain in older versions, it also is only a click away. You can remove duplicates by any cell value or set of cell values. It’s on the Data tab.

I hope this post has been helpful! If there are any other things you’d like to know how to do in Excel 2007, let me know in the comments!

Sharing this post is sharing the love for the new Excel. Please bookmark and share it! I’d appreciate it  =)