Module 7

Create a custom AutoFill list

Create a custom list if you have a list of products, students, or other values that you use frequently. You can automatically fill values from the list rather than manually typing them in cells every time you need them. This will allow for data entry to be faster and more accurate.Click and drag to select range A4:A7.You will create a custom AutoFill list from the names in this range.Click the File tab on the Ribbon.Excel displays Backstage view.Click Options.The Excel Options dialog box opens.In the left pane of the Excel Options dialog box, click Advanced.Advanced options for working with Excel appear in the right pane of the dialog box.In the General section, click the Edit Custom Lists button.The Custom Lists dialog box opens. The Custom lists box displays built-in custom lists as well as any lists you may have already created.Click the Import button.Excel inputs the list from the selected cells into the List Entries box as a new custom list and adds the list to the Custom lists box.Click OK.The Custom Lists dialog box closes. The Excel Options dialog box appears on the screen.Click OK.The Excel Options dialog box closes, and Excel returns to the worksheet window.

insert subtotals

Including subtotals in a range is a quick way to summarize and present your worksheet data. You can subtotal your data by location, by year, or by any field you want, and include a grand total at the bottom.Click the sort and filter arrow in cell B4.You want to insert subtotals for each Year in the worksheet, so you first need to sort the table by that field.On the Sort & Filter menu, click Sort Smallest to Largest.Excel sorts the Year field, sorting from smallest to largest values.Right-click cell A4.A shortcut menu opens. You need to convert the table to a range before you can add subtotals.On the shortcut menu, point to Table.A submenu opens.On the submenu, click Convert to Range.A Microsoft Excel dialog box opens asking you to confirm you want to convert the table to a range.Click Yes.Excel converts the table to a range. Note that the filter arrows disappear from the column headings.Click the Data tab on the Ribbon.Now you are ready to add subtotals.In the Outline group, click the Subtotal button.The Subtotal dialog box opens.In the dialog box, click the At each change in arrow.Excel displays a list of column headings.Click Year.You will create subtotals for each Year. By default, the subtotal will use the Sum function. You will accept this setting, but you need to change the values used in the subtotal.Under Add subtotal to, click the Goal check box to deselect it.Click the Actual Sales check box to select it.You want to subtotal the Actual Sales values for each Year. You will accept the remaining default settings in the dialog box.Click OK.Excel displays the subtotals, as well as a grand total for all records.

Delete duplicate records from a table

Remove duplicates from a table so calculations such as totals generate accurate results.Click and drag to select the range A3:D19.The table is selected and the Table Tools Design tab appears on the ribbon.Click the Table Tools Design tab.In the Tools group, click the Remove Duplicates button.The Remove Duplicates dialog box opens. You can select the column or columns from which you want duplicates removed. When the dialog box opens, all columns are checked, and the Select All button is selected. You will accept this setting.Click OK.Excel removes three duplicate records from the table. A Microsoft Excel dialog box opens with information concerning the number of duplicates removed and the number of table entries remaining.Click OK.The Microsoft Excel dialog box closes. Excel recalculates the table total to reflect the deleted records.

Sort a table based on a single field

Sorting your Excel table can arrange data in the order best suited to your needs. Sort your data by last name or other field to speed up your work flow by placing the records in a predictable order.Click cell E10.You want to sort the table on the Last Name field, so you need to select a cell in the table column for that field.In the Editing group on the Home tab, click the Sort & Filter button.The Sort & Filter menu opens.On the menu, click Sort A to Z.Excel sorts the table in ascending alphabetical order by the Last Name field.

Sort a table based on a custom sort order

Using a custom sort order lets you sort on multiple fields, which allows you to fine-tune your worksheet for data analysis. For example, sorting your table on a primary field such as a geographical area, and then sorting each primary field by a value such as a rating, allows you to compare values within the primary categories.Click cell A4.You must select the table before you can use the Sort command.In the Editing group on the Home tab, click the Sort & Filter button.The Sort & Filter menu opens.On the menu, click Custom Sort.The Sort dialog box opens. You will create your custom sort order here by specifying the sort column, Sort On setting, and sort order for each sort level you add.Click the Column Sort by arrow.Excel displays a list of fields in the table. The first field you choose will be the primary, or major, sort field.Click City.Excel will first sort all table records by City. The Sort On setting is Cell Values and the Order setting is A to Z. You will accept these settings and define your next sort field.In the Sort dialog box, click the Add Level button.Excel adds a second sort level to the dialog box.Click the Then by arrow.Within each City category, Excel will further sort the table records by the field you choose here.Click Sales Code.The Sort On setting is Cell Values, which you will accept, but you want to change the sort order.Click the Order arrow for the second sort level.Excel displays Smallest to Largest as the sort order.Click Largest to Smallest.Click OK.Excel sorts the table by City in ascending alphabetical order. Within each City category, Excel arranges the records by Sales Code in descending order.