Excel Table Formatting Tips – Change the Look of the Table

Get FREE Advanced Excel Exercises with Solutions!

When we create a table, Excel applies the default table style. Say I want to make the following range into a table. I just select any cell within the range. Press CTRL and T on my keyboard. “Create Table” dialog box appears, I click ok. This is the default style (on the left of the following image) applied to a newly created table.

Default Excel Table Formatting

But you can change the Default Style that will be applied to a table when it is created. In today’s post, we will discuss how to modify this style.

Select any cell within the table. “Table Tools Design” contextual tab appears. Click on the Design Tab. You see: the “Table Styles” group shows a row of styles. Move your mouse pointer over the Styles, you will get live previews of the Styles immediately.

Excel Table Formatting Tips Image 1

You can scroll down or up to see the other styles using the scroll bar and mouse pointer.

To get a full view of all the Styles, just click on the “More” drop-down below the down scroll bar. “Table Styles” group expands and you see all the pre-built styles for your table. There are three groups here:

Table Formatting Options

Light, Medium, and Dark. When you find a style that will make your table look better, just take your mouse over that style; see the live preview and if it is okay, you just click on it to apply it to your table. And you see the style of the table has been changed immediately.

You can add more Styles to this list in three ways.

You can create your own table style

From the aforementioned “More” drop-down menu, click on the “New Table Style” command. The “New Table Style” dialog box appears. You can use a name for your table style in the Name Field, by default it will show “Table Style 1”. I’ve entered “ExcelDemy1” in the Name field. You see a list of table elements in the Table Element window.

Creating your own table formatting style

You can change the format of any Table element. For example, you can change the format of the whole table, or just the format of the first column stripe or second column stripe or first row stripe and so on.

Let’s give you an example. I select the Whole Table, click on the Format button, “Format Cells” dialog box appears, I choose Strike Through as the effect, Font Style is Bold Italic.

Format Cells Dialog Box

Also, I am not going to use any border so I will uncheck that.

Excel Format Cells Dialog box. Border

Now open the Fill tab, I choose this (the following image):

Fill Effects Dialog Box

You can see a preview too here. I click OK.

New table Style preview

You can set your this “Table Style” (newly created table style) as the Default Table Style for all newly created table.

Now click again on the “More” drop-down, you see my created Table Style is placed under Custom list.

Custom Table Formatting

Now I know it’s gibberish (nonsense), no one can even read it. Let’s delete this table style.

Right-click on the Style and press Delete from the list. And Click OK.

Delete Table style

Note: Any built-in Table Styles cannot be deleted/modified

You can modify an existing table style

You can still customize them (built-in table styles) by using the Duplicate command in the list. Excel opens the “Modify Table Style” dialog box. You can change the Name of the Style in the Name field. You will see some of the Elements like we did the last time while creating a new style altogether and so on.

You can change the document theme to get more styles

The last way to get different color table styles under this list is using the Themes dropdown in the Page Layout Ribbon.

Excel Themes

We shall be discussing in more detail later how you can build your own theme for your Excel document.

Adding or Deleting Elements to Excel Tables

You can do more changes to your table by using some commands in the Table Tools Design contextual tab. Just click on any cell within a table. Design contextual tab appears. Click on the Tab. In the “Table Style Options” there are some checkboxes. Header Row is right now selected. If check out this option, table headings vanish; again I check in this Header Row option, we get back the heading of the table.

Adding and deleting elements to Excel Tables

If I check in the “total row” option, a new row appears below the table as we saw in the last post. There are many uses of this Total row.

Observe the next control Banded row. If I deselect it, the zebra orientation vanishes from the table. You can use Banded Columns that will implement zebra orientation across the columns.

Banded Rows in Excel Tables

If I select the “First Column” control, First Column will be in Boldface.

Boldfaced First Column of Excel Table

You can modify all of these options in the “Modify Table Style” dialog box. You know how to open the Modify Table Style dialog box and work with it. Filter Button option is selected. If you deselect this control, no filter buttons will be showing on the table. Click again to activate the Filter Button option.

So you see this is how you can give your table a makeover to match your company signature color palette or to connect with the topic more or maybe to do away with your boring regular tables whatever be your reason, hey we don’t judge! We are here to help. Do leave us in a comment your queries and feedback.

Thanks for the read stay tuned so some more tabular awesomeness in Excel.

Further Readings


Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF