Jazz Up Excel Spreadsheets Using Tables
By on July 20th, 2011

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!

Spreadsheet

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.

Tags: , , , ,
Author: Darrin Jenkins Google Profile for 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!

Darrin Jenkins has written and can be contacted at darrin@techie-buzz.com.
  • Mahen

    Thanks for the article, do you perhaps know how I could remove table formatting in Excel 2010?

    I’ve tried clear all formats, used the format painter, even after unlocking the cells, with no luck.

    • http://saygeek.blogspot.com/ Darrin Jenkins

      I suspect what you’re seeing are the filters at the top of the table. If you highlight the table there is an “Editing” group on the far right side of the Ribbon Toolbar. If you click “Clear” then “Clear Formats” it will remove the colors. Next you need to click “Sort & Filter” and click “Filter” and it will remove the arrows off the table headers. I hope this helps.

    • Alex

      Select a cell within the table so that you get the TableTools Design ribbon. Click on “Convert to Range”. The table formatting is removed and you can use everything normally again.

      • http://saygeek.blogspot.com/ Darrin Jenkins

        Thanks for the tip Alex! Thanks for reading Techie Buzz!

      • Kisti Sorensen

        This worked! Thank you so much!

  • Uday

    UNABLE TO COPIED CELLS IN THE SHEET WHICH AS FORMAT AS TABLE

    • http://saygeek.blogspot.com/ Darrin Jenkins

      Uday, are you trying to copy them into a different program or just somewhere else in the sheet? I am able to copy them in the same workbook. Maybe you can give me a little more detail of what you’re trying to do and I will do my best to help you out.

  • http://www.skai.co.uk Irena Sobolewska

    I have used “format as table” which is great. I now cannot add any columns or rows. The “insert” optons is greyed out. How do I either insert a column or remove the table formatting altogether. Unticking the options in the “design” ribbon still doesn’t allow me to insert anything

    • http://saygeek.blogspot.com/ Darrin Jenkins

      If you right click inside the table there should be an “insert” option on the menu that will allow you to insert table column.

      • http://www.skai.co.uk Irena Sobolewska

        No there isn’t. Well there is but it is greyed out so I can’t use it. However, I have solved the problem. It’s because the formatting is set for the whole spreadsheet. I had to change the formatting to be just for the cells that I currently have used, only then am I allowed to add columns or rows.

        • http://saygeek.blogspot.com/ Darrin Jenkins

          Ah I see. Good to know. :) Thanks for posting the follow up.

          • Saad Rehman

            Any solution to this brother? I would really like to use Convert to Table feature, except that it doesn’t let me add columns. Heck I can’t even add columns AFTER I remove the formatting!

          • http://saygeek.blogspot.com/ Darrin Jenkins

            If you look at the comments below you should see an answer to this question. Thanks.

 
Copyright 2006-2012 Techie Buzz. All Rights Reserved. Our content may not be reproduced on other websites. Content Delivery by MaxCDN