↑ Return to Microsoft Excel

Database Management in Excel

Excel has proven to be the software of choice for a broad range of database management tasks.

While Excel is not a true relational data management program like Access, it does a terrific job of handling a wide range of database tasks.

Excel has multiple features for organizing and managing data, so you can ensure data is entered correctly and that calculations and formulas are valid. Data organization features enable you to sort, filter, and group and outline data so that you can focus on the key parts of your data that are most important to you.

Excel also has validation features which ensure that data is correct, entered in the proper format, and that formulas are working correctly, all of which are incredibly important for maintaining accurate records.

This course explores organizing and subtotaling data in using the available features.

It also covers data validation, including error messages, invalid data prevention, formula auditing, error checking, and the Watch window.

 

 

What we will cover:

  • sort data
  • filter data in a range or table
  • group and outline data
  • insert subtotals in a worksheet
  • display different levels of data using outlines
  • outline data
  • filter and sort data
  • apply subtotals to data
  • organize worksheet data to facilitate data analysis
  • specify the data that is allowed in a particular range
  • recognize which error alert to use in given examples
  • locate and correct formula errors
  • use the Watch Window to audit formulas
  • locate and correct formula errors
  • add data validation to prevent the errors from recurring

 

What You Will Learn:

DATA VALIDATION

  • Restrict cell content by forcing users to pick entries from a pick list
  • Restrict cell content by value, text, or data/time
  • Restrict cell content by formula
  • Use auditing tools to check the validity of existing data

DATA FORM

  • Build and maintain a database with the Form feature

SORTING

  • Use multiple-key sorts from the menu or from button commands
  • Sort based on rearranging rows or rearranging columns
  • Sort based on cell color, font color, or icon; sort in random order
  • Sort text data based on customized lists—override alphabetical order
  • How to get rid of extra spaces that prevent matching and sorting

CREATING AUTOMATIC SUBTOTALS WITH THE SUBTOTALS COMMAND

  • Set up single and multiple-level subtotals
  • Expand and collapse displays quickly with grouping and outlining symbols

USING POWERFUL FILTERING TOOLS TO VIEW JUST THE DATA YOU WANT TO SEE

  • Set up filtering to see and print a subset of data
  • Create compound selection criteria for filtering data
  • Use Excel’s greatly expanded special filtering tools for Text, Number, and Date fields
  • Use the Advanced Filter to overcome limitations of standard filtering; use complex criteria

OTHER DATA TOOLS

  • Use the Text to Columns command to split data into multiple columns
  • Use the Remove Duplicates command to eliminate redundant records

DATABASE ANALYSIS TOOLS

  • Using SUMIF and COUNTIF and related functions for quick data analysis