Advanced Excel Course

Excel Advanced

Module 3: Advanced List Management

This module from our Excel advanced training course will explore applying data validation settings to your worksheet to control data entry. You will also identify and use database functions to perform calculations using multiple database fields.

Creating Data Validation Rules

Data validation allows you to create restrictions on what can be entered into a cell. You can have Excel display a warning message or prevent invalid entry if a user of the worksheet does not enter the correct type of value.

Use the following procedure to create a data validation rule.

1. Select the cells where you want to apply the data validation rule.
2. Select the Data tab from the Ribbon.
3. Select Data Validation. Select Data Validation.
4. On the Settings tab of the Data Validation dialog box, set up the Validation Criteria. Use the drop down lists to help you build your criteria. In this example, we are requiring a three-digit number.
5. Select the Input Message tab.

1. Enter a Title and Message that the user will see when he or she selects the cell.
2. Select the Error Alert tab.
3. Select the Style of error from the drop down list. Enter a Title and Error message to display if the user enters invalid data.
4. Select OK.

If you select the Settings tab and choose List from the Allow drop menu, you will see a data box (labeled Source) where you can enter permissible source data for the cell.

You can type the values that are permissible for the cell directly, or specify a range that contains the values.

If you specify a cell range, make sure there is an equals sign = in front of it.

For example, cell E2 may be being set up so that items in cells A2 to A9 (surrounded by a dashed border) will be available as choices for data entry.

Now, a user can choose from a validation drop list that is available for cell E2.

Using a Formula to Validate Entries

If you select Custom from the Allow drop menu, you can enter a formula to validate your data. You can base the formula on data from another cell if you wish.

Here the custom type has been chosen from the Allow drop list and a formula containing an IF function has been entered. The formula reads: IF(Not(G4=100),TRUE,FALSE).

This means that if the value entered into cell G4 (the cell that is being validated) is not 100, the function will return TRUE. If it is 100 the function will return FALSE. If the validation formula returns true, the data is permitted. If FALSE, the data is invalid. Basically, this formula allows any number other than 100 to be entered.

There are many possibilities for setting data validation rules. For example, you can select a range of cells, click within the range, and click the Data Validation button on the Data Ribbon to set up validation rules for the entire range. You can also AutoFill a cell with validation rules into other cells in a row or column. These cells will all have the same validation properties.

Don't forget only newer versions of Excel such as 2016, 2013, 2010 and 2007 have the new ribbon at the top. Older versions such as 2003, 2002/XP, 2000 and 97 us the Menu system, such as File, Edit etc.

Using Database Functions

Database functions allow you to perform operations on an Excel database that involve multiple fields. In a sense, they offer some of the power of array formulas, but generally make worksheets faster because they do not require the same amount of recalculation.

With database functions you can get counts, averages, or sums of your database on selection criteria involving multiple fields. Implementing database functions is a little like implementing advanced filters: you have to establish a criteria range first before you use the function itself.

Some useful database functions are:

• DSUM Used to create total values in a field based on specified criteria
• DAVERAGE Used to average values in a field based on specified criteria
• DCOUNT Counts the cells that contain numbers and meet the specified criteria
• DMAX and DMIN Return the largest and smallest values respectively from records that meet the specified conditions.
• DPRODUCT Multiplies values in a field according to specific conditions
• DGET Returns a single record value from a record that meets the specified conditions.

All of the database functions use the same argument format:

Function Name (Database range, Field to be returned or calculated, Criteria range).

Take a the data sample table as an example.

We will use the DSUM database function to calculate the total profit for all Tuesdays and Thursdays with Sales greater than 2200 and Profit less than 1900.

Creating a Criteria Range

The first step in using a database function is to set up a criteria range. We do this by entering the field headings that we want to use exactly as they are in the original data list. For this example, we will need the fields Day, Sales, and Profit for our criteria range. If we enter these field headings in cells F1, G1, and H21 respectively, we can then move on to setting up our criteria.

This is the criteria range for the DSUM database function.

In the row under the field headings, we have T*, >2200, <1900. This means that records with a Day starting with T, Sales greater than 2200, and Profit less than 1900 will be selected for summation.
If you wanted to join the criteria with a logical Or operator, you would offset the criteria on separate rows (like an advanced filter).

Entering the Database Function

The next step is to choose a cell (H4) to place the function in. To enter the function, activate the cell and click the function (fx) button on the formula bar. In the Insert Function dialogue box, choose DSUM from the database category to reveal the Function Arguments box.

First, select your original list with your mouse, or enter the range directly into the Database data area. Press Tab or use your mouse to move to the Field data area and type Profit (this is the field that will be summed).

Then, put your cursor in the Criteria data area, and select the entire criteria range (F1:H2) with your mouse. To finish, click the OK button.

You can see the sum of the Profit fields for records that meet the criteria in cell H4.

You could have also activated cell H4, and entered the function directly into the formula bar:

=DSUM(A1:D25,'Profit',F1:H2)

The database range is A1:D25

The field that is being summed is Profit

The criteria range is F1:H2.

If you enter a database function directly into the formula bar, remember to keep your cell ranges accurate, and put the field that will be summed, averaged, or otherwise operated on, in quotation marks.

This is an extract from our Excel Advanced training course.