Microsoft Excel 2010

1. MicrosoftExcel 2010 Chapter 5 Creating, Sorting, and Querying a Table

2. Objectives • Create and manipulate a table • Delete sheets in a workbook • Add calculated columns to a table • Use icon sets with conditional formatting • Use the VLOOKUP function to look up a value in a table • Print a table • Add and delete records and change field values in a table Creating, Sorting, and Querying a Table

3. Objectives • Sort a table on one field or multiple fields • Query a table • Apply database functions, the SUMIF function, and the COUNTIF function • Use the MATCH and INDEX functions to look up a value in a table • Display automatic subtotals • Use Group and Outline features to hide and unhide data Creating, Sorting, and Querying a Table

4. Project – Kenson College Scholarship Fundraiser Table Creating, Sorting, and Querying a Table

5. Project – Kenson College Scholarship Fundraiser Table Creating, Sorting, and Querying a Table

6. General Project Guidelines • Create and format the table • Sort the table • Obtain answers to questions about the data in the table using a variety of methods to query the table • Extract records from the table based on given criteria • Display subtotals by grouping data in the table Creating, Sorting, and Querying a Table

7. Guidelines for Creating a Table in Excel Creating, Sorting, and Querying a Table

8. Formatting a Range as a Table • Select the range to format • Click the Format as Table button (Home tab | Styles group) to display the Format as Table gallery • Click the desired table style Creating, Sorting, and Querying a Table

9. Formatting a Range as a Table Creating, Sorting, and Querying a Table

10. Modifying a Table Quick Style • Select a cell to activate the table • Click the Format as Table button (Home tab | Styles group) to display the Format as Table gallery and then right-click the desired table style • Click Duplicate on the shortcut menu to display the Modify Table Quick Style dialog box • Type the desired table style name in the Name text box to name the new style • With Whole Table selected in the Table Element list, click the Format button to display the Format Cells dialog box Creating, Sorting, and Querying a Table

11. Modifying a Table Quick Style • Select the desired font, font style, and color in the Format Cells dialog box • Click the OK button • Click the OK button Creating, Sorting, and Querying a Table

12. Entering Records into a Table Creating, Sorting, and Querying a Table

13. Adding New Fields to a Table • Enter the new column headings • Select the first cell under the new column heading, and then enter the desired formula • Ex: =[@[YTD FundsRaised]] / [@Quota] • Format the range as desired Creating, Sorting, and Querying a Table

14. Adding New Fields to a Table Creating, Sorting, and Querying a Table

15. Creating a Lookup Table Creating, Sorting, and Querying a Table

16. Using the VLOOKUP Function • With the desired cell selected, type the VLOOKUP function • Ex: =vlookup(i9, $l$3:$m$7, 2 Creating, Sorting, and Querying a Table

17. Adding a Conditional Formatting Rule with an Icon Set • Select the range to contain the conditional formatting • Click New Rule in the Conditional Formatting list to display the New Formatting Rule dialog box • Click the Format Style box arrow to display the Format Style list • Click Icon Sets in the Format Style list to display the Icon area in the Edit the Rule Description area • Click the Icon Style box arrow to display the Icon Style list and then click the desired icon style Creating, Sorting, and Querying a Table

18. Adding a Conditional Formatting Rule with an Icon Set • Enter the desired values for each icon in the New Formatting Rule dialog box • Click the OK button to display icons in each cell Creating, Sorting, and Querying a Table

19. Adding a Conditional Formatting Rule with an Icon Set Creating, Sorting, and Querying a Table

20. Using the Total Row Check Box • Make the table active • Click the Total Row check box (Table Tools Design tab | Table Style Options group) to add the total row and display the record count in the far-right column of the table • Click the cell in the total row • Click the arrow on the right side of the cell to display a list of available statistical functions • Click the desired function Creating, Sorting, and Querying a Table

21. Using the Total Row Check Box Creating, Sorting, and Querying a Table

22. Printing the Table • Activate the table, and then click File on the Ribbon to open the Backstage view • Click the Print tab to display the Print gallery • Click the Print Active Sheets in the Settings area to display a list of parts of the workbook to print • Select Print Selected Table to choose to print only the selected table • If necessary, change the orientation • Click the Print button to print the table Creating, Sorting, and Querying a Table

23. Printing the Table Creating, Sorting, and Querying a Table

24. Sorting a Table in Ascending Sequence by Name Using the Sort & Filter Button • Click a cell in the column to be sorted, and then click the Sort & Filter button (Home tab | Editing group) to display the Sort & Filter menu • Click the Sort A to Z command to sort the table in ascending sequence by the selected field Creating, Sorting, and Querying a Table

25. Sorting a Table in Ascending Sequence by Name Using the Sort & Filter Button Creating, Sorting, and Querying a Table

26. Sorting a Table Using the Sort Command on an AutoFilter Menu • Click the desired AutoFilter arrow to display the AutoFilter menu for the selected field • Click the desired sort command Creating, Sorting, and Querying a Table

27. Sorting a Table on Multiple Fields Using the Custom Sort Command • With a cell in the table active, click the Sort & Filter button (Home tab | Editing group) to display the Sort & Filter menu • Click Custom Sort on the Sort & Filter menu to display the Sort dialog box • Click the Sort by box arrow 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 Creating, Sorting, and Querying a Table

28. Sorting a Table on Multiple Fields Using the Custom Sort Command Creating, Sorting, and Querying a Table

29. Querying a Table Using AutoFilter • Display the AutoFilter 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 AutoFilter criterion Creating, Sorting, and Querying a Table

30. Showing All Records in a Table • Click the Filter button (Data tab | Sort & Filter group) to display all of the records in the table Creating, Sorting, and Querying a Table

31. Entering Custom Criteria Using AutoFilter • Click the Filter button (Data tab | Sort & Filter group) to display the AutoFilter arrows in the table • With the table active, click the desired field to display the AutoFilter menu • Point to Number Filters to display the Number Filters submenu • Click Custom Filter to display the Custom AutoFilter dialog box • Select the desired options for the AutoFilter • Click the OK button Creating, Sorting, and Querying a Table

32. Entering Custom Criteria Using AutoFilter Creating, Sorting, and Querying a Table

33. Creating a Criteria Range on the Worksheet • Select the desired range, and then click the Copy button (Home tab | Clipboard group) • Select the destination cell, and then press the ENTER key to paste the contents on the Office Clipboard to the destination area • Enter the desired criteria • Select the range, click the Name box in the formula bar, type the desired range name, and then press the ENTER key Creating, Sorting, and Querying a Table

34. Creating a Criteria Range on the Worksheet Creating, Sorting, and Querying a Table

35. Querying a Table Using the Advanced Filter Dialog Box • Activate the table • Click the Advanced button (Data tab | Sort & Filter group) to display the Advanced Filter dialog box • Click the OK button to hide all records that do not meet the comparison criteria Creating, Sorting, and Querying a Table

36. Querying a Table Using the Advanced Filter Dialog Box Creating, Sorting, and Querying a Table

37. Creating an Extract Range and Extracting Records • Activate the table • Click the Advanced button (Data tab | Sort & Filter group) 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 Creating, Sorting, and Querying a Table

38. Creating an Extract Range and Extracting Records Creating, Sorting, and Querying a Table

39. Displaying Automatic Subtotals in a Table • Select the cell to display a subtotal • Right-click anywhere in the table and then point to the Table command on the shortcut menu to display the Table submenu • Click Convert to Range on the Table submenu to display a Microsoft Excel dialog box • Click the Yes button to convert a table to a range • Click the Subtotal button (Data tab | Outline group) to display the Subtotal dialog box • Click the ‘At each change in’ box arrow and then click the column heading on which to create subtotals Creating, Sorting, and Querying a Table

40. Displaying Automatic Subtotals in a Table • If necessary, select Sum in the Use function list • In the ‘Add subtotal to’ list, select the values to subtotal • Click the OK button to add subtotals to the range Creating, Sorting, and Querying a Table

41. Displaying Automatic Subtotals in a Table Creating, Sorting, and Querying a Table

42. Zooming Out on a Subtotaled Table and Using the Outline Feature • Click the Zoom Out button as many times as desired to reduce the zoom percent • Click the row level symbol 2 on the left side of the window to hide all detail rows and display only the subtotal and grand total rows • Click each of the lower two show detail symbols (+) on the left side of the window to display detail Records and to change the show detail symbols to hide detail symbols • Click the row level symbol 3 on the left side of the window to show all detail rows Creating, Sorting, and Querying a Table

43. Zooming Out on a Subtotaled Table and Using the Outline Feature Creating, Sorting, and Querying a Table

44. Removing Automatic Subtotals from a Table • Click the Subtotal button (Data tab | Outline group) to display the Subtotal dialog box • Click the Remove All button (Subtotal dialog box) to remove all subtotals and close the Subtotal dialog box Creating, Sorting, and Querying a Table

45. Removing Automatic Subtotals from a Table Creating, Sorting, and Querying a Table

46. Chapter Summary • Create and manipulate a table • Delete sheets in a workbook • Add calculated columns to a table • Use icon sets with conditional formatting • Use the VLOOKUP function to look up a value in a table • Print a table • Add and delete records and change field values in a table Creating, Sorting, and Querying a Table

47. Chapter Summary • Sort a table on one field or multiple fields • Query a table • Apply database functions, the SUMIF function, and the COUNTIF function • Use the MATCH and INDEX functions to look up a value in a table • Display automatic subtotals • Use Group and Outline features to hide and unhide data Creating, Sorting, and Querying a Table

48. MicrosoftExcel 2010 Chapter 5 Complete