Excel - Manage drop-down lists Tutorial

In this video, you will learn how to manage drop-down lists in Excel.
The video covers various techniques such as hiding and unhiding columns or rows, password protecting worksheets, locking specific cells, editing and deleting dropdown list options, and managing named ranges.
This knowledge will help you effectively organize and control data in your Excel spreadsheets.

6:12
4433 views

Available in Microsoft365

Objectifs :

This document aims to provide a comprehensive guide on how to manage dropdown lists and protect data in Excel worksheets. It covers techniques for hiding and locking data, as well as editing dropdown list options effectively.

Chapitres :

Introduction to Data Protection in Excel
In Excel, protecting your data is crucial to prevent accidental changes. This guide will walk you through various methods to hide and lock data, ensuring that only authorized users can make modifications.
Hiding Data in Excel
To prevent accidental changes to dropdown list data, you can hide columns, rows, or even entire worksheets. Here’s how to do it: - **Hiding Columns or Rows:** 1. Select the columns or rows you want to hide. 2. Right-click on the selected area and choose 'Hide'. - **Unhiding Columns or Rows:** 1. Select the column or row adjacent to the hidden area. 2. Drag your mouse to the other side of the hidden columns or rows to reveal them. - **Hiding a Worksheet:** 1. Right-click on the worksheet tab. 2. Click 'Hide'. - **Unhiding a Worksheet:** 1. Right-click on any worksheet tab. 2. Click 'Unhide', select the worksheet, and click 'OK'.
Password Protecting Your Worksheet
To enhance data security, you can lock and password-protect your worksheet. Here’s how: - **Locking Cells:** - By default, all cells are locked, but this has no effect until the worksheet is protected. - To protect the worksheet: 1. Right-click the worksheet tab and select 'Protect Sheet'. 2. Enter a password and click 'OK'. 3. Re-enter the password and click 'OK' again. - Once protected, attempting to type in any cell will result in an error. - **Unprotecting a Worksheet:** 1. Right-click the worksheet tab and select 'Unprotect Sheet'. 2. Enter the password and click 'OK'.
Locking Specific Cells
To lock specific cells while allowing others to remain editable: 1. Select the entire worksheet, right-click, and choose 'Format Cells'. 2. In the 'Protection' tab, uncheck 'Locked' and click 'OK'. 3. Select the specific cells you want to lock, right-click, and choose 'Format Cells'. 4. Check 'Locked' and click 'OK'. 5. Finally, password protect the worksheet. Now, only the selected cells are locked.
Managing Dropdown Lists
Editing dropdown lists in Excel is straightforward. Here’s how to manage them: - **Editing Dropdown List Options:** - For a dropdown list using a comma-delimited source: 1. Select the cells with the dropdown list. 2. In the data validation source field, make the desired changes. - For a dropdown list based on a cell range: 1. Click a cell in the range and type the changes. 2. To insert a new option, right-click a cell in the range and type the new option. 3. Deleting a cell works similarly. - For a named range, click a cell in the range, type the change, or right-click to insert a new option.
Removing Dropdown Lists
To delete a dropdown list from cells: 1. Select the cells containing the dropdown list. 2. Click 'Data Validation'. 3. Click 'Clear All' and then 'OK'. 4. The dropdown list will be removed, but the selected values will remain. To delete those values, press 'Delete'.
Conclusion
This guide provides a solid understanding of how to apply and manage dropdown lists in Excel, as well as how to protect your data effectively. Mastering these techniques will enhance your ability to work with Excel and safeguard your information.

FAQ :

How do I hide columns or rows in Excel?

To hide columns or rows in Excel, select the columns or rows you want to hide, right-click on them, and choose 'Hide' from the context menu. To unhide, select the adjacent column or row, drag your mouse across the hidden area, and right-click to choose 'Unhide'.

What is the purpose of password protecting a worksheet?

Password protecting a worksheet prevents unauthorized users from making changes to the data. Only individuals with the password can edit the protected cells or the entire worksheet.

How can I create a dropdown list in Excel?

To create a dropdown list in Excel, select the cell where you want the dropdown, go to the 'Data' tab, click on 'Data Validation', and choose 'List' from the options. Then, specify the source of the list, which can be a range of cells or a comma delimited list.

How do I edit a dropdown list in Excel?

To edit a dropdown list, you can change the data in the source range or modify the comma delimited list in the data validation settings. If the dropdown is based on a named range, you can edit the named range in the Name Manager.

What happens if I try to edit a locked cell?

If you try to edit a locked cell in a password-protected worksheet, you will receive an error message indicating that the cell is protected and cannot be modified.

Quelques cas d'usages :

Data Entry in Forms

Using dropdown lists in forms can streamline data entry processes in various industries, such as healthcare or finance. By limiting user input to predefined options, organizations can reduce errors and ensure data consistency.

Budget Tracking

In financial management, dropdown lists can be used to categorize expenses. This allows users to select from a list of predefined categories, making it easier to track and analyze spending patterns.

Project Management

In project management tools, dropdown lists can help assign tasks to team members. By using a dropdown to select team members, project managers can ensure that tasks are assigned consistently and efficiently.

Inventory Management

In inventory management systems, dropdown lists can be used to select product categories or suppliers. This helps maintain organized records and simplifies the process of updating inventory data.

Survey Data Collection

When collecting survey data, dropdown lists can be used to standardize responses. This ensures that all participants select from the same options, making data analysis more straightforward and reliable.

Glossaire :

Dropdown List

A dropdown list is a user interface element that allows users to select an option from a predefined list. In Excel, it is often used to ensure data consistency and to limit user input to specific values.

Data Validation

Data validation is a feature in Excel that restricts the type of data or the values that users can enter into a cell. It is commonly used to create dropdown lists.

Password Protection

Password protection in Excel is a security feature that restricts access to certain features or data within a worksheet. Only users with the correct password can make changes.

Locked Cells

Locked cells are cells in an Excel worksheet that cannot be edited unless the worksheet is unprotected. By default, all cells are locked, but this only takes effect when the worksheet is password protected.

Named Range

A named range is a feature in Excel that allows users to assign a name to a specific range of cells. This makes it easier to reference the range in formulas and data validation.

Comma Delimited List

A comma delimited list is a type of data format where values are separated by commas. This format is often used in data validation to create dropdown lists.

00:00:05

you can hide the columns, rows,

00:00:07

or the entire worksheet that contains the data.

00:00:12

You can then unhide them, if you need to make changes.

00:00:16

You can also lock and password to protect the cells on

00:00:19

the worksheet or even the entire worksheet.

00:00:24

By password protecting the data, only people with

00:00:26

the password can make changes.

00:00:29

But it'll also require a little more effort on your part,

00:00:32

if you decide to make changes; because you have to unprotect the data first.

00:00:40

To hide columns or rows, select the columns or rows,

00:00:46

right click them, and click hide.

00:00:52

To unhide them, select the column or row on one side of

00:00:55

the hidden columns or rows, drag your mouse to the other side,

00:01:00

right click, and click unhide.

00:01:06

To hide a worksheet, right click the worksheet's tab,

00:01:12

and click hide.

00:01:15

To unhide a worksheet,

00:01:17

right click any worksheet tab, click unhide, and click OK.

00:01:26

By default all cells on a worksheet are locked;

00:01:29

but this has no effect until the worksheet is password protected.

00:01:34

To password protect the worksheet, right click its tab,

00:01:38

and click Protect sheet, enter the password,

00:01:44

and click OK; re-enter the password and click OK again.

00:01:51

Now if I try to type in any cell on the worksheet, I get an error.

00:01:57

To unprotect a worksheet, right click the worksheet's tab,

00:02:03

click Unprotect sheet, enter the password,

00:02:08

and click OK.

00:02:12

To lock specific cells, you first have to change to default for the worksheet.

00:02:18

Select the entire worksheet, right click it,

00:02:22

and click Format cells.

00:02:26

In Format cells, click the protection tab, uncheck Locked, and click OK.

00:02:36

Select the specific cells you want to lock,

00:02:40

right click them, and click Format cells.

00:02:45

In format cells, check Locked;

00:02:49

and click OK; then password protect the worksheet.

00:02:55

Only these cells on the worksheet are locked.

00:02:58

I can type in an unlocked cell,

00:03:05

but if I try to type in a locked cell, I get an error.

00:03:12

To change the options for a dropdown list, you edit

00:03:15

the data for the dropdown list, and possibly the data validation source field.

00:03:20

For a dropdown list that uses a comma delimited list in a source field,

00:03:25

select the cells with the dropdown list,

00:03:29

and in the data validation source field,

00:03:32

make the desired changes to the comma delimited list;

00:03:39

and click OK.

00:03:44

And we can see the updated list options.

00:03:49

For a dropdown list that's based on a cell range,

00:03:54

click a cell in the range, and type the changes you want to make.

00:04:00

To insert a dropdown list option, right click a cell in the range,

00:04:06

click Insert, click OK,

00:04:11

and type your new option in the cell.

00:04:14

Deleting a cell works similarly.

00:04:21

And again we can see the updated list options.

00:04:27

You can also select the cells in the dropdown list,

00:04:32

click Data validation, and set Source to the new cell range.

00:04:39

For a dropdown list that's based on a named range,

00:04:45

click a cell in the range, and type the change you want to make.

00:04:51

To insert a dropdown list option, right click a cell in the range,

00:04:57

click Insert, click OK,

00:05:00

and type your new option in the cell.

00:05:04

Deleting a cell works similarly.

00:05:11

And again we can see the updated list options.

00:05:18

To edit or delete a named range, click the formula's tab,

00:05:24

and click Name manager.

00:05:26

In Name manager, select the desired name, and click Edit or Delete

00:05:37

To delete a dropdown list from cells,

00:05:39

select the cells,

00:05:43

click data validation,

00:05:45

click clear all, and click OK.

00:05:53

The dropdown list is removed from the cells,

00:05:56

but not the values that have been selected; you can remove those by pressing Delete.

00:06:03

Now you've got a pretty good idea about how to apply and

00:06:06

use dropdown lists in Excel.

00:06:08

Of course there's always more to learn.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2495 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES