- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Chapter 20: Visualizing Data Using Conditional Formatting
on text, dates, blanks, nonblanks, and errors. This rule type is very similar to how conditional formatting was set up in previous versions of Excel.
•Format only top or bottom ranked values: Use this rule type to create rules that involve identifying cells in the top n, top n percent, bottom n, and bottom n percent.
•Format only values that are above or below average: Use this rule type to create rules that identify cells that are above average, below average, or within a specified standard deviation from the average.
•Format only unique or duplicate values: Use this rule type to create rules that format unique or duplicate values in a range.
•Use a formula to determine which cells to format: Use this rule type to create rules based on a logical formula. See “Formula-Based Conditions,” later in this chapter.
FIGURE 20.3
Use the New Formatting Rule dialog box to create your own conditional formatting rules.
Conditional Formats That Use Graphics
This section describes the three conditional formatting options that display graphics: data bars, color scales, and icons sets. These types of conditional formatting can be useful for visualizing the values in a range.
Using data bars
The data bars conditional format displays horizontal bars directly in the cell. The length of the bar is based on the value of the cell, relative to the other values in the range.
485
Part III: Creating Charts and Graphics
New Feature
The data bars feature is improved significantly in Excel 2010. Data bars now display proportionally (just like a bar chart), and there is now an option to display data bars in a solid color (no more forced color gradient) and with a border. In addition, negative values can now display in a different color, and to the left of an axis. n
A simple data bar
Figure 20.4 shows an example of data bars. It’s a list of tracks on Bob Dylan albums, with the length of each track in column D. I applied data bar conditional formatting to the values in column D. You can tell at a glance which tracks are longer.
On the CD
The examples in the section are available on the companion CD-ROM. The workbook is named data bars examples.xlsx.
FIGURE 20.4
The length of the data bars is proportional to the track length in the cell in column D.
Tip
When you adjust the column width, the bar lengths adjust accordingly. The differences among the bar lengths are more prominent when the column is wider. n
Excel provides quick access to 12 data bar styles via Home Styles Conditional Formatting Data Bars. For additional choices, click the More Rules option, which displays the New Formatting Rule dialog box. Use this dialog box to
486
Chapter 20: Visualizing Data Using Conditional Formatting
•Show the bar only (hide the numbers).
•Specify Minimum and Maximum values for the scaling.
•Change the appearance of the bars.
•Specify how negative values and the axis is handled.
•Specify the direction of the bars.
Note
Oddly, the colors used for data bars are not theme colors. If you apply a new document theme, the data bar colors do not change. n
Using data bars in lieu of a chart
Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 20.5 shows a three-column table of data (created by using Insert Tables Table), with data bars conditional formatting applied in the third column. The third column of the table contains references to the values in the second column. The conditional formatting in the third column uses the Show Bars Only option, so the values are not displayed.
FIGURE 20.5
This table uses data bars conditional formatting.
Figure 20.6 shows an actual bar chart created from the same data. The bar chart takes about the same amount of time to create and is a lot more flexible. But for a quick-and-dirty chart, data bars are a good option — especially when you need to create several such charts.
Using color scales
The color scale conditional formatting option varies the background color of a cell based on the cell’s value, relative to other cells in the range.
487
Part III: Creating Charts and Graphics
FIGURE 20.6
A real Excel bar chart (not conditional formatting data bars).
A color scale example
Figure 20.7 shows a range of cells that use color scale conditional formatting. It depicts the number of employees on each day of the year. This is a 3-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient.
FIGURE 20.7
A range that uses color scale conditional formatting.
On the CD
This workbook, named color scale example.xlsx, is available on the companion CD-ROM. n
488
Chapter 20: Visualizing Data Using Conditional Formatting
Excel provides four 2-color scale presets and four 3-color scales presets, which you can apply to the selected range by choosing Home Styles Conditional Formatting Color Scales.
To customize the colors and other options, choose Home Styles Conditional Formatting Color Scales More Rules. This command displays the New Formatting Rule dialog box, shown in Figure 20.8. Adjust the settings, and watch the Preview box to see the effects of your changes.
FIGURE 20.8
Use the New Formatting Rule dialog box to customize a color scale.
An extreme color scale example
It’s important to understand that color scale conditional formatting uses a gradient. For example, if you format a range using a 2-color scale, you will get a lot more than two colors. You’ll get colors with the gradient between the two specified colors.
Figure 20.9 shows an extreme example that uses color scale conditional formatting on a range of 10,000 cells (100 rows x 100 columns). The worksheet is zoomed down to 20% to display a very smooth three-color gradient. The range contains formulas like this one, in cell C5:
=SIN($A2)+COS(B$1)
Values in column A and row 1 range from 0 to 4.0, in increments of 0.04.
When viewed onscreen, the result is stunning; it loses a lot when converted to grayscale.
On the CD
This workbook, named extreme color scale.xlsx, is available on the companion CD-ROM. n
489
Part III: Creating Charts and Graphics
FIGURE 20.9
This worksheet, which uses color scale conditional formatting, displays an impressive color gradient.
Note
You can’t hide the cell contents when using a color scale rule, so I formatted the cells using this custom number format (which effectively hides the cell content):
;;;
Using icon sets
Yet another conditional formatting option is to display an icon in the cell. The icon displayed depends on the value of the cell.
To assign an icon set to a range, select the cells and choose Home Styles Conditional Formatting Icon Sets. Excel provides 20 icon sets to choose from. The number of icons in the sets ranges from three to five. You cannot supply your own icons.
490
Chapter 20: Visualizing Data Using Conditional Formatting
An icon set example
Figure 20.10 shows an example that uses an icon set. The symbols graphically depict the status of each project, based on the value in column C.
On the CD
All the icon set examples in this section are available on the companion CD-ROM. The workbook is named icon set examples.xlsx.
FIGURE 20.10
Using an icon set to indicate the status of projects.
By default, the symbols are assigned using percentiles. For a 3-symbol set, the items are grouped into three percentiles. For a 4-symbol set, they’re grouped into four percentiles. And for a 5-symbol set, the items are grouped into five percentiles.
If you would like more control over how the icons are assigned, choose Home Styles Conditional Formatting Icon Sets More Rules to display the New Formatting Rule dialog box. To modify an existing rule, choose Home Styles Conditional Formatting Manage Rules. Then select the rule to modify and click the Edit Rule button.
Figure 20.11 shows how to modify the icon set rules such that only projects that are 100% completed get the check mark icons. Projects that are 0% completed get the X icon. All other projects get no icon.
Figure 20.12 shows project status list after making this change.
491
Part III: Creating Charts and Graphics
FIGURE 20.11
Changing the icon assignment rule.
FIGURE 20.12
Using a modified rule and eliminating an icon makes the table more readable.
Another icon set example
Figure 20.13 shows a table that contains two test scores for each student. The Change column contains a formula that calculates the difference between the two tests. The Trend column uses an icon set to display the trend graphically.
This example uses the icon set named 3 Arrows, and I customized the rule:
492
Chapter 20: Visualizing Data Using Conditional Formatting
•Up Arrow: When value is >= 5
•Level Arrow: When value < 5 and > –5
•Down Arrow: When value is <= –5
In other words, a difference of no more than five points in either direction is considered an even trend. An improvement of at least five points is considered a positive trend, and a decline of five points or more is considered a negative trend.
Note
The Trend column contains a formula that references the Change column. I used the Show Icon Only option in the Trend column, which also centers the icon in the column. n
FIGURE 20.13
The arrows depict the trend from Test 1 to Test 2.
In some cases, using icon sets can cause your worksheet to look very cluttered. Displaying an icon for every cell in a range might result in visual overload.
Figure 20.14 shows the test results table after hiding the level arrow by choosing No Cell Icon in the Edit Formatting Rule dialog box.
493