Excel Table Formatting Tips – Change the Look of the Table
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.
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.
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:
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 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.
Read More: Formatting Excel Pivot Tables in 7 Ways!
Table of Contents
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.
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.
Lets 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.
Also, I am not going to use any border so I will uncheck that.
Now open the Fill tab, I choose this (the following image):
You can see a preview too here. I click OK.
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.
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.
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.
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 with 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 check boxes. 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.
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.
If I select the “First Column” control, First Column will be in Boldface.
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.