Use Excel’s Table Feature for Quick Calculations
In Microsoft Excel 2007 when you convert a range of data into a table, you will have access to some great features. You can summarize data in a column simply by clicking a button. In addition, you can create structured formulas within a table without creating range names. Since the table will have a name, you can refer to the data within the table by using the table name and/or the column headers.
First you must convert your list to a table. Make sure a cell is active within the list. On the Insert tab of the Ribbon, click the Table command. Make sure that there are no blank rows or columns within the list of data, and Excel will define and name the first table range Table1.
It is very easy to summarize data within columns. To do this, on the Design tab, in the Table Style Options group, click the Total Row checkbox. Every cell across the bottom of the table now has a drop-down arrow to allow you to choose a function for any column, such as Average, Count, Max, Min, Sum, etc.
Once data has been converted to a table, you can structure a formula by using column headers instead of standard cell references or range names. Advantages of using a structured formula in a table include:
- Once you type the references, the formula will be copied to the entire column automatically (even if you don’t start in the first cell of the column).
- The column headers may contain spaces or special characters.
- If you change the text in a column header, it will update in the formula.
- If you add or delete rows, the formulas will adjust and return the correct result.
In the simple illustration below, Total Sales can be calculated by typing =[# Sold]*[Item Cost]. Brackets are necessary when using this method. Notice that as you begin typing the column header text after the left bracket, you will see a drop-down list with the header name and a unique icon that identifies this as a column header:
Once the formula is entered, the correct result will automatically appear in the entire column. In addition to the structured formulas using column headers, you can also create formulas using the table name, for example =SUM(Table1). As a result, you can quickly obtain a grand total of all of the numeric data within a table. However, in order to avoid a circular reference, this formula must be typed outside of the table range.
For more information on other great features in Excel, you may want to consider taking an IT Training STEPS workshop.
