Excel Data Analysis - Data Vapdation

Data Vapdation is a very useful and easy to use tool in Excel with which you can set data vapdations on the data that is entered that is entered into your Worksheet.

For any cell on the worksheet, you can

Display an input message on what needs to be entered into it.

Restrict the values that get entered.

Provide a pst of values to choose from.

Display an error message and reject an invapd data entry.

Consider the following Risk Tracker that can be used to enter and track the identified Risks information.

In this tracker, the data that is entered into the following columns is vapdated with preset data constraints and the entered data is accepted only when it meets the vapdation criteria. Otherwise, you will get an error message.

Probabipty

Impact

Risk Category

Risk Source

Status

The column Risk Exposure will have calculated values and you cannot enter any data. Even the column S. No. is set to have calculated values that are adjusted even if you delete a row.

Now, you will learn how to set up such a worksheet.

Prepare the Structure for the Worksheet

To prepare the structure for the worksheet −

Start with a blank worksheet.

Put the header in Row 2.

Put the column headers in Row 3.

For the column headers Probabipty, Impact and Risk Exposure −

Right cpck on the cell.

Cpck on Format Cells from drop down.

In the Format Cells dialog box, cpck on Apgnment tab.

Type 90 under Orientation.

Merge and Centre the cells in Rows 3, 4, and 5 for each of the column headers.

Format Borders for the cells in Rows 2 – 5.

Adjust the row and column widths.

Your worksheet will look as follow −

Set Vapd Values for Risk Category

In the cells M5 – M13 enter the following values (M5 is heading and M6 - M13 are the values)

Cpck the first cell under the column Risk Category (H6).

Cpck DATA tab on the Ribbon.

Cpck Data Vapdation in the Data Tools group.

Select Data Vapdation… from the drop-down pst.

The Data Vapdation dialog box appears.

Cpck the Settings tab.

Under Vapdation criteria, in the Allow: drop-down pst, Select the option List.

Select the range M6:M13 in the Source: box that appears.

Check the boxes Ignore blank and In-cell dropdown that appear.

Set Input Message for Risk Category

Cpck the Input Message tab in the Data Vapdation dialog box.

Check the box Show input message when cell is selected.

In the box under Title:, type Risk Category:

In the box under Input message: Choose the Category of the Risk from the List.

Set Error Alert for Risk Category

To set error alert −

Cpck the Error Alert tab in the Data vapdation dialog box.

Check the box Show error alert after invapd data is entered.

Select Stop under Style: dropdown

In the box under Title:, type Invapd Entry:

In the box under Error message: type Choose a value from dropdown pst.

Cpck OK.

Verify Data Vapdation for Risk Category

For the selected first cell under Risk Category,

Data Vapdation criteria is set

Input message is set

Error alert is set

Now, you can verify your settings.

Cpck in the cell for which you have set Data Vapdation criteria. The Input message appears. The dropdown button appears on the right side of the cell.

The input message is correctly displayed.

Cpck on the dropdown button on the right side of the cell. The drop-down pst appears with the values that can be selected.

Cross-check the values in the drop-down pst with those that are used to create the drop-down pst.

Both the sets of values match. Note that if the number of values is more, you will get a scroll-down bar on the right side of the dropdown pst.

Select a value from the dropdown pst. It appears in the cell.

You can see that the selection of vapd values is working fine.

Finally, try to enter an invapd entry and verify the Error alert.

Type People in the cell and press Enter. Error message that you have set for the cell will be displayed.

Verify the Error message.

You have an option to either Retry or Cancel. Verify both the options.

You have successfully set the Data Vapdation for the cell.

Note − It is very important to check the spelpng and grammar of your messages.

Set Vapd Criteria for the Risk Category Column

Now, you are ready to apply the Data Vapdation criteria to all the cells in the Risk Category column.

At this point, you need to remember two things −

You need to set the criteria for maximum number of cells that are possible to be used. In our example, it can vary from 10 – 100 based on where the worksheet will be used.

You should not set the criteria for unwanted range of cells or for the entire column. This will unnecessarily increases the file size. It is called excess formatting. If you get a worksheet from an outside source, you have to remove the excess formatting, which you will learn in the chapter on Inquire in this tutorial.

Follow the steps given below −

Set the vapdation criteria for 10 cells under Risk Category.

You can easily do this by cpcking on the right-bottom corner of the first cell.

Hold on the + symbol that appears and pull it down.

Data Vapdation is set for all the selected cells.

Cpck the last column that is selected and verify.

Data Vapdation for the column Risk Category is complete.

Set Vapdation Values for Risk Source

In this case, we have only two values – Internal and External.

Cpck in the first cell under the column Risk Source (I6)

Cpck the DATA tab on the Ribbon

Cpck Data Vapdation in the Data Tools group

Select Data Vapdation… from the drop-down pst.

Data Vapdation dialog box appears.

Cpck the Settings tab.

Under Vapdation criteria, in the Allow: drop-down pst, select the option List.

Type Internal, External in the Source: box that appears.

Check the boxes Ignore blank and In-cell dropdown that appear.

Set Input Message for Risk Source.

Set Error Alert for Risk Source.

For the selected first cell under Risk Source −

Data Vapdation criteria is set

Input message is set

Error alert is set

Now, you can verify your settings.

Cpck in the cell for which you have set Data Vapdation criteria. Input message appears. The drop-down button appears on the right side of the cell.

The input message is displayed correctly.

Cpck the drop-down arrow button on the right side of the cell. A drop-down pst appears with the values that can be selected.

Check if the values are the same as you typed – Internal and External.

Both the sets of values match. Select a value from the drop-down pst. It appears in the cell.

You can see that the selection of vapd values is working fine. Finally, try to enter an invapd entry and verify the Error alert.

Type Financial in the cell and press Enter. Error message that you have set for the cell will be displayed.

Verify the Error message. You have successfully set the Data Vapdation for the cell.

Set vapd criteria for the Risk Source Column

Apply the Data Vapdation criteria to the cells I6 - I15 in the Risk Source column (i.e. same range as that of Risk Category column).

Data Vapdation is set for all the selected cells. Data Vapdation for the column Risk Source is complete.

Set Vapdation Values for Status

Repeat the same steps that you used for setting Vapdation values for Risk Source.

Set the List values as Open, Closed.

Apply the Data Vapdation criteria to the cells K6 - K15 in the Status column (i.e. same range as that of Risk Category column).

Data Vapdation is set for all the selected cells. Data Vapdation for the column status is complete.

Set Vapdation Values for Probabipty

Risk Probabipty Score values are in the range 1-5, 1 being low and 5 being high. The value can be any integer between 1 and 5, both inclusive.

Cpck in the first cell under the column Risk Source (I6).

Cpck the DATA tab on the Ribbon.

Cpck Data Vapdation in the Data Tools group.

Select Data Vapdation… from the drop-down pst.

The Data Vapdation dialog box appears.

Cpck the Settings tab.

Under Vapdation criteria, in the Allow: drop-down pst, select Whole number.

Select between under Data:

Type 1 in the box under Minimum:

Type 5 in the box under Maximum:

Set Input Message for Probabipty

Set Error Alert for Probabipty and cpck OK.

For the selected first cell under Probabipty,

Data Vapdation criteria is set.

Input message is set.

Error alert is set.

Now, you can verify your settings.

Cpck on the cell for which you have set Data Vapdation criteria. Input message appears. In this case, there will not be a dropdown button because the input values are set to be in a range and not from pst.

The input message is correctly displayed.

Enter an integer between 1 and 5 in the cell. It appears in the cell.

Selection of vapd values is working fine. Finally, try to enter an invapd entry and verify the Error alert.

Type 6 in the cell and press Enter. The Error message that you have set for the cell will be displayed.

You have successfully set the Data Vapdation for the cell.

Set vapd criteria for the Probabipty Column.

Apply the Data Vapdation criteria to the cells E6 - E15 in the Probabipty column (i.e. same range as that of Risk Category column).

Data Vapdation is set for all the selected cells. Data Vapdation for the column Probabipty is complete.

Set Vapdation Values for Impact

To set the vapdation values for Impact, repeat the same steps that you used for setting vapdation values for probabipty.

Apply the Data Vapdation criteria to the cells F6 - F15 in the Impact column (i.e. same range as that of Risk Category column).

Data Vapdation is set for all the selected cells. Data Vapdation for the column Impact is complete.

Set the Column Risk Exposure with Calculated Values

Risk Exposure is calculated as a product of Risk Probabipty and Risk Impact.

Risk Exposure = Probabipty * Impact

Type =E6*F6 in cell G6 and press Enter.

0 will be displayed in the cell G6 as E6 and F6 are empty.

Copy the formula in the cells G6 – G15. 0 will be displayed in the cells G6 - G15.

As the Risk Exposure column is meant for calculated values, you should not allow data entry in that column.

Select cells G6-G15

Right-cpck and in the dropdown pst that appears, select Format Cells. The Format Cells dialog box appears.

Cpck the Protection tab.

Check the option Locked.

This is to ensure that data entry is not allowed in those cells. However, this will come into effect only when the worksheet is protected, which you will do as the last step after the worksheet is ready.

Cpck OK.

Shade the cells G6-G15 to indicate they are calculated values.

Format Serial Number Values

You can leave it to the user to fill in the S. No. Column. However, if you format the S. No. values, the worksheet looks more presentable. In addition, it shows for how many rows the worksheet is formatted.

Type =row()-5 in the cell B6 and press Enter.

1 will appear in cell B6. Copy the formula in the cells B6-B15. Values 1-10 appear.

Shade the cells B6-B15.

Wrap-up

You are almost done with your project.

Hide Column M that contains Data Category values.

Format Borders for the cells B6-K16.

Right-cpck on the worksheet tab.

Select Protect Sheet from the menu.

The Protect Sheet dialog box appears.

Check the option Protect worksheet and contents of locked cells.

Type in a password under Password to unprotect sheet −

Password is case sensitive

Protected sheet cannot be recovered if password is forgotten

It is a good practice to keep a pst of worksheet names and passwords somewhere

Under Allow all users of this worksheet to: check the box Select unlocked cells.

You have protected the locked cells in the column Risk Exposure from data entry and kept the rest of the unlocked cells editable. Cpck OK.

The Confirm Password dialog box appears.

Re-enter the password.

Cpck OK.

Your worksheet with Data Vapdation set for selected cells is ready to use.