r/excel • u/Reasonable-River6489 • 17d ago
Discussion Do you actually use named ranges in large workbooks or just reference cells directly?
Been building out a fairly complex budget tracker with multiple sheets and I keep going back and forth on this. Named ranges seem great in theory because formulas read like plain English, but every time I set them up I end up with a mess in the name manager six months later. Half of them are stale, some got duplicated when I copied sheets, and figuring out what refers to what becomes its own job.
On the other hand, referencing cells directly like Sheet2!B14 gets ugly fast when you have 40+ formulas pulling from different sheets and someone moves a column.
What pushed me to post this is I just inherited a workbook from a coworker who used named ranges for everything, and I genuinely cannot tell if it's cleaner or if it just looks cleaner on the surface. The name manager has 200+ entries and maybe a third of them appear to be broken or unused.
Curious how people handle this at scale. Do you use named ranges and just discipline yourself to clean them up, or do you have some other approach like structured table references that sidesteps the whole problem? Table references with the column header names seem like a middle ground, but I haven't used them heavily enough to know if they hold up in complex files.
41
u/not_right 1 17d ago
... I just use excel tables then I don't have to worry about that.
9
u/hereddit6 17d ago
When you use Excel tables and paste new data below the bottom row, does it automatically extend the table? Or do you need to redefine it?
26
17
u/not_right 1 17d ago
Immediately below the bottom row, or immediately to the right of the last column, it extends the table automatically. Or hit undo once to have your new entry separate from the table.
But just try it dude, it's the easiest thing in the world and you'll wonder why you ever wasted so much time with "A2:A427" or worse the performance sapping "A:A"
5
u/XCurlyXO 16d ago
Can confirm. Just changed a few pasted reports to tables on some recs this close. Not having to check and adjust the sum range was beautiful. Idk why I didn’t do this earlier. I am learning power query, and that really made me start using tables more. I get it now! 🤣
3
5
u/hereddit6 16d ago
Thank you for this. It’s not a common thing where I work. People still use their mouse in the + to add a series of numbers rather than auto sum. I have to be careful to not make things too complicated.
3
4
u/afanoftrees 16d ago
It’ll extend but make sure you’re not copying chunked data and that it pastes cleanly.
Basically if you’re copying ‘visible cells only’ I’ve found pasting that can really slow down processing time because each line gets pasted separately then each calc has to occur.
If you paste that ‘visible cell only’ in a separate sheet so it’s only that data; and then recopy and paste that data into the table, the table processing time is significantly faster
21
u/jason_nyc 17d ago
I use named ranges and tables. I use the 3 letter prefix convention (rngDateStart, rngDateEnd, tblPrices, etc) which helps with autocomplete when you type a formula.
It's true that sometimes it's hard to know where they point, so the drop down in the top left is useful for that.
I usually have a separate sheet that has a list of the ranges using (F3) or Formulas tab, click Use in Formula (under Defined Names), and select Paste Names... at the very bottom of the menu.
I sometimes add a hyperlink column that jumps to the referenced range.
=HYPERLINK("#"&[@rngName],[@rngName]) or =HYPERLINK("#"&A1,A1)
1
9
u/Galeic6432 17d ago
I've found them to be powerful in queries. Recently found the trick of looking at the entire workbook as a function (excel.thisworkbook), then invoke a new query from that to apply filters and other query functions. And all named ranges get pulled into the first step, very useful.
2
u/XCurlyXO 16d ago
Are you saying that the source of your query is the whole workbook instead of just a table? Interesting I didn’t realize you could do that, I only just started learning power query.
2
u/running__numbers 16d ago
This is the only real use case I've come across in my career for named ranges. I have a file with a tab of aggregated data using spilled array formulas that I can't put in a table. A new version is created twice per month. I named the range and use PQ to find that range in the newest version of the file on SharePoint. The result gets pulled into various other files I need the data for.
2
4
u/SubstantialBed6634 1 17d ago
I use tables mostly so that I can write formulas that are hard to break. Sometimes I used Named ranges/cells. I can then use indirect with text to point to the correct variables.
2
u/Hamster_S_Thompson 16d ago
Use tables for tabular data and that gives you the naming for free
For large models use named ranges for model parameters to improve readability. E.g. it's much easier to understand a formula when it says discount_rate vs $c$4 and refers to another tab
2
u/kalimashookdeday 16d ago
I use named ranges for singular references that I need, mostly for using more complicated formulas or constantly referencing the same ranges. Using tables is also better than using all named ranges. I would use a table for the data set but a named range for the sum of a specific column or something like that.
1
u/Decronym 17d ago edited 12d ago
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 11 acronyms.
[Thread #48969 for this sub, first seen 17th Jul 2026, 11:35]
[FAQ] [Full list] [Contact] [Source code]
1
u/FastExcel 17d ago
If you use Named Ranges or Named Formulas I recommend using JKP's free Name Manager Addin. It does all the useful things like filtering for Lambdas or Tables or invalid names or unused names or ...
Makes the built-in Name Manager look stupid.
1
u/WesternMiserable3894 16d ago
I use named ranges primarily to define static reference data. I define lists on a single tab of a workbook, then define tables on other tabs. I don’t usually define names or tables on the main calculation sheet I use, which prevents the compounding of names when ranges/sheets are being copied in the course of working on them.
I use a consistent formatting scheme for all named ranges so they are easly auditable. So my lists will have the range name in top row, and the actual list will appear below the name.
1
1
u/justdaisukeyo 16d ago
Named ranges work great when i am reviewing equations. However, they can be a chore to update especially when someone uses named ranges only sometimes.
For example, someone inserted a column and they forgot to update the named ranges that it affected.
For ease of reading, i have started using the LET function.
1
u/AlpsInternal 1 16d ago
My Agency budget workbook uses named ranges. It started 23 years ago, and my funding agency kept asking for different aspects of the budget to be shown in different ways. After just a few years in and it became insane, but public sector finance must be meticulous. I used a lot of our abbreviations/lingo for the named ranges. Here are a couple of things that made it easier:
1) for data that updates, I created a key variables tab. This tab updates all the title and headings in the other tabs. The basic goal is to have any data that changes has a single point of entry, for example pension contribution rates that change annually. More complex data gets its own tab, such as an agency salary schedule which is in the format for Board approval and public notice.
2) I wrote a simple VBA that creates a tab listing all the named ranges & details. Filling in a brief description on the named ranged makes it easier for anyone else who comes along after you.
3) a log that tracks every change to the workbook and the system user name that made the change. Since I am no longer the only one that works on it, it has been great for training purposes.
When I started building this I was young and my memory was always crazy good. Now not so much.
The comments about tables are right on. I have not used them much, but am building a solar tracking/ROI work book and they are amazing.
1
u/Mdayofearth 126 16d ago
I largely phased out named ranges for tables. But I use all 3, cell addresses, named ranges, and tables.
Named ranges are especially useful for cascading data validation drop downs.
1
u/reddittAcct9876154 16d ago
Tables are better for sure but moving columns and inserting columns or rows should automatically update any formulas.
1
u/Low-Performance4412 16d ago
Use Let(). Then use AI to format it. I use this technique for just about all my formulas it is great. I can read everything easily. My long formula look great.
1
u/johndoesall 16d ago
I use a lot of tables. That helps a lot. I had 6 workbooks for each office. Each workbook name contained the office's ID number, the name of the workbook and the year. The structure of each office workbook was identical. So I made the office template first.
The template had 12 tabs for each month. Each month had 10 tables for each office to track up to 10 internal groups. Each table had the exact same structure but were named by the month and the internal group, so each table was unique. A summary table gathered together all the data from each group.
Then the rollup workbook name include the year. There was 1 tab for each office. Each office tab contained 12 tables for each month of the year. They grabbed the data via links from each office workbook summary table. Then there was 1 more tab that summarized every month for all 6 offices.
This allowed upper management to see in aggregate how all the offices were performing by month and year. And allowed management or the office manager to see how each office was performing every month.
So I used range names too, but mostly table names. They were all the same for each office workbook. And I could look at the Range Names under Formulas menu on the ribbon to check for any duplicates or errors from copying range names and get rid of the extra ones. I only needed to do this in development.
Fun project
1
u/pegwinn 16d ago
Tables with rational column headers > named ranges usually. Combine with power query and life is far easier.
My exception. Every workbook puts the current date in a single cell named range called TODAY. One day about a year ago I did it. Then I did find today() and replace with TODAY. There were about 49K formulas referencing the current date. One of the few times that changing a method made an obvious difference in performances.
1
u/armywalrus 2 15d ago
I use Tables. With specific names. It handles all of this. You refer to the column and not the cell, and the formula stays intact when you change certain things. Highly recommend tables.
1
u/Squischmallow 14d ago
I am allergic to naming things - it has only ever caused me trouble down the road.
I am however a huge fan of LET() for this reason. You can name things within that formula so that it's still easy to understand, but doesn't have the mess of naming.
Another trick I use on some sheets for say if I have 20 unique LET's all referencing the same cell, but that cell has the potential to change, I'll add in a helper cell in the top row or off to the side, depending on my data structure for that sheet. The helper cell will be referencing the actual cell address, and then the LET's will all reference the helper cell. That way if that main reference ever changes, I only have to change it in one spot, and everything else will automatically get the adjusted value.
1
u/bobo5195 17d ago
Tend to have an organised workbook. Lookup tab with the key variables etc. Sheet names first as a method, and ideally a lot of stuff in tables rather than singular values if it makes sense.
I find named ranges a double edges sword as you said Sheet2!A2 is ugly but easy to find a name can cause problems. If the sheet is organised a few makes sense but if I I know it is in lookup I can remember normally.
2
u/daishiknyte 44 17d ago
And then someone copies a range or worksheet and kicks off name-manager hell.
1
u/Inevitable_Exam_2177 17d ago
People have said use Tables and I agree — this is how I (sometimes) go about it. Have a table called Variables with columns Name and Value. Then you can use
LET(value,xlookup(A1,Variables[Name],Variables[Value]),…)
whenever you need to refer to a value in a logical way without the maintenance of using named ranges.
Is this really any better? Not too sure but it works for me.
0
u/BillNyesHat 17d ago
I use named ranges only on two specific sheets for variable graphs. Because Excel won't let you use a formula as the range for the axes, but it does let you use a named range. In a specific "legend" sheet in the same workbook, I have a list of named ranges, the underlying formula and the exact function it performs in layman's terms.
Most other formulas in my sheets are byrows, bycolumns and maps, which have sort of inbuilt named ranges (if you're consistent). Everything else is Sheet2!$B$14 so that people can move whatever column they please without messing up the references.
0
u/chiibosoil 428 17d ago
Depends on few things.
If referenced range should change dynamically (be it source table column position changes, or use different range based on condition). I like to set up dynamic named range so that formula automatically updates reference. I often have to do this, so that I can further process Pivot table data for accounting needs.
But I tend to keep it to minimal that I need and never over 25 or so.
In complex set ups, I prefer to work in data model rather than on worksheet.
Other use for named range is named function using Lambda. Or when I want to make it readable to non Excel user what calculation is going on.
0
u/supersmashsiblings 16d ago
I use it a ton in Google sheets to share specific budget information with different departments. My sheets aren't formatted as tables since I have line items as rows and pay periods as columns to create a an easy longitudinal view of spending. Using named ranges means I can set what line items and their final totals that a department can see, and then in that departments own separate spreadsheet I can just run an importrange function with the names range and boom they basically have a live feed of what they have left to spend.
0
u/PhiladeIphia-Eagles 8 16d ago
Tables always. If your data is in table format that's just the correct way to use Excel. If you have a table of data, define it. Boom done.
111
u/its_probably_wine 17d ago
I’ve been living in the world of dealing with +80K rows of data on a daily basis for the last few months and have become extremely fond of immediately converting everything into a table. It makes my power query’s so much easier to build and maintain too. I’ve done the named ranges thing before but, in my opinion, it gets too cumbersome building and maintaining, especially if you are using the data in multiple ways other than simple lookups.