Keep SKU codes as text in Google Sheets tables

Set the table’s SKU column to Text, but first verify that its values still match the source identifiers. A column type is not a recovery mechanism for zeros or digits already lost during entry or import.

Google Sheets has Tables with column types and table references, plus classic Named ranges. This guide uses a small inventory table to show where each belongs. Use the current Google Sheets web app on a computer and a practice file you can edit; no add-on or API is needed. Google’s table rollout covers Workspace, Workspace Individual and personal accounts. Table availability

1. Begin with known text, not already-converted numbers

For this synthetic exercise, enter headings SKU, Item, Quantity in A1:C1. Use the formulas shown in column A so their text output can be checked before making a table.

Row	A: SKU formula	B: Item	C: Quantity
2	=MID("ID:000123",4,6)	Notebook	2
3	=MID("ID:12345678901234567890",4,20)	Pen	3
4	=MID("ID:1E10",4,4)	Adapter	5

These are practice formulas typed into ordinary cells, not formula wrappers inserted into a production CSV. Google’s MID reference documents substring extraction from a string, with the first character indexed as 1.

In E2 enter =ISTEXT(A2) and in F2 =EXACT(A2,"000123"). Repeat the text check for rows 3 and 4 and compare them with their respective expected strings. All checks should be TRUE. See ISTEXT and EXACT.

For real records, use your untouched source export as the authority. If an imported cell already says 123 instead of 000123, stop and recover it from that source before converting the range to a table. Choosing Text later cannot tell you how many zeros belonged there.

2. Convert the range and choose types

1. Select A1:C4 and choose Format → Convert to table.
2. Use the table menu to name it Inventory.
3. In the SKU column menu, choose Edit column type → Text.
4. Set Item to Text and Quantity to Number.
5. Re-run the exact-string checks from step 1.

Google documents Text, Number and other column types; incompatible values can produce type warnings. None permits mixed types and removes that column-type constraint. It is not the best choice when all SKUs should be text. Use tables in Google Sheets

Do not confuse a warning disappearing with proof that the identifier survived. Record 000123, its length and its exact comparison result before and after table conversion.

3. Check quantities with a table reference

In an unused cell outside the table, enter:

=SUM(Inventory[Quantity])

Expected result: 10. This is the same naming pattern as DeptSales[Sales Amount]: table name followed by the column heading in brackets. Google’s table references update as records are added or removed from the table. Table-reference documentation

Add a fourth table record for Cable, quantity 4, with the SKU text produced by =MID("ID:03-04",4,5). Make sure the record is inside the table; use its range controls if necessary. The expected total becomes 14. Verify that the new SKU still equals 03-04, not a date value.

The purpose of this separation is practical: SKU identifies a product, while Quantity is something to add. A numeric-looking identifier should not be summed just because it contains digits.

4. Use a named range for a different purpose

Outside the table, enter Tax rate in H1 and 0.08 in H2. Select H2, choose Data → Named ranges, name it tax_rates, and save. In H4 enter =100*tax_rates; expected result: 8.

This retains the classic named-range workflow: the name refers to a selected cell or range. Use a valid name without spaces and avoid cell-address syntax. Name a range of cells

Keep the three systems distinct:

• Table names identify structured records and their columns, such as Inventory[Quantity].
• Named ranges label chosen cells, such as tax_rates.
• Named functions define reusable calculations and arguments. They are not a table column or a cell label.

A fixed named range should not be substituted for a table reference on the assumption that it has the same growth behavior. Test any expanding dataset explicitly.

Troubleshooting and safe recovery

An SKU column is set to Number: Inspect its actual values against the source first, then change the column type to Text. Do not claim the type change repairs earlier numeric conversion.

The column menu shows filtering controls: Use the column-menu control before looking for Edit column type; Google’s help distinguishes this from the filter menu.

The long SKU is visually abbreviated: Inspect the value and exact comparison rather than judging by column width. Widening a column can reveal a display issue but cannot reverse lost information.

An external import supplies mixed values: Reconcile the original characters in a staging area before adding records to Inventory. Do not accept mixed values merely because None suppresses the table’s type expectations.

You want to remove the table styling: Google distinguishes Revert to unformatted data, which leaves data, from Delete table, which removes its data too. Work in a copy when trying either. Table menu choices

Table views and group-by views are available for organizing records, but neither substitutes for verifying identifier type and exact content.

Testing disclosure: Google documentation reviewed October 16, 2026; sample string extraction and totals independently checked. This tutorial has not been tested in Google Sheets. Column-type warnings, table conversion and table-reference growth remain application checks.