facebook google plus twitter
Webucator's Free Intermediate Microsoft Excel Tutorial

Lesson: Working with Tables

Welcome to our free Intermediate Microsoft Excel tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2019 Training course.

Working with tables in Excel 2019 can help you organize your data. In Excel, you can group data together into a table and then manipulate that table.

Lesson Goals

  • Learn to format data in Excel as a table.
  • Learn to modify Excel tables.

Format Data as a Table

To format data in Excel as a table:

  1. Highlight the data that you want to turn into a table. Highlight Data
  2. From the Home tab, in the Styles section, click Format as Table. Format as Table Command
  3. From the drop-down list, select a table style to apply. Table Style
  4. In the Format As Table dialog box, click OK. Format as Table Dialog Box

Move between Tables and Ranges

When working in tables in Excel, you can select cells and cell ranges as you would within a worksheet, by clicking to select a cell and clicking and dragging to select a worksheet.

However, to select rows and columns in a table, you need to click the left edge of the row or the top edge of the column, respectively, when it turns to an arrow.

Select Row

Select Column

Modify Tables

You can modify Excel tables in a number of different ways.

Add and Remove Cells within a Table

To insert or remove rows or columns of cells within a table, select the row or column to be removed, right-click, and select Insert or Delete.

Add Row

Delete Row

Change Table Styles

To change the style of a table, from the Table Tools Design tab, select an option from the Table Styles group.

Table Tools Design Tab

Define Titles

By default when you create a table in Excel, titles are created by default; headers are turned into titles.

Headers

To turn this feature off, from the Table Tools Design tab, in the Table Style Options group, uncheck the Header Row check box.

Uncheck Header Row

Band Rows and Columns

You can band rows and columns in an Excel table. To do this, select the Table Tools Design tab and in the Table Style Options group, check the check boxes to band rows or columns.

Banded Rows

Total Row Option

When you select the Total Row checkbox on the Table Tools Design tab, Excel adds a row at the end of your table showing the totals of the data in your columns.

Total Row Option

Remove Styles from Tables

You may want to remove styles that you applied to your Excel table.

To remove styles, select the Table Tools Design tab and in the Table Styles group, select the arrow to view more options, and then select Clear.

Remove Table Styles

Creating and Modifying a Table in Excel

Duration: 5 to 10 minutes.

In this exercise, you will create an Excel table and modify it.

  1. Open the Sales - Table.xlsx file from your Excel2019.2/Exercises folder.
  2. Create a table that includes the data in all of the cells, using a style of your choice.
  3. Now, remove the style that you chose.

Solution:

  1. Select the cell range of the data, so from A1:D4. Format As Table Dialog Box
  2. From the Home tab, in the Styles section, click Format as Table. Format as Table Option
  3. From the drop-down list, select a table style of your choice to apply. Style Choice
  4. In the Format As Table dialog box, click OK. Click OK
  5. Select the Table Tools Design tab and in the Table Styles group, select the arrow to view more options, and then select Clear. Clear
  6. Save the workbook.