Editor’s Note: “How to make Excel tables look good?” – this is a general question by a new user of Excel. I asked Taryn to make a detailed article on this. I checked the whole article and it is really a helpful article for the new Excel table users (even professionals sometimes lack this skill). I hope you will enjoy this article. – Kawser Ahmed
Excel Tables are extremely useful for organizing and showcasing data. They can also be formatted using the features available in Excel, in order to be visually striking and appealing. We are going to look at some handy tips to make Tables in Excel, look great.
So, let’s get started with a simple example to illustrate how to create a Table in Excel and then how to format this Table, using some formatting features and tricks.
Table of Contents
- 1 Introduction
- 2 Creating a Table in Excel
- 3 How to Make Excel Tables Look Good
- 3.1 Using built-in predefined Table Styles to quickly format a Table
- 3.2 Changing the theme of the workbook in order to change the colors in Table Styles
- 3.3 Editing the theme colors of the workbook, in order to change the colors in Table Styles
- 3.4 Clearing the style from a Table
- 3.5 Creating your own Table Style
- 3.6 Adding a Total Row and Turning the Filter Buttons of the Table off
- 3.7 Inserting and Formatting a Table Slicer
- 3.8 Converting a Table back to a range, with the formatting of the Table still intact.
- 4 Download Working File
- 5 Conclusion
- 6 Useful Links
Footwork skills in tennis are one of the critical skills needed, for players, to advance to more professional levels. Tennis footwork involves getting into the best position, in order to return difficult shots. There are different footwork patterns and many tennis coaches advise different types of drills, in order to enhance footwork skills and footwork patterns.
A hypothetical tennis coach is evaluating different players at his clinic, their level of footwork skills as well as the number of matches they’ve won for the season. The source data is shown below.
The source data should be organized in rows and columns, with a header row at the top categorizing the data in the columns. The data as it is now is currently a range or referred to as a range.
There should be no other data right next to the range, i.e in the cells surrounding the range. Each column should contain only one type of data and there should be no completely blank rows or breaks within the range.
Creating a Table in Excel
1) Select one cell in the data range and go to Insert>Tables>Table.
2) The Create Table Dialog Box should appear and usually, Excel automatically detects the data for the Table, if the data is setup correctly. The My table has headers option is also checked by default.
3) Click OK and the Table below is created.
4) It is always good practice to name your Table precisely, especially if your workbook contains lots of tables. So, in order to name the Table we just created, select one cell in the Table and go to Table Tools>Design>Properties and change the name of the table to FootworkT.
5) You can now refer to the table using its name, in formulas for example.
How to Make Excel Tables Look Good
Using built-in predefined Table Styles to quickly format a Table
1) You can quickly change the appearance of your newly created Excel Table, using the built-in Table styles.
2) Select one cell in the FootworkT table, go to Table Tools>Design>Table Styles and click on the drop-down arrow and choose one of the built-in Table Styles available. You can get a preview by just hovering over each style if you are not sure.
3) In this case, we went to Table Tools>Design>Table Styles and selected Table Style Dark 9 as shown below.
4) This style is thus applied to the Table.
5) The colors used in the Table are drawn from the default Office theme.
Changing the theme of the workbook in order to change the colors in Table Styles
1) The colors provided in the Table Styles options are drawn from the default Office theme, in order to quickly change the options provided there, one can change the theme of the Workbook.
2) Go to Page Layout>Themes> and click on the drop-down arrow below Themes and select another theme, that is not the default Office theme, in this case, the Slice theme.
3) The Table Style draws its colors from the Slice theme and the effect of the change on the actual Excel Table is shown below.
4) To see the change that has been effected on all the Table Styles options, through changing the theme from Office to Slice, select one cell in the table and go to Table Tools>Design>Table Styles and click on the drop-down arrow, in order to see the alternate colour schemes drawn from the new theme.
Editing the theme colors of the workbook, in order to change the colors in Table Styles
1) You can also alternatively, change the theme colors yourself, or set the theme colors yourself in order to effect changes in the Table Styles options.
2) With any theme currently selected, go to Page Layout>Themes> and click on the drop-down next to Colors.
3) Choose the Customize Colors option…………
4) In the Create New Theme Colors Dialog Box, select the drop-down arrow next to Text/Background – Dark 2 and choose More Colors…
5) Select the Custom Tab and enter the following values R 87, G 149 and B 35, in order to set this dark green color and click OK.
6) In the Create New Theme Colors Dialog Box, select the drop-down next to Text/Background – Light 2 and choose More Colors…
7) Select the Custom Tab and enter the following values R 254, G 184 and B 10, in order to set this orange color and click OK.
8) In the Create New Theme Colors Dialog Box, select the drop-down next to Accent 1 and choose More Colors…
9) Select the Custom Tab and enter the following values R 7, G 106 and B 111, in order to set this dark turquoise color and click Ok.
10) Now, in the Create New Theme Colors Dialog Box, select the drop-down next to Accent 2 and choose More Colors…
11) Select the Custom Tab and enter the following values R 254, G 0 and B 103, in order to set this pink color and click Ok.
12) Give your new customized theme color set a name and click Save.
13) The effect of changing the theme colors to this customized set is immediately reflected in the FootworkT Table, as shown below.
14) It is also reflected in the Table Styles Options, going to Table Tools>Design>Table Styles and click on the drop-down arrow, in order to see the new Table Styles drawn from the new customized theme colors set.
Clearing the style from a Table
1) You can also totally clear the style from a Table, by selecting one cell in the Table and going to Table Tools>Design>Table Styles and clicking on the drop-down arrow next to Table Styles.
2) Select Clear in order to clear the formatting associated with the specific table style chosen.
3) Any formatting associated with the specific table style chosen is now cleared as shown below.
Creating your own Table Style
1) You can create your own Table Style in Excel and format the header row, columns in the Table and rows in the Table precisely.
2) With a cell in the Table selected, go to Table Tools>Design>Table Styles and click on the drop-down arrow next to Table Styles and select New Table Style.
3) One can now format individual elements of the Table, using the New Table Style Dialog Box.
4) The first element we are going to format is the Whole Table element. Select the Whole Table and then choose Format.
5) The Format Cells Dialog Box should appear, choose the Font Tab, and under font style choose Bold Italic.
6) Go to the Fill Tab, under the Background Color option, choose More Colors…
7) Select the Custom Tab, and set R 133, G 229 and B 255 as shown below and then click Ok.
8) Click Ok again.
9) Now select the Header Row element as shown below and click Format.
10) The Format Cells Dialog Box should appear as before, choose the Font Tab, and under Font Style choose Bold and change the font color to White, Background 1.
11) Choose the Border Tab, select the thick line style and the color Gray – 25%, Background 2, Darker 50%.
12) Choose Outline, in order to outline the entire Header Row with this border formatting.
13) Then select the Fill Tab, under Background Color, choose More Colors.
14) Select the Custom Tab, and set R 11, G 135 and B 52 as shown below and then click Ok.
15) Click Ok again.
16) Give your new Table Style a name and check the Set as default table style for this document option, in order to ensure that all tables created in the workbook have this format, in order to contribute to a streamlined look.
17) Applying this Custom Table Style to the FootworkT Table results in the following look.
Adding a Total Row and Turning the Filter Buttons of the Table off
1) One can also add a Total Row and turn the filter buttons of the Table off, quite easily.
2) With one cell in your table selected, go to Table Tools>Design>Table Style Options and check Total Row in order to add a Total Row and uncheck the Filter Button in order to turn off the filter buttons of the header row as shown below.
Inserting and Formatting a Table Slicer
1) Table Slicers allow one to filter the data in the Table according to the column categories, these slicers can also be formatted to match the overall Table formatting.
2) First things first, format the Table with a certain style, by going to Table Tools>Design>Table Styles and choose Table Style Medium 4 (make sure the theme is the default Office theme).
3) Now the entire table is formatted with this style as shown below.
4) With one cell in the table selected, go to Table Tools>Design>Tools>Insert Slicer.
5) Choose one or more slicers to filter the data by, in this case, we’ll choose Footwork Evaluation as shown below and then click Ok.
6) The Slicer with the default style appears as shown below.
7) One can change the style of the slicer using one of the built-in styles. With the slicer selected, go to Slicer Tools>Options>Slicer Styles and select one of the default built-in Styles and choose Slicer Style Light 2 as shown below.
8) This changes the Slicer Style to the following format.
9) With the Slicer selected, go to Slicer Tools>Options>Buttons and Change the number of columns to 3 and then with the Slicer still selected, go to Slicer Tools>Options>Size and Change the height of the slicer to 1.69 cm and the width to 6.7 cm as shown below.
10) With the slicer still selected, we now want to create a new custom Slicer Style to match the Table Style we chose. So we go to Slicer Tools>Options>Slicer Styles and we click on the drop-down next to Slicer styles and select New Slicer Style as shown below.
11) In the New Slicer Style Dialog Box, choose the Whole Slicer element and then click Format.
12) In the Fill Tab, under Background Color, choose Fill Effects…
13) Using the Fill Effects Dialog Box, Change Color 1 to White, Background 1, Darker 25% and change Color 2 to White, Background 1 as shown below.
14) Under Shading styles make sure Horizontal is selected and choose the third variant as shown below.
15) Click Ok and then select the Border Tab, choose the thin line style and White Background 1, Darker 35% and then select Outline as shown below.
16) Click Ok and then name your newly created Slicer Style as shown below and click Ok.
17) Apply the new Slicer Style you just created.
18) Go to View>Show>and uncheck Gridlines in order to see the full effect of the formatting.
Converting a Table back to a range, with the formatting of the Table still intact.
1) In order to convert a Table back to a range, select a cell in the Table as shown below and go to Table Tools>Design>Tools>Convert to Range.
2) A Dialog Box should appear asking you if you want to convert the Table to a normal range, select Yes.
3) The Table should now be converted to a normal range, but with the formatting selected still intact as shown below.
Creating a Table in Word is usually done a little bit differently to the way it’s done in Excel. However, Word also supplies extensive formatting options for Tables.
1) Usually one inserts the Table in Word first and then fills in the data in the columns and rows.
2) In a Word document go to Insert>Tables and click on the drop-down arrow underneath Tables and choose the dimensions of your table that you would like to create, in this case, we chose a five column by eight row table.
3) In order to quickly format this table using the built-in styles provided, make sure a cell in the table is selected and go to Table Tools>Design>Tables Styles and select one of the preformatted styles with colors drawn from the default theme. In this case, we’ll choose List Table 5 Dark – Accent 4 and the table is quickly formatted.
4) You can also easily delete this newly created table if need be, by selecting the move handle in order to select the entire Table as shown below.
5) Right-click and select Delete Table in order to delete the Table you just created easily.
And there you have it.
Download Working File
Tables can be created and formatted extensively in both Excel and Word, it is often necessary to format tables for visual appeal or printing purposes.
Please feel free to comment and tell us about your Table formatting tricks and tips for Excel and Word.