You can use conditional formats, which change a cell’s appearance based on the value it contains, to indicate how your results compare to your targets.
- Organizations of all kinds, whether they are corporations, charities, or social clubs, need to keep track of how their group is doing. Businesses set sales and profit goals, charities have fundraising goals, and social groups often collect dues to pay for their get togethers. You can use conditional formats, which change a cell's appearance based on the value it contains, to indicate how your results compare to your targets. I'll demonstrate how to create straight forward conditional formats in this movie. My sample file is the conditional workbook, and you can find it in the chapter six folder of the exercise files collection.
As I mentioned, a conditional format changes the appearance of the value in a cell based on that cell's contents. In this workbook, I have a worksheet that indicates solar efficiency for a set of solar panels. It's for January 1 through January 11 of 2018, and you can see the capacities, or percent of capacity, in column B. Let's say that I want to color code these results so that anything that is 80% or below would be shown in red, have a red background, and any value 90% or higher would be in green, and we'll leave the values between 80 and 90 just with the regular formatting as we see here.
To create a conditional format, select the cells to which you want to apply the format. In this case that's B4 through B14, and then on the 'Home' tab of the ribbon, click the 'Conditional Formatting' button. Point to 'Highlight Cells Rules.' In this case we want to display values that are less than or equal to 80% in red, so I will click 'Less Than.' Now note I said less than and not less than equal to. So I will click this control here, and select 'less than or equal to' which is at the bottom.
Now I can define a format. There's one applied, 'Light Red Fill with Dark Red Text' that I don't particularly care for that. So I will click the 'Format with' control's arrow, and click 'Custom Format.' The 'Format Cells' dialogue box appears, and the font rather than having it be red, or brick red in this case, I will click the 'Color' control's down arrow and I'll make it white. That's because I'll be putting it against a red background. Now I'll click 'Fill,' and rather than having a pink background color I'll click the 'Background color' down arrow and I'll click red.
Primary red under 'Standard Colors.' There's that. I'll click 'OK,' and my format is applied. Finally I need to identify the value by which I want to compare. So I will click in the value box next to 'less than or equal to,' and I'll make it 0.8 which is the same as 80%. With that in place, I'll click 'OK,' and the format has been applied.
If I wanted, I could create a second conditional format for the same group of cells. So I will select B4 though B14, click 'Conditional Formatting,''Highlight Cells Rules' in this case 'Greater Than.' Rather than greater than, I want to have greater than or equal to. So I'll click 'greater than,' click 'greater than or equal to' and then this time I'll set my comparison value, so let's say greater than or equal to 95%. So I'll type 0.95, and I'll click the 'Format with' control's arrow, click 'Custom Format.' I'm still on 'Background color' which I'll change to green.
So I'll change that to primary green under 'Standard Colors' and then click 'Font' and my color is currently red. Instead, I will click that and I will change it to black. Everything looks good, I'll click 'OK' click 'OK' again, and the format has been applied. Conditional formats let you tell at a glance how your organization is performing. Make sure to test the rules that you create though. Bad information is worse than no information at all.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.