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 1Advanced Formatting Techniques

3. Advanced Formatting Techniques Quick Links to Presentation Contents • Conditional Formatting • CHECKPOINT 1 • Fraction and Scientific Formatting • Special Number Formats • Create a Custom Number Format • Wrap and Shrink Text to Fit within a Cell • Filter a Worksheet Using a Custom Filter • Filter and Sort Data Using Conditional Formatting or Cell Attributes • CHECKPOINT 2

4. Conditional Formatting • Conditional formatting applies format changes to the cells within a range that meet a certain condition. • Cells that do not meet the condition remain unformatted. • Changing the appearance of a cell based on a condition allows you to quickly identify values that are high or low or that represent a trend.

5. Conditional Formatting…continued To apply conditional formatting using a predefined rule: • Select the desired range. • Click the Conditional Formatting button in the Styles group in the Home tab. • Point to the desired rule category. • Click the desired rule. • If necessary, enter the parameter values. • If necessary, change the format options. • Click OK. Conditional Formatting button

6. Conditional Formatting…continued • Using the Top/Bottom Rules list you can elect to highlight cells based on a top ten or bottom ten value or percent, or by above average or below average values. Top/Bottom Rules option

7. Conditional Formatting…continued To create and apply a new formatting rule: • Select the desired range. • Click the Conditional Formatting button in the Styles group in the Home tab. • Click the New Rule option. continues on next slide… New Rule option

8. Conditional Formatting…continued • At the New Formatting Rule dialog box, click the desired rule type. • Add the desired formatting attributes. • Click OK twice. New Formatting Rule dialog box

9. Conditional Formatting…continued To edit the formatting rule: • Select the desired range. • Click the Conditional Formatting button in the Styles group in the Home tab. • Click the Manage Rules option. • Click the desired rule. • Click the Edit Rule button. continues on next slide… Edit Rule button

10. Conditional Formatting…continued • At the Edit Formatting Rule dialog box, make the desired changes to the parameters and/or formatting options. • Click OK twice. Edit Formatting Rule dialog box

11. Conditional Formatting…continued To delete a formatting rule: • Click the Conditional Formatting button in the Styles group in the Home tab. • Click the Manage Rules option. • Click the Show formatting rules for arrow and click the This Worksheet option. • Click the desired rule. • Click the Delete Rule button. • Click OK. Delete Rule button

12. Conditional Formatting…continued • Format a range of values by using an icon set to classify data into three to five categories. • Excel places an icon in a cell to visually portray the cell’s value relative to the other cell values within the selected range. • Icons are assigned to cells based on default threshold values for the selected range. For example, if you choose the 3 Arrows (Colored) icon set, icons are assigned as follows: • Green up arrow for values greater than or equal to 67 percent • Red down arrow for values less than 33 percent • Yellow sideways arrow for values between 33 and 67 percent

13. Conditional Formatting…continued To apply conditional formatting using an icon set: • Select the desired range. • Click the Conditional Formatting button in the Styles group in the Home tab. • Point to the Icon Sets option. • Click the desired icon set. • Deselect the range. Icon Sets option

14. Conditional Formatting…continued • Excel 2010 also provides the ability to conditionally format cells using two-color scales, three-color scales, or data bars to provide visual guides to identify distributions or variations within a range. • Use a data bar to easily see the higher and lower values within the range. • A data bar is added to the background of a cell. The length of the bar is dependent on the value of the cell as it relates to the range. A cell with a higher value within the range displays a longer bar than a cell with a lower value within the range.

15. Conditional Formatting…continued • Color scales format the range using either a two-color or three-color palette. • Excel provides twelvecolor scale options, half of which are two-color combinations and half of which are three-color combinations. • The color applied to a cell illustrates the cell’s value in comparison to higher or lower values within the range. • Color scales are useful for viewing the distribution of data.

16. Conditional Formatting…continued • The image below displays the payroll worksheet for Vantage Video Rentals with data bar and color scale conditional formatting applied. conditional formatting

17. Conditional Formatting…continued To apply conditional formatting using a formula: • Open the New Formatting Rule dialog box. • Click the Use a formula to determine which cells to format option. • Type the desired formula. • Click the Format button. • At the Format Cells dialog box, change the desired formats. • Click OK two times. Use a formula to determine which cells to format option

18. CHECKPOINT 1 • The Conditional Formatting button is located in this tab. • File • Home • Page Layout • Data • Format a range of values using this to classify data into three to five categories. • data bar • object • rule • icon set Answer Answer Next Question Next Question • This defines the criterion by which the cell is selected for formatting. • data bar • object • rule • icon set • Use this to easily see the higher and lower values within the range. • data bar • object • rule • icon set Answer Answer Next Question Next Slide

19. Fraction and Scientific Formatting To apply fraction formatting: • Select the desired range. • Click the Number Format list arrow in the Number group in the Home tab. • Click the More Number Formats option. continues on next slide… More Number Formats option

20. Fraction and Scientific Formatting…continued • At the Format Cells dialog box, click the Fraction option in the Category list box. • Click the desired option in the Type list box. • Click OK. • Deselect the range. Fraction option

21. Fraction and Scientific Formatting…continued • Scientific formatting converts a number to exponential notation. • Part of the number is replaced with E + n where E means exponent and n represents the power. • For example, the number 1,500,000.00 formatted in scientific number format displays as 1.50E+06. In this example, +06 means add 6 zeros to the right of the number left of E and then move the decimal point 6 positions to the right.

22. Special Number Formats To apply a special number format: • Select the desired range. • Click the Format Cells: Number dialog box launcher in the Number group of the Home tab. • Click the Special option in the Category list box. • Click the desired option in the Type list box. • Click OK. • Deselect the range. Special option

23. Create a Custom Number Format • You can create a custom number format for a worksheet in which you want to enter values that do not conform to any of the predefined number formats.

24. Create a Custom Number Format…continued continues on next slide…

25. Create a Custom Number Format…continued

26. Create a Custom Number Format…continued To create a custom number format: • Select the desired range. • Click the Format Cells: Number dialog box launcher in the Number group of the Home tab. • Click the Custom option in the Category list box. • Click General in the Type list box. • Press Delete. • Type the desired custom format codes. • Click OK. • Deselect the range. Custom option

27. Wrap and Shrink Text to Fit within a Cell • Several options exist for formatting long labels that do not fit within the column width. • The column width can be expanded, the font can be reduced to a smaller size, a group of cells can be merged, or you can allow the text to spill over into adjacent unused columns. • Additional options, available in the Format Cells dialog box with the Alignment tab selected, include Wrap text and Shrink to Fit(located in the Text control section).

28. Wrap and Shrink Text to Fit within a Cell…continued To wrap text in a cell: • Select the desired cell(s). • Click the Wrap Text button in the Alignment group in the Home tab. • Deselect the cell(s). Wrap Text button

29. Wrap and Shrink Text to Fit within a Cell…continued To shrink text to fit within a cell: • Select the desired cell(s). • Click the Format Cells: Alignment dialog box launcher in the Alignment group in the Home tab. • Click the Shrink to fit option in the Text control section. • Click OK. • Deselect the cell(s). Shrink to fit option

30. Filter a Worksheet Using a Custom Filter To filter using a Custom AutoFilter: • Select the desired range. • Click the Sort & Filter button in the Editing group in the Home tab. • Click the Filter option. continues on next slide… Filter option

31. Filter a Worksheet Using a Custom Filter…continued • Deselect the range. • Click the filter arrow button in the desired column. • Point to the Number Filters option. • Click the desired filter category. continues on next slide… Number Filters option

32. Filter a Worksheet Using a Custom Filter…continued • Enter the criteria at the Custom AutoFilter dialog box. • Click OK. Custom AutoFilter dialog box

33. Filter and Sort Data Using Conditional Formatting or Cell Attributes To filter by icon set: • Select the desired range. • Click the Sort & Filter button in the Editing group in the Home tab. • Click the Filter option. • Deselect the range. • Click the filter arrow button in the desired column. • Point to the Filter by Color option. • Click the desired icon. Filter by Color option

34. Filter and Sort Data Using Conditional Formatting or Cell Attributes…continued To filter by color: • Select the desired range. • Right-click within the selected range. • Point to the Filter option. • Click the desired filter option. Filter option

35. Filter and Sort Data Using Conditional Formatting or Cell Attributes…continued To sort by color: • Select the desired range. • Click the Sort & Filter button in the Editing group in the Home tab. • Click the Filter option. • Deselect the range. • Click the filter arrow button in the desired column. • Point to the Sort by Color option. • Click the desired color. Sort by Color option

36. Filter and Sort Data Using Conditional Formatting or Cell Attributes…continued To define a custom sort: • Select the desired range. • Click the Sort & Filter button in the Editing group in the Home tab. • Click the Custom Sort option. • At the Sort dialog box, define the color to sort first and add a level for each other color. Sort dialog box

37. CHECKPOINT 2 • This type of formatting converts a number to an exponential notation. • exponential • conversion • mathematic • scientific • Text wrapped within a cell causes this to automatically increase. • font size • page size • column width • row height Answer Answer Next Question Next Question • In a custom number format, use this format code to separate the positive value format from the negative value format. • # • 0 • ; • ? • The Sort & Filter button is located in this tab. • File • Home • Insert • Data Answer Answer Next Question Next Slide

38. Advanced Formatting Techniques Summary of Presentation Concepts • Apply conditional formatting by entering parameters for a rule • Apply conditional formatting using a predefined rule • Create and apply a new rule for conditional formatting • Edit, delete, and clear conditional formatting rules • Apply conditional formatting using an icon set, data bars, and color scale • Apply conditional formatting using a formula • Apply fraction and scientific formatting • Apply a special format for a number • Create a custom number format • Apply wrap text and shrink to fit text control options • Filter a worksheet using a custom AutoFilter • Filter and sort a worksheet using conditional formatting or cell attributes