Excel Tables and Data Validation Practice Questions with Solutions

Introduction

Excel Tables and Data Validation help you organize data and control what users can enter into a worksheet. In this chapter, you will practice converting data into Excel Tables, using table features, adding calculated columns, working with structured references, creating drop-down lists, setting validation rules, and controlling incorrect entries. The questions cover different practical situations so you can test your Excel skills without repeating the same concept. Excel Tables and Data Validation practice questions with solutions to help you understand the concepts.

Question 1: Convert a Product List into an Excel Table

Problem Statement

Convert the following product data into an Excel Table so that it can be easily sorted, filtered and managed.

Excel Data

Solution

1. Select the complete data range.
2. Go to Insert → Table.
3. Make sure My table has headers is selected.
4. Click OK.
5. Choose a suitable Table Style if required.

Expected Output

The product data should become an Excel Table with filter buttons in the column headers.

The table should allow you to easily:

• Filter products
• Sort products
• Add new records
• Apply table formatting

Question 2: Add a New Record to an Excel Table

Problem Statement

An existing customer table contains five customers. Add a new customer record to the table.

Excel Data

Add:

Solution

1. Click the first empty row directly below the Excel Table.
2. Enter:
   • C106
   • Simran
   • Delhi
   • 36000
3. Press Enter.

Excel should automatically extend the table to include the new record.

Expected Output

The table should now contain 6 customer records, including Simran.

Question 3: Create a Calculated Column in an Excel Table

Problem Statement

Create an Excel Table for product sales and calculate the total sales amount using Quantity × Price.

Excel Data

Solution

1. Select the data and convert it into an Excel Table.
2. Click the first empty cell under Total Sales.
3. Enter:

=[@Quantity]*[@Price]

4. Press Enter.

Excel should automatically fill the formula throughout the calculated column.

Expected Output

The formula uses structured references instead of traditional cell references.

Question 4: Create a Drop-Down List for Department

Problem Statement

Create a Department column where users can select only one of these departments:

• IT
• HR
• Finance
• Marketing
• Sales

Excel Data

Solution

1. Select the Department cells, for example C2:C6.
2. Go to Data → Data Validation.
3. Under Allow, select List.
4. In the Source box, enter:

IT,HR,Finance,Marketing,Sales

5. Click OK.

Expected Output

Each Department cell should contain a drop-down list from which the user can select:

IT, HR, Finance, Marketing or Sales

Values outside the specified list should not be accepted under the default validation settings.

Question 5: Allow Only Whole Numbers for Product Quantity

Problem Statement

Create a Quantity column where users can enter only whole numbers from 1 to 100.

Excel Data

Solution

1. Select the Quantity cells.
2. Go to Data → Data Validation.
3. Set Allow to Whole Number.
4. Set Data to between.
5. Enter:
   • Minimum: 1
   • Maximum: 100
6. Click OK.

Expected Output

Valid entries include:

• 1
• 25
• 50
• 100

Values such as:

• 0
• 101
• 25.5

should not satisfy the validation rule.

Question 6: Allow Only Valid Dates

Problem Statement

Create a Joining Date field where employees can enter dates only between 1 January 2025 and 31 December 2025.

Excel Data

Solution

1. Select the Joining Date cells.
2. Go to Data → Data Validation.
3. Select Date under Allow.
4. Select between.
5. Enter the Start Date:

01/01/2025

6. Enter the End Date:

31/12/2025

7. Click OK.

Expected Output

Dates within 2025 should be accepted according to the validation rule.

Dates outside the specified range should be rejected.

Question 7: Create a Drop-Down List from a Cell Range

Problem Statement

Instead of typing department names directly into Data Validation, create the list in a separate area and use that range as the source of the drop-down.

Excel Data

Create this list somewhere in the worksheet, for example in H2:H6:

Employee table:

Solution

1. Enter the department names in H2:H6.
2. Select the Department cells.
3. Go to Data → Data Validation.
4. Select List.
5. In the Source box, select:

=$H$2:$H$6

6. Click OK.

Expected Output

The Department cells should display a drop-down containing:

• IT
• HR
• Finance
• Sales
• Marketing

Using a cell range makes it easier to update the list later.

Question 8: Prevent Duplicate Employee IDs

Problem Statement

Create an Employee ID field where each ID must be unique. Prevent users from entering an ID that already exists.

Excel Data

New employee IDs will be entered below this list.

Solution

1. Select the Employee ID input range, for example A2:A100.
2. Go to Data → Data Validation.
3. Select Custom under Allow.
4. Enter:

=COUNTIF($A$2:$A$100,A2)=1

5. Open the Error Alert section.
6. Set an appropriate error message such as:

Employee ID already exists. Please enter a unique ID.

7. Click OK.

Expected Output

If a user attempts to enter an Employee ID that already exists in the selected range, Excel should display the configured validation error.

Question 9: Create a Dependent Data Entry Setup

Problem Statement

Create two drop-down lists:

Category:

• Electronics
• Accessories

Product:
The product options should depend on the selected category.

Use these lists:

Solution

First create the category lists in the worksheet.

Create named ranges for the product lists:

• Select the Electronics products and name the range Electronics
• Select the Accessories products and name the range Accessories

Create the Category drop-down:

1. Select the Category cell.
2. Go to Data → Data Validation.
3. Select List.
4. Enter:

Electronics,Accessories

Create the Product drop-down:

1. Select the Product cell.
2. Open Data → Data Validation.
3. Select List.
4. Enter:

=INDIRECT(A2)

Assuming the Category selection is in A2.

Expected Output

If the user selects:

Electronics

the Product drop-down should show:

• Laptop
• Monitor
• Printer

If the user selects:

Accessories

the Product drop-down should show:

• Keyboard
• Mouse
• Webcam

Question 10: Create a Complete Order Entry Table

Problem Statement

Create an order entry table for a small business. Use Excel Table features and Data Validation together.

The table should contain:

• Order ID
• Customer
• Category
• Quantity
• Payment Status

Requirements:

• Category should use a drop-down.
• Quantity should accept only whole numbers from 1 to 100.
• Payment Status should use a drop-down containing:
  • Paid
  • Pending
  • Cancelled
• Convert the entire dataset into an Excel Table.

Excel Data

Solution

1. Enter the order data.
2. Select the complete range.
3. Convert it into an Excel Table using Insert → Table.
4. Select the Category column.
5. Apply Data Validation → List.
6. Use:

Electronics,Accessories

7. Select the Quantity column.
8. Apply Data Validation → Whole Number.
9. Set the allowed range from 1 to 100.
10. Select the Payment Status column.
11. Apply Data Validation → List.
12. Enter:

Paid,Pending,Cancelled

Expected Output

The final order table should:

• Work as an Excel Table.
• Provide a Category drop-down.
• Allow only valid quantities from 1 to 100.
• Provide a Payment Status drop-down.
• Automatically expand when new records are added.

This question combines Excel Tables, structured data entry and multiple Data Validation rules.

Key Takeaways

• An Excel Table converts a normal data range into a structured dataset.
• Excel Tables automatically provide filter buttons and structured formatting.
• Tables can automatically expand when new records are entered directly below them.
• Structured references make formulas easier to understand inside Tables.
• A calculated column can automatically apply a formula to the entire Table column.
• Data Validation controls the type of information users can enter.
• Drop-down lists help prevent inconsistent entries.
• Number validation can restrict values to a specific range.
• Date validation can restrict entries to a particular date range.
• Custom Data Validation can use formulas for more advanced rules.
• Data Validation and Excel Tables can be combined to create controlled data-entry systems.

FAQs

1. What is an Excel Table?

An Excel Table is a structured range of data that provides features such as automatic filtering, formatting, structured references and automatic expansion.

2. How do I create an Excel Table?

Select your data and choose Insert → Table. Make sure My table has headers is selected if your first row contains column headings.

3. What is Data Validation in Excel?

Data Validation is an Excel feature that controls what type of information can be entered into a cell.

4. How do I create a drop-down list in Excel?

Select the cells, go to Data → Data Validation, choose List, and provide the allowed values or a cell range containing the values.

5. Can I use a cell range as the source of a drop-down list?

Yes. Instead of manually entering values, you can select a range containing the allowed options.

6. Can Data Validation allow only numbers?

Yes. Data Validation can restrict cells to whole numbers or decimal values and can also define minimum and maximum limits.

7. Can Excel Data Validation restrict dates?

Yes. You can allow only dates within a specified range, such as dates between January 1 and December 31 of a particular year.

8. What is a calculated column in an Excel Table?

A calculated column contains a formula that Excel automatically applies to other rows in that Table column.

9. What are structured references in Excel?

Structured references refer to Excel Table columns by their names instead of traditional cell references. For example:

=[@Quantity]*[@Price]

10. Can Data Validation prevent duplicate values?

Yes. A Custom Data Validation formula can be used to check whether a value already exists and prevent duplicate entries.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar