Get a Table Look with Only the Formatting in Excel

Add to Favorites

How to get the nice formatting of a table without turning your data into an actual table.

Formatting data as a table gives you nice banded rows and makes your data much easier to read and analyze.

Steps to Get the Table Look

  1. Select anywhere in your data table or select the entire data table and then hit Ctrl + T or go to the Insert tab and click Table.

  2. A small window will appear.  Make sure it has selected your data correctly and, if your data has headers, make sure there is a check next to My table has headers

  3. You now have a table. Click anywhere in the table and go to the Design tab that should appear and look to the Table Style Options and Quick Styles section. Here, you can easily adjust how the table looks.
  4. You data should still be selected and, if it's not select it.  Then go to the Design tab and click Convert to Range

  5. Hit Yes in the small window that opens.
  6. That's it!

Now you have beautifully formatted data without the potential burden of having it formatted as a table.

Notes

Before you ask why someone would want to do this instead of keeping the data as a table, it's because sometimes you don't want or need a table in your worksheet and having the added functionality can be annoying.

Download the Excel file that accompanies this tutorial to try this out yourself.


Downloadable Files: Excel File

Similar Content on TeachExcel
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Using Formulas with Tables in Excel
Tutorial: Easy way to reference data tables and make formulas within a data table. This allows you ...
Show Fewer Decimal Places Without Losing Precision in Excel
Tutorial: How to show a smaller number that looks better without sacrificing decimal places or preci...
Format a Date to Show Only the Day of Week in Excel
Tutorial: How to format a Date so that only the day of the week is visible from that date. Result: ...
Display the Current Day of the Week in Excel
Tutorial: Use a formula or formatting to display the current day of the week in Excel. Sections: For...
Format Cells in The Number (Numerical) Number Format in Excel
Macro: This free Excel macro formats selected cells in the Number or Numerical number format in E...