There is rarely a workbook I use that does not contain at least one Excel Table. What is an Excel Table? An Excel Table is a range of related cells grouped to make managing and analyzing much easier. An Excel Table provides a reference to a self-expanding range of cells with a header row and filtering. In addition, an Excel Table delivers the following advantages over using just a range of cells:
- Excel Table name – a customizable name applied to the range of cells for easy reference and to include table cells and ranges within a formulas
- Excel Table formatting – providing quick and easy formatting of the data range
- Calculated columns – inputting a formula into a single cell will result in it being replicated to the other cells in the table column
- Total Row – provides a variety of calculations for a table column, including sum, count, average, and so on.
To convert a range of related cells to an Excel Table, first make sure you have a header row (the first row containing the description of the column) and then with a cell in the range selected, click Format as Table on the Home tab.
Clicking the command shows the Table formats available. Select the one you would like to use. Now confirm the range chosen by Excel is correct and that it has identified your first row has headers.
The range of related cells is now an Excel Table. Note the Table Design tab at the end of the ribbon. This tab is only available when you have selected a cell within the Table. The tab provides access to the Excel Table tools, including the place to change the table name, which I recommend doing.
To demonstrate one of the significant advantages of Excel Tables, we will summarize the data using the sumifs function. The sumifs function sums a range of cells based on up to 127 different criteria.
=sumifs(sum_range, criteria_range1, criteria1,….)
For our example, we will summarize the sales data by region. The Table has been renamed to Sales, and the resulting formula is as follows.
The sum range in the formula is the table name plus the column header in square brackets sales[Amount]. When typing the formula, the autocomplete will show the table name in the list of options. Then after entering the opening “[“ the list of headers is displayed in the autocomplete for selection.
Excel Tables make it much quicker and easier to input formulas correctly.
If you would like to bring your Excel skills to a new level with Excel Tables, please check out our webinar K2’s Excel Tables – Database Technology Comes to Spreadsheets – On Demand (2 hours)
Enjoy Excel Tables within your workbooks.
If you would like to bring your Excel skills to a new level with Excel Tables, please check out our webinar K2’s Excel Tables – Database Technology Comes to Spreadsheets – On Demand (2 hours)
Enjoy Excel Tables within your workbooks.
RSS Feed