How to create a table in Excel

You need a suitable dataset before you can start creating a table. If you want to use features like the filter or sort function, you should also assign appropriate column headers to the data. You can easily integrate data from an external source using the import feature in Excel. To do this, go to the “Data” tab and click the “Get Data” button. Supported import sources include documents (text, CSV, XML, JSON), databases (including Microsoft Access and SQL Server database), Microsoft Azure, and Facebook:

Alternatively, you can manually enter the data for your new Excel table. In the following example, which is also the basis for further steps in this Excel tutorial, we’ve created a small dataset with ten customers. The table lists the name, age, revenue and year of the customer’s first purchase:

Once you’ve inserted your dataset, you can convert it into a formatted Excel table. To do this, first select the complete dataset by holding down the left mouse button and then selecting all Excel cells you want to convert:

Once you’ve selected the dataset, click the “Format as table” button on the “Home” tab. Excel will then show you an overview of the available predefined styles:

In the dialog box that appears, confirm the dataset cells (in this example, all cells from A1 to D11) and select the “My table has headers” checkbox if you have titled the columns of your dataset. Then click “OK” to create the selected Excel table:

If you create a formatted Excel table for your dataset, Excel adds a sort and filter feature to the table by default. You can use this feature to sort the values of the individual columns alphabetically or numerically or filter them according to your own criteria. To do this, simply click the drop-down arrow icon in the header line of a column and select the option you want to use.

For example, in the Excel table in this tutorial, you can sort customers by revenue (highest to lowest) by clicking the arrow icon in the Sales column and selecting “Sort largest to smallest”:

Like the sort function, the filter function automatically differentiates between text and number filters, which you use according to the contents of the column. For example, you can use this feature to display only those customers who are under 50 years old in the Excel table in this tutorial. To do this, choose “Number Filters” and then “Is less than.” In the “Custom AutoFilter” dialog box that appears, enter the value “50” and confirm the filter by choosing “OK”:

If you don’t want to use the filter or the sort function for your Excel table, you can also turn off the feature. First, select one of the cells with the drop-down arrow icon and then click “Sort & Filter” in the upper menu bar (on the Home tab). Then select “Filter” from the drop-down menu to turn off the functions and hide the icons:

One of the advantages of using a formatted Excel table is that you can add a totals row that automatically presents the results of different calculation options selected from a drop-down list. For example, you can display the sum, the average, specific individual values as well as the minimum or maximum value. To add a total row to your Excel table, simply right-click any cell of the table and select “Table” and then “Totals Row” from the menu:

The totals row uses the familiar drop-down arrow icons. Click this icon in the table column for which you want to select a calculation option. Then select an option to display the result of the calculation in the row. In the following example, we want Excel to calculate the averages for the age and revenue of the customers in the table:

The advantage of creating a formatted Excel table is that you can present the data in charts that are automatically adjusted as you make changes to the table or table values. Follow these steps to create a dynamic chart:

1. Open the “Insert” tab.
2. Select all rows for which you want to present data in a chart.
3. Click “Recommended Charts” in the “Charts” section of the ribbon and select the chart type you want to use. Alternatively, you can click on one of the preselected chart icons.

If you no longer need certain (or any) formatting in your Excel table or you’ve accidentally formatted cells, you can undo the formatting of these cells at any time. This function is located on the “Home” tab of the ribbon. You can use it as follows:

1. Select the cells for which you want to clear the formatting.
2. Click the “Clear” button in the “Editing” section.
3. Select “Clear Formats” from the drop-down menu.