For what you need named range?


1. Open my example file;

2. Copy A3:A17 and PasteValues e.g. to I3 and edit header to 'Literals' (or simply enter new header and data below header);

3. Select any cell in new datarange, and then Insert>Table (check 'My table has headers'). Rename table to tLiterals;

4. In formula for Total, replace tData[Values] with tLiterals[Literal]. The formula refers to new table now (This is an example how attach the formula to another existing Table. Of course, when another table was not defined before and there is no need to preserve old one, you can simply replace the header with new one, select any Table cell, activate Design menu, and edit Table name. The formula now refers to column Literals in table tLiterals! And this happens without any formula editing);

5. When you now have another workbook where a table named tLiterals is defined, and which also has column Literals, then you can enter exactly same formula into this workbook too (or copy the formula in Edit mode and paste it into formula window in another workbook. Using Edit mode when copying and pasting is essential for preventing references to another workbook.).


As you see, the was no range defined at all!

You need to define named ranges when using Tables in case:
a) You want to define data validation lists. Data validation can not use Table formula syntax, and can't refer to Tables or Table elements. But you can select e.g. column datarange in Table, and then define the selection as named range. Resulting named range will be dynamic. E.g. when you defined a named range lLiterals, the the formula for it will be '=tLiterals[Literals]'. You can use this named range as source for Data Validation List;

b) You need to refer to some part of Table, and you can't do this using Table Formulas. E.g. you have a Table, where some amount of rows at bottom are empty, and you must refer to filled part only.