Jazz Up Excel Spreadsheets Using Tables

Format As TableTake a look at the spreadsheet in the picture below? What does it say to you? Do you get the feeling you’re looking back in time at an old black and white TV? What this spreadsheet needs is some spunk! Today I would like to show you how Excel can turn this bland spreadsheet into a work of art!


Microsoft Excel 2010 has a real handy feature called “Format as Table”. This feature can be found on the “Home” tab of the ribbon toolbar.  See the picture below.

Format As Table

To use this feature, highlight your spreadsheet, and click the  “Format as Table” button. This will drop down a menu with different samples of tables you can choose from. Click one of the choices on the menu to apply it to your spreadsheet. A dialog box will pop up asking you to confirm where the data for your table resides.  Since you highlighted your spreadsheet to begin with, this data should be correct as shown. Since our spreadsheet does have column headers you want to make sure that the “My table has headers” check box is checked. Click OK to apply the table style to your spreadsheet.

Confirm Data Window

In the picture below, you will notice that your table style has been applied. You will also notice that there are little downward pointing arrows beside each column heading. These are filter arrows. If you click the filter arrow beside the heading labeled “State”, for example, you will have the option to filter the list to only show the states you want to see.

Table Headers

When you click in your newly formatted table, you will notice a new tab on the ribbon toolbar called “Design”. In the picture below, you will see this “Design” tab gives you the option to change the table styles. If you hover your mouse over the different table styles, the table changes to give you a preview of how it will look if that style is applied; however, the style will not be applied until you click it.

Design Tab

On the same “Design” tab, you will see “Table Style Options” group. Here you can add different formatting to the first column. You can even add a “Total Row”, as shown in the picture below.

Total Row

As you can see, using the “Format as Table” option in excel, is an easy way to add color and functionality to your spreadsheet. How would you use this feature in your spreadsheet?  Comments are always welcome. Please see the comment field at the bottom of this post.

Published by

Darrin Jenkins

Darrin is an IT manager for a large electrical contractor in Louisville KY. He is married and has 3 kids. He loves helping people with their technology needs. He runs a blog called Say Geek!