Excel Tables offer a range of features for working with list-style data
Much of the data that you use Excel to analyze comes in a list form. You might need to sort the data, filter it, sum it, and perhaps even chart it. Excel tables provide superior tools for working with data in list form.
If you want to sum columns of data automatically so that the totals show only the sum of visible cells (for example), Excel's Tables features can do it. And if you want to format any Excel data in just a couple of quick steps, Excel's Tables features can handle that task, too. And as for using a form instead of punching numbers into ordinary spreadsheet cells, Tables once again can do the job.
Here are my top 10 secrets for managing lists of data using Excel Tables.
1.Create a Table in Any of Several Ways
The first step in learning how to work with Excel's Tables features is to use the program to create a table. You'll need a list with column headings and (if you wish) row headings. Select the data, including the heading rows and columns, and click Insert > Table. Visually confirm that the range you've selected is correct, click the My table has headers checkbox, and click OK. Excel will then create a formatted table for you. If you would prefer to choose a particular table format, select the same data area and click Home (instead of Insert); then choose a table style from the Table Styles gallery.
You can create a table from the Insert tab or (as here) from the Home tab, where you can choose a style at the same time.
2. Remove the Filter Arrows
When you want to use some features of an Excel table, but you don't plan to filter or sort your data, you can hide the filter arrows. To do this, click somewhere inside the table and then click Data > Sort & Filter > Filter. Now you can toggle between hiding the arrows with one click and revealing them with the next. The shortcut keystroke combination Shift-Ctrl-L accomplishes the same thing.
Click the Filter option to toggle the display of the filter arrows on or off.
3. Take the Format but Ditch the Table
Formatting data as an Excel table is the quickest way to achieve a neatly formatted range of cells in Excel. The only potential problem is that it may seem that you can't get the formatting without getting all the unwanted table features as well. But while this limitation is technically true, you don't have to keep the table features if you don't want them. To borrow a table style for any worksheet, first create the data as a table, making sure to choose your preferred table style for formatting it.
Next, click inside the table and then click Table Tools > Design > Convert to Range. Click Yeswhen Excel prompts you with 'Do you want to convert the table to a normal range?' and the table will revert to being a regular range--but with its attractive formatting intact.
4. Fix Ugly Column Headings
The filter arrows in an Excel table's column headings look downright ugly when those headings are right-justified. The arrows cover the rightmost characters in the headings, and there is no obvious way to fix the problem. The workaround is to indent the content from the right side of the cell. To do this, select the cells containing the headings that are partly hidden and click Home > Increase Indent. If the cell contents respond by jumping to the left edge of the cell, click Home > Align Rightto return them to right justification. Click Increase Indent more than once as necessary to position the heading text well clear of the filter arrows.
Use the Increase Indent option with right-aligned column headings to push them to the left of the filter arrows.
5. Add New Rows to a Table
Rows in a table behave a little differently from rows in a regular worksheet. If you need to add a new row to a table, and if the Totals row is not visible, click in the bottom right cell in the table and press the Tab key. This simple procedure adds a new row to the table, just as it would if you were working with a Word table.
To add rows to the end of a table, drag the small indicator in the bottom right corner of the table to add more rows and more adjacent columns, if desired. To add a row inside a table, click in a cell either above or below where the row should be inserted and click eitherHome > Insert > Insert Table Row Above or Home > Insert > Insert Table Row Below, depending on where you want the new row to appear. The table's formatting will automatically adjust so that the new row is correctly formatted.
To add a new row inside a table, use the special insert options that appear only when you've selected a cell inside a table.
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.
When you choose to total a column in a table, Excel will add a SUBTOTAL function that totals only the values in visible cells.
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.
Charts created from Excel tables automatically expand and contract as you add data to or remove data from the table.
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: ClickFile > 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 theForm 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.
Excel's hidden Form feature creates a data entry form for your table automatically.
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 Filterand 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 300000and 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.
Excel's built-in AutoFilters feature gives you a select range of prebuilt filters for filtering table data.
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, clickData > 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.
OR searches across multiple columns are relatively complex and can't be generated by using the AutoFilter options.
No comments:
Post a Comment