Home » Uncategories » How to Make a Drop Down List in Powerpoint 2013 TUTORIAL

How to Make a Drop Down List in Powerpoint 2013 TUTORIAL

How to Make a Drop Down List in Powerpoint 2013

The tutorial demonstrates 4 quick ways to create an Excel data validation list (drop-down listing) - based on a listing of values, range of cells, named range and a dynamic dropdown. It also shows how to create a dropdown from another workbook, edit and delete data validation lists.

Excel driblet-down list, aka drop down box or philharmonic box, is used to enter data in a spreadsheet from a pre-defined items list. The chief purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes information input faster.

How to create a drop-down list in Excel
Brand a drop-downwards list from another workbook
How to set Excel data validation non working
Additional options for the Excel drop-downwards box
- Display a message when a cell with the dropdown is clicked
- Allow inbound other information in a philharmonic box
How to edit an Excel drop downward listing
How to delete a drib-down box

How to create an Excel drib-down list

On the whole, there are iv means to make a drib down menu in Excel, and all of them have their own strong and weak points. Beneath you volition find a quick outline of the chief advantages and drawbacks as well every bit the detailed step-past-step instructions for each method:

Drop-down box based on a listing of values
Drop downwards box based on a named range
Excel data validation list from table
Drop down list based on a range of cells
Create a dynamic dropdown in Excel

This is the fastest 3-step mode to create a drib-downwards box in all versions of Excel 2016, 2013, 2010, 2007 and 2003.

1. Select a cell or range for your drop-downward listing.

You lot showtime by selecting a prison cell or cells where you desire a drop-downwards box to announced. This can be a single jail cell, a range of cells or the entire column. If you select the whole column, a drop down menu will be created in each cell of that column, which is a real fourth dimension-saver, for example, when you are creating a questionnaire.
Select a cell or range for your drop-down list.

Yous can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.

2. Utilize Excel Data Validation to create a drop-down list.

On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.
Use Excel Data Validation to create a drop-down list.

iii. Enter the list items and choose the options.

In the Data Validation window, on the Settings tab, do the following:

In the Let box, select List.
In the Source box, blazon the items you desire to appear in your drop-down menu separated by a comma (with or without spaces).
Make certain the In-cell dropdown box is checked; otherwise the drop-down arrow won't appear next to the prison cell.
Select or clear the Ignore blank depending on how you want to handle empty cells.
Click OK and you are done!

Enter the list items and choose the options for your Excel dropdown.

At present, Excel users simply click an arrow next to a cell containing a dropdown box, and and then select the entry they want from the drop downwardly carte.
Excel users click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu.

Well, your driblet-down box is prepare in under a minute. This method works well for small Excel data validation lists that are unlikely to always change. If it'south not the example, consider using one of the following options.

This method of creating an Excel data validation list takes a bit more time, but it may salve even more fourth dimension in the long run.

i. Type the entries for your driblet-down list.

Select the entries yous want to appear in your drib-down menu in an existing worksheet or type the entries in a new sheet. These values should be entered in a single cavalcade or row without any blank cells.

For example, let'south create a drop-downwards list of ingredients for your favorite recipes:
Type the entries for your Excel drop-down list.

Tip. It's a good idea to sort your entries in the order you desire them to announced in the drop-downward menu.

two. Create a named range.

You can really skip this pace and create your drop-down listing based on a range of cells, but named ranges really brand managing Excel driblet-downward lists easier.

Select all the entries you desire to include in the driblet down list, correct-click them, and choose Define Name from the context menu. Alternatively, you can click Name Manager on the Formulas tab or press Ctrl + F3.
In the Proper name Manager dialog, click New.
In the Name field, type a name for your entries, make sure the right range is displayed in the Refers to box, and then click OK. Be sure your range proper noun doesn't have any spaces or hyphens, use underscores (_) instead.

Give a name to the range and make sure it includes the cells you want.

Tip. A faster mode to create a named range in Excel is to select the cells and type the range name straight in the Proper noun Box. When finished, click Enter to save the newly created named range. For more information, please come across how to define a proper name in Excel.
The fastest way to create a named range in Excel.

3. Employ Data Validation.

Click in the cell where you desire the driblet-down list to appear - information technology can exist a range of cells or the unabridged column, in the aforementioned sheet where your listing of entries is located or in a dissimilar worksheet. Then, navigate to the Data tab, click Data Validation and configure the rule:

In the Allow box, select List.
In the Source box, type the proper name you lot gave to your range preceded by an equal sign, for example =Ingredients.
Make sure the In-cell dropdown box is checked.
Click OK.

Configure your Excel drop-down list.

Note. If you are creating a drop-down based on a named range, and that named range has at least one blank prison cell, selecting the Ignore bare box allows whatsoever value to be entered in the validated jail cell.

If the source list contains more than viii items, your drop-down box will accept a scroll bar like this:
An Excel drop-down list with a scroll bar

Instead of using a regular named range, you tin can convert your data to a fully functional Excel table (Insert > Table or Ctrl + T), and then create a information validation listing from that table. Here's how:

Create a named range for a column of data, non including the header jail cell in the table. To refer to the data cells, you tin use ane of the following methods:
Create a data validation list based on a named range similar explained in the previous example.

Why may you want to utilize a table? First and foremost, because it lets you create a dynamic drop-down listing that volition update automatically as you add or remove items from the table.

To create a drop-downwards box based on a range of cells, bear out these steps:

Type the items in split up cells.
Select the cell where you want the drop-down list to appear.
On the Information tab, click Data Validation.
Place the cursor in the Source box or click the Collapse Dialog icon, and select the range of cells to include in your drop-downward list. The range may exist in the same or in a different worksheet. If the latter, you simply go to the other sheet and select a range using a mouse.

If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will become updated automatically in all the cells that contain it, once you remove or add new entries to the source list.

The easiest way to create such a dynamically updated drop-down listing in Excel is past creating a named listing based on a table. If for some reason yous prefer a usual named range, and so reference it using the OFFSET formula, every bit explained below.

You commencement by creating a usual dropdown based on a named range as described higher up.
In step 2, when creating a name, you put the following formula in the Refers to box.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Where:
- Sheet1 - the sheet's proper noun
- A - the column where the items of your drib-down list are located
- $A$one - the prison cell containing the first particular of the list

As you lot meet, the formula is comprised of 2 Excel functions - Showtime and COUNTA. The COUNTA role counts all non-blanks in the specified cavalcade. Beginning takes that number and returns a reference to a range that includes only non-empty cells, starting from the starting time cell you specify in the formula.
Creating a dynamic drop-down list in Excel using the OFFSET formula

The master advantage of dynamic drop-downwards lists is that you won't accept to change the reference to the named range every fourth dimension afterwards editing the source list. Y'all simply delete or type new entries in the source listing and all of the cells containing this Excel validation list will get updated automatically!

How this formula works

In Microsoft Excel, the Showtime(reference, rows, cols, [height], [width]) function is used to return a reference to a range consisting of a specified number of rows and columns. To forcefulness it to return a dynamic, i.e. continuously changing range, we specify the post-obit arguments:

reference - jail cell $A$1 in Sheet1, which is the first item of your drop-down list;
rows & cols are 0 considering you don't desire to shift the returned range either vertically or horizontally;
height - the number of non-empty cells in cavalcade A, returned by the COUNTA office;
width - 1, i.east. one cavalcade.

Y'all can make a drop-down menu in Excel using a list from another workbook as the source. To practise this, you will accept to create two named ranges - i in the source book and another in the book where you wish to use your Excel Data Validation list.

Note. For the drop-down list from some other workbook to piece of work, the workbook with the source list must be open up.

A static dropdown list from another workbook

The dropdown list created in this way won't update automatically when you add or remove entries in the source list and you will have to modify the source listing reference manually.

ane. Create a named range for the source listing.

Open the workbook that contains the source list, SourceBook.xlsx in this example, and create a named range for the entries y'all want to include in your drib-down list, east.g. Source_list.
Create a named list in the source workbook.

two. Create a named reference in the main workbook.

Open the workbook in which you want the driblet down list to appear and create a proper name that references your source list. In this example, the completed reference is =SourceBook.xlsx!Source_list
Create a name in the main workbook that references the source list.

Note. You have to enclose the workbook's proper name in apostrophes (') if information technology contains any spaces. For example: ='Source Volume.xlsx'!Source_list

iii. Apply Data Validation

In the main workbook, select the jail cell(due south) for your drop-downward list, click Information > Data Validation and enter the name you created in pace 2 in the Source box.
Apply Data Validation.

A dynamic dropdown list from some other workbook

A dropdown listing created in this way will get updated on the fly once you've fabricated any changes to the source list.

Create a range name in the Source workbook with the Commencement formula, equally explained in Creating a dynamic drib-down.
In the main workbook, apply Data Validation in the usual way.

Excel Information Validation does not work

The Data Validation option is greyed out or disabled? There are a few reasons why that might happen:

Driblet-down lists can't be added to protected or shared worksheets. Remove the protection or finish sharing the worksheet, and then effort to click Data Validation once more.
You are creating a drop down list from an Excel table that is linked to a SharePoint site. Unlink the table or remove the table formatting, and try again.

Additional options for the Excel drop-downwards box

In most cases, the Settings tab's options we've discussed to a higher place absolutely suffice. If they don't, two more than options are available on the other tabs of the Information Validation dialog window.

Display a message when a cell with the dropdown is clicked

If you want to show your users a pop upwardly bulletin when they click any cell containing your drib-down list, proceed in this fashion:

In the Information Validation dialog (Data tab > Information Validation), switch to the Input Message tab.
Make sure the option Prove input message when cell is selected is checked.
Type a championship and bulletin in the corresponding fields (up to 225 characters).
Click the OK button to save the message and close the dialog.

Configuring a message to be displayed when a cell with the drop-down list is clicked.

The issue in Excel will look similar to this:
A message is displayed when a cell with the drop-down list is clicked.

Allow users to enter their own data in a philharmonic box

By default, the drop-down list you create in Excel is non-editable, i.east. restricted to the values in the list. Still, you can allow your users to enter their ain values.

Technically, this turns a drop-down list into an Excel combo box. The term "combo box" means an editable dropdown that allows users to either select a value from the list or type a value directly in the box.

In the Data Validation dialog (Data tab > Information Validation), go to the Error Alert tab.
Select the "Show error warning after invalid data is entered" box if yous want to show an alarm when a user attempts to enter some data that is not in the drop-down menu. If y'all don't want to show any message, clear this check box.
To brandish a warning message, pick one of the options from the Fashion box, and type the title and message. Either Information or Warning will allow the users enter their own text in the combo box.

And this is how your customized alarm message may expect like in Excel:

Tip. If yous are not certain what title or message text to type, you tin can exit the fields empty. In this case, Microsoft Excel will display the default alarm "The value you entered is not valid. A user has restricted values that tin can exist entered into this cell."

Later on yous've created a drop-downwardly listing in Excel, you might desire to add together more entries to information technology or delete some of the existing items. How you exercise this depends on how your drop down box was created.

Editing a comma separated drib-down list
Editing a drop-down menu based on a range of cells
Editing an Excel drop-down list based on a named range

If you've created a comma separated drop down box, proceed with the following steps:

Select a prison cell or cells that reference your Excel Data Validation list, i.e. cells containing a drop-down box that you want to edit.
Click Data Validation (Excel ribbon > Data tab).
Delete or type new items in the Source box.
Click OK to save the changes and close the Excel Data Validation window.

Tip. If you want to utilise the changes to all the cells containing this drop-down list, select the "Use these changes to all other cells with the aforementioned settings" option.

If you lot have created a drop-downwardly box by specifying a range of cells rather than referencing a named range, then proceed in the following mode.

Head over to spreadsheet containing the items that announced in your drop-down box, and edit the list in the way you lot want.
Select the cell or cells containing your driblet-downward list.
Click Data Validation on the Information tab.
In the Excel Data Validation window, on the Settings tab, modify the cell references in the Source box. Y'all tin can either edit them manually or click the Collapse Dialog icon.
Click the OK button to save the changes and close the window.

If y'all have created a named range based drop-down box, then you tin can just edit your range'south items and then modify the reference to the Named Range. All drop-downwards boxes based on this named range will become updated automatically.

Add or delete items in the named range.
Open the worksheet containing your named range, delete or type new entries. Call back to accommodate the items in the order you want them to appear in your Excel driblet-down list.
Change the reference to the Named Range.
- On the Excel ribbon, get to the Formulas tab > Name Manager. Alternatively, press Ctrl + F3 to open the Name Managing director window.
- In the Proper name Manager window, select the named range you want to update.
- Change the reference in the Refers to box past clicking the Plummet Dialog icon and selecting all the entries for your drop-down list.
- Click the Close button, and and so in the confirmation bulletin that appears, click Aye to save your changes.
Tip. To avert the necessity to update the named range's references later on each modify of the source list, you tin create a dynamic Excel driblet-down bill of fare. In this case, your dropdown list will go updated automatically in all associated cells as soon as you lot remove or add new entries to the listing.

If you no longer want to have drop-downwardly boxes in your Excel worksheet, y'all can remove them from some or all cells.

Removing a drop-down menu from selected cell(s)
Deleting a drop-downwardly list from all cells in the current sheet

Select a jail cell or several cell from which yous want to remove drop down boxes.
Go to the Data tab and click Data Validation.
On the Settings tab, select the Clear All push.

This method removes the driblet-down menus from the selected cells, but keeps the currently selected values.

If you lot want to delete both a dropdown and the cells' values, you lot tin can select the cells and click the Clear all button on the Abode tab > Editing group > Clear.

In this way, you can remove a driblet-down list from all associated cells in the electric current worksheet. This won't delete the same drop-down box from cells in other worksheets, if any.

Select any cell containing your drib-downwardly listing.
Click Data Validation on the Information tab.
In the Data Validation window, on the Settings tab, select the "Apply these changes to all other cells with the same settings" check box.
Once you lot check it, all of the cells referencing this Excel Information Validation listing will go selected, as you tin can meet in the screenshot below.
Click the Clear All button to delete the drop-downward list.
Click OK to save the changes and close the Data Validation window.

This method deletes a drib-down list from all the cells containing it, retaining the currently selected values. If you created a dropdown based on a range of cells or based on a named range, the source list volition also remain intact. To remove it, open the worksheet containing the driblet-downwards list's items, and delete them.

At present you know the basics of Excel driblet-down lists. In the side by side article, we will explorer this topic further and I will show yous how to create dependent drop down lists with conditional Data Validation and how to create a drop-downwardly box from another workbook. Delight stay tuned and thanks for reading!

How to Make a Drop Down List in Powerpoint 2013 TUTORIAL

How to Make a Drop Down List in Powerpoint 2013