facebook google plus twitter
Webucator's Free Intermediate Microsoft Excel 2013 Tutorial

Lesson: Advanced Formatting

Welcome to our free Intermediate Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2013 Training course.

In this lesson, you will learn about advanced formatting.

Lesson Goals

  • Use conditional formatting to display cells differently based on their values.
  • Quickly format tables using styles.
  • Format cells using styles.

Applying Conditional Formatting

Conditional formatting enables you to automatically draw attention to interesting, exciting, concerning, unusual, or other data. Uses of conditional formatting include:

  1. Highlighting the highest or lowest numbers in a report.
  2. Highlighting numbers above or below a certain number.
  3. Highlighting specific values with specific colors.
  4. Drawing attention to specific dates.
  5. Highlighting cells that contain specific text.
  6. Highlighting numbers within a certain range.
  7. Visually displaying the values within cells.

Conditional formatting functionality includes:

  1. Highlight Cells Rules. Highlight numbers greater than, less than, between, or equal to specific numbers. Also highlight cells that include specific text, dates with a specified range, and duplicate values.
  2. Top/Bottom Rules. Highlight the top or bottom X or X% results in a data set, or numbers that are above or below average.
  3. Data Bars. Visually display values by filling portions of cells with colors based on the values.
  4. Color Scales. Visually display values by associating cell colors with the values in the cells.
  5. Icon Sets. Visually display values using icons.
  6. Formatting Based on Values in Other Cells. Formatting can be based on the value within the cell itself or on the value within another cell.

To apply conditional formatting:

  1. Choose the cell or cells to which you wish to apply conditional formatting.
  2. On the HOME tab, in the Styles group, click the Conditional Formatting command:Conditional Formatting Command
  3. Select the rule or format you wish to apply by clicking on it:Rule or Format
  4. If necessary, fill out the requested information in the dialog box that appears.

Watch and follow along as your instructor shows you some of the many ways you can use conditional formatting.

Using Conditional Formatting

Duration: 15 to 25 minutes.

In this exercise, you will practice using conditional formatting in a number of different ways.

  1. Open Conditional Formatting.xlsx from your Excel2013.2/Exercises folder.
  2. In Sheet1, in the Listing Price column, highlight the most expensive 20% of houses using yellow fill with dark yellow text.
  3. In Sheet1, in the Town column, highlight all cells containing "Fayetteville" using light red fill.
  4. In Sheet1, in the Square Feet column, use blue gradient fill to visually demonstrate the size of each house.
  5. In Sheet1, in the Bedrooms column, highlight all cells showing 4 bedrooms using a red border.
  6. In Sheet1, in the Bathrooms column, use icon indicators to draw attention to houses that have 1 or 4 bathrooms.
  7. In Sheet2, in the Date Due column, highlight all past dates in light red fill with dark red text, current dates in yellow fill with dark yellow text, and future dates in green fill with dark green text. Tip: When creating the rules, instead of entering today's actual date, enter "=today()" (do not enter the quotation marks).

Solution:

  1. In Sheet1 in the Listing Price column, highlight the most expensive 20% of houses using yellow fill with dark yellow text.
    1. Select column B.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Top/Bottom Rules, and then Top 10%...:Top 10%... Option
    3. In the Top 10% dialog box, increase the % to "20", select Yellow Fill with Dark Yellow Text, and click OK:Selected Fill Color
  2. In Sheet1, in the Town column, highlight all cells containing "Fayetteville" using light red fill.
    1. Select column C.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    3. In the Equal To dialog box, enter "Fayetteville", select Light Red Fill, and click OK:Selected Options
  3. In Sheet1, in the Square Feet column, use blue gradient fill to visually demonstrate the size of each house.
    1. Select column D.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Data Bars, and then under Gradient Fill select Blue Data Bar:Blue Data Bar Option
  4. In Sheet1, in the Bedrooms column, highlight all cells showing 4 bedrooms with a red border.
    1. Select column E.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    3. In the Equal To dialog box, enter "4", select Red Border, and click OK:Red Border Option
  5. In Sheet1, in the Bathrooms column, use icon indicators to draw attention to houses that have 1 or 4 bathrooms.
    1. Select column F.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Icon Sets, and then select one of the options under Indicators:Indicators Options
  6. In Sheet2, in the Date Due column, highlight all past dates in light red fill with dark red text, current dates in yellow fill with dark yellow text, and future dates in green fill with dark green text.
    1. Select cells B2:B9.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Less Than...:Less Than... Option
    3. In the Less Than dialog box, enter today's date, select Light Red Fill with Dark Red Text, and click OK:Dark
    4. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    5. In the Equal To dialog box, enter today's date, select Yellow Fill with Dark Yellow Text, and click OK:Yellow Fill with Dark Yellow Text Option
    6. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Greater Than...:Greater Than... Option
    7. In the Greater Than dialog box, enter today's date, select Green Fill with Dark Green Text, and click OK:Green Fill with Dark Green Text Option

Working with Styles

Styles can be applied both to tables and to individual or groups of cells. Styles can also be applied to charts and illustrations, though applying styles to charts and illustrations is not covered in this lesson.

Applying Styles to Tables

Converting Data to a Table

If your data is not already formatted as a table, then convert it to a table:

  1. Select the cells you wish to convert to a table. If you select just one cell, then Excel will guess which other cells you wish to include and will ask you to verify.
  2. On the HOME tab, in the Styles group, click the Format as Table command:Format as Table Command
  3. Select one of the options from the drop-down list:Available Options
  4. In the Format as Table dialog box, verify which cells contain the data for the table and click OK:Data in Cells

Note: Lists can also be converted to tables using the Table command on the INSERT tab. This is covered in the lesson on working with lists.

Changing the Style Applied to Your Table

When you select a cell in a table, the TABLE TOOLS DESIGN tab appears:TABLE TOOLS DESIGN Tab

To change the style applied to your table:

  1. Select any cell in your table.
  2. Click the More arrow in the Table Styles group to see all the styles available:Table Styles Group More Arrow
  3. Move your mouse over the many different styles available in the Table Styles group to see what your table will look like if you select that style:Table Styles Group
  4. Select one of the styles by clicking on it.

Applying Styles to Cells

To apply a style to a cell or group of cells:

  1. Select the cell or cells to which you wish to apply a style.
  2. On the HOME tab, in the Styles group, click the Cell Styles command:Cell Styles Command
  3. Move your mouse over the many different styles available in the drop-down list to see what your cell(s) will look like if you select that style:Style Preview
  4. Select one of the options from the drop-down list by clicking on it.

Copy Styles from Template to Template

When you save a style, it is then available in the Custom styles category, appearing on the Cell Styles menu when a worksheet contains relevant cells.

Working with Styles

Duration: 5 to 15 minutes.

In this exercise, you will convert data to a table, change the format of the table, and format cells.

  1. Open Styles.xlsx from your Webucator/Excel2013.2/Exercises folder.
  2. Convert the data in cells A3:E8 to a table using one of the Light styles.
  3. Change the format of the table to one of the Dark styles.
  4. Apply a cell style to cells A1:C1.

Solution:

  1. Convert the data in cells A3:E8 to a table, using one of the Light styles.
    1. Select a cell in the range A3:E8:
    2. On the HOME tab, in the Styles group, click the Format as Table command:Format as Table Command
    3. Select one of the Light style options from the drop-down list:Light Style Options
    4. In the Format as Table dialog box, verify which cells contain the data for the table and click OK:Cells Containing Data
  2. Change the format of the table to one of the Dark styles.
    1. Select a cell in the range A3:E8:
    2. Click the More arrow in the Table Styles group:Table
    3. Select one of the Dark style options from the list:Dart Style Options
  3. Apply a cell style to cells A1:C1.
    1. Select cells A1:C1.
    2. On the HOME tab, in the Styles group, click the Cell Styles command:Cell Styles Command
    3. Choose a style from the styles available by clicking on it:Available Styles

Creating and Modifying Templates

You can save a workbook as a template to use in the future. To create a custom template:

  1. Select the FILE menu tab and then click Save As. Navigate to where you want to save the template by clicking Browse.
  2. In the Save As dialog box, from the Save as type drop-down list, select one of the template options. Template Options
  3. Click Save to save the template (by default, it is saved in the following location: C:\Users\user name\Documents\Custom Office Templates).

Modify a Custom Template

To modify a custom template that you have created:

  1. Select the FILE menu tab and then click Open. Navigate to the C:\Users\user name\Documents\Custom Office Templates folder.
  2. Open the template you wish to modify and make changes.
  3. From Backstage view, select Save As, and then from the Save as type drop-down list, select one of the template options.
  4. Click Save to save the changes.