Excel Lesson 9 Applying Advanced Formats to Worksheets

1. Excel Lesson 9Applying Advanced Formats to Worksheets Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Create custom number formats. • Use conditional formatting rules. • Create conditional formatting formulas. • Format tables. • Create custom AutoFilters. 2 2

3. Objectives (continued) • Apply themes. • Switch banded rows and columns. • Add or delete rows and columns in tables. • Add totals to tables.

4. Vocabulary • conditional formatting formulas • custom AutoFilter • banded columns • banded rows • tables • themes 4 4

5. Introduction • Microsoft Excel has a number of advanced tools and features. • Can be used to create professional-looking spreadsheets • Allows advanced formatting techniques, such as tables, themes, and conditional formatting

6. Creating Custom Number Formats • You can create your own format by selecting Custom category in Format Cells dialog box. Custom format in Format Cells dialog box

7. Using Conditional Formatting Rules • Conditional formatting applies a font, border, or pattern to worksheet cells when certain conditions exist in those cells. • Conditional formatting is applied using the New Formatting Rule dialog box.

8. Using Conditional Formatting Rules (continued) • New Formatting Rule dialog box

9. Creating Conditional Formatting Formulas • You can create your own conditional format rule using a conditional formatting formula. • In the New Formatting Rule dialog box: • Select the “Use a formula to determine which cells to format” option. • Enter the conditional formatting formula.

10. Formatting Tables • Tablesprovide professional presentation features for displaying worksheet data. • Excel offers a variety of table formats in the Table Format gallery. • On the Home tab in the Styles group, click the Format as Table button. • Displays the Table Format gallery

11. Formatting Tables (continued) • Table Format gallery

12. Creating Custom AutoFilters • Custom AutoFilter displays only cells that meet specific criteria. Custom AutoFilter dialog box

13. Applying Themes • Themes • Borders, background colors, shading, and graphic effects are applied instantly to an entire workbook • You can apply a theme from the Theme gallery. • To open the Theme gallery: • On the Ribbon, click the Page Layout tab • In the Themes group, click the Themes button

14. Switching Banded Rows and Columns • Banding • Banded rows: one row will have a lighter format, and the adjacent row will have a darker format • Banded columns: same kind of formats appear in columns • To switch between banded rows and banded columns, use the Table Tools Design tab.

15. Adding or Deleting Rows and Columns in Tables • Commands on the Insert and Delete menus in the Cells group on the Home tab • Maintain the banding formatting of a table Insert menu options

16. Adding Totals to Tables • Excel retains the format when adding totals to rows or columns of data in a table. • To add totals to rows in a table: • Type a new column heading next to the far-right column heading and press Enter. • To add a total row at the bottom of a column: • Select Total Row in the Table Style Options group on the Design tab.

17. Adding Totals to Tables (continued) • Table with totals added

18. Summary In this lesson, you learned: • You can create a custom number format and apply it to data in the worksheet cells. • Conditional formatting rules enable you to highlight data that meets specific criteria. • Conditional formatting formulas let you highlight data based on the criteria you enter in the formula.

19. Summary (continued) • Formatting data and text as a table applies various professional formats and filters. • Custom AutoFilters give you the option to apply specific filters based on criteria entered into the custom AutoFilter dialog box.

20. Summary (continued) • Themes let you apply a formatting scheme throughout the workbook, thereby eliminating the time-consuming task of applying individual formats in each worksheet. • You can select and change banded rows and banded columns in a table. • You can add a total row and total column in a table.