Excel Tips. View an alphabetical listing of Excel Tech Tips. Adding Data Forms to the Excel 2010 Quick Access Toolbar. Those of you that enjoyed using the feature. Find out how to place your Excel data into a Table, and discover how Tables will save you time and effort with these 9 benefits. Install Windows Media Format 11 Runtime Software. Update Access From Excel Table Reference' title='Update Access From Excel Table Reference' />Table Formula in Excel Something I didnt Know Till YesterdayTable formulas were something that I discovered recently. Actually our reader m b commented that he prefers to convert a range to a table and then employ table formulas instead of named ranges. That got me curious enough to explore them further and heres what I learnt. A Table in Excel. A table is a feature in Excel that makes it easier to format and analyze a set of data points in a spreadsheet. Tables were introduced in Excel 2. Lists feature in the earlier versions. In Excel 2. 00. 7 onwards, you can also use the table formulas to extract data from a table. How Does One Create a Table. In Excel 2. 00. 7 and later, all you have to do to convert a given range to a table is to simply select the range and then click the Table button under the Insert tab on the ribbon. Better still, as our reader Sam pointed out, use the shortcut key CTRL T. So What Good is the Table For. Lots actually. Formatting Completely change the look and feel of your data with a few mouse clicks. Summarize by adding row for Total Just turn on the check box for Total Row and you have a new row inserted just below the data set with the totals. Not only totals, you can select any cell in this row and choose from a number of aggregation options such as count, min, max etc. Export and Share Export and share the table with other users using Share. Point. Provide a Name to the Table You can give the table a specific name say SalesData and use it later in your formulas. To give a new name to the table, open up the Name Manager under the Formulas tab and then edit the table name. Table Formulas in ExcelFlaming Bisons You made me read all this just to show what an Excel table looks like I already know what it is so why dont you come straight to the point Oh That was rude. Did nobody ever tell you that patience is a kingly virtue Table Formulas let you access table in a easy and intuitive manner. Lets begin by converting a range to a table. When you create a new table, Excel will provide with a default name, say something like Table. This may not be most intuitive of names and you may wish to rename it to something else that is easier to remember and comprehend for others. Open the Design tab and overwrite the text in the Table Name box to something like sales. The opens up a whole new box of chocolates You can now refer to and use the entire table, individual columns, rows, data range, headers or totals in your formulas. Using a Specific Column from the Table in a Formula. Say you wanted to know the average for all items in the Revenue column. Enter something like AVERAGEsalesRevenue in a cell and smile. The formula is so intuitive that it hardly needs explaining. However being the aI am, let me be me. The salesRevenue string refers the data points in the Revenue column of the sales table. Target would have referred to the Target column of the same table. We can now use this like any other range in any excel formula. So MAXsalesRevenue, LARGEsalesRevenue,5 and COUNTsalesRevenue are all valid formulas. Using a Specific Row from the Table in a Formula. Whats good for a column is good for the row. Format, however differ. To refer to a row in a table, we use the symbol. So if you want to refer to the the 1. So something like countifsales,lt would give you count of non blank cells in the particular row of the table. If you copy the same formula to the cell immediately below, the corresponding values from the next row would be returned even though the formula hasnt changed. Using the Entire Table in a Formula. Whats good for a column and row must be good for the table To refer to the entire table, use salesAllTo refer to only the data portion of the table, use salesDataTo refer to the headers, use salesHeadersUsing the SUBTOTAL Formula with the Table. Another interesting feature of the Table is the use of SUBTOTAL function. The SUBTOTAL formula has two parts the first one indicates the formula to use for aggregation and the second one contains the range to use. So SUBTOTAL9,A1 A1. A1 A1. 0 while SUBTOTAL1,A1 A1. Coming back the the Excel Table, you can aggregate over the entire table or a portion of it the values by using the SUBTOTAL formula and providing it with the reference to a particular row, column or the entire table. Just as in the example above, you can get the average of the Revenue column by using SUBTOTAL1, salesRevenue. If you noticed, when we turned on the Total Row option, a new row with the total for columns got added to the table. We can now go ahead and modify the formulas using any of the formula options shown above. So the totals are not limited to just being summing but can very well be extended to averages, min, max, variance etc. Using the Totals for a Particular Column in a Formula. To refer to the total for a column in the table, say Revenue, we can now write something similar to salesTotals,Revenue. Please note that the total value returned may not be the total SUM but is determined by the SUBTOTAL function parameter used to aggregate the column. Lets illustrate this with an example. If the SUBTOTAL formula in the Total Row for the Revenue column contained an aggregation function parameter with a value of 4, the total would have returned maximum value from the column. Now when somebody wants to refer to this total using salesTotals,Revenue, the total returned would not be a sum of column values but the maximum. I have a feeling that theres more that can be done with table formulas.