Microsoft Excel 2007 – Level 2

1. Chapter 3 Microsoft Excel 2007 – Level 2 WORKING WITH TABLES AND DATA FEATURES

2. Create a table in a worksheet Expand a table to include new rows and columns Add a calculated column in a table Format a table by applying table styles and table style options Add a total row to a table and add formulas to total rows Sort and filter a table Split contents of a cell into separate columns Remove duplicate records Restrict data entry by creating validation criteria Convert a table to a normal range Create subtotals in groups of related data Ungroup data Summarize data using database functions DSUM and DAVERAGE Summarize data using the SUBTOTAL function Performance Objectives

3. An Excel Table Each column contains similar information Each column is a field Column labels are field names Each row is a record

4. Header row or field names row - the Table’s first row must contain the column headings (labels or field names) Each column heading must be unique Recommend that headings be formatted differently from the rest of the data in the table There cannot be any blank rows A worksheet may contain more than one table Creating a Table

5. Converting a Range to a Table Select the range to be converted, then Click Verify the range and click OK

6. Modifying a Range Automatic expansion when adding rows or columns Click to showexpansion options Type into first row after the table or first column right of the table

7. Add a Calculated Column to a Table Table expandedwith first entry into the column Type formula into cell Formula is propagatedautomatically

8. Applying Table Styles Click any cell in the table Hover mouse over style to preview it,click to apply it Hide/show header row Hide/show total row Formattingemphasis

9. Adding a Total Row to a Table Click any cell in the table Check Total Row Added when Total Row checked Click a cell in the total row and click the desired function

10. Sorting and Filtering a Table Click filter arrow for the column Sort by that column only Clear filter Click desired options Filter by Attorney(s) Total row shows results for filtered records

11. Sorting by Multiple Columns Click any cell in the table, then click Click down arrows to choose field and order Click to add a column to the sort

12. Clear Sort or Filters Click any cell in the table, then click

13. Split the Contents of a Cell into Separate Columns Insert a columnto the right of the column to be split Then click

14. Split the Contents of a Cell into Separate Columns …/2 Click

15. Split the Contents of a Cell into Separate Columns …/3 Clickdatadelimiter Click

16. Split the Contents of a Cell into Separate Columns …/4 Clickcolumn data format Verify accuracy Click

17. Split the Contents of a Cell into Separate Columns …/5 Result ofSplit

18. Removing Duplicate Records Click any cell in the table, then click Choose those fieldsthat might contain duplicate values Then click

19. Select the cell(s) to be validated either data already entered or validation on data entry Specify validation parameters: the type of data acceptable values the range of dates, time, or text length values in a list that has been set up as a drop-down list when the cell is active Optionally add an input message Optionally add an error alert message Data Validation

20. Data Validation…/2 Bottom button displays a menu Select the cell(s)to have data validation, then click Select the type of data to allow Fill in restrictions, based on the data type Click OK

21. Using Data Validation…/3 Data Validation dialog box Click the down arrow to display the data types to be allowed

22. Validating Numeric Data Data Validation dialog box Click the down arrow to display the operator options Click to return to the worksheet to enter these values Enter the smallest allowable number Enter the highest allowable number

23. Entering Invalid Data An error message displays when data validation is set for a cell and a user tries to enter invalid data

24. Including an Input Message Data Validation dialog box Enter text to appear in the Message Title bar Enter the message to be displayed When a user selects the cell, the input message will be displayed

25. Including an Error Message Data Validation dialog box Enter text to appear in the Message Title bar Enter the message to be displayed Select the style -Stop -Warning -Information

26. Error Message Styles Stop Warning Information

27. Restricting Data Entry to Values within a List Select the cell(s)to have data entryfrom a list, then click Choose List from the Allow drop-down Type the values in the list

28. Restricting the Length of Data Entered Select the cell(s)to have data entryfrom a list, then click Choose Text Length from the Allow drop-down Choose the operator Type the length value

29. Subtotaling Related Data Data must be in a normal range, rather than a Table range Data must sorted in order of the fields to be grouped Data must not have blank rows Excel will create subtotal rows and a grand total row

30. Convert a Table to a Normal Range To use the Subtotal feature or to treat the data as a normal range in the worksheet Click any cell in the table, then click Click

31. Subtotaling Related Data ../2 Select the rangeto have subtotalsand click Click to select the fieldto have the subtotal Click to select other Summary functions Click the check box next to the field(s) containing the values to be subtotaled

32. Subtotaling Related Data ../3 Subtotals are displayed in outline view Level symbols Hide /ShowDetail Level buttons Subtotals Grand Total

33. Subtotaling Related Data ../4 To remove Subtotals and the Grand total Select the rangethat has subtotalsand click ThenClick

34. Grouping and Ungrouping Data To group data, select the rangethat has subtotalsand click To ungroup data, select the desired range and click

35. Using Database Functions Use Database functions to analyze a data range (database), based on criteria set up in separate cell ranges =DSUM(database, field, criteria) =DAVERAGE(database, field, criteria) Database Criteria DSUM formulas

36. Steps to Use DFunctions 1. Set up the database range- First row must have column headings with unique names 2. Set up the criteria range(s)- Must not overlap the database range- First row must have column headings with names that exactly match those in the database range- Second row contains a value or expression that indicates which database records should be used for the function calculation 3. Insert a Dfunction formula into a cell outside the database or criteria ranges Consider creating range names for the database / criteria ranges

37. Inserting a Database Function Insert tab, Function Choose Database Dfunctions are listed

38. Entering Function Arguments Database range Field within the DB Criteria range Enter the appropriate arguments

39. Include records witheither condition Include records witheither condition Include records withboth conditions Criteria Range Examples

40. Database Functions

41. Using the SUBTOTAL Function Use to obtain calculated results for filtered records or records not hidden =SUBTOTAL(function_num,ref1,ref2,…) Range(s) of cells to consider for calculation Only works for columns of numbers

42. Features Summary How do you create a table? Select the range to be converted, then Click Verify the range and click OK

43. Features Summary How must be unique about the first row of a table? The first row must contain unique names for eachcolumn

44. Features Summary How do you add rows or columns toa table? Type into a cell immediately below or immediately to the right of the table; the table will be expanded automatically

45. Features Summary How do you add a total row toa table? Click any cell in the table Check Total Row Added when Total Row checked Click a cell in the total row and click the desired function

46. Features Summary How do you filter a table? Click the down arrow beside a column header

47. Features Summary How do you perform a multilevel sort? Click any cell in the table, then click Click down arrows to choose field and order Click to add a column to the sort

48. Features Summary How do you set up data validation? Select the cell(s)to have data validation, then click Select the type of data to allow Fill in restrictions, based on the data type

49. Features Summary How do you convert a table to a normal range? Click any cell in the table, then click Click

50. Features Summary How can you split a range of data into two columns? Insert a column to the rightof the column to be split Then, click the Text to Columns buttonin the Data Tools group