I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.

Conditional Drop Down List (Excel)

There are two named ranges,

1)

myCategoryH

that refers to the range E1:E3 and

2)

myTableH

that refers to range F1:G3.

A defined name, myItemListH, is created with the following formula =INDEX(myTableH,MATCH(Sheet1!A2,myCategoryH,0),0) that will return a row that is matched by the contents of cell A2.

In cell A2, add a Data Validation list with the source being =myCategoryH. In cell B2, add a Data Validation list with the source being =myItemListH in the conditional drop down list from Excel.

Now you’re done.

Cell A2 will give you a drop-down list of Fruit, Vegetables, or Other Stuff. And cell B2 will read the value in cell A2, match and return the proper row number, and return an array of values for that row.

Two Ways to Use the INDEX Function to Return an Array

This is a simple case of using the INDEX function in a slightly different way. Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). The zero means: return all rows (of column 2).

If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). Here the zero means: return all columns (of row 2).

This comes from the Help section of the INDEX function where in Excel 2011 it reads:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press ⌘+RETURN.

Practical Considerations

Adding to each category list across columns is problematic. For one thing adding more data to the table will require inserting a column within the table range to avoid renaming the range. Obviously a standard Excel “Table” won’t work with this type of list. And if you have a very large spreadsheet the number of columns will become limiting long before the number of rows would.

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.

Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.

Update Your List Range with VBA

Put your data validation drop-down cell on one worksheet and the reference list range on another worksheet. Example: Sheet1 contains a cell with data validation. Sheet2 contains a data range (the list) that is given a defined name of myList. Add some VBA code in the Sheet2 Deactivate routine to update the named range.

Private Sub Worksheet_Deactivate()
Dim rng As Range
Set rng = Sheet2.Range("myList").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Name = "myList"
End Sub

This is an event-based programming technique, which I commonly use with Excel 2003.

Use Some Table INDEX Magic

This is a variation of the same thing, but no VBA programming is warranted. Instead, use a Table for your reference list data. Then create a defined name with the INDEX Function, and use that name for the data validation list.

Tables automatically update their ranges when expanded and the INDEX function will too. Example: Create the defined name myListFormula and use =INDEX(Table1,0,1) as the formula. Then when setting the data validation list, use =myListFormula as the list reference.

Example Worksheet

I’ve put together a workbook with the two examples listed above. The first technique, with VBA, uses two worksheets: Lookup 1 and Data 1. The Data 1 worksheet has the VBA code, which updates the named range when deactivated. You can add or subtract to this list and the data validation list on the Lookup 1 sheet will automatically be updated.

Both Lookup sheets have data validation in cell A2, which is a list of names. I’ve added another column for the city that uses a formula to get the right value from the list.

Lookup 1 Sheet

Data 2 Sheet with VBA Code

The second example uses Lookup 2 and Data 2 worksheets. The Table is on the Data 2 worksheet. When you add or subtract data from this Table and the defined name myListFormula will automatically update the data validation list on the Lookup 2 worksheet. Be sure to look at the Define Name dialog box (on the Mac) or the Name Manager (Windows) to see the INDEX formula.

Since there is no OFFSET function, updating at random intervals, in either of these examples, I can rest easy. That’s one less thing to slow down your worksheet.

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

Click Special… which will bring up the Go To Special dialog box.
Select Last cell and click OK.

The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

Select the Used Range by Navigating Back Home

Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

Selecting the Used Range

To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

Create a Macro to Select the Used Range

You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.

Sub ActiveSheetUsedRange()
ActiveSheet.UsedRange.Select
End Sub

Or you could get fancy with this code.

Sub SelectUsedRange()
Dim rng As Range
Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
Range(Cells(1, 1), rng).Select
End Sub

Add a Keyboard Shortcut for the Macro

To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

Note: This macro will not work if a Chart sheet is selected.

The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart.

Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations during these months isn’t a bad idea, so I’m going to look at pay periods for the next three years.

Add a Column of Dates

I’ll enter the first pay period, then create a formula that adds 14 days and copy it down to get my date range.

Since a PivotTable will “see” the underlying serial date, I’ll need to add another column for Month and give it a “Month-Year” format so the PivotTable will group similar Months together. For this I’ll use the TEXT formula. The “Pay Period” Date is used for the first value argument, and then “mmm-yy” for the format_text argument.

So the formula in cell B3 =TEXT(A3,”mmm-yy”)

As you can see in the screen shot, the months Dec-11 and Jun-12 have three pay periods. A PivotTable will quickly summarize more than one year and show the number of times a pay period happens each month.

Add a PivotTable

The steps to create a PivotTable in Excel 2011 are as such.

Select a cell inside the data range

Click the Data tab on the Ribbon

Click the PivotTable drop-down arrow and select Create Manual PivotTable…

On the PivotTable dialog box, click OK

You’ll get a new worksheet that shows an empty PivotTable Layout. There’s an introductory PivotTable popup box that has a link to Learn more about PivotTables, which brings up the Help system topic About PivotTables. Click the x to dismiss this help box.

The PivotTable Builder box is also shown. This object looks quite a bit different from the traditional Windows counterpart. My first reaction was that it looks funky. Nevertheless, it’s the functionality that counts.

Arrange the PivotTable Layout

Click and drag Month from the Field name area to the Row Labels area. Then click Month again and drag it to the Values area. (Yes that’s right, you’re dragging Month twice.)

In the Values area you should see Count of… and to see the rest, just click the i to bring up the field name list.

Sort the PivotTable

Click inside the Data area (like cell B5) of the PivotTable and then select Descending from the Sort icon drop-down list on the Toolbar.

The top of the list shows months with 3 pay periods. Just what I was looking for.

You’ll notice the descending sort doesn’t leave the Row Labels in ascending order. (Nov-12 doesn’t follow Jun-12, etc.)

A Better Formula

You can change the Month formula to =TEXT(A2,”yyyy-mm”) and the Row Labels will show up in year-month format in ascending order.

While this took some time to explain, the reality is when I do this it takes about two minutes. And the bulk of the time is generating the dates and adding the formula.

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column of data.

The situation is depicted below. Cell C2 is active, and has the formula =B2+A2. I want to copy it down to the rest of the column in this data range. However, cells B6 and B11 are empty, along with countless others below the visible table range. (Pretend this data table is huge.)

Here is some VBA code that will Fill Down the formula.

Sub FillDownFormula()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Filldown a formula for in column of data.
' Assumes a data table with headings in the first row,
' the formula in the second row and is the active cell.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rng As Range
Dim rngData As Range
Dim rngFormula As Range
Dim rowData As Long
Dim colData As Long

' Set the ranges
Set rng = ActiveCell
Set rngData = rng.CurrentRegion

' Set the row and column variables
rowData = rngData.CurrentRegion.Rows.Count
colData = rng.Column

' Set the formula range and fill down the formula
Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
rngFormula.FillDown
End Sub

The key is setting the formula range (rngFormula). I take the entire region of data (rngData) and offset by 1 row because I don’t want the header row, then I resize the rows in the range by subtracting 1 from the total number of rows because I now need one less row in the range.

Next I offset the entire range by the row number of the active cell, but have to subtract one column because I offset from column 1, not column 0. And finally I resize the data range to 1 column, which gives me the single-column range I want with the formula in the top row.

The routine does no error checking and is restricted to using the active cell that has a formula. But it does the trick given those limitations.

The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table

Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then converted to a Table.

Create a Dynamic Defined Name for Category List

Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above.

This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name.

To create a defined name in Windows choose Formulas > Name Manager then click New…. On a Mac choose Insert > Name > Define…. This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.

Create a Category Drop Down List with Data Validation

Type Category in cell A1 for the column heading. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =MyCategory in the Source box, and click OK.

Now cell A2 has a drop down button that shows the Header row of the Table. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)

Column B will hold a drop-down list for the Item, which is dependent upon the Category. This takes two defined names to work properly.

Create the First Defined Name for Items

[Update: Select cell B2 before you follow this next step.]
Create a defined name with the following formula:

This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1.

If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line. Not an elegant solution.

However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list.

Create a Second Defined Name for Items

We can create a second defined name that will give us a dynamic list with the exact count. The following OFFSET formula will do the trick.

=OFFSET(myItemList,0,0,COUNTA(myItemList),1)

This formula uses the first defined name (myItemList) but alters the height by counting the items. I named this defined name myItem, as you can see below in the Name Manager screen shot.

Create an Item Drop Down List with Data Validation

Type Item in cell B1. Select cell B2 and open the Data Validation dialog box. Choose List and enter the following formula =myItem, then click OK.

The result is a dynamic drop-down list in the Item column that’s dependent on the Category selection in column A, and returns the exact list.

Convert to a Table

Now it’s time to covert this to a Table, and by doing so the Data Validation will be preserved and automatically expand with the addition of more rows.

Select cell A2, then in Windows (Excel 2007, 2010) choose Insert > Table, verify the information in the Create Table dialog box, and click OK.

On a Mac (Excel 2011), select cell A2 then choose Tables > New > Insert Table with Headers.

At this point I normally turn off the Data Filter because it’s rather annoying.

As your Table expands, with more rows or columns, this dynamic drop-down list will work just fine. To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.

Potential Problem with OFFSET

As you might have guessed the OFFSET formula depends on having items at the top of the list. Should your data have blank rows in the middle of the column, the drop-down won’t have all the items listed yet show blank cells in the list.

In this case it’s best to use the first defined name (myItemList) in creating a dependent, drop-down list for Item. At least you’ll get all the data, even if it does have some blanks in the list.

Reference Table Location

For illustration purposes I’ve shown the reference Table on the same worksheet as the Category and Item Table. Normally I would place the reference Table on a different worksheet. In this instance none of the formulas would change.

[UPDATE]

I’ve had numerous questions about the details of this post so I’m putting a link here to download the file.

Tables have structured data so you can easily use column references in formulas. But with the Table name AND column name, the reference can be rather lengthy and hard to understand at first glance.

As an alternative, you can simply create a dynamic column reference in a Table by using the INDEX function in a Defined Name.

First I will explain how the INDEX function works to capture a dynamic Table range, then I’ll create a Defined Name using the INDEX function, use it in a formula, and see what happens when things change.

The Dynamic INDEX

The syntax is INDEX(array, row_num, [column_num]) where array refers to the Table name, the row_num reference is set to zero (0), and the column_num is a number of the Table column you want to reference.

Using zero (0) for the row_num argument forces all rows in the column to be returned.

Create a Defined Name Formula with INDEX

I’m going to use an example for the column Number Setups in Table1.

Table1

In Excel 2010 and 2007 (Windows) choose Formulas > Name Manager and click New. In the New Name dialog box type nSetup. In the Refers to: box type the formula =INDEX(Table1,0,4) then click OK. You can also omit the zero for an equivalent formula =INDEX(Table1,,4).

This next example will use the Table1 column Setup Hours. In Excel 2011 (Mac) choose Insert > Name > Define. In the Define Name dialog box type sHours into the Names in workbook box. In the Refers to: box type the formula =INDEX(Table,1,5) then click OK.

I have an existing formula for Average Hours per Setup using structured Table references that looks like this:

Using the Defined Names we create a formula like this:

=SUBTOTAL(109,sHours)/SUBTOTAL(109,nSetups)

Both formulas are equivalent, but the second is shorter and a bit more clear (in a techno-geek kinda way).

Changes to Be Aware Of

If you change the name of the Table, Excel will modify everything related to the name so there are no worries about the Define Name formula. I changed the Table name to i and here’s what the Defined Names look like.

If I move the Table to some other place in the worksheet, there’s no effect to the Defined Name formulas because the column references are for the Table and not the worksheet.

However, if you move the column location within the Table, the Defined Name will not change its reference to the new column position. The column reference has to be manually changed in the INDEX function within the Defined Name.

Conclusion

The moral of this story is that Defined Names using the INDEX function to return a dynamic range reference in a Table can be used to make formulas easier to read, but moving a column within a Table can invalidate the reference.

Excel Tables have a Total Row that can be toggled on and off from the Ribbon. Several functions are available when the Total Row is turned on.

In Excel 2010 and 2007 (Windows) select a cell inside the Table, then choose Table Tools and click the check box for Total Row. In Excel 2011 (Mac) select a cell in the Table and choose Tables, then select the check box for Total Row.

A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.

Average, Count, Count Numbers, Max, Min, Sum, StdDev, and Var are options on the drop-down list for each cell in the Total Row. You can even choose More Functions…, which we won’t go into here.

When you choose a function from the list, what you really get is the SUBTOTAL function, where the first argument is the function, and the second argument is the Table column data. In the picture below, 101 represents the Average function, and [Net Product] is the data. Excel inserts this formula automatically when you select Average from the drop-down list.

Using SUBTOTAL in the Total Row means that when the Table is Filtered, the function will give the correct answer.

If you toggle the Total Row off, Excel remembers the function and it will appear again when you toggle the Total Row back on.

Total Row Reference in a Formula

The Total Row can be used in a formula outside the Table. Notice the Avg Hours Per Setup formula below uses #Totals to refer to the Total Row in the Table.

By using the the Setup Hours and Number Setups from the Total Row, you can Filter the Table and get the correct answer for Average Hours per Setup.

The problem occurs when the Total Row is toggled off, which leaves this formula without a reference.

If you recall from my last post on Tables, I used the following formula for Average Hours Per Setup =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) which has a different flaw. It does not work when the Table is Filtered.

Create a Formula That Works

We can replace SUM in this last formula with the SUBTOTAL function =SUBTOTAL(109,Table1[Setup Hours])/SUBTOTAL(109,Table1[Number Setups]).

Now when we Filter the Table the calculation is correct and we don’t have to worry about having the Total Row toggled on for the formula to work.

SUBTOTAL with Windows and Mac

It’s much easier to use the SUBTOTAL Function with Windows versions of Excel (2007, 2010) because autocomplete gives you the different function numbers for the first argument.

The Mac version (2011) doesn’t have autocomplete but you can access Help, which brings you to another window with some information that helps you decide which function number to use for SUBTOTAL.

Hidden values refers to entire rows that are hidden. Excel Tables use “Ignores hidden values” as function numbers when you select a function from the drop-down list in the Total Row.

Table Availability in Excel Versions

As you may have guessed Excel 2003 (Windows) doesn’t have Tables. They have what is referred to as Lists, which is the pre-cursor to Tables. Lists don’t have any structured data.

The same is true for Excel 2008 (Mac), which only has Lists and no Tables. If you have a file with Tables and open it with an earlier version having only Lists then you get a warning.

The moral of this is to upgrade, Tables are worth it.

I created a data table on a worksheet, then converted it to an Excel Table. Before the conversion, I had entered a formula for Gross Product in the last column. This formula looks like most formulas do in Excel, with cell references, as pictured below. The Gross Product formula in cell L5 is =K5+J5+I5.

The great thing about converting to a Table is that when I add more data to the table, the formula is automatically copied down for me.

If I change this formula, then Excel will automatically copy the change to all rows in the column. And it doesn’t matter if I change the first cell at the top of the column or change a cell in the middle, all rows in the column will be modified.

Using Column References in a Table Formula

Tables have a structured data format you can see when adding a formula after the Table has been created. In my example I will reenter the Gross Product formula so that it equals Net Product plus Waste Setup plus Waste Run. Instead of cell references, Excel uses column references for the Table.

The formula =[@[Net Product]]+[@[Waste Setup]]+[@[Waste Run]] was entered in row 18 of the Table and was immediately copied to all rows in the column when I pressed Enter.

Notice that each column reference in the formula is located between square brackets [ ]. The @ sign refers to “this row” and is also included in square brackets. So the formula in cell L5 for Gross Product equals the values for Net Product in row 5 plus Waste Setup in row 5 plus Waste Run in row 5.

An equivalent formula using the SUM function is =SUM(Table1[@[Net Product]:[Waste Run]]).

Again, when I changed the formula in row 6, it was copied to all rows in the Gross Product column.

Formulas Outside the Table Range

As you may recall, Table Names in Excel are like range names and show up in formula autocomplete lists. The structured column references also show up in formula autocomplete lists.

I want to calculate the average hours per setup in the Table so I use the formula =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) in cell E2, which is outside the Table range. Formula autocomplete helps me build the formula by listing all the columns in the Table — after I type the Table name followed by an open square bracket [.

Notice the Table1 name precedes each column reference. You have to remember the first few letters of the Table name so it will show up in the formula autocomplete list. Once the Table name is entered you have to remember to type an open square bracket [ to bring up the autocomplete list for the columns.

Or you can simply use the mouse to select the columns you want in the formula and the column syntax is entered automatically.

Using a formula that references an entire Table column will expand to include any and all data for that column.

The same thing goes if I reference the Table in a formula. If I want to know the total number of rows in the Table, I can simply enter =ROWS(Table1) into a cell and it will always give the correct number. (Filtering the Table has no effect on this formula.)

Tables have a built in Total Row feature that we’ll cover in my next post on Tables.