Skip to content

How to Format Excel Tables

When using Microsoft Excel, one of the most powerful features to capitalize on is the Excel Table.

An Excel Table is simply a defined range within your spreadsheet. This kind of table makes it easier to manage and analyze groups of related data. To learn more about what makes up an Excel Table, check out our post, “Turn the Tables.”

In this article, we will focus on how to design and format your Excel Tables so that they are much easier to read and use.

Also, one of the best ways to learn is through action, so this post will present an example of how to go through the process of formatting an Excel Table.

How to Format Example

When you first create an Excel Table, it is usually shown in the default style depicted below.

To change the format of this Excel Table, you start by creating a new custom Table Style. To create a new style, begin by selecting any cell in your Table. Next, click on the down arrow on the right side of the Table Styles group under the Table Design tab in the Excel Ribbon.

Then select New Table Style.

The New Table Style dialog box appears.

The first thing you should do is give a new name to your table style.

Next, under Table Element, select Whole Table and then click the Format button.

As the Format Cells dialog box appears, click on the Border tab.

Under color, change the color to White, Background 1, Darker 25%. (See image below)

Next, click the Outline button to apply a border of your selected color to the outside edges of the Table, and then click Ok.

Back in the New Table Style dialog box, select Second Row Stripe as the new Table Element and click on the Format button once more.

Click the Fill tab in the Format Cells dialog box, and then click on More Colors.

Click on the Custom tab in the Colors dialog box.

Here you can select or type in the RGB or Hex value to specify an exact color of your choosing. For this particular style, type in a Hex value of #F7F7F7.

This value represents a very light gray color. Go ahead and click Ok, and then click on Ok once more.

The last thing to format is the header row of the table. Select Header Row for the Table Element, and then click the Format button one more time.

Click on the Fill tab in the Format Cells dialog box and then select the color White, Background 1, Darker 5% indicated in the image below.

 

Next, click on the Font tab, and change the Font Style to Bold.

Finally, click on the Border tab, select the color White, Background 1, Darker 25%, and then click the Outline border button.

You can then click Ok. Finally, click Ok once more to finish creating your new Table Style.

Now that you’ve created your new Table Style, all that remains is to apply it to your Excel Table. To apply your new Table Style, start by selecting any cell in your table, and under the Table Design tab in the Excel Ribbon, click on the down arrow on the right side of the Table Styles Group.

You should now see your new Table Style listed under the Custom category.

Click on your new Table Style to apply it to your Excel Table.

The very last step is to turn off the gridlines by going to the View tab in the Excel Ribbon and uncheck the box next to Gridlines.

You now have a nicely formatted Excel table to work with going forward. And that’s how you format an Excel Table in Microsoft Excel.

This Post Has One Comment

  1. This is so cool! Let me be the first to say that I underestimated the power of Excel Tables. One of the reasons why I never used it was the ugly formatting! Thanks for making tables more appealing with this post! I appreciate the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top