Working with tables in Excel

According to the ‘Dikke Van Dale’, a table is an ‘ordered list of data’. If we look at the Excel program, everything seems to be a table. But looks are deceiving. The program does not automatically consider an ordered list as a unit. To work with table functions, Excel first needs to know which data makes up a table. That is very simple. How exactly, we explain below.

A table is a useful thing in Excel. This is automatically formatted, making the data clearer. For example, consider a list of 100 addresses. Other actions you can apply to tables include:

• Headers
  A header indicates what data a table contains. For example, a mailing list contains headers such as: ‘address’, ‘city’, ‘postal code’. A table starts with this text.
• layout
  The appearance of a table can easily be changed using ‘table styles’.
• To sort
  Filter buttons (see below) make sorting very easy.
• Filter
  A table automatically gets filter buttons. This makes it easy to filter all kinds of data. You can read how.

Start by entering the data you want to put in a table. For example, a mailing list.

• Open Excel by clicking Start > Excel.
• Enter the names (headers) of the data in the first row. For example, in cell A1 ‘First name’, B2 ‘Last name’, C1 ‘Address’, D1 ‘Postal code’, E1 ‘City of residence’, etc.
• Then enter addresses, one in each row.

When everything has been entered, the table can be created. If you have a lot of data and want to add to the table later, that’s no problem at all. Prepare the table as described below and complete the data at a later date.

• Click on any cell that contains data.
• Click on the tab Start.
• Under ‘Styles’, click Format as table.
• Choose a format by clicking on it.
• Put a tick in front of My table contains headers.
• click on OK.

The table has been created.

If you don’t like the chosen layout, change it in a few mouse clicks:

• Click on any cell within the table.
• Make sure the ‘Home’ tab is selected. click on Start if another tab is active.
• Under ‘Styles’, click Format as table.
• Choose a different format by clicking on it.

To delete a row (from left to right) or a column (from top to bottom) of data:

• Right-click on the number next to the row to be deleted. Click on the letter(s) for a column.
• click on remove.

A table can easily be expanded with new data. In this case a new name with corresponding address.

• Select the first empty cell below the table.
• Start entering the data. The table automatically expands and the formatting is applied to the new data.

The sort function changes the order of the data in a table.

• Sort the table by clicking the drop-down arrow to the right of the headers.
• Click on the desired sort option. The options depend on the contents of the table: text can be sorted from A to Z or from Z to A. Numbers from low to high or from high to low and data from old to new or from new to old.

A sorting arrow appears next to the fold-out arrow. It shows that that data has been sorted.

With the Filter function you can only make visible those data that meet the criteria you entered. For example, with a table with birthdays, you can only show those people whose birthdays are in November.

• Click the drop-down arrow to the right of the headers.
• Choose from the filters at the bottom of the drop-down menu. A check mark means that the data is visible, without a check mark it becomes invisible.
• click on OK.

Filters can be applied to several data at once. Active filters can be recognized by a drawn filter at the fold-out arrow.

• Turn off the filter again? Then click on the fold-out arrow.
• click on Filter out [koptekst tabel].

In addition to the filters that you can check, there are other filter options. Allows you to sort data from a table that matches certain properties. For example: only select addresses in Rotterdam or only show the details of people who do not have an email address at Chello.

• Click the drop-down arrow to the right of the headers.
• click on Text filters.
• Choose from ‘Is (not) equal to’, ‘Begins/Ends with’, ‘Contains (not)’ or ‘Custom filter’.
• Enter the desired criteria.
• click on OK.