Apply Table Styles and Convert Tables to Ranges

Why styles and convert-to-range matter on MO-210

Still under Create and format tables in Domain 3, Certiport expects you to Apply table styles and Convert tables to cell ranges. These are separate skills: one changes how the table looks while it remains a table; the other dissolves the table object so you are left with a normal range—often still painted with the style's colors and borders.

Typical task language: "Apply Table Style Medium 2 to the table", "Apply the Light Blue table style", "Convert the table to a range", or "Convert the Inventory table back to a normal cell range." Success depends on selecting inside the table, using Table Design, and understanding what conversion keeps versus what it removes.

Apply a table style

1. Click any cell inside the Excel table so the Table Design contextual tab appears.
2. Open the Table Styles gallery (Large gallery in the Table Styles group; use the More button to expand).
3. Hover thumbnails to read the style name in a ScreenTip (for example Table Style Light 14, Table Style Medium 7).
4. Click the style the project names.

The style applies immediately to the whole table—headers, banded rows, and accent colors update together. You do not need to select every row first; one active cell inside the table is enough.

Style galleries and naming on the exam

MOS descriptors often use Microsoft's gallery names:

• Light styles — subtler banded rows, lighter header fills
• Medium styles — stronger header contrast and clearer banding
• Dark styles — heavy header fills and high-contrast bands

If the task says "Table Style Light 14", expand the Light section of the gallery and match that exact name—do not guess a Medium style that merely looks similar on your monitor. Hover until the ScreenTip matches the descriptor. Theme colors can shift absolute RGB values across workbooks, but the named style is what graders and instructions reference.

Home → Format as Table can create a table and apply a style in one step. If a table already exists, prefer Table Design → Table Styles so you do not accidentally create a second table or disrupt the existing object.

Table Style Options vs the style gallery

Beside the gallery, Table Design includes check boxes such as Header Row, Banded Rows, First Column, Last Column, Banded Columns, and Total Row. Those options modify how the chosen style paints the table; they are not substitutes for picking a gallery style. A later Domain 3 section covers configuring those options in depth. For this skill, focus on selecting the correct gallery style. If a task only says "apply Table Style Medium 9", change the gallery selection; leave option check boxes alone unless instructed.

Convert a table to a cell range

Convert to Range turns a table back into a plain worksheet range.

1. Click any cell in the table.
2. Go to Table Design.
3. In the Tools group, click Convert to Range.
4. Confirm when Excel asks whether you want to convert the table to a normal range.

After conversion:

• The Table Design tab no longer appears for that block.
• Filter arrows from the table disappear (the range is no longer a table; AutoFilter is not automatically left on).
• Structured references in formulas that pointed at the table may convert to ordinary A1-style references or need review if anything breaks.
• Auto-expansion stops—typing below the block does not grow a table that no longer exists.
• Total Row table feature is gone; any total values that were sitting in cells may remain as static values/formulas depending on state at conversion.

Keep formatting implications

Excel keeps the visual formatting that the table style had applied—fills, fonts, and borders often remain on the cells so the sheet still looks "styled." That is intentional: Convert to Range is how you remove table behavior while preserving a polished appearance for printing or for users who should not get filter arrows and structured references.

Implications for the exam:

Do not assume conversion clears all formatting. If a later step says "Clear formatting" on that range, that is a separate Home → Clear command. Conversely, do not use Clear Formats when the task only asked to convert to a range—you would strip the look the project may still want.

When projects ask for both style and convert

Order matters. Common patterns:

1. Create table → apply style → leave as table — stop after the gallery click.
2. Create table → apply style → convert to range — style first so the kept formatting matches the requested look, then Convert to Range.
3. Convert without a style change — conversion keeps whatever style was already on the table (often the default from creation).

If you convert first, you can no longer use Table Design → Table Styles on that object; you would be formatting a plain range with cell styles or Format Cells instead, which is usually the wrong tool for a table style instruction.

Convert to Range vs deleting the table another way

• Convert to Range — Correct MOS command; keeps data and usually keeps style formatting.
• Clearing the sheet / deleting rows — Destroys data; not equivalent.
• Copying values elsewhere and deleting the table sheet — Overkill and risky under timed conditions.
• Ungrouping or unmerging cells — Unrelated; does not remove table objects.

If the descriptor says "convert the table to a range" or "convert to a normal range of cells", use Convert to Range only.

Verifying your work

After applying a style:

• Header fill and banded row pattern should match the named gallery style.
• Table Design should still be available—proof the object is still a table.

After converting:

• Click inside the former table: Table Design should be gone.
• Data values should still be present in the same cells.
• Visual banding from the last style often remains as regular cell formatting.

Common exam pitfalls

• Applying a cell style from Home → Styles when the task named a table style.
• Choosing a Light style when the task specified Medium (or the reverse) because the colors looked close enough.
• Converting to a range before applying the required table style—Table Styles gallery becomes unavailable.
• Using Data → Filter after conversion and thinking you recreated a table—you only added AutoFilter to a range.
• Clearing formats after conversion when the project wanted the styled appearance retained.
• Selecting cells outside the table so Table Design never appears, then hunting the wrong Ribbon tab.

Practice checklist

1. Create a small table, apply Table Style Medium 2, confirm the ScreenTip name, then switch to a Light style and back.
2. With Medium 2 applied, Convert to Range; confirm Table Design disappears but fills remain.
3. Recreate a table on the same block, apply Light 14, convert again—build muscle memory for style-then-convert sequences.
4. Intentionally select a cell outside the table and notice Table Design hides; click back inside before changing styles.

Those drills cover apply-style and convert-to-range for MO-210 Domain 3.