Learn how to change table style in Excel to acquire the required skills to present your Excel worksheet in a professional way that the audience can easily read.

Excel Table Styles and Table Styles Options enable you to present data as tables in various ways. You can add sort and filter, total rows, structured references, alternative colored rows, calculated columns, and so on with the click of a button. No need to go through the manual process of using Fonts, Styles, Editing, etc., tools individually to appear at the same result that Excel can do better.

Not to mention, you can customize these built-in Excel Table Styles to meet your worksheet design requirements or create a custom Excel Table Style from scratch. Then, you can use it as the default Excel table formatting for the whole worksheet or workbook.

There are a lot more than just these options. You can use advanced automation to manage one or different Excel table styles across the workbook. So, keep reading this article to learn all the tricks of changing table styles in Excel.

Also read: Everything You Need to Know About Excel Tables

Reasons to Change Table Style in Excel

Find below why you might need to learn how to change one table style to another in Excel:

• Changing the table style can make data easier to read and understand, especially when using different colors, fonts, and formatting options.
• Different table styles can enhance the visual appeal of your spreadsheet, making it more engaging and professional-looking.
• Applying varying styles can help emphasize certain data points, such as highlighting important values or trends.
• Using consistent table styles across multiple worksheets or workbooks creates a cohesive and unified appearance.
• By utilizing different styles for headers, subheaders, and data cells, you can create a clear hierarchy within your table.
• Changing table styles allows you to customize the appearance of your data to suit your preferences or match your organization’s or client’s branding policies.
• When presenting or creating reports, using appropriate table styles can make your data more presentable and accessible to your audience.
• Certain table styles can make it easier to visually analyze data patterns and relationships, aiding in decision-making.

Also read: 7 Ways to Make a Table in Microsoft Excel

Now that you’ve learned the basis for using Excel Table Styles, find below various methods to modify Excel table formatting effortlessly:

From the Home Tab (Format as Table)

The basic way to apply table formatting to a range of data or change an existing table style is the Format as Table command in the Styles block on the Excel Home tab. Find below how it works with real Excel datasets:

1. To change the table formatting of the existing dataset, click any cell on your Excel worksheet.
2. Now, go to the Home tab on the Excel ribbon menu.
3. Navigate to the Styles command block.
4. There, you must click the Format as Table drop-down arrow.
5. Choose any table style you prefer from three categories: Light, Medium, and Dark.

6. The Create Table dialog will pop up.
7. Now, highlight the cell range or table to which you’d like to apply the new table style.
8. Checkmark the My table has headers checkbox to format the header rows as well.
9. Click OK to apply the new table style.

The above image shows the altered Excel table shown in this tutorial.

Also read: 3 Ways to Copy Multiple Tables to One Table in Microsoft Excel

From the Table Design Tab

Format as Table is nothing but a glimpse into a more expansive Excel table management tool known as the Table Design tab. Unlike the general Excel ribbon tabs like Home, Insert, Formulas, etc., the Table Design tab isn’t readily visible as you open the Excel application.

You must insert a table into the worksheet to view the Table Design menu. This menu has three important tools that you can use to modify an existing Excel table dataset. These are Table Styles, Table Style Options, and Insert Slicer.

Find below the ways to alter your Excel tables using these tools:

How to Use Table Styles

1. Click on any cell of the existing table.
2. The Table Design tab will open automatically.
3. Go to the Table Styles block.

4. Click the Quick Styles drop-down arrow.
5. You should now see pre-configured table styles in Light, Medium, and Dark themes.
6. You can choose any from the list.

7. If you’re looking for something unique, click the New Table Style button at the bottom of the Quick Styles menu.
8. Here, you can choose from a list of 13 different table elements for changing table style in Excel.
9. Chose a Table Element in the list and click Format to modify the selected item.
10. On the Format Cells dialog box, you can customize several table constituents like Font, Border, and Fill.

How to Use Table Style Options

1. Select the table and go to the Table Design menu.
2. Go to the Table Styles Option block.
3. By default, three items will be active here and these are Header Row, Filter Button, and Banded Row.
4. You can add additional Table Styles Options like Total Row, First Column, Last Column, and Banded Column.

The above image is an example of a modification of Excel table formatting using Table Style Options. Table A is the original table and Table B is the transformed copy after using a Total Row, First Column, and Banded Column.

How to Use Slicer

Excel Table Slicer allows you to create interactive buttons to display a specific set of data selected into the Slicer tool.

It also changes the table style heavily since it includes a new object in the worksheet and reduces the size of the existing table when you slice it. Here’s how to use Slicer on tables:

1. Highlight any cell of the table and go to the Table Design menu.
2. Inside the Tools commands block, click on the Insert Slicer button.

3. The Slicer tool will automatically analyze the table and show possible slicing options by columns.
4. Click a column and click OK.

5. The Excel Table Slicer will show up on the worksheet.
6. Click an entry on the Slicer object to show a specific dataset on the table.

Also read: 5 Ways to Remove Table Formatting in Microsoft Excel

From the Review Tab (Accessibility > Format as Table)

Suppose the existing table formatting isn’t suitable for a visually challenged audience. So, you need to apply a different table style.

For your assistance, Excel added the Format as Table feature also into the Check Accessibility command on the Review tab. Here’s how you can use it:

1. Highlight a table or a single cell of the table.
2. Click the Review tab.
3. Go to the Accessibility section and click the Check Accessibility button.
4. On the context menu that drops down, hover the mouse cursor over the Format as Table option.
5. In the overflow menu, you’ll see the Quick Styles menu for table formatting.
6. At the bottom, you also get the New Table Style menu.

Change Table Style Using Excel VBA

loop through all table objects in the workbook and change the style

If you want to automate the table formatting modification process in Excel, you can use Excel VBA. Find below the script you can use along with the instructions to create a VBA macro using the code:

1. Call the Excel VBA Editor by pressing the Alt + F11 keys.
2. On the Excel VBA Editor click the Insert button.
3. Select the Module option.
4. Inside the new blank module, copy and paste the following Excel VBA script:

Sub ApplyTableStyleToAllTables()
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through all tables (ListObjects) in the worksheet
        For Each tbl In ws.ListObjects
            ' Apply the desired table style
            tbl.TableStyle = "TableStyleMedium6"
        Next tbl
    Next ws
End Sub

5. Click the Save button to save the script.
6. Click the close button to close the Excel VBA Editor.

Now that you’ve created a VBA macro, here’s how you can execute it:

1. Hit the Alt + F8 keys to open the Macro dialog box.
2. Select the ApplyTableStyleToAllTables macro.
3. Hit Run to execute the code.

The above VBA script will apply the Blue, Table Style Medium 6 formatting to all the table objects in the workbook. If you wish to use another table style, simply change the table formatting code "TableStyleMedium6" with the one you prefer.

Suppose, you don’t want to apply the same table style to all the table objects in your Excel workbook. Instead, you’d like to apply different table formatting to different worksheets. Then, use the following Excel VBA script. In this code, you can manually change the sheet reference within the script:

Sub ApplyTableStyleToSpecificSheet()
    Dim targetSheet As Worksheet
    Dim tbl As ListObject
    
    ' Set the target worksheet reference
    Set targetSheet = ThisWorkbook.Worksheets("Sheet3") ' Replace "SheetName" with the actual sheet name
    
    ' Check if the target sheet exists
    If Not targetSheet Is Nothing Then
        ' Loop through all tables (ListObjects) in the target worksheet
        For Each tbl In targetSheet.ListObjects
            ' Apply the desired table style
            tbl.TableStyle = "TableStyleMedium5"
        Next tbl
    Else
        MsgBox "Sheet not found!"
    End If
End Sub

Change Table Style Using Office Scripts

Another automation feature for Excel is Office Scripts. It enables you to run the same automation script on the Excel for Microsoft 365 desktop and Excel for the web app. Find below the quick steps to use an Office Scripts code to change table style in Excel:

1. Click the Automate tab.
2. Click the New Script button.
3. In the Code Editor that pops up, copy and paste the following Office Scripts code:

function main(workbook: ExcelScript.Workbook) {
    // Get all tables in the workbook
    let tables = workbook.getTables();

    // Loop through each table and change the table style
    for (let i = 0; i < tables.length; i++) {
        let table = tables[i];
        table.setPredefinedTableStyle("TableStyleDark5");
    }
}

4. Click the Save script button.
5. Hit Run to execute the code.

The above script will automatically change the table formatting of all the table objects in the workbook to Table Style Dark 5. If you want to use any other table formatting style, replace the "TableStyleDark5" code element with that specific style code.

Note: Office Scripts is currently available with Microsoft 365 Business Standard or a better subscription plan. Also, the IT admin of your company must enable your Microsoft 365 profile to use Office Scripts programming on Excel for Microsoft 365 and Excel for the web app. However, if you’re an individual using the same paid subscription, Office Scripts should be active by default.

How to Set a New Default Table Style

A new default table style or custom table style signals Excel to always use that table formatting whenever you select a cell range and press Ctrl + T, the shortcut to insert a table.

When your Excel worksheets require stringent branding and formatting as per the client or business organization’s rules, you set a custom table style as a default table formatting.

Find below the steps to create a custom table style and set it as the default table style for your Excel workbook:

1. Suppose, you already selected a table style from the Quick Styles list and now you need to customize it. Click anywhere on that table.
2. Now, open the New Table Style dialog box. Refer to the steps mentioned earlier in this Excel tutorial.
3. Then, select table elements and click Format for each to customize the elements.

4. All the formatting takes place on the Format Cells dialog box since you’re effectively customizing the cell styles.
5. On the Format Cells dialog box, click OK once done.

6. You now see the preview of the newly created table style in the Preview section on the New Table Style dialog box.
7. Checkmark the Set as default table… checkbox.
8. Click OK to close the New Table Style window.

9. You should now see the newly-created table formatting in the Custom section of the Quick Styles menu.
10. Select the style to format the existing table.
11. When you create a new table in the future in this workbook, Excel will use this custom table style automatically.

Conclusions

The ability to change table style in Excel is an indispensable data visualization skill. If you’re into data analysis and visualization, you must learn this skill and practice regularly to improve.

Find above the best ways to modify table styles in Excel using Excel application interface buttons like Format as Table, Table Design tab, New Default Table, etc.

Also, you can automate and accelerate the process for a large workbook by using the Excel VBA and Office Scripts-based methods.

Follow along with the methods explained so far and comment below the one you liked the most. Also, if you know another best method to achieve the same, don’t hesitate to mention it in your comment.