Webucator's Free Intermediate Microsoft Excel Tutorial
Lesson: Advanced Formatting
Welcome to our free Intermediate Microsoft Excel tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2019 Training course.
In this lesson, you will work with Excel's advanced formatting options.
- Learn to use conditional formatting to display cells differently based on their values.
- Learn to quickly format tables using styles.
- Learn to 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:
- Highlighting the highest or lowest numbers in a report.
- Highlighting numbers above or below a certain number.
- Highlighting specific values with specific colors.
- Drawing attention to specific dates.
- Highlighting cells that contain specific text.
- Highlighting numbers within a certain range.
- Visually displaying the values within cells.
Conditional formatting functionality includes:
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.
Top/Bottom Rules. Highlight the top or bottom X or X% results in a data set, or numbers that are above or below average.
Data Bars. Visually display values by filling portions of cells with colors based on the values.
Color Scales. Visually display values by associating cell colors with the values in the cells.
Icon Sets. Visually display values using icons.
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:
- Choose the cell or cells to which you wish to apply conditional formatting.
- On the Home tab, in the Styles group, click the Conditional Formatting command:
- Select the rule or format you wish to apply by clicking on it:
- If necessary, fill out the requested information in the dialog box that appears.
Using Conditional Formatting
Duration: 15 to 25 minutes.
In this exercise, you will practice using conditional formatting in a number of different ways.
- Open Conditional Formatting.xlsx from your Excel2019.2/Exercises folder.
- In Sheet1, in the Listing Price column, highlight the most expensive 20% of houses using yellow fill with dark yellow text.
- In Sheet1, in the Town column, highlight all cells containing "Fayetteville" using light red fill.
- In Sheet1, in the Square Feet column, use blue gradient fill to visually demonstrate the size of each house.
- In Sheet1, in the Bedrooms column, highlight all cells showing 4 bedrooms using a red border.
- In Sheet1, in the Bathrooms column, use icon indicators to draw attention to houses that have 1 or 4 bathrooms.
- 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).
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:
- 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.
- On the Home tab, in the Styles group, click the Format as Table command:
- Select one of the options from the drop-down list:
- In the Format As Table dialog box, verify which cells contain the data for the table and click OK:
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:
To change the style applied to your table:
- Select any cell in your table.
- Click the More arrow in the Table Styles group to see all the styles available:
- 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:
- Select one of the styles by clicking on it.
Applying Styles to Cells
To apply a style to a cell or group of cells:
- Select the cell or cells to which you wish to apply a style.
- On the Home tab, in the Styles group, click the Cell Styles command:
- 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:
- 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.
- Open Styles.xlsx from your Webucator/Excel2019.2/Exercises folder.
- Convert the data in cells A3:E8 to a table using one of the Light styles.
- Change the format of the table to one of the Dark styles.
- Apply a cell style to cells A1:C1.
Creating and Modifying Templates
You can save a workbook as a template to use in the future. To create a custom template:
- Select the File menu tab and then click Save As. Navigate to where you want to save the template by clicking Browse.
- In the Save As dialog box, from the Save as type drop-down list, select one of the template options.
- 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:
- Select the File menu tab and then click Open. Navigate to the C:\Users\user name\Documents\Custom Office Templates folder.
- Open the template you wish to modify and make changes.
- From Backstage view, select Save As, and then from the Save as type drop-down list, select one of the template options.
- Click Save to save the changes.