How to Build an Excel Table in Microsoft Office 365

To make managing and analyzing your data in Microsoft Excel easier, you can turn a range of cells into an Excel table. This feature was previously known as an Excel list in earlier versions of the software program, and in Microsoft Office 365, the Excel table gives you more options, features and shortcuts to visually group and analyze your data.  

For more productivity-enhancing tips, tricks and shortcuts, add Mastering Microsoft Excel for Office 365 Level 100 to your shopping cart and gain access to 24 modules of on-demand training.

Features in Excel Tables

• A header row: This is included by default in every table column, which is used to filter or sort your table data quickly. It can turned off, if necessary.
• Banded rows: Shaded rows in the Excel table that helps the user better view and distinguish the data.
• Calculated columns: Users can create a calculated column by entering a formula in one cell in a table column, which is then instantly applied to all other cells in that column.
• Total row: Excel provides an Autosum dropdown list once a total row is added to the table, which allows the user to select functions like SUM and AVERAGE. Once an option is selected, the table will automatically convert them to a SUBTOTAL function. This function will ignore hidden rows by default.
• Sizing handle: A handle in the lower right corner of the table, which allows you to drag the table to the size you want.

Steps to Build an Excel Table

• Place the cursor in the data area. You don’t have to highlight all the cells you want to include in your table. As long as you don’t have any blank rows or columns in the area, no matter its width or length, Excel will automatically highlight the entire data area for you.

• On the Home tab, select Format as Table or putting your cursor in your data area and select CTL + T. Then choose your preferred table style.
• A window will open, asking if you’d like to format your data as a table and if your table has headers. Keep in mind that the Header Row is automatically added as the first row of the table, but you have the option to turn it off or on. If the table has no headers, Excel will add default header names like Column 1 and Column 2, which can be changed at any time. If you have a header row and don’t use it, Excel will treat that row as data.
• Once your table has been formatted, it will look like a table with colorful header and shaded rows. If you want to change the look of your table without changing the table properties, unselect banded rows in the Table Design options or on the Styles Gallery, you can choose Clear at the bottom to remove the design while maintaining the table properties.  
• One final option for building a table is to highlight all the cells you want to include in your table. This will cause the quick analysis tool to pop up on the right corner (the box with a lightning bolt), which has a table tab for formatting.

SkillPath offers three levels of Excel training, from beginner to advanced. Take the interactive Microsoft Excel quiz to decide which level is right for you.