6. Calculate Accurate Totals
Anyone who has ever tried to use the SUM function to total a column of data in which some of the rows are hidden has received a nasty surprise: The SUM function calculates the total of all of the cells in a range, whether they’re visible or not. This characteristic of the function means that the result won’t be the total of the numbers in the visible rows—and that discrepancy can be a huge problem. The way tyo avoid this difficulty is to use the SUBTOTAL function instead. Excel will do this automatically when you use its Total row feature for your table.
When you want to add a total row to the table, click inside the table, right-click, and choose Table > Totals Row; or click inside the table and click Table Tools > Design > Total Row. In either case, a total row will appear at the foot of the table. If the last column contains numerical values, Excel will automatically use a SUBTOTAL function to sum them.
To add a total to any other column, click in the appropriate cell in the Total row, and in the drop-down menu click SUM. This operation will add a SUBTOTAL formula to the cell that will total only visible values when the table is filtered. You may choose other calculation options from this drop-down list, including Min, Max, Count, and Average.
7. Create a Chart From Table Data
One significant benefit of formatting a list as a table is that charts created from table data change dynamically when you add data to or remove data from the table. So a column chart that charts the values in a range will expand to incorporate new values when you add them to the table. This is the case whether you add data to the bottom of the table or introduce a new column to the right of it. Creating a chart based on the table is the same as creating any chart in Excel—only the behavior of the chart is different. Tables of this type are extremely useful when you work with data that expands or contracts over time.
8. Enter Data Using a Simple Form
Typing lots of data across a wide table can be quite cumbersome; often, entering data into a form is easier. Earlier versions of Excel included a handy Form tool; that tool is still available, but you won’t find it on the Ribbon. To make it easier to find, you can add it to Excel’s Quick Access Toolbar: Click File > Options > Quick Access Toolbar. In the Choose Commands From list, click All Commands and then scroll down and click Form…. Click Add to add the tool to the Quick Access Toolbar, and then click OK.
To use the form, click somewhere inside your table and then click the Form button to display a form dialog area. The form heading is the sheet name, and the form contains boxes where you can preview the current form data and add new data. To add new data, click New and type the data into the relevant text boxes. To view the form data, click Find Prev or Find Next to move through the data one row (record) at a time. To exit the form, click Close.
9. Sort and Filter Table Data
One key feature of Excel’s tables is their ability to sort and filter the data in the table. To perform either of these actions, click the down arrow to the right of any table column and then choose a Sort or Filter option. The two Sort options available are ‘ascending’ and ‘descending’. The Filter options vary depending on whether you’re working with a column of numbers, text, or dates.
You can then select from among a number of predefined options, or click Custom Filter and build your own. Alternatively you can create complex filters such as AND and OR filters. For example, locating values in a column that are less than $200,000 or more than $400,000 involves using an OR filter. To create it, click Custom Filter and then build both parts of the search in the dialog area, making sure to click the OR option. Similarly you can create AND filters that work across two columns, thereby enabling you to display information such as “All entries for Canada, where sales are greater than $300,000.” In this case you would select to view only ‘Canada’ in the Location column. In the Sales column, click Numbers Filters > Custom Filter > is greater than, type 300000 and click OK.
Any column that has a filter in place will show a filter icon instead of the downward-pointing triangle, so you can see at a glance where the filters are. To clear a filter, click the Filter icon and click Clear filter from; or click Home > Sort & Filter > Clear to clear all filters from all columns in the table.
10. Create Complex OR Searches Across Multiple Columns
One type of search that you can’t build using the menu options is an OR search of the type “All entries for Canada or where sales are greater than $200,000.” Consequently you must write a search instruction of this type in a different way. To do so, first copy the table heading row and paste it a few rows immediately below your table. Beneath these headings, in the Location column, type ="=Canada" and in the second row, in the Sales column, type >200000. Click inside the table, click Data > Advanced > Filter the list, in-place. Confirm that the List range is the table range, and set the Criteria Range to an area covering the second set of headings and the two data rows below it. Then click OK to filter the list.
If you’ve enjoyed this article, check out these related stories: