Excel Lesson 9 Applying Advanced Formats to Worksheets
Download
Report
Transcript Excel Lesson 9 Applying Advanced Formats to Worksheets
Excel Lesson 9
Applying Advanced Formats to
Worksheets
Microsoft Office 2010
Advanced
1
Cable / Morrison
Objectives
Excel Lesson 9
2
Create custom number formats.
Use conditional formatting rules.
Create conditional formatting formulas.
Format tables.
Create custom AutoFilters.
Cable / Morrison
Microsoft Office 2010 Advanced
Objectives (continued)
Excel Lesson 9
3
Apply themes.
Switch banded rows and columns.
Add or delete rows and columns in tables.
Add totals to tables.
Cable / Morrison
Microsoft Office 2010 Advanced
Vocabulary
Excel Lesson 9
4
conditional formatting formulas
custom AutoFilter
banded columns
banded rows
tables
themes
Cable / Morrison
Microsoft Office 2010 Advanced
Introduction
Microsoft Excel has a number of advanced
tools and features.
Excel Lesson 9
–
5
–
Can be used to create professional-looking
spreadsheets
Allows advanced formatting techniques, such as
tables, themes, and conditional formatting
Cable / Morrison
Microsoft Office 2010 Advanced
Creating Custom Number Formats
Excel Lesson 9
You can create your
own format by
selecting Custom
category in Format
Cells dialog box.
Custom format in Format Cells dialog box
6
Cable / Morrison
Microsoft Office 2010 Advanced
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.
Excel Lesson 9
7
Cable / Morrison
Microsoft Office 2010 Advanced
Using Conditional Formatting
Rules (continued)
New Formatting
Rule dialog box
Excel Lesson 9
8
Cable / Morrison
Microsoft Office 2010 Advanced
Creating Conditional Formatting
Formulas
You can create your own conditional format
rule using a conditional formatting
formula.
In the New Formatting Rule dialog box:
Excel Lesson 9
9
Cable / Morrison
–
–
Select the “Use a formula to determine which
cells to format” option.
Enter the conditional formatting formula.
Microsoft Office 2010 Advanced
Formatting Tables
Excel Lesson 9
10
Tables provide 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
Cable / Morrison
Microsoft Office 2010 Advanced
Formatting Tables (continued)
Table Format gallery
Excel Lesson 9
11
Cable / Morrison
Microsoft Office 2010 Advanced
Creating Custom AutoFilters
Custom AutoFilter displays only cells that
meet specific criteria.
Excel Lesson 9
Custom AutoFilter dialog box
12
Cable / Morrison
Microsoft Office 2010 Advanced
Applying Themes
Themes
Excel Lesson 9
–
You can apply a theme from the Theme
gallery.
To open the Theme gallery:
–
–
13
Borders, background colors, shading, and graphic
effects are applied instantly to an entire workbook
On the Ribbon, click the Page Layout tab
In the Themes group, click the Themes button
Cable / Morrison
Microsoft Office 2010 Advanced
Switching Banded Rows and
Columns
Banding
Excel Lesson 9
–
14
–
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.
Cable / Morrison
Microsoft Office 2010 Advanced
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
Excel Lesson 9
–
Insert menu options
15
Cable / Morrison
Microsoft Office 2010 Advanced
Adding Totals to Tables
Excel Lesson 9
16
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.
Cable / Morrison
Microsoft Office 2010 Advanced
Adding Totals to Tables
(continued)
Table with totals
added
Excel Lesson 9
17
Cable / Morrison
Microsoft Office 2010 Advanced
Excel Lesson 9
Summary
18
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.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)
Excel Lesson 9
19
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.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)
Excel Lesson 9
20
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.
Cable / Morrison
Microsoft Office 2010 Advanced