Filtering data and validating data in excel

You can also sort text and dates, either alphabetically, by format (such as color), or by a custom list you create.

Auto Filter details () Filter for blank cells, non-blank cells, use criteria to filter, filter by cell or font color, just to name a few. Advanced filter details () You can use the Advanced Filter to create more powerful filters, such as filtering for unique records and using operators such as OR.

An Excel data validation drop down list only shows 8 items at a time, and with a long list of items, it might take you a while to scroll through the list.

filtering data and validating data in excel-42

Course summary A brief reminder of the key points in this course.

More courses available at Microsoft Office Training.

Instead of showing all the names, you can type a letter (or letters) in cell B3.

This runs a macro, to show a short list of names in the drop down, based on the starting letter(s).

And Sort and Filter are some of the most commonly used features to help you.

To change the order of your data, you’ll want to sort it.The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically.Note: It’s best to put your lists on a separate worksheet (hidden if necessary) so that no one can edit them.In addition to creating a short list for the data validation drop down, the macro also filters the list of orders.When you type one or more letters in cell B3 on the Summary sheet, then press Enter, all the sales orders with a name that starts with that letter sequence are filtered onto the Summary sheet.Note: The following examples use the Custom option where you write formulas to set your conditions.

Tags: , ,