21 January, 2008

Create a List of Values to Pick from in Excel

I was working through some what-if analysis in Excel, and I wanted a simple way to adjust some values within my calculations based on input from my colleagues. I did my typical spreadsheet creation, section for input values and sections for outputs, and a graph of the output.

Now I wanted my graph to update whenever I changed the input values. I could easily type in a new value, but that quickly got tedious. Using Data Validation, I was able to create drop-downs for the inputs that frequently might change.

Here's how to do it.
  1. Create your worksheet, with a section that lists your input values
  2. In a new column, list the values you want in the drop-down (it must be on the same worksheet)
  3. Put your cursor in the field where you want the drop-down and go to Data | Validation...
  4. On the Settings tab, select List from the list labeled Allow
  5. Also on the Settings tab, click the table icon next to the Source field and highlight your list (step 2)
  6. Press Enter to accept
  7. Click OK
  8. With the new drop-down arrow next to the filed, select the input from the list

You can have as many of these as you would like, just remember to have the list data on the same worksheet. You can also hide the column(s) that contain your lists, so users of your spreadsheet will not see them.

1 comment:

Dr. Iccapot said...

You can even use list data in other worksheets of your workbook, using named ranges.

Here is how:

-Highlight the cells, say S25:S42, in whatever worksheet you want.
-Click inside the “Name” box on the toolbar at the top of your screen.
When you click inside this box, you see the cell name (S42) is highlighted . While it’s highlighted, whatever you type will replace what’s in there, so just type a name, say MYLIST then hit Enter key.
(Some rules about range names: names can not have spaces in them, Names can not begin with a number, names can not use special characters)
-Now, back to your data validation settings: in the SOURCE box type:
=MYLIST

And that's all.

You can use the name zone in every Excel Function: =SUM(MYZONE) is the same of =SUM(S2:S42)
You can use the drop down list in the "Name" box to navigate in your workbook.

Hope this helps...