Managing and Analyzing Data in Excel 2010

These Excel tutorials offer easy-to-use database commands and methods for maintaining an Excel database.

In this course, Dennis Taylor shares easy-to-use database commands and methods for maintaining an Excel database. The course covers sorting, adding subtotals, auto-filtering, and using the Excel Advanced Filter feature and specialized database functions.

Introduction

• Welcome
• Using the exercise files

1. Sorting Data

• Sorting from the Sort menu
• Sorting from the toolbar
• Multi-key sorting
• Sorting based on the order of data in custom lists
• Sorting by color font, color background, or icon
• Sorting columns
• Sorting data in random order

2. Filtering Data

• Using single- and multiple-column text filtering
• Taking a look at special numeric filters
• Harnessing special date filters
• Creating a top-ten list by value or percent
• Creating custom filters
• Copying and sorting filtered lists
• Recognizing the limitations of standard filtering

3. Creating Automatic Subtotals in Sorted Lists

• Setting up subtotals
• Creating multiple levels and copying subtotals

4. Using Advanced Filters

• Using the Advanced Filter for complex OR criteria
• Using the Advanced Filter for complex multiple-field criteria
• Using the Advanced Filter to create unique lists from repeating field data

5. Eliminating Duplicate Data

• Using the Remove Duplicates command
• Using a specialized array formula to identify data that's been duplicated
• Using an array formula to count the number of unique items in a list

6. Using Database Analysis Tools

• Using SUMIF, COUNTIF, and related functions for quick data analysis
• Using database functions like DSUM, DAVERAGE, and DMAX

Conclusion

• Next steps