What is an Excel table?
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, share, and update.
Convert the data into an excel table:
- Select the dataset and got to Insert and select the table option in the table group.
- Select the dataset and press Ctrl+T
How do I recognize an Excel Table?
By default, Excel tables have a very distinctive appearance, 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
The main benefits of converting data into Excel tables will be explained under the following topics.
- Table headers remain visible even when you scroll down
- Tables are very easily formatted
- Add or remove banded rows and columns from the Design tab, by simply ticking or un-ticking the checkboxes
- Change the Style of the Table, using the Styles Gallery shown below
- Filters are added to your data
- Using calculated columns i.e. Tables drag formulas down automatically
- You can add Totals to Tables automatically
- Inserting and deleting rows and columns
- Using structured reference i.e. Tables automatically name ranges and every time you update your table with new or additional data.
- Tables automatically expand when you add new rows or columns
- Dynamic charts
- You can use Slicers and Timelines to filter your data and charts.
- You can add Forms to facilitate the entry of new data
Steps to add the data entry form option to the Quick Access Toolbar:
- Right-click on any of the existing icons in the Quick Access Toolbar.
- Click on ‘Customize Quick Access Toolbar.
- In the ‘Excel Options’ dialog box that opens, select the ‘All Commands’ option from the drop-down.
- Scroll down the list of commands and select ‘Form’.
- Click on the ‘Add’ button.
- Click OK.