How to sort related data in Excel with tables

A table in Excel is a block of cells with related data formatted as in the form of a table. By formatting a data block as a table, you can easily perform various tasks on the data in the table without affecting other data on the worksheet. These tasks include sorting data, filtering data, quick calculations, adding column totals, and visually formatting a table.

The information in this article applies to Excel 2019, 2016, 2013, 2010, and Excel for Mac.

Contents
  1. Insert table
  2. Table functions
  3. Manage table data
  4. Delete table but keep data

Insert table

Before creating a table, enter data into the table. When entering data, do not leave blank rows, columns, or cells in the data block that will form the table.

To create a table in Excel:

  1. Select a cell in the data block.

  2. Select Insert

  3. Select table † Excel selects the entire block of continuous data and opens a dialog box Create table

    If Excel has trouble formatting the table correctly, highlight the data before choosing the option Insert table

  4. If your data has a header, check the box My table has headings

  5. Select Okay to create a table.

Table functions

The most notable features that Excel adds to the data block are:

  • Drop-down menus in column headings with sort, filter, and search options.

  • Alternate shaded lines that make the data more readable.

  • Calibration buttons are located on the corners of the table.

  • Quick analysis icon (in Excel 2013 and later) that appears in the lower-right corner of the active cell when two or more cells in a table are selected. It makes it easier to analyze data using charts, pivot tables, subtotals, and conditional formatting.

Manage table data

Sort and filter options

The sort and filter drop-down menus added to the title bar make it easy to sort tables in ascending or descending order, by font, or by cell background. You can also define a custom sort order. In addition, the filter options in the menu allow you to:

  • Only show data that matches the specified criteria.

  • Filter by font or cell background color.

  • Search for specific records by matching individual data fields.

Add and remove fields and records

The size descriptor makes it easy to add or remove entire rows (records) or columns (fields) of data from a table. To resize the table, drag the resize handle up, down, left, or right.

Data deleted from a table is not removed from the table, but is no longer included in table operations such as sorting and filtering.

Calculated Columns

With a calculated column, you can enter one formula in one cell in a column and automatically apply that formula to all cells in the column. If you don’t want all cells to be included in the calculation, delete the formula from those cells.

If you only want the formula in the starting cell, use the Undo function to remove it from all other cells.

Function “Total number of lines”

The number of records in a table can be counted by adding a total row to the end of the table. The summary row uses the SUBTOTAL function to count the number of records.

In addition, other Excel calculations such as SUM, AVERAGE, MAX and MIN can be added using the drop down options. These additional calculations also use the SUBTOTAL function.

  1. Select a cell in the table.

  2. Go to the tab Table tool design

  3. In a group Table style options Check the box General string

General string appears as the last row in the table and returns the word . again Total in the leftmost cell and the total number of records in the rightmost cell. cell.

To add other calculations to the total row:

  1. Select in the overview line cell The in which the calculations should be displayed. A drop-down arrow appears on the right side of the cell.

  2. Select drop-down arrow to open the options menu.

  3. Select required calculation in the menu to add a cell to it.

The formulas you can add to the total row are not limited to menu calculations. Formulas can be added manually to any cell in the total row.

Delete table but keep data

If you decide you don’t need a table for your data, here’s how to delete it without damaging its contents:

  1. Select a cell in the table.

  2. Go to the tab Table tool design

  3. In a group instruments select Convert to Range to open the confirmation dialog for deleting the table.

  4. Select Yes for confirmation.

Table features such as drop-down menus and the format guide are removed, but data, row shading, and other formatting features are preserved.

Leave a Reply

Your email address will not be published.