Shelly Cashman Microsoft Excel 2016 Module 6 Creating

Shelly Cashman: Microsoft Excel 2016 Module 6: Creating, Sorting, and Querying a Table © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 1

Objectives (Slide 1 of 2) • Create and manipulate a table • Delete duplicate records • Add calculated columns to a table with structured references • Use the VLOOKUP function to look up a value in a table • Use icon sets with conditional formatting • Insert a total row • Sort a table on one field or multiple fields • Sort, query, and search a table using Auto. Filter © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 2

Objectives (Slide 2 of 2) • Remove filters • Create criteria and extract ranges • Apply database and statistical functions • Use the MATCH and INDEX functions to find a value in a table • Display automatic subtotals • Use outline features to group, hide, and unhide data • Create a treemap chart © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 3

Project – Coffee Craft Daily Services (Slide 1 of 2) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 4

Project – Coffee Craft Daily Services (Slide 2 of 2) • Roadmap • • • Create and format a table Use LOOKUP tables in the worksheet Insert calculated fields using a structured references Apply conditional formatting and icon sets Sort tables Query a table Extract records with criteria ranges Use database functions and conditional functions Display automatic subtotals, outline, and treemap chart © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 5

Creating a Table (Slide 1 of 3) • To Format a Range as a Table • Select the range to format • Click the ‘Format as Table’ button on the HOME tab to display the Format as Table gallery • Click the desired table style • To Name the Table • Click anywhere in the table and then display the TABLE TOOLS DESIGN tab • Click the Table Name text box end enter the desired table name © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 6

Creating a Table (Slide 2 of 3) • To Remove Duplicates • Click the Remove Duplicates button on the TABLE TOOLS DESIGN tab to display the Remove Duplicates dialog box • Click the Select All button to select all columns • Click the OK button to remove duplicate records from the table • Click the OK button to finish the process • To Enter a New Record into a Table • Select the desired cell • Type in the information. After typing the information, tap or click the cell to the right or press the RIGHT ARROW key to move to the next field © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 7

Creating a Table (Slide 3 of 3) • To Center Across Selection • Select the desired range. Right-click to display the shortcut menu • Click Format Cells to display the Format Cells dialog box • Click the Alignment tab and then click the Horizontal button in the Text alignment area • Click ‘Center Across Selection’ in the Horizontal list to center the title across the selection • Click the OK button to apply the settings © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 8

Using a Lookup Table • To Use the VLOOKUP Function • With the desired cell selected, type the VLOOKUP function - Ex: =vlookup(f 9, $L$3: $M$6, 2) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 9

Adding Calculated Fields to the Table • To Create Calculated Fields • Click the desired cell • Click the ‘Accounting Number Format’ button on the HOME tab and then click the Decrease Decimal button on the HOME tab twice so that data is formatted as whole dollars • Type =[YTD Sales] * [Commission Rate] to enter the formula with structured references and then click the Enter box in the formula bar to create a calculated column © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 10

Conditional Formatting • To Add a Conditional Formatting Rule with an Icon Set • Select the range to contain the conditional formatting • Click the Conditional Formatting button on the HOME tab to display the Conditional Formatting gallery • Click New Rule in the to display the New Formatting Rule dialog box • Click the Format Style button to display the Format Style list • Tap or click Icon Sets in the Format Style list to display the icon style area in the Edit the Rule Description area • Tap or click the Icon Style box arrow to display the Icon Style list and then tap or click the desired icon style • Enter the desired values for each icon in the Value box • Tap or click the OK button to display icons in each row of the table © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 11

Working with Tables in Excel • To Insert a Total Row • Click the table to make it active • Click the Total Row check box in the TABLE TOOLS DESIGN tab to add the total row and display the record count in the last column of the table • Click the cell in the total row • Click the button on the right side of the cell to display a list of available statistical functions • Click the desired function © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 12

Sorting a Table (Slide 1 of 2) • To Sort Ascending • Click a cell in the column to be sorted, and then tap or click the Sort & Filter button on the HOME tab to display the Sort & Filter menu • Click the ‘Sort A to Z’ command to sort the table in ascending sequence by the selected field • To Sort Descending • Click a cell in the column to be sorted and display the DATA tab • Click the ‘Sort Largest to Smallest’ button to sort the table in descending sequence by the selected field © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 13

Sorting a Table (Slide 2 of 2) • To Custom Sort a Table • With a cell in the table active, click the ‘Sort & Filter’ button on the HOME tab to display the Sort & Filter menu • Click Custom Sort on the Sort & Filter menu to display the Sort dialog box • Click the Column Sort by button to display the field names in the table • Click the first field on which to sort to select the first sort level • Select the desired options for Sort On and Order • Click the Add Level button to ask a new sort level, and then repeat the previous two steps • Click the OK button to sort the table © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 14

Querying a Table Using Auto. Filter (Slide 1 of 3) • To Sort a Table Using Auto. Filter • Click the filter button in the desired sort column to display the filter menu • Click ‘Sort Oldest to Newest’ on the filter menu to sort the table in ascending sequence by the selected field. Click outside the column to view the sorted data • To Query a Table Using Auto. Filter • Display the Auto. Filter menu for the field to query • Remove the check marks next to the fields you wish to hide • Click the OK button to apply the Auto. Filter criterion © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 15

Querying a Table Using Auto. Filter (Slide 2 of 3) • To Remove Filters • Display the DATA tab • Click the Clear button on the DATA tab to display all of the records in the table • To Search a Table Using Auto. Filter • Click the filter button in the desired column to display the filter menu • Click the Search box, and then type the desired search string © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 16

Querying a Table Using Auto. Filter (Slide 3 of 3) • To Enter Custom Criteria Using Auto. Filter • • • Click the filter button in the desired cell to display the filter menu Click Number Filters to display the Number Filters submenu Click Custom Filter to display the Custom Auto. Filter dialog box Select the desired options for the Auto. Filter Click the OK button to display records in the table that match the custom Auto. Filter criteria • To Turn Off Auto. Filter • Display the DATA tab • Click the Filter button on the DATA tab to turn off the filter buttons in the table • Click the Filter button on the DATA tab again to show the filter buttons in the table © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 17

Using Criteria and Extract Ranges (Slide 1 of 2) • To Query Using a Criteria Range • Enter the criteria data in the desired cells • Click the table to make it active • Display the DATA tab and then click the Advanced button to display the Advanced Filter dialog box • Click the OK button to hide all records that do not meet the comparison criteria © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 18

Using Criteria and Extract Ranges (Slide 2 of 2) • To Extract Records • Activate the table • Click the Advanced button on the DATA tab to display the Advanced Filter dialog box • Click ‘Copy to another location’ in the Action area to cause the records that meet the criteria to be copied to a different location on the worksheet • Click the OK button to copy any records that meet the comparison criteria in the criteria range from the table to the extract range © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 19

Using Database Functions • To Use the DAVERAGE and DCOUNT Database Functions • With the desired cell selected, type the DAVERAGE function or DCOUNT function - Ex: =daverage(a 8: i 22, “Satisfaction Survey”, o 2: o 3) - Ex: =dcont (a 8: i 22, “Satisfaction Survey” q 2: q 3) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 20

Using the SUMIF, Countif, Match and Index Function (Slide 1 of 3) • To Use the SUMIF Function • With the desired cell selected, type the SUMIF Function - Ex: =sumif(c 9: c 22, ”Restaurant/Hotel”, i 9: i 22) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 21

Using the SUMIF, Countif, Match and Index Function (Slide 2 of 3) • To Use the COUNTIF Functions • With the desired cell selected, type the COUNTIF function - Ex: =countif(c 9: c 22, ”QSR”) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 22

Using the SUMIF, Countif, Match and Index Function (Slide 3 of 3) • To Use the MATCH and INDEX Functions • With the desired cell selected, type a lookup value - Ex: =index (a 9: i 22, match(q 10, a 9: a 22, 0), 5) © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 23

Summarizing Data (Slide 1 of 3) • To Convert a Table to a Range • Click the filter button in the desired column and then click ‘Sort A to Z’ to sort by category • Right-click anywhere in the table and then click Table on the shortcut menu to display the Table submenu • Click ‘Convert to Range’ to display a Microsoft Excel dialog box • Click the Yes button to convert a table to a range © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 24

Summarizing Data (Slide 2 of 3) • To Display Subtotals • Click in the desired criterion field • Click the Subtotal button on the DATA tab to display the Subtotal dialog box • Click the ‘At each change in’ button and then click Category to select a column heading on which to create subtotals • If necessary, click the Use function button and then select Sum in the Use function list • In the ‘Add subtotal to’ list, click desired values to subtotal • Click the OK button to add subtotals to the range © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 25

Summarizing Data (Slide 3 of 3) • To Use the Outline Feature • Click the desired column heading • One at a time, click each of the plus signs (+) in column two on the left side of the window to display detail records for each category © 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 26