How do tables work in excel

Use the arrow keys to select and the TAB key to confirm. To enter a column name, enter an opening square bracket [ after the table name follow the same process - type a few letters, select with arrow keys, and use TAB to confirm. Video: Introduction to Structured References and Tables. You can quickly check a structured reference with the formula bar.

For example, the following formula will select data in the "Address" column in the "Properties" table shown above:. All Excel tables have a style applied by default, but you can change this at any time. Select any cell in the table and use the Table Styles menu on the Table Tools tab of the ribbon. With one click, the table will inherit the new style. Table formatting is not a requirement of Excel tables.

To use a table without formatting, select the first style in the styles menu, which is called "None". Tip: you can use this style to remove all table formatting before converting a table back to a normal range.

When you apply a table style, local formatting is preserved by default. However, you can optionally override local formatting if you want. Right-click any style and choose "Apply and Clear formatting":. You can right-click any style and choose "Set as Default". New tables in the same workbook will now use the default you set.

Note: to set a default table style in new workbooks, create a custom start-up template as described in this article. In the template file, set the default table style of your choice.

When you use a table as the source for a pivot table, the pivot table will automatically stay up to date with changes in data. Watch the video below to see how this works. Video: Use a table for your next pivot table. Tables are a great way to create dynamic charts. Microsoft Excel is convenient for creating tables and doing calculations. Its working area is a set of cells to be filled with data.

Consequently, the data can be formatted, used for building graphs, charts, summary reports. For a beginner, working with tables in Excel may seem complicated at the first glance. It is differs considerably from the principles of table construction in Word. However, let us start from the very basics: creating and formatting tables.

By the time you reach the end of this article, you will understand there is no better tool for creating tables than Excel.

Working with Excel tables for dummies does not tolerate haste. There are different ways to create a table for a specific purpose, and each of them has its advantages. Therefore, let us start with assessing the situation visually. It is a set of cells in columns and rows. An Excel Table is not just any range of data with headings, but a specific Excel object that unlocks additional properties.

Contrary to a random set of data, Tables work as a whole, something that can be very useful and make your Excel spreadsheet much easier to use, to share and to update. It usually looks like this:. How do I recognize an Excel Table? By default, Excel tables have the very distinctive appearance you can see above, with blue banded rows. However this format can be changed so it doesn't guarantee you actually have a Table.

But you can know you have an Excel Table when you select a cell and the Design tab shows up on the ribbon, allowing Table-specific features, like shown in the screenshot below:. How to create a Table in Excel? That's super easy!

The following dialog box shows up:. Tick the checkbox "My table has headers" if there was already headers in the selected range, otherwise Excel will automatically add a headers row because Excel Tables always require to have headers.

Click OK, and the table is created. A good practice would be to go to the Design tab and rename your Table. It will be useful later when formulas or data sources refer to it. Why should you use Excel tables? There are many benefits to using Excel tables, because Excel recognizes that each column is a separate field. Reason 1: Tables are very easily formatted. When a Table is created, Excel automatically applies a specific formatting to it.

But you can easily change this formatting:. Add or remove banded rows and columns from the Design tab, by simply ticking or un-ticking the checkboxes as shown in the screenshot below.

Change the Style of the Table, using the Styles Gallery shown below. This is very easy and will let you change the Table to your preferred Style really really quick. Look on the screenshot below how the same Table can look different when simply changing the applied Style.

Reason 2: Table headers remain visible even when you scroll down. Excel recognizes the top row of the Table as column headers. And when you scroll down, you will always see the headers, allowing you to know anytime with which column you are working, as in the screenshot below:.

This is as long as your selection is within the Table, but the A,B, C letters will be displayed as usual when your selection is not within the table. Reason 3: Filters are added to your data. OK that's a small one, but when you create a Table, Excel will add filters to it, making it ready to use.

Reason 4: Tables automatically expand when you add new rows or columns. If you add any data in a cell adjacent to your Table, the Table will automatically be resized to include this in a new row or column.

Important tip : Excel Tables have a little handle at the bottom-right, as you can see below:. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information.