When you format a range as a table in Excel, you receive the benefit of table styles, which allows you to apply several formatting attributes at one time.
Follow the steps below to format a range as a table:
- Click in or select the range you would like to use as your table (it does not need to contain data prior to creating your table).
Notice, when you start with an existing contiguous range of data, you do not have to select the range first – simply click somewhere in the range and Excel will recognize it automatically.
- On the Home tab of your Ribbon, click, Format As Table and then click the table style that you would like to apply. Or, on the Insert tab, click Table.
- In the Format As Table dialog box that appears, Excel will confirm what it believes to be the correct range for your table. If it is not correct, you can simply drag to select the correct range without closing the dialog box.
- With the Format As Table dialog box still open, if your selected range includes table headers, check the box labeled My Table Has Headers. If not, then obviously leave that box blank and a header row will be added.
- Click OK to format your table.
If you started with the Table option on the Insert tab, the default table style for the active workbook will be applied to the new table.
Once your table style has been applied, simply click anyplace inside your table to apply a new table style from either the Format As Table gallery on your Home tab or from the Table Styles gallery on the Table Tools Design tab.
But there is so much more that you can do with tables in MS Excel:
Your table will automatically expand when you add adjacent data in rows or columns, so your newly added data is formatted to match your table without any extra work on your behalf!
- You can drag the icon below, which appears at the bottom-right corner of all tables, to resize the table range. You can use it to drag left, right, up, or down.
- Simply drag selected table rows or columns to recorder them. As you drag, a gray bar will appear. This bar indicates where the selected column or row will fall when you release the cursor.
- You can only reorder complete rows or columns in a table. If you drag a few cells from a row or column, Excel will assume that you want to replace the destination cells, as it does when you drag cells on a worksheet outside of a table and will prompt you in that regard.
- As always, a right-click will give you choices: insert, delete or select table rows and columns.
You cannot modify built-in table styles, but you can duplicate them to create your own table styles based on built-in styles.
Follow the steps below to learn how:
- On the Home tab of your Ribbon, click Format As Table or, on the Table Tools Design tab, click Table Styles. From either of these galleries, either click New Table Style or Right-click an existing style and then click Duplicate.
- In the New Table Quick Style dialog box, select the table element to customize and then click Format.
- The Format Cells dialog box will display with Font, Border and Fill options available to you.
- Set the options you would like to format for the selected table elements and then click OK.
- Repeat for every part of your table that you would like to customize.
- If you would like to use the new table style as the default for all tables in the active workbook, check, the option Set As Default Table Quick Style For This document.
- Click OK to create your new style.
The style will now appear under the Custom heading at the top of the Table Style and format As Table galleries.
This was written for version 2007 but with some navigational changes will apply to version 2010 as well.