The screenshot below is an extract from a course booking system used by a company that runs Excel training courses. There’s actually 1137 rows in the report. G1 and G2 will contain formulas that calculate total revenue and total number of attendees.
This spreadsheet will be re-used each month however, because it is showing year-to-date, every month, there will be a different number of rows of data with the result that the formulas will need to be edited. For example in January, the formula in G1 might be =SUM(D2:D100) and in February it might be =SUM(D2:D210).
One option would be to enter the formula as =SUM(D2:D1048576) however, this is considered by many to be wasteful as it may consume unnecessary processor resources.
What is a Table?
A better option is to convert this "regular range" into a Table. A Table in Excel is simply a column-and-row structure with headings. Tables have a number of additional features above-and-beyond those of a regular range.
Converting a Regular Range to a Table
- Select a single cell in the range to be converted
- From the Ribbon, select Insert > Table
- Excel "guesses" the range to be converted and more often than not it is correct but in the event that it isn’t, simply edit the range in the Where is the data for your table box
- Ensure that the My table has headers box is ticked so that the first row of the selected range is treated as headings
- Click OK
Formatting the Table
By default, filter arrows are added to the heading cells and a blue-and-white style is added to the cells.
If you want to turn off the filter arrows, click into any cell in the table. This displays the Table Tools Ribbon (which is only displayed when the cursor is in a Table) and remove the tick from the Filter Button checkbox.
If you want to change or remove the style, click the down-pointing arrow to the right of the Table Styles and either select a different style or select Clear
Renaming a Table
Every Table has a name which Excel assigns when the Table is created. To change the name, use the Table Name box at the left hand side of the Table Tools Ribbon. Table names must not contains spaces. In this example, I left the table name as Table1
Creating the Formula
The formula in G1 will add up all the numbers in column D but only within the table (i.e. from D2 to Dx where x represents the last row of the Table)
- In G1, type =SUM(
- Type Table1 which is the name of the Table
- Type a "square bracket" – [
- A list of all the headings is displayed
- Double click on Revenue
- Type a closing "square bracket" – ]
- Type a closing normal bracket – to end the SUM function )
- Press Enter
The completed formula is =SUM(Table1[Revenue])
Repeat this process for the Total Attendees: =SUM(Table1[Attendees])
Thank you!
This is going to be very helpful.