Excel Table Styles & Formats

Updated on December 27, 2023
Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel Table Styles & Formats?

Excel Table Styles & Formats is a feature in Excel that is used on datasets in a worksheet to make it more presentable in a colorful or organized way.

Excel provides the Table Styles & Formats in Excel in various inbuilt formatting styles. However, we can create our own style formats and save them for future use.

For example, select a dataset, and press “Ctrl+T” to create a table for which we can add table styles. Then, click the “Table Design” tab, and select the desired style, as shown below.

Excel Table Styles - 1

The final table will look as shown in the above image.

Key Takeaways

  • The Excel Table Styles & Formats is an amazing option in Excel to make the data more organized, presentable, or easily understood by the audience during a presentation.
  • It helps us highlight the data as per our requirement, alternate rows from row 1, or we can start highlighting from the headers and then alternate the rows.
  • We can copy the Excel Table Styles & Formats using the “Format Painter” option, and the “Conditional Formatting” option too.
  • If the “Table Design” tab is not found on the ribbon,
    • Check if we have converted the initial dataset to a table using the “Ctrl+T” shortcut.
    • Ensure to click on any cell on the created table’s dataset to activate the tab.

How To Create And Format The Table Styles In Excel?

We can create and format the table styles in Excel as follows:

  • First, choose the entire dataset.
  • Next, pressing the shortcut keys “Ctrl+T”.
  • The “Create Table” window appears with the selected cell range. Here, check/tick the “My table has headers” checkbox, and click “OK”, as shown below. [It will automatically create a table without altering the values.]
How to create and style tables in Excel - 1
  • Now, we will see a new tab, i.e., the “Table Design” tab on the Excel ribbon. So, select the “Table Design” tab → go to the “Table Styles” group → click the “Quick Styles” option drop-down, as shown below.
How to create and style tables in Excel - 2
  • Finally, select the table styles or the format as desired from the “Light, Medium, Dark” categories, as shown below.
How to create and style tables in Excel - 3

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

We will consider some examples to create an excel tableCreate An Excel TableIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more and format the table styles.

Example #1 – Creating the Table

Let us start the process by creating the table. We have a simple sales table for ten years, given below.

Change the Table Style step 1

The procedure to create a table are as follows:

First, place a cursor inside the data, and press the “Ctrl + T” (shortcut key to create tables). We will see a “Create Table” box, check/tick the box “My data has headers”, and click “OK”.

Change the Table Style step 2

Now, we have our table created. It looks as shown below.

Change the Table Style step 3

Example #2 – Change the Default Format of Table Style in Excel

Excel applied one of the default table styles in Excel. In Excel, we have many inbuilt table styles to select. We can change the existing table style to Excel at any time.

The steps used to change the default format of the table style in Excel are as follows:

  1. We must first place a cursor inside the table. As soon as we select one table cell, the “Design” tab will appear on the ribbon.


    Default Table Style step 1

  2. Under the “Design” tab, we have many inbuilt table styles to select.


    Default Table Style step 2

  3. Then, we must click the “Table Styles” drop-down list. We can see three categories: “Light,” “Medium,” and “Dark”.


    Default Table Style step 3

  4. We can select any one of them to change the existing table style. If we hover over a specific table style, Excel shows its preview immediately.

Example #3 – Create Your Own Table Format Style in Excel

We are not restricted from working with in-built table styles. Rather, we create our table style to satisfy ourselves. Under “New Table Style”, let us see how to design our new table style.

Create Table Format step 1

Now, we must click the “New Table Style”.

The “New Table Quick Style” window appears, as shown below.

Create Table Format step 2

The first thing we need to do is to give a table name. Therefore, we have provided the name “My Table Style” in the “Name:” field, as shown below.

Create Table Format step 3

Under the “Table Element”, we must select the topic we want to modify. Here, we have chosen the “Header Row”, and then click the “Format” button, as shown below.

Create Table Format step 4

The “Format Cells” window appears. Here, select, “Font” as “yellow”, “Font Style” as “Bold”, and “Underline” as “Double”, or as per our desire, as shown below.

Create Table Format step 5

Next, we must go to the “Fill” tab, select the “Background Color:”, here, “Green”,and click “OK”.

Create Table Format step 6

Finally, we have created a style for our “Header Row”, and we can see the preview.

Create Table Format step 7

Under “Table Element”, select the “First Column.”

Create Table Format step 8

Again, click “Format.” Then, go to “Font”, choose the color “black,” and in “Font Style,” “Bold.”

Create Table Format step 9

We must go to “Fill”, select the “Background Color” as “light red”, and click “OK”.

Create Table Format step 10

Now, we can see the preview.

Create Table Format step 11

We have created our table format in Excel. Next, we must apply and see the table style in Excel. Then, under the “Design” tab, click on the drop-down list of “Table Styles.” Firstly, we may see the “Custom” table created, and select that table style.

Create Table Format step 12

Now, we have applied our table format style in Excel, and our final table style is shown below.

Create Table Format step 13

Important Things To Note

  • We must ensure that the “My data has headers” checkbox is ticked while creating a table.
  • While designing our table style, we need to prepare each section separately.
  • We can play with many formulas by applying the “Total Row” option.
  • We must always give a table a name because it is easy to find which tables we are working on.

Frequently Asked Questions (FAQs)

1. How to Remove Excel Table Styles & Formats?

We can remove the selected Excel Table Styles & Formats as follows:

First, click on any cell of the table to activate the “Table Design” tab.

Next, select the “Table Design” tab → go to the “Table Styles” group → click the “Quick Styles” option drop-down, as shown below.

Excel Table Styles - FAQ 1 - 1

Finally, click the “Clear” option seen at the end, to remove the table style.

FAQ 1 - 2

2. What are the advantages of Excel Table Styles & Formats?

A few advantages of Excel Table Styles & Formats are,

When that data is presented at any conference, the audience can easily comprehend.
We can set the desired table style, or create one as per our points of highlight.
We can save the created custom table style for future use, which saves time by redoing it again for another dataset.

3. How to create Excel Table Styles & Formats without using the shortcut key?
We can create the table without the shortcut as follows:

First, select a cell range where we want the table to appear.

Next, select the “Home” tab → go to the “Styles” group → click the “Format as Table” option drop-down → select any style format, as shown below.

Excel Table Styles - FAQ 3

4. Is there any other way to copy the Excel Table Styles & Formats?

Yes, we can copy the Excel Table Styles & Formats using the “Format Painter” option as follows:

First, select the entire created table style, as shown below.

Excel Table Styles - FAQ 4 - 1

Next, select the “Home” tab → go to the “Clipboard” group → click the “Format Painter” option, as shown below.

FAQ 4 - 2

Finally, go to any cell, here, cell H1, and click on the cell. Immediately, the table style will appear, as shown below.

FAQ 4 - 3

Download Template

This article must help understand Excel Table Styles & Formats with its formulas and examples. You can download the template here to use it instantly.

You can download this Table Style Excel Template here – Table Style Excel Template

This article has been a guide to Excel Table Format & Styles. Here we use existing format, create new styles, save for future, examples, downloadable excel template. You may also look at these useful functions in Excel: –