Get a worksheet that is clean, visually appealing, and interactive. You will love to this with every data set in Excel!

Table formatting is one of the most powerful Excel tools for anyone who is collecting and interpreting data sets. The big benefit of the table formatting options is that they turn static data in tables in a dynamic database. Let´s see how it works.

Table formatting in Excel in 2 minutes

For this exercise we will use a data export form the Google Search Console. The Google Search Console is mighty tool that shows website owners at which position they rank for Google queries. As with many tools nowadays you can extract data from the Google Search Console via an CSV file export. This gives you an ugly data set. But within a few minutes we will turn this pure data in an interactive, clean and pretty table.

Format as a table in 5 steps

  1. Select any cell inside your set of data, then go to Home > Styles > Format as Table
  2. In the popup menu select with headings for our example
  3. Excel auto-detects the perimeter of the table and also auto-detects headings
  4. Clean up data and remove everything we don´t need for our analysis
    • delete columns you don´t need (in our example click rate)
    • auto adjust column width (double click on borders)
    • format as numbers (mark column and right click, then select format cells as numbers)
    • turn off gridlines (tick off gridlines under layout)
  5. Freeze first row (layout > freeze first row)

As a result you get a data table that is clean and interactive. Make it a habit to apply these 5 quick steps with every data set you work in Excel.