What Is Excel Table Styles & Formats?
The Excel Table Styles & Formats are options to make Tables visually more presentable. And we can highlight trends by formatting specific rows and columns in a Table, which helps make decisions quickly.
Users can use the Excel Table Styles to maintain a uniform format for all Tables across a workbook. It ensures the data is highly organized and easier to interpret.
For example, the following dataset shows students and their test scores in different subjects.
And the requirement is to show the above data in an Excel Table and style it in the desired format.
Then, we can use the Table option to create the Excel Table and use the Excel Table Styles templates in the Design tab to style the Table in the required format.
In the above Excel Table Styles & Formats example, we first click a cell in the given dataset and select the Table option in the Insert tab to display the dataset in an Excel Table.
And then, we must select a cell in the Excel Table to enable the Design tab in the ribbon. Next, select the required Table style from the Quick Styles drop-down option in the Table Styles group in the Design tab. We can click the More button in the bottom-right corner of the option to explore the available predefined Excel Table Styles templates.
Further, we can check and uncheck the boxes in the Table Style Options group in the Design tab to format the required features in the Excel Table.
Table of contents
Key Takeaways
- Excel Table Styles & Formats enable us to create visually appealing Excel Tables and highlight specific Table data to draw the audience’s attention.
- Users can use the Styles & Formats to maintain consistency in the Tables’ style and format across a report and improve their readability.
- We can use the Table option in the Insert tab to create an Excel Table. And then, use the Table Styles and Table Style Options groups’ commands in the Design tab to choose and apply the required styles and formats to the Excel Table. We can also use the Format as Table option in the Home tab to achieve the required Excel Table in the desired style and format.
- While we can use the predefined Table style templates, we can also create custom styles to apply to the Excel Tables.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
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.
How To Create And Format The Table Styles In Excel?
The steps to create and format the Excel Table Styles are as follows:
- Click on a cell in the given dataset or select the entire dataset.
- Choose the Insert tab – Select the Table option to open the Create Table window. Otherwise, use Ctrl + T to open it.
[Alternatively, we can choose a cell in the given dataset and choose the Home tab – Format as Table option – The required Table style from the predefined options.
The above step will open the Create Table window.
Please note that the alternative method will automatically help create the Excel Table in the chosen style through the Create Table window.]
- The Create Table window will show the chosen dataset range. And ensure the My table has headers option check box is checked before clicking OK to create an Excel Table with the column headers as in the source dataset.
- Click inside the Excel Table to enable the Design tab in the ribbon.
- Click the More button in the bottom-right corner of the Quick Styles option in the Table Styles group in the Design tab. Next, hover the cursor over the predefined Table styles, categorized under the Excel Table Styles names – Light, Medium, and Dark, for a quick preview. And then, click on the Table style that fits our requirements.
- Finally, check the required option checkboxes in the Design tab – Table Style Options group to format the specific elements in the Excel Table according to the requirements.
But sometimes, the predefined options under the Excel Table Styles names may not fit our requirements. In such scenarios, we can create a custom style from scratch using the New Table Style option under the Quick Styles option in the Design tab.
[Alternatively, we can click on a cell in the given dataset and choose the Home tab – Format as Table option – New Table Style option.
The above step will help us to access the New Table Style window.
Please note that the alternative method will allow us to set the required custom style settings in the New Table Style window first. And then, the Create Table window will open to enable us to create the Excel Table in the required custom style.]
The New Table Style window opens, where we can enter the style name in the Name field, which is, by default, Table Style 1. And then, select the required Table Element option and click Format.
The Excel Format Cells window opens, where we can use the various options in the Font, Border, and Fill tabs to set the required Excel Table Formats settings.
Click OK in the Format Cells and the New Table Style window.
Now, if we click the More button of the Quick Styles option, the custom Table style will show under a new category, Custom, in the Quick Styles option.
We can click the required custom style from the Design tab to apply to the Excel Table.
So, we can use different combinations of the Quick Styles options and Table Style Options group settings to achieve the Excel Table Styles & Formats that best suit our needs.
However, if we apply a custom Table style, the format options, Banded Rows, First Column, Last Column, and the Banded Columns, in the Table Style Options group will not work.
And if we must format the elements mentioned above, we must use the New Table Style window to select the required elements and format them individually.
Examples
Check out the following examples, explaining the Styles & Formats in detail.
Example #1 – Creating The Table
We must create an Excel table before applying the required Excel Table Styles & Formats in a worksheet.
For example, the image below shows a dataset.
The steps to show the above dataset as an Excel Table are as follows:
Step 1: Select a cell in the given dataset range or the entire dataset, and choose the Insert tab – Table option.
Step 2: The Create Table window opens. And it shows the chosen dataset range.
Check the My table has headers checkbox and click OK.
Thus, the dataset changes into an Excel Table as depicted below:
Example #2 – Change The Default Format Of Table Style In Excel
The following example explains how to change the default Table style format using the available Excel Table Styles & Formats options.
The image below shows a dataset.
We can create an Excel Table with the given dataset and set the required style and format using the options in the Design tab.
Step 1: Select a cell in the dataset or the entire dataset and press Ctrl + T to open the Create Table window.
The Create Table window will show the given dataset range and ensure the table headers option is checked.
And click OK to convert the dataset range into an Excel Table in the default style.
Step 2: Select a cell in the Excel Table to enable the Design tab in the ribbon.
And then, click the Quick Styles drop-down option in the Table Styles group in the Design tab.
Step 3: Click on the required Table style to choose and apply it to the Excel Table.
[Alternatively, select Home – Format as Table – The required Table style template.
The chosen style gets applied to the Excel Table.]
Step 4: Check the First Column check box in the Table Style Options group in the Design tab to format the first column of data in the Excel Table.
Thus, the chosen Table style and format options result in an Excel Table in the required format.
Example #3 – Create Your Own Table Format Style In Excel
The following example shows how to create custom Excel Table Formats.
The image below shows a dataset.
We can create a custom style template to achieve the required Table format using the New Table Style option in the Design tab. And the steps are as follows:
Step 1: Select a cell in the given dataset range or the entire dataset. And then press Ctrl + 1 to open the Create Table window.
And, with the specified dataset range being correct and the table headers option checked, click OK in the Create Table window to obtain the Excel Table.
Step 2: Select a cell in the Excel Table and choose Design – More button in the Quick Styles option – New Table Style.
[Alternatively, select Home – Format as Table – New Table Style.
The above step will open the New Table Style window.]
Step 3: The New Table Style window opens, where we must enter a suitable name in the Name field.
Next, we will select the Whole Table option as the Table Element and click Format.
Step 4: The Format Cells window opens.
And we can set the required Table style settings as shown below:
We set the required font color using the Color field drop-down option in the Font tab.
And then, we set the required fill pattern color and style using the Pattern Color and Pattern Style fields drop-down options in the Fill tab.
Click OK to exit from the Format Cells window.
We can check the Preview of the custom Table style in the New Table Style window.
Likewise, we can select the other required elements, one by one, in the Table Element section and repeat step 4 for each element to custom format their styles in the Excel Table.
Next, click OK to exit the New Table Style window.
Step 5: Click on a cell in the Excel Table and choose Design – More button in the Quick Styles option – select the required custom style template under the Custom category.
Step 6: Select the Total Row option in the Table Style Options group in the Design tab. We will see the last row in the Excel Table displaying the total rows in the last column in the Excel Table.
And hence, the final Excel Table with the custom style and format will appear as depicted below:
Important Things To Note
- Check the My table has headers checkbox while creating an Excel Table before applying the required Excel Table Styles & Formats. It will ensure the first row in the Table is the header row.
- The keyboard shortcut to create an Excel Table is Ctrl + T.
- When creating a custom Excel Table style, we must select the specific elements from the New Table Style window and format them individually. And in the case of custom style, the Banded Rows, First Column, Last Column, and Banded Columns options in the Table Style Options group in the Design tab will not work.
Frequently Asked Questions (FAQs)
We can clear Table formatting style without losing Table data in Excel using the Clear option in the Table Styles group in the Design tab.
For example, the following image shows an Excel Table in the style highlighted in the Table Styles group in the Design tab.
Then the steps to clear the chosen Table formatting style are as follows:
Step 1: Select a cell in the Excel Table to enable the Design tab in the ribbon.
Step 2: Click the More button of the Quick Styles option in the Table Styles group in the Design tab.
And choose the Clear option.
The output shows the Excel Table not in the previously chosen Table style.
Furthermore, clicking the data range enables the Design tab in the ribbon. And the option highlighted in the Table Styles group in the Design tab suggests that the Excel Table is intact but without any formatting style.
You can convert to a Table without changing formatting in Excel using the following steps:
1. Choose a cell in the given dataset or the entire dataset. And then, press Ctrl + T to open the Create Table window.
2. Ensure the chosen dataset range is correct and check the table headers option in the Create Table window. And click OK to change the dataset into an Excel Table.
3. Click on a cell in the Excel Table to enable the Design tab in the ribbon.
4. Select the Design tab – More button in Quick Styles – Clear.
5. Press Ctrl + Shift + L to remove the filters from the Table.
The configuration options for Table styles and format in Excel 2016 are:
• Header Row
• Total Row
• Banded Rows
• First Column
• Last Column
• Banded Columns
• Filter Button
• Quick Styles
Download Template
This article must be helpful to understand the Excel Table Styles & Formats, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Excel Table Styles & Formats. We explain how to create and format table styles with examples & points to remember. You can learn more from the following articles –
Leave a Reply