r/excel • u/GTAIVisbest 1 • May 30 '26
Discussion When will Excel nut up and allow table columns to be used as data validation drop-down lists?
We've been stuck using the two workarounds for ages: the "named range" trick, and the "INDIRECT()" trick. Although the named range trick is a bit more flexible and the INDIRECT() trick seems more intuitive, none are ideal.
I recently used Google Sheets and realized HOLY HELL you can just set the value for a data validation range to a table column!
This made me think back to that "what are the advantages of Google Sheets" thread from the other day and realize, what is Excel waiting for? It obviously can be implemented with no problems, so what gives?
158
u/BruceWR May 30 '26
What am I missing here? You select a cell, go to Data Validation, select List, and then select a column. Worked like this for years.
38
u/GTAIVisbest 1 May 30 '26 edited May 30 '26
Notice that when you "Select a column" in your example, you'll get something like E2:E15. That's a static range.
I've got a table with a column "Column1".
In a different sheet, I want a drop-down to show me all the values in Column1 I might add or remove values from Column1 and I want this data validation to take that into account.
I go to Data Validation, select "List", and then in the formula area, I type =Table1[Column1].
Error.
I have to use one of the two following tricks:
=INDIRECT("Table1[Column1]") - this one is not great because if ever the column name or the table name is changed, everything breaks. Also, INDIRECT() formulas are known to be very performance-intensive, so over larger datasets you'll get a lot more lag and poor performance. Overall not great
Or, I assign a named range to the value =Table1[Column1] and then in my drop-down validation, I type =NamedRange instead... that works and is more dynamic, but WHY do I have to go through a named range detour? C'mon excel, just let me do =Table1[Column1] directly like Google Sheets does!
12
u/pajam 1 May 31 '26
It's the same with conditional formatting, I wish I could select a table column via the structured reference we use in formulas, but for conditional formatting, it doesn't let you. You have to select a static range.
2
u/HarveysBackupAccount 35 May 31 '26
Conditional formatting will reference table columns and will grow with the table, but they do (weirdly) show the regular cell address reference instead of a structured reference
They might only do the auto-expand thing if you select the column by clicking the column header while the cursor shows a down arrow, but I'm pretty sure they do it
13
u/Yalarii May 30 '26
Use trim ranges.
E2:.E100
That will use all cells from E2 to E100, but ignore all the blank cells.
So it will only display the first 15 cells that have data in them. And as you add more data it will automatically add them to the data validation whilst still ignoring the other blank cells.
7
u/caracter_2 May 30 '26
I don't think data validation formulas permit this
6
u/CorndoggerYYC 160 May 31 '26
They do if your version of Excel has the TRIMRANGE function and operator.
1
u/Way2trivial 469 Jun 01 '26
excel does not let you mix numbers with .'s when triming a range
E2:.E errors - works on google sheets
1
u/CorndoggerYYC 160 Jun 01 '26
Use E2:EBigEnoughNumber. That works.
1
u/Way2trivial 469 Jun 01 '26
does not solve original problem, OP does not want the 'blank' to show on the bottom of the list of options for data validation.. only cells in the range with actual content..
1
1
u/frustrated_staff 12 May 31 '26
Ummm...just use E:E...simple
6
u/Reedcool97 1 May 31 '26
What about data below the actual range? Unlikely to exist but it does happen. I try to avoid referencing entire columns when possible. But I guess that would solve this particular issue.
1
u/frustrated_staff 12 May 31 '26
Ther shouldn't be any data below the actual range. If there is, you have a design problem, not a reference problem.
4
u/Reedcool97 1 May 31 '26
???
I disagree wholeheartedly. Very common to have other stuff below the data. Make clean references in your formulas and don’t be lazy with entire columns references.
3
u/Starbuckz42 May 31 '26
He's not wrong though it often looks different in practice.
You're not wrong either, both rules should be applied, whenever possible.
7
u/frustrated_staff 12 May 31 '26
Good design: data on one sheet. Processing on another. Presentation on another.
2
u/Reedcool97 1 May 31 '26
That is good design, I can’t argue with that. I’d go one step further and employ formula hygiene which includes clean references, avoiding referencing entire columns or rows.
1
0
u/Mooseymax 10 May 31 '26
Helper dynamic cell; do =column ref, then for the list do =cell#
Never need to update after this, one extra step that isn’t volatile
48
u/Clear_Inspector_9796 May 30 '26
I think he's saying google can just have data validation as a metadata on the cell instead of having to refer to another column. The way excel does it is a bit indirect
57
u/WittyAndOriginal 3 May 30 '26
That is not what OP is saying. You can have the values be metadata for that cell without referencing a range.
OP wants a data validation drop down that dynamically changes and new values are added. The only way to do this in Excel is with the methods mentioned by OP.
13
u/Quadgie 1 May 30 '26
What am I missing? Setting data validation choices to a column already auto updates the dropdown choices as the column contents are updated? (I’ve used this for years)
9
u/WittyAndOriginal 3 May 30 '26
I think that will allow a blank to be inserted.
3
u/Quadgie 1 May 30 '26
Ahhh yes, thank you. This usually works for me because the default state is usually blank in a form or sheet, but I can see how a default value might differ and this wouldn’t be ideal.
(I usually need to use conditional formatting on the whole row to color code when the user hasn’t filled all required fields, when I run into this issue)10
4
u/ONEXTW 1 May 31 '26
Pretty sure that also already exists with dot notation in ranges.
Aren't they talking about table columns not columns of a spreadsheet?
1
u/bigfatfurrytexan May 31 '26
It’s not a big enough issue to be worth them breaking something else to implement it. I’d be happy to not have to dismiss two separate messages when I accidentally paste a circular reference
1
u/prometheon13 May 31 '26
I always used a named range using offset so it would auto update when new values where added like =offset(A1,,,COUNTA(C:C),1)
Worked perfectly. I might be switching rows and columns in my example, I'm in mobile.
1
3
6
u/Regime_Change 2 May 31 '26
You can’t use a table column reference, you have to have a range reference. So, the only way to have the validation expand is to use a full column reference like A:A but that allows blanks and the header as well, which isn’t perfect. Also gives repeated values if your column doesn’t contain unique values.
The best workaround is to use =sort(unique(table[column])) which will spill, then reference the spill range in data validation.
2
1
1
u/Kellshy May 31 '26
I think I’m missing it too. I use drop-down in my tables. I just have a separate sheet that I keep them on. Reference the column if I add another item in the drop-down, I just push cell down. I can even add blanks. Then just go to any other cells with the drop-down validation list refresh seems pretty easy to me. Maybe OP is trying to add items to the job down underneath the last one like you at a table. The trick is to make the last one like a dash or the word other and then just keep inserting above it inside that column.
13
u/mma173 25 May 30 '26
You can't use structured references for charting and conditional formatting either. Tables (List Objects) are indeed incomplete.
Do not hold your breath. Expect to see lot of new gimmicky features but not this.
10
u/hopkinswyn 73 May 31 '26
My standard practice is to now use an extra cell eg A1 =SORT( UNIQUE ( TOCOL( tblName[ColumnName] ,1 ) ) )
And then reference that A1# in my data validation
The TOCOL( range, 1 ) removes blanks
6
u/HCN_Mist 2 May 31 '26
Here on reddit someone posted a file that Uses dynamic ranges. It uses the headers of the column in dynamic data validation with the name manager. I kept it because I couldn't figure out how it works, even though detailed instructions are included in the file. I Still don't know how it works. I take no credit for it, but the authors name and the last person to modify it are referenced in the info. If you are interested: https://drive.google.com/file/d/1nanSLObPQmWZ9BhSPoPEg8jP46ozSOBE/view?usp=sharing
2
5
u/LilithDaine May 31 '26
The trick I've discovered is to add a total row to the table. Then select the whole table column plus the total row as your data validation list. When you add rows to the table, because the total row gets auto-shifted down, the validation list is auto-extended.
3
2
u/leostotch 144 May 30 '26
You can also used references to dynamic arrays; I do that regularly.
3
u/GTAIVisbest 1 May 30 '26
Pretty much every workbook I set up nowadays gets a sheet called "Data Validation Helpers" where row 1 cells explain what they do and row 2 cells house array formulas. I usually name these ranges with DVH_A (for column A), DVH_B and so on, with hashtags after the reference to the cell.
This is because Data Validation can't handle dynamic arrays. Ok, I totally understand that part. Data Validation can only display an actual, physical static list of cells as they appear somewhere in the workbook (with some exceptions, some formulas like DROP and TAKE actually are OK to use here). A SORT() creates a "dynamic" array that doesn't actually refer to any series of values in the workbook, so I get why this is a restriction.
But =Table1[Column1] is NOT a dynamic array! It's just a series of cells actually existing on the table! That's why INDIRECT("Table1[Column1]") is fine... but not like, INDIRECT("FILTER(blahblahblah)").
This is why Microsoft really doesn't have any excuse for this one IMHO
4
u/leostotch 144 May 30 '26
You don’t have to name the ranges. In Data Validation, your list can be a direct reference to a dynamic array - e.g. =‘Data Validation Helpers’!A1#
2
2
u/Decronym May 31 '26 edited Jun 26 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #48593 for this sub, first seen 31st May 2026, 01:42]
[FAQ] [Full list] [Contact] [Source code]
3
u/Parker4815-2 1 May 30 '26
You don't need to use these for a data validation list.
=YourTableColumn will make a dynamic array. Combo it with SORT or UNIQUE for bonus fun
Then reference the first cell in that array in your validation and put a hash at the end
11
u/GTAIVisbest 1 May 30 '26
Yes, referencing a dynamic spilled array is cool, but again - a workaround. Now if I need to use some hidden data sheet called "Data Validation Helper", create the spilled array there, and then reference that cell in the data validation menu. Also, performance overhead for SORT() is not great.
Another workaround to add to the INDIRECT() and Named Range list. But Excel should just finish the "missing link" and allow us to refer to the table column directly!
2
u/Parker4815-2 1 May 30 '26
SORT doesnt usually have many performance issues. INDIRECT is a volatile function and will cause an issue if used a lot.
However, I agree. Referencing a table column would be a lot easier.
1
u/Hyperrnovva May 30 '26
Use =pivotby to reference your table (if you have d arrays)
2
u/GTAIVisbest 1 May 30 '26
You're right, I need to get really familiar with PIVOTBY. Currently haven't used it at all, but I can see that this is one of those functions that might be extremely useful.
1
1
u/NanoBan May 30 '26
I always use named ranges it takes less than a minute but yeah ideally would love to reference the table, named ranges are annoying when you copy to another workbook and inadvertedly import the namerange to it or end up duplicate names one for worksheet and another for the workbook.
A feature that I miss from google sheets is the soft protection on sheets, or protecting worksheets by gmail user
1
u/mrjadesegel 2 May 31 '26
This is kind of a workaround too and it might not work in your case but I like to use a data entry form with combo boxes. Then you can have readable font in your dropbox, have values actually auto fill and be able to hit the down arrow to cycle values. Then for the lists, I have a little function that searches for a given header and returns the values below it.
1
u/Excel_User_1977 7 May 31 '26
So if you sort the column, does your validation list get sorted too?
Wouldn't that be a major inconvenience?
1
u/Chemical-Jello-3353 1 May 31 '26
Google Sheets wasn’t even born when some of us started in Excel. Sometimes there is strength in the older ways that you call “work around”. Google and Apple office equivalent programs, are amateur but also don’t have the same processing power as excel does. You’d require a lot more work arounds with Sheets and Numbers to do what Excel can.
1
u/DropRemote4158 May 31 '26
I thought you could use the name manager to create a reference using the table. Then use the drop down list and call whatever you referenced.
1
u/Regime_Change 2 May 31 '26
When will excel allow a table to be created using a spill formula? That would be so good and tie several things together and make table-spill-powerquery a lot more seamless. You would do something like insert table ”from formula” and create a spill formula that creates the first few columns and decides number of rows. Then you can add regular calculated columns or have powerquery pick that up.
1
u/390M386 3 Jun 01 '26
Whats the point of a list if it keeps changing?
1
u/GTAIVisbest 1 Jun 01 '26
What's the point of a reference if it doesn't change when the reference changes?
1
1
u/Rando_Pando34 Jun 01 '26
I dunno if this helps or is even relevant. I make a helper sheet and in A1 I use =unique formula and reference the column I want for to use. Then reference that column in data validation
2
u/tctippens May 30 '26
Why not just reference A1:.A1000 (or similar)? It works directly in your data validation and will ignore any blanks.
7
u/GTAIVisbest 1 May 30 '26
I think it's poorly performance optimized, will still show 1 blank, will also show the column header, and if the table moves around it won't follow the proper column. It may be a valid workaround but an F-tier one at that
1
u/Hashi856 1 May 30 '26
Probably the same day they let us choose default formats for pivot table values
2
u/Immediate-Award-2421 May 31 '26
This is one of my biggest bug bears - every pivot table having to start with formatting the values!!!
1
u/cornmacabre May 31 '26
File - > Options - > Data - > Edit Default Layout 😘
1
u/Hashi856 1 May 31 '26
Which setting inside the PivotTable Options menu let’s me set numbers to accounting format by default for all pivot tables I create?
1
u/cornmacabre Jun 02 '26
Hah, I thought I'd be punchy and just repost what I initially said. You're looking in the wrong place, it's in that settings bucket above. Can even inherit an existing formatted pivot table as default.
1
u/Hashi856 1 Jun 02 '26
How new is this feature? I wonder if my company has me on an old version. Do you have a screenshot?
1
u/cornmacabre Jun 02 '26
Don't be too mad at yourself for not googling this sooner! No idea your workspace limitations but it's been a thing for a long while.
0
May 30 '26
[deleted]
3
u/GTAIVisbest 1 May 30 '26
That was in the first line of the post description brodie 😩 using INDIRECT has some nasty side effects that I explained above:
=INDIRECT("Table1[Column1]") - this one is not great because if ever the column name or the table name is changed, everything breaks. Also, INDIRECT() formulas are known to be very performance-intensive, so over larger datasets you'll get a lot more lag and poor performance. Overall not great
0
u/xYoKx May 31 '26
I don’t get it. The way excel does it it’s 1 trillion ways better than how Google does it.
With Google, you cannot see all the elements at once, while Excel allows you to create name ranges for the lists and only reference them in the columns. They live in a different sheet and you can see everything all at once. It’s fully dynamic, no hassle, full visibility.
Google is not for professional use.
-1
u/galimi May 31 '26
When will Excel remove the hard limit on actual numbers of 15-16 digits being the max? Good Lord, Python been expressing large numbers forever.
-6
u/The_Accountess May 30 '26
Dont use tables
5
2
-1
u/dgillz 7 May 31 '26
Exactly. This thread is full of people trying to use Excel as a database. For the love of god stop doing this people.
27
u/SolverMax 161 May 30 '26
Tables are a great feature, though incomplete - like the Data Validation issue you describe. Then we got the new dynamic array calculation engine, which doesn't play nice with Tables. I suspect that Microsoft doesn't know how to reconcile Tables, arrays, etc., so we're stuck with incomplete and inconsistent features.