How to print all options in a drop down list in Excel?
When working with drop-down lists in Excel, you might encounter situations where you need a quick way to print out every option available in a particular drop-down. This could be useful for documentation, review, training, or when planning data entry with colleagues who need a hard copy of all possible choices. Although Excel does not provide a direct built-in feature to automatically print all drop-down list options, there are several practical approaches you can use. This article introduces both a VBA-based solution and a manual method suitable for most users. You will also find troubleshooting tips and practical advice to ensure your workflow is as smooth as possible.
Print all options on a drop down list with VBA code
If your drop-down list is based on data validation or a form control, and you'd like to automatically print each option as a separate printout (for example, to see how your worksheet looks after selecting each possibility), using VBA is the most efficient method. This is especially useful when you have data or calculations that depend on the drop-down choice and you need a hard copy for each possible scenario. Please follow these steps carefully to avoid common pitfalls:
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window. If the key combination doesn't work, make sure your keyboard shortcuts are enabled and that you have installed the VBA component in your Office installation.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Copy and paste the VBA code below into the opened Module window. Please see screenshot:
VBA code: Print all options in a specified drop-down list into separate printout
Sub Iterate_Through_data_Validation()
Dim xRg As Range
Dim xCell As Range
Dim xRgVList As Range
Set xRg = Worksheets("Sheet1").Range("B8")
Set xRgVList = Evaluate(xRg.Validation.Formula1)
For Each xCell In xRgVList
xRg = xCell.Value
ActiveSheet.PrintOut
Next
End SubNote: In the code, Sheet1 is set as the worksheet name which contains the drop-down list you intend to print, and B8 is the specific cell with the drop-down. Make sure to replace these with your actual worksheet name and cell reference. Incorrect references may result in errors or unintended results.
3. With the code in place, press the F5 key or click the Run button to execute the macro. This will cycle through each value in the drop-down list, update the specified cell accordingly, and send a separate printout for each entry. The process may take some time if there are many options, and your printer settings (such as default printer and number of copies) will apply automatically.
If you encounter errors such as 'Subscript out of range' or the incorrect cell being updated, double-check your sheet name, drop-down cell reference, and ensure you have a compatible drop-down (Data Validation or Form Control).
This approach is particularly suitable when each selection impacts extensive calculations or formatting elsewhere in your workbook and you require a printed output showcasing each scenario. On the downside, if the drop-down source contains a very large list, the macro may require a significant amount of time and can trigger multiple print jobs in rapid succession. Always review your print settings before running the macro to avoid unnecessary paper usage.
Print all options by listing drop-down source data directly
In many cases, your drop-down list is based on a cell range or a manually entered list of items, which is already visible somewhere in your worksheet or easy to recreate. If you simply need a printed list of all available choices—rather than printing the worksheet in every possible state—you can use the following straightforward approach, which does not require any VBA or advanced features.
Applicable Scenarios: This solution is ideal if your drop-down list uses a range of cells as its source data or a clear comma-separated list in the Data Validation dialog. It's also a practical method for quickly providing team members or stakeholders with a reference list of options.
Advantages: No need for macros, less risk of generating multiple printouts, instantaneous if your source list is well managed.
Limitations: This method will not reproduce the effect of the entire worksheet with each drop-down option selected; it simply prints the list of possible values.
Steps:
✓ If your drop-down is based on a cell range (for example, options are in A1:A10):
- Go to the worksheet and locate the range that serves as the drop-down source. This range is set under Data Validation – typically, you can find it in Data > Data Validation > Source field.
- Select the entire source range (e.g., A1:A10) with your mouse.
- Press Ctrl+P or go to File > Print.
- Under the Print settings, make sure to choose "Print Selection" (if you only wish to print the list and not the whole sheet).
- Check the Print Preview to confirm your selection appears as desired, then print the list.
✓ If your drop-down uses a manually entered list (e.g., "Apple,Banana,Orange"):
- In your worksheet, choose an empty column and enter the items one-by-one, each into its own row (e.g., "Apple" in B1, "Banana" in B2, etc.).
- Alternatively, use the Data Validation dialog to view the list, copy from the Source field, and paste as values into a column.
- Once the entire list is in the column, follow the steps above to print the selection.
Additional Tip: If you have a very long comma-separated list and wish to split it into separate cells for easy printing, you can use the "Text to Columns" feature under Data > Text to Columns, using a comma as the delimiter.
This manual approach is most efficient for static, short-to-medium length lists. For lists that change often, or if you frequently need to print all worksheet versions with each option selected, consider automating with VBA as described earlier.
Troubleshooting & Additional Tips:
- If the drop-down list source is on another worksheet or hidden, you may need to unhide sheets or check formulas that achieve dynamic ranges.
- Always verify which printer will be used through Print Preview, especially before running any VBA code that triggers multiple print jobs.
- For Formula-driven drop-down lists (such as dynamic array sources), consider copying and pasting their current visible items into a new column for printing.
- Remember that printing extensive lists can use a lot of paper and ink. Check Print Preview to avoid waste and adjust settings as needed.
- If no direct printout is required and you simply want an export, you can also copy the list and paste it into another application (Word, Notepad, etc.) for alternative formatting or sharing.
Summary Suggestion: For users who need to repeatedly print drop-down options, organizing a dedicated source list in a clearly labeled area of your workbook can save time. If you often require worksheet printouts for each scenario, keep your VBA macro up-to-date and check all references before running. Regularly review your drop-down sources, especially after workbook changes or updates, to ensure ongoing accuracy and convenience.
Easily print specified pages in current worksheet in Excel:
With the Print Specific Pages utility of Kutools for Excel, you can easily print specified pages in current worksheet such as print all odd pages, all even pages, print just current page, as well as print custom page range as below screenshot shown.
Download and try it now! (30-day free trail)
Related Articles:
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in