slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management PowerPoint Presentation
Download Presentation
Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management

Loading in 2 Seconds...

play fullscreen
1 / 38

Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management - PowerPoint PPT Presentation


  • 163 Views
  • Uploaded on

Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management Chapter 1 Advanced Formatting Techniques. Advanced Formatting Techniques. Quick Links to Presentation Contents. Conditional Formatting CHECKPOINT 1 Fraction and Scientific Formatting

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management' - havard


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

Excel 2010

Level 2

Unit 1Advanced Formatting, Formulas, and Data Management

Chapter 1Advanced Formatting Techniques

advanced formatting techniques
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
conditional formatting
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.
conditional formatting continued
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

conditional formatting continued1
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

conditional formatting continued2
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

conditional formatting continued3
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

conditional formatting continued4
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

conditional formatting continued5
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

conditional formatting continued6
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

conditional formatting continued7
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
conditional formatting continued8
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

conditional formatting continued9
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.
conditional formatting continued10
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.
conditional formatting continued11
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

conditional formatting continued12
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

checkpoint 1
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

fraction and scientific formatting
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

fraction and scientific formatting continued
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

fraction and scientific formatting continued1
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.
special number formats
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

create a custom number format
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.
create a custom number format continued2
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

wrap and shrink text to fit within a cell
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).
wrap and shrink text to fit within a cell continued
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

wrap and shrink text to fit within a cell continued1
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

filter a worksheet using a custom filter
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

filter a worksheet using a custom filter continued
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

filter a worksheet using a custom filter continued1
Filter a Worksheet Using a Custom Filter…continued
  • Enter the criteria at the Custom AutoFilter dialog box.
  • Click OK.

Custom AutoFilter dialog box

filter and sort data using conditional formatting or cell attributes
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

filter and sort data using conditional formatting or cell attributes continued
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

filter and sort data using conditional formatting or cell attributes continued1
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

filter and sort data using conditional formatting or cell attributes continued2
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

checkpoint 2
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

advanced formatting techniques1
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