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 ap­pro­pri­ate 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:

Al­ter­na­tive­ly, 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 pre­de­fined 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 in­di­vid­ual columns al­pha­bet­i­cal­ly or nu­mer­i­cal­ly 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 au­to­mat­i­cal­ly dif­fer­en­ti­ates 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 Aut­oFil­ter” 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 ad­van­tages of using a formatted Excel table is that you can add a totals row that au­to­mat­i­cal­ly presents the results of different cal­cu­la­tion options selected from a drop-down list. For example, you can display the sum, the average, specific in­di­vid­ual 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 cal­cu­la­tion option. Then select an option to display the result of the cal­cu­la­tion 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 au­to­mat­i­cal­ly 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 “Rec­om­mend­ed Charts” in the “Charts” section of the ribbon and select the chart type you want to use. Al­ter­na­tive­ly, you can click on one of the pre­s­e­lect­ed chart icons.

If you no longer need certain (or any) for­mat­ting in your Excel table or you’ve ac­ci­den­tal­ly formatted cells, you can undo the for­mat­ting 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 for­mat­ting.
2. Click the “Clear” button in the “Editing” section.
3. Select “Clear Formats” from the drop-down menu.