In this video, learn how to find the highest and lowest values in your worksheets by using top ten conditional formats to change those cells’ formatting so the values stand out.
- [Narrator] Sometimes it seems like we are inundated with Top Ten lists. The top ten movies, the top ten vacation spots and so on. Businesses also use top ten lists to reward their employees, perhaps giving awards to their top ten sales representatives. In this movie, I will show you how to find the highest and lowest values in your worksheets, and change the cells formatting so the values stand out. My sample file is the "TopTen" workbook. You can find it in the Chapter Six folder of your exercise files collection.
This workbook contains a single worksheet, and on it I have ten day's worth of solar efficiency data for a set of solar panels. What I'd like to do is identify, oh say, perhaps the top three days from my data set. To do that, I can create a Top Ten conditional format and set it to three values. I'll select cells B4 through B13, those contain my values, then on the home tab of the ribbon, I'll click the conditional formatting button, point to top/bottom Rules which is the second item, and then I can select whether to look the top ten items, the top percentage, bottom ten, or bottom percent, or above average or below average values.
To create a top ten item filter, click top ten items, and here you can see that we are formatting only the top or bottom ranked values. It's currently set to top, although I could also change it from top to bottom, and instead of having ten values, let's say that I only want to see the top three. To change the format, let's say I don't like this light red fill and dark red text, I'll click the format with controls arrow, and click custom format.
The font, rather than having it be red, let's say I click the color controls arrow and click black, and then I'll change the background color, which is fill, and change it from this light red or pink, clicking the background color controls. This is for solar efficiency; Solar is good so I will click a green square, make it light green, and click okay. And when I click okay, the format should be applied. And it is. You can see that ninety five, ninety four, and ninety one percent values were the top three.
If there is a tie, then Excel applies the conditional format to the first cell with a particular value. Now say that I wanted to see the bottom three. To do that, I can select the cells again, and then click the conditional formatting button on the home tab of the ribbon, point to top/bottom rules, and this time we'll do bottom ten percent. Here instead of formatting the top values, I will format the bottom ten percent, which in this case will be only one, so I'll change it from ten percent to twenty percent which will show the bottom two.
We've already worked through formatting, and light red with dark red text is good enough, so I'll click okay, and you see that my bottom two values, seventy five and seventy six percent, have the selected format applied. Identifying the highest and lowest values in your worksheet provides valuable information about your business. You should use top and bottom conditional formats in combination with filters to learn as much as you can about your data.
- 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.