How to create a drop-down list in Excel

How to create a drop-down list in Excel

Trending 6 months ago 65

Implementing a drop-down database successful Excel is simply a speedy and businesslike mode to take predefined data. In the process, you’re capable to prevention clip from manually entering specified information into a fixed spreadsheet. Drop-down lists are cleanable for respective purposes, specified arsenic inputting accusation into a form.

Here's however to make a drop-down database successful Microsoft Excel.

Create a drop-down database by utilizing a scope of cells

The astir communal mode to make a drop-down database successful Excel with aggregate selections is by utilizing a range, which relies connected utilizing information from different cells.

Step 1: Choose a file wherever you privation to see the information that volition beryllium shown successful the associated drop-down list. This tin beryllium from the aforesaid spreadsheet wherever the drop-down database volition beryllium located, but you tin besides see them successful a abstracted spreadsheet (add a caller spreadsheet astatine the bottom). The second enactment volition people marque your superior spreadsheet look much tidy, professional, and little cluttered.

Step 2: Type successful each the information entries into the column, with each introduction having its ain cell.

Inputting entries for a drop-down database  connected  Microsoft Excel.

Step 3: Click the compartment wherever you privation the drop-down database to appear.

Selecting the compartment  for a drop-down database  connected  Microsoft Excel.

Step 4: Click the Data tab astatine the apical and past prime the Data Validation button.

The Data Validation fastener  connected  Microsoft Excel.

Step 5: Within the Allow menu, prime List. An arrow volition beryllium displayed connected the borderline of the Source field. Click that arrow.

The List enactment    wrong   the Allow paper   connected  Microsoft Excel.

Step 6: Once the aforementioned arrow has been selected, you’ll beryllium taken backmost to your spreadsheet’s main view. From here, simply caput to the file wherever you entered the data. Now, prime the scope of cells successful question by dragging the cursor from the archetypal compartment down to wherever your past compartment is located.

Selecting a scope  of cells for a drop-down database  connected  Microsoft Excel.

Step 7: In the Data Validation mini pop-up window, click the arrow button. ​​The Source barroom volition see the full compartment scope you prime successful the supra steps. Click OK.

You volition present person a drop-down database paper located successful the compartment you chose from measurement 3.

The ​​Source barroom  that includes the scope  of cells required for a drop-down list.

Create a drop-down database by manually inputting information into Source field

There are a fewer ways to make a drop-down database connected Excel. This circumstantial method is the astir straightforward and is peculiarly effectual for a Microsoft Excel spreadsheet beginner and for lists that won’t necessitate changeless updates.

Step 1: Select the compartment successful the file wherever you privation to input a drop-down list.

Step 2: Click the Data fastener from the apical enactment of menus and past take the Data Validation button.

Step 3: Click the Allow paper from the consequent model that pops up. Now prime List.

Step 4: Within the Source field, participate precisely what you privation to beryllium included successful the drop-down list. Be definite to use a comma aft each word(s) oregon number.

Manually entering information  into the ​​Source tract  for a drop-down database  connected  Microsoft Excel.

Step 5: Click OK.

The compartment you initially selected from Step 2 volition present person a functioning drop-down database consisting of each the accusation you inputted successful Step 5.

Displaying a connection erstwhile the drop-down database compartment is selected

Once you’ve created your drop-down list, you tin marque it much accessible by adding an Input Message.

Step 1: Select the compartment wherever the drop-down database is located. Then click the Data Validation button.

Step 2: Select the Input Message option. Enter a applicable rubric and the substance you privation to beryllium displayed erstwhile clicking the drop-down database itself successful the Input message box. The substance added present is constricted to 225 characters.

Displaying a connection   for a drop-down database  connected  Microsoft Excel.

Displaying an mistake alert

Similar to however you tin insert a connection that describes the intent of the drop-down list, you tin besides show an mistake alert, which tin look erstwhile substance oregon information that’s not recovered wrong the database is entered.

Step 1: Select the compartment wherever you created your drop-down. Click the Error Alert tab wrong the Data Validation model and past tick the Show mistake alert aft invalid information is entered box.

Enter a customized rubric and a message. Should the rubric oregon substance fields beryllium near empty, the rubric volition automatically beryllium acceptable to Microsoft Excel, portion the connection volition read: “The worth you entered is not valid. A idiosyncratic has restricted values that tin beryllium entered into this cell.”

Entering the applicable  information  for displaying an mistake  alert for a drop-down database  connected  Microsoft Excel.

Step 2: Choose a benignant from the ones provided specified arsenic the Stop (X) sign. Click OK.

Alternatively, if you privation a connection to popular up that doesn’t forestall individuals from entering invalid information not included wrong the drop-down list, prime either Information oregon Warning from the Style menu. Both options athletics their ain design.

Displaying an mistake  alert for a drop-down database  connected  Microsoft Excel.

Protecting your drop-down list

To forestall idiosyncratic from viewing oregon tampering with the root of the drop-down list’s data, you tin fastener those cells.

Step 1: Go to the file wherever your information for the drop-down database is entered. Now prime the cells you privation to fastener successful bid to forestall changes from unauthorized sources.

After you’ve selected the country you privation to lock, click the Home tab. Within the Alignment section, click the tiny arrow astatine the bottommost right. This volition unfastened the Format Cells window. Head to the Protection tab, click the Locked check box, and past click OK.

Locking the cells wherever  the information  entries are located for a drop-down database  connected  Microsoft Excel.

Step 2: Click the Review tab. Select Protect Sheet oregon Protect Workbook. Make immoderate adjustments arsenic per your requirements and click OK.

The Protect Worksheet enactment    for locking cells connected  Microsoft Excel.

Editors' Recommendations