Consider this scenario. You want to calculate
the average monthly sales in your
college canteen. Column A has the Jan, Feb,
Mar, and April while column B has the corresponding
amounts, viz., Rs 1200, 1500,
1700, and 1600. You would normally use a
formula like: =AVERAGE(B2:B5)
Now, if you add the Sales of May, June,
and July, you must manually change the
formula to =AVERAGE(B2:B8)
Next you want to insert Region and
Product between column A and B. You must ensure
formula changes to =AVERAGE(D2:D8)
Then, you want to find out total sales, lowest
sales, and highest sales. You need to add formulas:
=SUM(D2:D8); =MIN(D2:D8); and =MAX(D2:D8).
Finally you want to add columns E and F for
Sales Returns and Discount, and update the formula
logic as average of Net Sales. That is Sales
– Returns--Discount. Your formula must now
change to =AVERAGE(D2:D8)-AVERAGE(E2:E8)-
AVERAGE(F2:F8). You must do the same for SUM,
MIN, and MAX formulas.
If you are already exhausted, welcome to some
cleaner formulae.
These are like =AVERAGE(NetMonthlySales);
=SUM(NetMonthlySales); =MIN(NetMonthlySales);
and =MAX(NetMonthlySales). And imagine not
having to manually update the formula every time
columns and rows are added. That's what you get
when you combine Table feature and Name Manager!
What is Table
Table is a formatted data range with built-in functionality
to transform ordinary data ranges into structured,
intelligent datasets. With Ctrl + T, you can convert any
data into a Table. The most useful advantage of Tables
is automatic expansion. When you add new rows or
columns, the Table instantly includes them—along
with any formulas and formatting.
Tables also introduce structured references, allowing
formulas to use column names instead of
traditional cell references. For example,
instead of writing =SUM (B2:B20), you can
use =SUM(Table1[Sales]) making formulas
easier to read.
Tables also integrate seamlessly with
PivotTables, charts, and Power Query, ensuring
all analysis always reflects the latest
data. Because Tables are dynamic, linked
reports update automatically when the
source changes—saving time and preventing
inconsistencies. Other advantages
include built-in filtering and sorting.
What is Column-Based Name Manager
As spreadsheets grow in size, managing formulas
with traditional cell references can become error
prone. This is where Excel’s Column-Based Name
Manager helps. By assigning meaningful names to
entire columns, users can replace cryptic cell ranges
with clear, readable labels making spreadsheets easier
to build, understand, and maintain.
The Name Manager allows you to define names
for ranges, constants, or formulas. Instead of writing
formulas like =SUM (B2:B500), you can simply use
=SUM(Sales). This approach dramatically improves
formula clarity, especially in large datasets.
Column-based naming ensures consistency and
error reduction. When new rows are added, named
columns can automatically expand, ensuring formulas
continue to work without manual updates.
Financial models, dashboards, and reports become
easier to audit because formulas describe what they
calculate rather than where the data lives. For example,
=Revenue–Expenses communicates intent far
better than =D2–E2.
From a productivity standpoint, Name Manager
supports centralised control. All defined names can
be viewed, edited, or deleted from one place. Names
can also be scoped locally (to a worksheet) or globally
(to the entire workbook), offering flexibility for complex
projects.
So, what are you waiting for? Go ahead and start
using.