Create Tables Using Excel 2013 Tutorial

Welcome to the fourth lesson Create Table of the Excel 2013 MOS Foundation tutorial, which is part of the Microsoft Excel 2013 MOS Foundation Course. In this lesson, we will learn how to create tables and work with its data in Excel 2013.

Before we get started, let us look into the objectives of this lesson in the next section.

Objectives

By the end of this lesson on Create Tables, you will be able to:

• create tables

• format tables

• convert tabulate data into the normal data range

• remove duplicates

• filter tables and sort data

In the next section, we'll discuss tables in Excel 2013.

Tables in Excel 2013

An Excel table is a series of adjacent cells that have been formatted as a named Excel object. It has functionalities beyond a simple data range. It allows you to filter and sort tabulate data, as well as insert a pivot table.

Excel 2013 allows you to perform a series of actions within tables. It allows you to:

• format data and copy formulas to a new road

• perform quick calculations of column totals and apply other mathematical functions, such as count max, min, etc

• define table names and search for data

• analyze a named table object online

• create formulas using table names

In the next section, we will learn how to create and format a table.

Create and Format a Table

The table content should first be evaluated to identify cells. Then functional table elements, such as header, rows, and the total number of rows need to be defined.

The table can be formatted using various styles, emphasizing the titles headings and the number format. The simplest way to create a table is by converting it to an existing data range. The table can have an existing table format or a new thematic style.

Business Scenario

John is currently working on a bonus report and wants to create a table for the data he has prepared.

The data for the bonus report is shown below:

Steps to follow:

• Click the Insert tab.

• Click to select the Table tab. The Create Table pop-up window is displayed.

• Click to select A1 cell.

• Select the cell range A1 to I10.

• Select the My table has headers check box.

• Click Ok.

Define table Names

A newly created table is assigned a name based on its order of creation in the workbook, such as table1, table2, etc. Table names can be defined using the Name Manager under the Formulas tab. By defining a table name, you simplify the process of creating formulas. Once the table name is defined or new data are added, the source data range is automatically updated.

Business Scenario

Jones manager has asked him to prepare a bonus report for the previous year. He wants to define table names for the report he has prepared.

The table for the bonus report is shown here.

Steps to follow:

• Click to select the Table.

• Click the Design Tab.

• Type the preferred name for the table and press Enter.

• To edit the table name, click the Formulas tab.

• Click Name Manager and locate your table name.

• Click edit to change your table name.

• Click Ok.

• Click Close

Insert Table Rows and Columns

There are different ways of inserting new rows and columns in a table in Excel 2013. They are listed below.

Insert Sheet Rows

This option allows you to insert new rows in the table above or below the selected rows.

Insert Sheet Columns

This option allows it to insert new columns in the table to the left or the right of the selected columns.

Insert Table Columns To The Left

This option allows you to insert columns to the left side of the selected columns. Range references helped to automatically update cell references while inserting cells in a worksheet.

Business Scenario

John has been working on the bonus report. Now his manager wants him to add the details of a new employee to the existing table. Also, John discovers that he has missed on the serial number column in the table.

The bonus table is shown here.

Steps to Follow:

• To add a column, click on the Insert item.

• Select the Insert Table Columns to the Left menu item.

• To add a row, click on the particular cell above which new row has to be added

• Click the Insert item.

• Click to select the Insert Table Rows Above menu item.

Delete Table Rows and Columns

There are several ways of deleting table rows and columns in Excel 2013. They are listed below:

Delete Table Rows

This option allows you to delete selected table rows. After the excel application deletes the selected table rows, other rows automatically get shifted upward.

Delete Table Columns

This option allows you to delete selected columns. After the excel application deletes the selected table columns, other columns will shift leftward.

When you press the delete key on any table cell, Excel will only delete the content and not the entire row or column.

Business Scenario

John's manager has asked him to publish the bonus report online. But before that, he has asked John to remove the SSN number and the employee details of those working in the Human Resource department.

The data for the bonus report is shown here.

Steps to Follow:

• Select one cell from the column to be deleted.

• Click to select the Delete menu item.

• Click to select the Delete Table Columns menu item to delete the entire column.

• Select one cell from the row to be deleted.

• Click to select the Delete menu item.

• Click to select the Delete Table Rows menu item to delete the entire row.

Move Table Rows and Columns

Excel 2013 allows you to move rows within a table by using the ‘Insert Copied Cells’ function. If you want to move columns, you will need to select the columns and use the cut and paste or copy and paste options.

Business Scenario

As per the latest requirement, the bonus report John has been working on should first display the last name of the employees. He is also required to present the information on the Sales and Marketing team together. John has to incorporate these changes.

The bonus data table is shown here.

Steps to Follow:

• Select the required column.

• Right-click the selected menu.

• Select the Cut menu item.

• Click to select a column before which the cut column needs to be inserted.

• Right-click on that particular column.

• Click to select the Insert Cut Cells menu item.

• Select the required row.

• Select the Cut menu item.

• Click to select a row before which the cut row needs to be inserted.

• Right-click that row and select the Insert Cut Cells menu item.

Convert to Range

In specific instances of data representation, you do not need tabular performance. Excel 2013 gives you an option to retain only the data within a table. By using the Convert to Range option in tables, you can convert tabular data into a normal data range.

Business Scenario

John's manager wants to present the bonus report at a stakeholder meeting. He wants the bonus table to be represented in a normal range, i.e. without any table format. He has asked John to remove the table format and convert it into a normal data range, ensuring the data is retained, as is.

The bonus data table is shown here.

Steps to Follow:

• Select the table.

• Click on Design tab from Table Tools

• Click on “Convert to Range.”

• The Microsoft Excel pop-up window is displayed. Click the Yes button.

• The table appears in the normal format.

Remove Duplicates

Copying data from different sources leads to replication of data entries. Excel 2013 allows you to remove duplicate records in a table and keep only unique data.

Business Scenario

John's manager wants to present the bonus report at the stakeholder meeting. During the review, he has found some duplicate entries in the table. He's asked John to remove the duplicate entries to avoid confusions.

The bonus reports are shown here.

Steps to Follow:

• Select the table.

• Click Data.

• Click the Remove Duplicates menu item.

• Select the required Columns checkboxes in the Remove Duplicates pop-up window.

• Click Ok.

• Click Ok on the Microsoft Excel pop-up window.

Design Table Styles

Table styles are a combination of the formatting elements, which include fonts, borders, and fills, that are coordinated to provide a polished appearance.

The available table styles are based on the worksheet theme colors. We can change the table style by choosing from an array of available styles.

We can create a table and apply a specific table style at the same time. This could be done by selecting the data range and applying the required style from the quick styles menu.

Business Scenario

John has completed the bonus report. Before submitting the report to his manager, he decides to design the table to make it more visually appealing.

The bonus data table is shown here.

Steps to Follow:

• Select a cell

• Click Design from Table Tools.

• Click Table Style. A drop-down appears with various table style options.

• Click to select the appropriate table style.

Table Style Options

Table elements formatting is designed to make table entries easier to differentiate and include an emphasized first and last column and banded rows and columns. There are various table styles options in Excel 2013.

Some of them are as follows.

Header Row

This option is used to display or hide the table header, which is usually the first row of a table.

Total Row

This option is used to display totals and other arithmetic computations at the bottom of the table. The Total Row is formatted to differentiate it from the rest of the table content. Different calculations, such as count, sum, average, max, min, standard deviation, and variance can be part of the last row.

In the next section, we will understand how to use filters in tables.

Filter Tables

Filters allow you to study and segregate data using three different parameters. They are:

Filter by Value

This option allows you to search data using values and logical conditions, such as equals, does not equal, begins with, and contains.

Filter by Search

This option allows you to search data by manually entering a name in the search box, using wildcard characters like @, ?.

Filter by Color

This option allows you to search data using both sell, color, and font color.

Business Scenario

The finance department has requested John to provide the details of employees of the Sales department with earnings of more than forty thousand dollars. John has to filter the data from the existing bonus table.

The bonus report is shown here.

Steps to Follow:

• Select the column header to be filtered.

• Click Filter menu item in the Data tab.

• Click the arrow button next to column header.

• A screen tip appears. Click to select “Greater Than Or Equal To…” menu item from Number Filters option.

• Type the measurement value. Click the OK button.

Sort Table Data

There are two ways to sort a data range. They are:

Sort by columns

This option allows you to arrange data in one form or columns in a worksheet or a table in either ascending or descending order. To sort multiple columns we must specify, in the sort dialogue box, the order in which they should be sorted.

Sort by rows

This option is available only when the dates arranged contains information that could be arranged in either direction. To successfully sort data from left to right, we need to select a data range that includes only data and not headers.

The Excel application assumes that the first row of the worksheet comprises column headings. Hence it does not include the first row while sorting data. However, hidden content within a table is included in the sorts operation.

Some standard sort orders are:

• A to Z for text,

• smallest to largest for numbers,

• oldest to newest for dates

• Arranging data using features such as cell color, font color, and cell icon.

Business Scenario

John’s manager is preparing for a meeting and has asked John to sort the earning details report based on department name and then by earnings.

The data for the bonus report is shown here.

Steps to Follow:

• Select any header cell from the table.

• Click Sort & Filter under the Home tab.

• To sort data in ascending order, select Sort A to Z.

• To customize the sorting based on multiple columns, Select Custom Sort… from the Sort & Filter group.

• Select appropriate options from the Sort by drop down

• Click Add Level from the Sort pop-up and select the appropriate option from the Then by drop-down and click OK.

Summary

Let us summarize what we've learned till now.

• An excel table allows you to filter and sort tabulate data, as well as insert pivot for a table.

• Tables can be formatted with various styles with an emphasis on titles, headings, and number format. Range references help to automatically update cell references while inserting cells in a worksheet.

• ‘Remove duplicates’ option allows you to filter duplicate records from the data.

• ‘Table styles’ are a combination of formatting elements that provide a refined appearance to documents.

• Table element formatting makes table entries easier to differentiate.

• Filters allow segregation of data using three different parameters - Filter by search, Filter by value, and Filter by color.

Conclusion

With this, we have come to an end of this lesson on Create Table. In the next lesson, we will look into applying formulas and functions in Excel 2013.