Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management

2. Excel 2010 Level 2 Unit 1Advanced Formatting, Formulas, and Data Management Chapter 3 Working with Tables and Data Features

3. Working with Tables and Data Features Quick Links to Presentation Contents • Create Tables • Sort and Filter a Table • CHECKPOINT 1 • Data Tools • Convert a Table to a Normal Range • Subtotal Related Data • Group and Ungroup Data • CHECKPOINT 2

4. Create Tables • Columns are called fields and are used to store a single unit of information about a person, place, or object. • The first row of the table contains column headings and is called the field names row or header row. Each column heading in the table should be unique. • Below the field names, the rows of data are called records. A record contains all of the field values related to one person, place, or object.

5. Create Tables…continued To create a table: • Select the desired range. • Click the Insert tab. • Click the Table button in the Tables group. continues on next slide… Table button

6. Create Tables…continued • At the Create Table dialog box, click OK. • Deselect the range. Create Table dialog box

7. Create Tables…continued • Once a table has been defined, typing new data in the row immediately below the last row of the table or in the column immediately to the right of the last column causes the table to automatically expand to include the new entries. • Excel displays the AutoCorrect Options button after the table is expanded. AutoCorrect Options Button

8. Create Tables…continued • The contextual Table Tools Design tab contains options for formatting the table. Table Tools Design tab

9. Create Tables…continued To change the Table Style options: • Make the desired cell active within the table. • Click the Table Tools Design tab. • Click the desired style in the Table Styles gallery OR click the More button in the Table Styles gallery and click the desired style at the drop-down gallery. drop-down gallery

10. Create Tables…continued To change the table style: • Make the desired cell active within the table. • Click the Table Tools Design tab. • Click the desired option in the Table Style Options group. Table Style Options group

11. Create Tables…continued To add a total row: • Make the desired cell active within the table. • Click the Table Tools Design tab. • Click the Total Row check box in the Table Style Options group. • Click in the total row in the column where the function will be added. • Click the down-pointing arrow. • Click the desired function. function

12. Sort and Filter a Table To sort a table: • Click the desired filter arrow button. • Click the desired sort order. • Click OK. sort order options

13. Sort and Filter a Table…continued To filter a table: • Click the desired filter arrow button. • Click the desired filter options. • Click OK. filter arrow button

14. CHECKPOINT 1 • Columns are called this and are used to store a single unit of information. • header row • records • tables • fields • Below the field names, data entered in rows are called this. • header row • records • tables • fields Answer Answer Next Question Next Question • The first row of the table contains column headings and is called the field names row or this. • header row • records • tables • fields • This contextual tab contains options for formatting the table. • Table Tools Format • Table Tools Design • Table Tools Setup • Table Tools Page Answer Answer Next Question Next Slide

15. Data Tools • The Data Tools group in the Data tab includes features useful for working with data in tables. Data Tools group

16. Data Tools…continued To split text into multiple columns: • Insert the blank column(s) next to the source data. • Select the data to be split. • Click the Data tab. • Click the Text to Columns button in the Data Tools group. continues on next slide… Text to Columns button

17. Data Tools…continued • At the Convert Text to Columns Wizard - Step 1 of 3 dialog box, with Delimited selected in the Choose the file type that best describes your data section, click the Next button. continues on next slide… Convert Text to Columns Wizard - Step 1 of 3 dialog box

18. Data Tools…continued • At the Convert Text to Columns Wizard - Step 2 of 3 dialog box, click the desired check box in the Delimiterssection. • Click the Next button. • At the final Convert Text to Columns Wizard dialog box, click the Finish button. • Deselect the range. Convert Text to Columns Wizard - Step 2 of 3 dialog box

19. Data Tools…continued To remove duplicate rows: • Select the desired range or make the cell active in the table. • Click the Data tab. • Click the Remove Duplicates button in the Data Tools group. • At the Remove Duplicates dialog box, select the desired columns to compare. • Click OK. continues on next slide… Remove Duplicates dialog box

20. Data Tools…continued • At the Microsoft Excel message box, click OK. Microsoft Excel message box

21. Data Tools…continued • Consider conditionally formatting duplicate values first to view the records that will be deleted.

22. Data Tools…continued • Excel’s data validation feature allows you to control the type of data that is accepted for entry in a cell. • You can also set parameters that validate whether the entry is within a certain range of acceptable values, dates, times, or text length.

23. Data Tools…continued To create a data validation rule: • Select the desired range. • Click the Data tab. • Click the Data Validation button in the Data Tools group. • Specify the validation criteria in the Settings tab. • Click the Input Message tab. continues on next slide… Settings tab

24. Data Tools…continued • Type the input message title and text. • Click the Error Alert tab. continues on next slide… Input Message tab

25. Data Tools…continued • Select the error style. • Type the error alert title and message text. • Click OK. Error Alert tab

26. Data Tools…continued

27. Convert a Table to a Normal Range To convert a table to a range: • Make a cell active within the table. • Click the Table Tools Design tab. • Click the Convert to Range button in the Tools group. • At the Microsoft Excel message box, click Yes. Microsoft Excel message box

28. Subtotal Related Data • A range of data with a column that contains multiple rows with the same field values can be grouped by those values. Subtotals can then be created for each group automatically. • You can choose from a list of functions for the subtotal, such as Average or Sum, and you can also create multiple subtotal values for each group. • Excel displays a new row with a summary total when the field value for the specified subtotal column changes content. • A grand total is also automatically included at the bottom of the range.

29. Subtotal Related Data…continued To create subtotals: • Select the desired range. • Click the Data tab. • Click the Subtotal button in the Outline group. continues on next slide… Subtotal button

30. Subtotal Related Data…continued • At the Subtotal dialog box, select the field to group by in the At each change in list box. continues on next slide… At each change in list box

31. Subtotal Related Data…continued • Select the desired function in the Use function list box. • Select the field(s) to subtotal in the Add subtotal to list box. • Click OK. • Deselect the range. Use function list box

32. Subtotal Related Data…continued • Excel displays the subtotals with buttons along the left side of the worksheet area used to show or hide the details for each group using Excel’s Outline feature. Show Detail button

33. Group and Ungroup Data To ungroup data by rows: • Select the grouped range within the outlined worksheet. • Click the Ungroup button in the Outline group. • At the Ungroup dialog box, click OK. Ungroup dialog box

34. Group and Ungroup Data…continued To group data by rows: • Select the range to be grouped within the outlined worksheet. • Click the Group button in the Outline group. • At the Group dialog box, click OK. Group dialog box

35. CHECKPOINT 2 • The Text to Columns button is located in this tab. • Home • Data • Insert • Page Layout • The Subtotal button is located in this tab. • Page Layout • Insert • Home • Data Answer Answer Next Question Next Question • This feature allows you to control the type of data that is accepted for entry in a cell. • Formatting • Conditional Formatting • Data Validation • Data Check • Excel can create an outline with up to how many levels? • 8 • 6 • 4 • 2 Answer Answer Next Question Next Slide

36. Working with Tables and Data Features Summary of Presentation Concepts • 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 cells • 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 • Group and ungroup data