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

2. Excel 2013 Level 2 Unit 1 Advanced 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 • Text Functions • 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 the Quick Analysis button: • Select desired range. • Click Quick Analysis button located at the bottom right of selected cells. • Click desired rule. • If necessary, enter parameter values. • If necessary, change format options. • Click OK. Quick Analysis button

6. Conditional Formatting - continued To apply conditional formatting using a predefined rule: • Select desired range. • Click Conditional Formatting button. • Point to desired rule category. • Click desired rule. • If necessary, enter parameter values. • If necessary, change format options. • Click OK. Conditional Formatting button

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

8. Conditional Formatting - continued To create and apply a new formatting rule: • Select desired range. • Click Conditional Formatting button. • Click New Rule option. continues on next slide… New Rule option

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

10. Conditional Formatting - continued To edit the formatting rule: • Select desired range. • Click Conditional Formatting button. • Click Manage Rules option. • Click desired rule. • Click Edit Rule button. continues on next slide… Edit Rule button

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

12. Conditional Formatting - continued To delete a formatting rule: • Click Conditional Formatting button. • Click Manage Rules option. • Click Show formatting rules for arrow and click This Worksheet option. • Click desired rule. • Click Delete Rule button. • Click OK. Delete Rule button

13. 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% • Red down arrow for values less than 33% • Yellow sideways arrow for values between 33 and 67%

14. Conditional Formatting - continued To apply conditional formatting using an icon set: • Select desired range. • Click Conditional Formatting button. • Point to Icon Sets option. • Click desired icon set. • Deselect range. Icon Sets option

15. Conditional Formatting - continued • Excel 2013 also provides the ability to conditionally format cells using two-color scales, three-color scales, or data bars to provide visual guides for identifying distributions or variations within a range. • Use a data bar to easily see the higher and lower values within the range. • A data bar appears in 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.

16. Conditional Formatting - continued • Color scales format the range using a two-color or three-color palette. • Excel provides 12 color scale options, half of which are two-color combinations and half of which are three-color combinations. • The gradation of color applied to a cell illustrates the cell’s value relative to the rest of the range. • Color scales are useful for viewing the distribution of data.

17. Conditional Formatting - continued • The image below displays the payroll worksheet for ViewItVideo with data bar and color scale conditional formatting applied. conditional formatting

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

19. CHECKPOINT 1 • The Conditional Formatting button is located on 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

20. Fraction and Scientific Formatting To apply fraction formatting: • Select desired range. • Click Number Format button arrow. • Click More Number Formats option. continues on next slide… More Number Formats option

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

22. 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.

23. Special Number Formats To apply a special number format: • Select desired range. • Click Number group dialog box launcher. • Click Special option in Category list box. • Click desired option in Type list box. • Click OK. • Deselect range. Special option

24. 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.

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

26. Create a Custom Number Format -continued

27. Create a Custom Number Format -continued To create a custom number format: • Select desired range. • Click Number group dialog box launcher. • Click Custom option in Categorylist box. • Click General in Typetext box. • Press Delete. • Type desired custom format codes. • Click OK. • Deselect range. Custom option

28. 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).

29. Wrap and Shrink Text to Fit within a Cell -continued To wrap text in a cell: • Select desired cell(s). • Click Wrap Text button. • Deselect cell(s). Wrap Text button

30. Wrap and Shrink Text to Fit within a Cell -continued To shrink text to fit within a cell: • Select desired cell(s). • Click Alignment group dialog box launcher. • Click Shrink to fit option in Text control section. • Click OK. • Deselect cell(s). Shrink to fit option

31. Text Functions continues on next slide…

32. Text Functions - continued continues on next slide…

33. Text Functions - continued continues on next slide…

34. Text Functions - continued

35. Text Functions - continued To use the substitute text formula: • Make desired cell active. • Type =SUBSTITUTE(. • Type source text cell address. • Type a comma. • Type text to be changed in quotation symbols. • Type a comma. • Type replacement text in quotation symbols. • Type ). • Press Enter. SUBSTITUTE text formula

36. Text Functions - continued To convert text to uppercase: • Make desired cell active. • Type =UPPER(. • Type source cell address OR type text to convert in quotation symbols. • Type ). • Press Enter. convert text to uppercase

37. Filter a Worksheet Using a Custom Filter To filter using a Custom AutoFilter: • Select desired range. • Click Sort & Filter button. • Click Filter option. continues on next slide… Filter option

38. Filter a Worksheet Using a Custom Filter -continued • Deselect range. • Click filter arrow at top of desired column. • Point to Number Filters option. • Click desired filter category. continues on next slide… Number Filters option

39. Filter a Worksheet Using a Custom Filter -continued • Enter criteria at Custom AutoFilter dialog box. • Click OK. Custom AutoFilter dialog box

40. Filter and Sort Data Using Conditional Formatting or Cell Attributes To filter by icon set: • Select desired range. • Click Sort & Filter button. • Click Filter option. • Deselect range. • Click filter arrow at top of desired column. • Point to Filter by Color option. • Click desired icon. Filter by Color option

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

42. Filter and Sort Data Using Conditional Formatting or Cell Attributes -continued To sort by color: • Select desired range. • Click Sort & Filter button. • Click Filter option. • Deselect range. • Click filter arrow at top of desired column. • Point to Sort by Color option. • Click desired color. Sort by Color option

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

44. 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 on this tab. • FILE • HOME • INSERT • DATA Answer Answer Next Question Next Slide

45. 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 • Modify text using the text functions PROPER, UPPER, LOWER, SUBSTITUTE, RIGHT, LEFT, MID and TRIM • Filter a worksheet using a custom AutoFilter • Filter and sort a worksheet using conditional formatting or cell attributes