Excel Table Styles: How to Apply and Customize Them

An Excel table style is a coordinated set of formatting applied to an Excel table, such as a colored header row and alternating row bands. To apply one in Excel for Microsoft 365 on Windows, click a cell in the table, open the Table Design tab, and choose a style from the Table Styles gallery; if the data is only a regular range, first select it and choose Insert > Table.

What an Excel table style does

A table style controls the visual appearance of a table. Depending on the selected style and its options, it can format the header row, data rows, first or last column, and total row. Styles commonly use a theme’s colors, so a workbook’s tables can have a consistent look without manually coloring individual cells.

There is an important distinction between a styled range and an Excel table. Applying a fill color, borders, or a built-in cell style to a range changes its appearance, but does not give it the table features described here. A real Excel table has a name, structured column headers, filter buttons, and behavior that helps it expand as you add data. Applying a table style changes the table’s look; it does not by itself turn an ordinary range into a table.

For example, a sales list with columns for Date, Region, and Amount can be converted into a table and given a blue banded style. The formatting makes rows easier to follow, while the table features make it easier to sort and filter the records. These are related benefits, but they are not the same thing.

How to create a table and apply a style

In Excel for Microsoft 365 on Windows, check that the data has a header row and does not contain blank rows or columns within the intended table. Then follow these steps:

Select a cell in the data, or select the complete data range.
Choose Insert > Table. The Create Table dialog shows the range Excel intends to use.
Check the range and select My table has headers if the first row contains column names. Click OK.
Click a cell in the new table to display the contextual Table Design tab.
Open the Table Styles gallery on that tab and click a style. Use the gallery’s More button to see additional choices.

On Windows, Ctrl+T is a shortcut for opening the Create Table dialog when a suitable range is selected. On Mac, the equivalent shortcut is Command+T. In Excel for the web, you can use Insert > Table; the available interface and shortcut behavior may differ from the desktop apps.

If the data is already an Excel table, skip the creation steps. Select any cell in it and choose another style in Table Design > Table Styles. The new appearance is applied to the table without replacing its contents or changing its formulas.

What the table style options control

The style gallery is only part of the formatting control. In the Table Style Options group on the Table Design tab, checkboxes let you turn certain table features on or off. Options can include Header Row, Total Row, Banded Rows, First Column, Last Column, and Banded Columns. The exact display can vary with the window size or Excel version.

Header Row: Displays and formats the column headings. A header row is useful for identifying fields and using the table’s filter controls.
Banded Rows: Alternates formatting across data rows, which can help readers track a record across a wide sheet.
First Column and Last Column: Emphasize the edge columns, such as a label column or a final total, when the chosen style provides that treatment.
Total Row: Adds a summary row at the bottom of the table. Its cells can offer summary calculations, such as a sum or average, selected from the cell’s drop-down menu.
Banded Columns: Alternates formatting by column rather than by row. This can help distinguish fields, though it may be distracting in a wide table.

These checkboxes are separate from the style itself. If a style appears not to show alternating rows, for example, check whether Banded Rows is enabled. Some styles also use subtler differences in color, so the visual effect may not be dramatic.

How to choose a table style

Choose a style for readability and the way the worksheet will be used, not just for color. A pale style with a clear header often works well for a table that will be printed or viewed for long periods. Stronger contrast can help a dashboard or presentation, but dark fills may use more ink and make text harder to read if the font color is not suitable.

Consider the following when choosing:

Contrast: Make sure the header and body text remain legible, including for readers viewing the sheet on a small screen.
Row tracking: Banded rows are especially useful when a table has many columns or records.
Consistency: Use a small, consistent set of styles across related sheets instead of giving every table a different visual treatment.
Existing workbook theme: Theme-based styles can coordinate with other workbook colors. If the workbook theme changes, theme colors may also change.
Printing: Preview the sheet before printing. A style that looks clear onscreen may need adjustment for grayscale printing or limited page width.

For a small inventory list, a light style with banded rows may be enough. For a report with a prominent final column, a style with a distinct last-column treatment could make that field easier to spot. The options are visual aids, not a replacement for descriptive headings or clear data organization.

Customizing a style or its appearance

You can change a table’s style at any time by selecting it and choosing another entry from Table Design > Table Styles. You can also adjust the style options independently. For instance, a reader may prefer the same blue style with Banded Rows turned off, or may want a Total Row shown for a quick summary.

For more control, Excel desktop for Windows includes a New Table Style command in the Table Styles gallery. It lets you define formatting for table elements such as the header row, first column, or whole table. The dialog provides a preview and formatting controls. Custom style availability and editing options can differ across platforms, so a custom style created in desktop Excel may not be equally easy to create or modify in Excel for the web.

Directly formatting cells in a table is also possible, but it can make the appearance less consistent with the chosen style. If a manually formatted cell does not look as expected, reapplying a table style or reviewing the relevant Table Style Options may help. For a one-off emphasis, such as highlighting a late payment, conditional formatting may be more appropriate than changing the table’s general design.

What changes when the table grows

One practical advantage of a real Excel table is that it can extend its formatting when you add data directly below it or enter data in the adjacent row or column. New rows generally inherit the table’s style and table behavior. If Excel does not include new data as expected, check that there is no blank row or column separating it from the table and confirm that it is inside the table boundary.

Table styles do not determine what the data means or how it is calculated. They also do not guarantee that every new row has the right number format, formula, or validation rule in every situation. Review new entries, especially when pasting data from another workbook or application. Pasted formatting can produce an appearance that differs from the table’s style.

When you click a table’s Total Row cell, Excel provides a menu of summary choices. The displayed result depends on the choice and the data in that column; for instance, a count is not the same as a sum. If you later turn off the Total Row option, the summary row is removed from the table display. Use it as a convenient table summary, and verify that the selected calculation matches the question you want answered.

Table styles versus regular cell formatting

Use a table style when the data is a structured list that benefits from headers, filters, and automatic expansion. Use regular cell formatting when you only need to change the appearance of a range, such as coloring a worksheet title or laying out a form that is not a list of records.

Cell styles, found on the Home tab, are another distinct feature. They apply predefined formatting to selected cells, but do not convert those cells into a table. Likewise, manually adding borders and fills does not create filter buttons or table-specific behavior. If you need a sortable list with a consistent appearance, create a table first and then choose its style.

A table can be converted back to a normal range using Table Design > Convert to Range in desktop Excel. The data remains on the sheet, but table functionality is removed. The existing formatting may remain as ordinary cell formatting, so converting to a range is not the same as clearing the style.

Common table-style problems

If the style gallery seems unavailable or the Table Design tab is missing, select a cell inside the table. Contextual table tools appear when a table is selected; they do not appear for an ordinary range. If the selected data has no table features, create a table with Insert > Table before looking for table styles.

If the wrong row became the header, check the My table has headers setting during creation. You can also review the table’s column headings and correct them. Excel needs column headers to identify fields; generic or blank headings can make the table harder to understand and may be replaced with default names.

If stripes or a highlighted total are not visible, inspect the relevant Table Style Options checkbox and try another style with more obvious contrast. If the table looks inconsistent after pasting, pasted cell formatting may be competing with the table’s appearance. Reapply the desired style and check the result, taking care not to remove formatting that carries useful meaning.

Finally, do not assume that selecting a colorful style changes the values, formulas, or sort order. A table style is presentation formatting. Sorting and filtering are separate table tools, and a table’s data should still be checked for accuracy regardless of how polished it looks.

Frequently Asked Questions

How do I find table styles in Excel?
Select a cell inside an Excel table, then open the Table Design tab and look in the Table Styles gallery. If the tab does not appear, the selected range may not be a table.

Can I apply a table style without creating a table?
No. Table styles apply to Excel tables. For an ordinary range, use formatting on the Home tab, or create a table with Insert > Table if you want table features as well as a table style.

Can I change a table style after creating the table?
Yes. Select a table cell and choose a different option under Table Design > Table Styles. Changing the style does not require recreating the table.

How do I remove a table style but keep the table?
Open the Table Styles gallery and choose the option for clearing the table style, if available in your Excel version. The table remains a table, so its headers, filters, and other table features can remain. You can also select a plain style for a minimal appearance.

Why are my table rows not alternating colors?
Check that Banded Rows is selected in the Table Style Options group on the Table Design tab. Also try a style with clearly contrasting row bands; some styles use very subtle shading.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.