Excel Table vs Range: Which One Your Sheet Needs

Every comparison of Tables and ranges ends up as a feature list. Tables have structured references, filter buttons, banded rows and a total row. Ranges have none of that. All true, and none of it answers the question you actually have, which is whether the sheet in front of you should be one.

Use an Excel Table when the data will grow, when formulas or a pivot need to follow it as it grows, and when people will filter it. Use a plain range when the sheet is a finished report, when it needs merged cells or spilled array formulas inside the data, or when you want full control over how it looks. Converting is cheap in both directions: Ctrl+T (or Ctrl+L) turns a range into a Table, and Table Design, then Convert to Range, turns it back. The formatting stays when you convert back, so to drop the look as well, clear the Table style first. The matrix below gives a verdict for six common sheet jobs.

What does a Table do that a range doesn't?

A range is just a block of cells. It has no idea where it ends, so every formula, chart and pivot that points at it has a fixed address, and that address goes stale the day someone adds row 501.

A Table is a named object that knows its own boundaries. Microsoft's overview of Excel tables lists what comes with it: filter buttons in every header, banded rows, calculated columns that fill a formula down the whole column, an optional total row and a sizing handle. The parts that matter in practice are three.

It grows by itself. Type in the row directly under the last Table row and the Table expands to include it. Paste a block with the same number of columns underneath and it takes the whole block. Anything pointing at the Table grows with it.

Formulas use names instead of addresses. =SUM(Sales[Amount]) instead of =SUM(C2:C501), and inside a row, [@Amount] means "the Amount in this row". Microsoft's structured references documentation explains that these references adjust automatically as the data changes, which is the entire point: nobody has to remember to update C501 to C502.

The total row respects filters. The Table's total row writes SUBTOTAL(109, ...) rather than SUM, so when you filter to one region the total shows that region, not the whole column.

What a Table does not do is make your data look good. The default blue style is a style, not a design, and table design is a separate job from turning on the Table object.

When is a plain range the better choice?

When the sheet has stopped changing. A monthly report that goes out once and gets archived gains nothing from auto-expansion, and the Table machinery (filter arrows in the header, the Table Design tab, a style layered under your formatting) is overhead someone else has to understand.

A range is also the right choice when the layout needs things a Table refuses, which is the next section, and when the file is headed for print or PDF. A header row that belongs to a Table can't be restyled freely without fighting the Table style, and a finished document is the place you most want direct control over every fill.

The honest rule: Tables are for data you keep adding to. Ranges are for data you're done with. Most people's sheets are one or the other, and the mistakes come from treating a log like a report or a report like a log.

Situation matrix: six sheet jobs, one verdict each

The sheet's job	Table or range	Why
Monthly log (expenses, sales, tickets, one row per event)	Table	New rows arrive every week. Formulas, charts and pivots that point at the Table pick them up without anyone editing an address.
One-off report (sent once, read, archived)	Range	It will never grow. A Table adds filter arrows and a style layer the reader doesn't need and you'd have to override.
Dashboard source (the data tab a summary sheet reads from)	Table	Summary formulas written as Sales[Amount] keep working as the source grows. Hard-coded ranges silently stop short.
Shared entry sheet (several people typing rows in)	Table	Calculated columns fill themselves on new rows, so a colleague who types in row 212 doesn't leave the formula column blank. Filter buttons let each person find their own rows.
Printed list (a phone list, a stock count, a sign-in sheet)	Range	Print wants exact control over fills, borders and a repeating header row. Nothing about a Table helps on paper.
Pivot source (anything you will pivot more than once)	Table	Rows added to a Table are included when the pivot refreshes. A pivot on a fixed range needs its source edited every time the data grows.

Notice the pattern. Four of the six are "Table" and every one of those four is about the future: new rows, new people, new refreshes. The two "range" jobs are finished documents. If you can't decide, ask whether anyone will add a row to this after today.

Why won't a Table accept merged cells or spilled formulas?

Because both break the one promise a Table makes, which is that every row has the same columns and every column holds one kind of thing.

Merged cells are simply unavailable. Microsoft's merge and unmerge instructions say it directly: if Merge & Center is disabled, check that the cells "aren't formatted as an Excel table." The button greys out inside a Table on Windows, and Merge is dimmed in Excel for the web for the same reason. If your layout depends on a merged header spanning several columns, a Table is the wrong container. The better fix is usually to stop merging anyway, and the alternatives to merged cells cover Center Across Selection and when a spanning header is legitimate.

Spilled array formulas are refused. A formula like =SORT(...), =UNIQUE(...) or =FILTER(...) returns many values that spill into the cells below. Microsoft's guide to correcting a #SPILL! error is plain about it: "Spilled array formulas aren't supported in Excel tables." Type one into a Table column and you get #SPILL!. The fix Microsoft gives is to move the formula outside the Table or convert the Table to a range.

In practice the second option is rarely the right one. The better pattern is to keep the raw data in the Table and put the spilled formula in an empty area beside it or on another tab, pointing at the Table by name: =UNIQUE(Sales[Region]). The Table grows, the spill grows with it, and neither gets in the other's way. The same page also notes that spilled arrays can't spill into merged cells, so a range full of merges isn't a safe landing spot either.

How do Table styles interact with your own formatting?

This is where most "why won't my colours change" complaints come from. A Table style is a layer. Your own formatting (a fill you apply from the Home tab, a font colour, a border) sits on top of it and wins. So you can apply a light green fill to one row of a Table and it will show, but the Table style is still underneath, still banding the other rows, still colouring the header.

That produces three recurring problems.

1. Your banding fights the Table's banding. If you colour every other row by hand or with conditional formatting and the Table style also has Banded Rows ticked, you get two patterns. Pick one. If you want the Table's, leave your fills off. If you want your own, untick Banded Rows in Table Design. The trade-offs between banding approaches are in the alternating row colours guide.
2. Manual fills don't move with sorting the way the style does. The style repaints bands after a sort. Your hand fills travel with the cells they were applied to, so after a sort they land in random places.
3. Clearing formats doesn't clear the style. Home, Clear, Clear Formats removes your direct formatting and leaves the Table style standing. To remove the style itself, select a cell in the Table, open the Table Styles gallery and choose Clear at the bottom (or the None style at the start of the Light group), as Microsoft's guide to formatting an Excel table describes. The Table stays a Table, just without the style's colours.

If you want the Table's behaviour and your own look, the clean route is to clear the Table style, then format the cells directly. One layer, no fighting.

How do you convert a Table back to a range without losing the look?

The conversion itself is two clicks, per Microsoft's steps for converting an Excel table to a range:

• Windows: click anywhere in the Table, go to Table Design, and in the Tools group choose Convert to Range. Or right-click the Table, then Table, then Convert to Range.
• Mac: click in the Table, open the Table tab, choose Convert to Range, then Yes to confirm.
• Excel for the web: right-click the Table, then Table, then Convert to Range.

What goes: the filter arrows, the Table Design tab, auto-expansion and calculated columns. Every structured reference in your formulas is rewritten as an ordinary absolute address, so Sales[Amount] becomes something like $C$2:$C$501. The numbers still calculate. They just stop growing.

What stays: the look. Microsoft notes that the formatting remains after conversion, so the header colour and banding you saw are now ordinary cell fills. That's exactly what you want when the sheet is finished and headed to a reader.

If you want the look gone too, do it in this order. Clear the Table style first (Table Styles gallery, then Clear), then convert. Convert first and the old style is baked into plain cells, and removing it means Clear Formats, which also removes any number formats and fonts you set yourself.

Two checks after converting. First, the Total row, if it was on, is now a row of SUBTOTAL formulas with fixed addresses; they still ignore filtered rows, but if you add data below them, they won't include it. Second, any other workbook that referred to the Table by name needs a look, because the name it pointed at no longer exists.

What happens to a Table in Google Sheets?

It depends on when you tried. Google Sheets has had its own tables since 2024 (Format, then Convert to table, per Google's help page on using tables in Google Sheets), but for a while an Excel Table didn't arrive as one. It arrived as a block of coloured cells.

That changed in August 2026. Google's announcement of improved Excel file importing in Sheets says Excel tables now import as Sheets tables, and it is available to all Workspace customers and personal Google accounts. The announcement doesn't itemise which details survive the trip, so treat the specifics as something to check in your own file: open the imported copy, click into the table, and test one formula that used a structured reference before you rely on it.

Two practical rules for mixed Excel and Sheets teams:

• If the file is data that people will keep adding to in Sheets, leave it as a Table in Excel. It now lands as a table on the other side.
• If the file is a finished report, convert to a range before you send it. A range is just cells with formatting, and cells with formatting travel between the two apps with the fewest surprises. Google has a way back too: a Sheets table can be reverted to unformatted data, which removes the table's style and leaves the data as it was.

Format the range, keep the decision yours

SuperSheet's Excel formatter produces a formatted range, not a Table, and that is deliberate. You drop in an .xlsx or .csv, approve a plan, and download a real .xlsx with number formats set per column, a frozen header row, columns sized to their content, optional row banding applied as plain cell fills, and a totals row of live SUM formulas. Nothing is uploaded; it runs in your browser.

Because the output is a range, there is no Table style underneath to fight when you tweak a colour afterwards. And if the sheet turns out to be a log after all, one decision is left to you: click in the data and press Ctrl+T. The formatting stays where it is, and you get auto-expansion on top. One note if you do: the totals row is a SUM over a fixed span of rows, so add new data by inserting rows above it rather than typing below it, or let the Table's own total row take over.

FAQ

Is an Excel Table the same as Format as Table?

Yes. Home, then Format as Table, creates a real Excel Table and applies the style you pick in the same step. Insert, then Table, and the keyboard shortcut Ctrl+T (Ctrl+L also works on Windows; Command+T on a Mac) create the same object with the default style. The confusion comes from the name: "Format as Table" sounds like it only changes the look, but it switches on every Table behaviour, including filter buttons, auto-expansion and structured references. If you only wanted the look, apply the fills yourself or convert to a range afterwards.

Does converting to a range remove the formatting?

No. Convert to Range removes the Table behaviour and keeps the formatting, so the header colour and the banded rows stay as ordinary cell fills. That's usually what you want when you're finishing a report. If you want a completely plain range, clear the Table style from the Table Styles gallery first and then convert. Doing it the other way round leaves you with fills you can only remove through Clear Formats, which also wipes number formats and fonts.

Do Tables make a file slower?

Not in any way most people will notice. Microsoft doesn't publish a performance penalty for Tables, and a Table is essentially a named range with rules about how it grows. When a file with Tables is slow, the usual cause is something inside it: thousands of lookup formulas in a calculated column, volatile functions, or conditional formatting rules duplicated across many ranges. Those would be just as slow in a plain range. If a big file feels sluggish, look at the formulas before blaming the Table.

Can a pivot table use a Table as its source?

Yes, and it is the better source for anything you'll refresh. A pivot built on a Table points at the Table's name, so when new rows are added to the Table, a refresh picks them up. A pivot built on a plain range points at a fixed address and quietly ignores anything added below it until someone edits the source. If the data behind a pivot will ever grow, convert it to a Table before you build the pivot, and give the Table a clear name in Table Design so the source is readable.