Use Excel Conditional Formatting to Enhance Data Analysis

Learn to use Conditional Formatting to Develop Your Excel Skills by Applying Colour Scales, Data Bars, Icons and More!
4.0 (1 rating) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
615 students enrolled
$200
Take This Course
  • Lectures 73
  • Contents Video: 1 hour
    Other: 1.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 3/2015 English

Course Description

Join my community of over 6,000 students learning my courses!

This course has been updated in September 2016.

What is conditional formatting and why is it useful for you?

Conditional formatting allows the user to format a group of cells based on one or multiple criteria. This means that you do not have to format the cells manually and you can let Excel do all the work for you! Also, if there is change in the data Excel will automatically change the formatting, if the cell will now meet a different criteria.

Most frequently I use conditional formatting to allow me visualize the data, meaning that I can start analyzing the data before even looking at the values themselves. This means that you can increase your productivity working with Excel to a high degree using conditional formatting.

You can preview the introduction lectures in order to see some of the applications of conditional formatting in order to get a better understanding how it can be used.

About this course:

In this course I show everything step-by-step, meaning that I will be showing each single button that I press. By doing this I want to make sure that you memorize all the steps and can apply conditional formatting in your own Excel spreadsheets.

This course is designed for a beginner/intermediate level. You need to have Excel fundamentals, meaning that you will have to know how to open an Excel file, save it and other similar fundamentals. However, you do not need to have an advanced knowledge of Excel. The most advanced part of this course is when I will be teaching you to use formulas to make a condition. However, I will explain each formula in depth, meaning that you do not have to worry that you will not understand it.

However, if you are an advanced Excel user, you might still want to go through the course in order to refresh your knowledge and possibly learn a couple of new tips.

By the end of this course you will be able to:

  • Become more productive with Excel and save time
  • Enhance the data analysis capabilities with conditional formatting
  • Learn how to use conditional formatting
  • Perform multiple conditional formatting rules to the same group of cells
  • Use conditional formatting to highlight group of cells
  • Use conditional formatting top and bottom rules
  • Use conditional formatting icons to highlight cells
  • Use conditional formatting based on text rather than simply numbers
  • Learn how to use formulas in conditional formatting
  • Create various custom rules using formulas
  • Edit existing conditional formatting rules
  • Impress your manager, colleagues and clients

Other benefits from taking this course:

  • You will get lifetime access to this course
  • This course is frequently reviewed and you will get access to the new lectures for free
  • Unconditional Udemy 30 day money-back guarantee - that's my personal promise of your success!
  • My help is available to you, if you get stuck or have a question
  • I will provide you discounted coupon codes for my other Excel courses

Click the “Take this course" button at top right corner. Without the knowledge of conditional formatting you are missing awesome opportunities.

What are the requirements?

  • Have Microsoft Excel 2007 or Later (It Works for Previous Versions But The Menu Is Different)
  • Access to computer/Internet
  • Fundamental Knowledge of Excel

What am I going to get from this course?

  • Understand Wide Applications of Conditional Formatting
  • Apply and Modify Conditional Formatting
  • Apply Multiple Conditional Formatting Rules to the Same Group of Cells
  • Create Interactive Tables Using Conditional Formatting
  • Create Custom Conditional Formatting Rules
  • Edit Any Conditional Formatting Rules That Have Been Created In The Past
  • Use Conditional Formatting to Become More Productive Working with Excel

What is the target audience?

  • Anyone Wanting to Improve Their Excel Knowledge
  • People Having Fundamental Knowledge of Excel
  • If You Are Intermediate Or an Expert, You Can Still Join the Course to Refresh Your Excel Knowledge and Possibly Learn a Few Additional Tips

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction and the Uses of Conditional Formatting
03:49

Short demonstration of conditional formatting.

02:53

Two more examples how I have used conditional formatting

01:01

Short video explaining what you should know before you take this course about Microsoft Excel

Article

Small reminder how to improve the user experience with Udemy.

Downloadable Material for This Section Is Located in This Lecture
Article
Section 2: How to Remove Conditional Formatting
01:59

Explanation of three different ways you can delete existing conditional formatting.

Section 3: Highlight Cell Rules
01:50

In this lecture I am talking about how to highlight cells based on greater than a value.

01:36

Learn how to highlight cells with values less than a certain value.

01:10

Learn how to use between function. in conditional formatting.

01:46

In this lecture you will learn about the equal to function in conditional formatting.

01:32

In this lecture you will learn about text that contains function in conditional formatting.

02:49

You will learn about highlight cell rule called "A Date Occurring"

02:53

In this lecture you will learn about highlighting cells with either duplicate values or unique values.

Section 4: Top and Bottom Rules
01:14

In this lecture you will learn about top and bottom rules, where you can highlight x amount of top or bottom values. The X depends on your choice.

01:24

Similar way to the last lecture, in this you will learn also to highlight X amount of bottom or top items. However, in this lecture we will be looking at X amount of %.

01:27

In this lecture we will be looking, how you can easily highlight cells that have values below or above the average value for a given range.

Section 5: Data Bars
03:41

In this lecture you will be taught about both types of data bars, which are gradient and solid fills.

Section 6: Icon Sets
01:26

Short introduction about icon sets and how the amount of icon affect the results of the conditional formatting.

01:03

Explanation of directional icon sets in conditional formatting.

01:35

In this lecture you will lean about shapes and indicators under icon sets in conditional formatting.

01:05

In this lecture you will lean about ratings under icon sets in conditional formatting.

Section 7: Slightly More Advanced Conditional Formatting
01:40

In this lecture you will learn about conditional formatting based on a specific cell and this method is hihgly recommended to use for everyone as it becomes simpler to edit the Excel file and change the Conditional Formatting criteria.

01:59

How you can use conditional formatting together with filters in order to find the data you want to analyze.

03:17

In this lecture I am explaining the order of conditional formatting, as there might be cases, where multiple rules will be applied to same cells. Also, I cover, how you can change the order in this lecture.

Section 8: Using Formulas in Conditional Formatting
04:24

This lecture will teach you about the menu of the custom formulas for conditional formatting.

02:45

In this lecture you will learn how to lock rows, columns or both. This is crucial knowledge, when you start building your own formulas in conditional formatting.

04:05

This lecture will teach you how you can highlight cells with time information using custom formulas.

02:01

In this lecture we will cover how you can create custom icon set ranges rather than using the presett.

02:52

Instead of only highlighting the cells in the columns, you will learn how to highlight the whole row based on one column.

02:15

In this lecture you will learn how to make a dropbox and use it to select what you want to highlight using conditional formatting.

Comparing Values In Two Columns Using Formulas
01:30
02:44

In this lecture you will learn how to compare two lists either for matches or mismatches, depending on your choice.

03:05

Learn how to change the colours of the alternating rows.

01:06

Rather than using presets, learn how to write formula for finding duplicates.

Finding Duplicates Using Criteria From Two Columns
01:30
01:43

There are cases, where you need to compare subtotals. Conditional formatting is perfect for that.

Downloadable Material for This Section Is Located in This Lecture
Article
Section 9: Conclusion
Article

In this lecture I simply remind you that I will keep this course up to date and I will continue to add new lectures.

Section 10: BONUS: Discounts of My Other Courses
Article

Other Excel courses I have created.

Section 11: BONUS: Audio Version For Your MP3 Player/iPod
What Is Conditional Formatting?
03:49
More Examples of Using Conditional Formatting
02:53
Excel Knowledge Prerequisites Before Taking This Course
01:01
How to Remove Conditional Formatting
01:59
Greater Than
01:50
Less Than
01:36
Between
01:11
Equal To
01:46
Text That Contains
01:32
A Date Occurring
02:49
Duplicate Values/Unique Values
02:53
Top and Bottom X Amount of Items Conditional Formatting
01:14
Top and Bottom X% of Items Conditional Formatting
01:24
Above and Below Average Conditional Formatting
01:27
Gradient and Solid Data Bar Fills
03:41
Introduction and Explanation of Icon Sets
01:26
Directional Icon Sets
01:03
Shapes and Indicator Icon Sets
01:35
Ratings Icon Sets
01:05
Conditional Formatting Based on a Specific Cell (Highly Recommended)
01:40
Using Conditional Formatting In Filters
02:00
Order of Conditional and How to Change It
03:17
Introduction to Creating Your Own Custom Conditional Formatting Rules
04:24
Before Creating Custom Formulas You Need to Know How to Lock Rows and Columns
02:45
Highlight Cells Containing Time Information Using Formulas
04:05
Creating Custom Icon Set Ranges
02:01
Highlighting the Whole Row Based on a Column
02:52
Dynamic Way of Selecting Whole Row Based on a Cell
02:15
Comparing Values In Two Columns Using Formulas
01:30
Comparing Two Different Lists For Matches or Mismatches
02:44
Changing Colours of Alternating Rows Using Formulas in Conditional Formatting
03:05
Finding Duplicates Using Formulas in Conditional Formatting
01:06
Finding Duplicates Using Criteria From Two Columns
01:30
Using Conditional Formatting to Find Discrepancies
01:43

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

John Smith, Online Entrepreneur

Over 5,900 unique students from 131 different countries are studying my courses.

I have been an online entrepreneur since 2010 using various methods of earning money online. I have multiple YouTube channels, tried working with affiliate marketing, consultant on Internet, Forex, selling e-books and an online teacher on Udemy. I love to share my own experiences both from my entrepreneur life and also when I was working in one of the largest corporations in the world (see my CV below).

YouTube:

YouTube: Over 7 million viewers on my main channel, increasing by 200,000 viewers per month. I have several other channels, which are training related.

Udemy:

I started teaching on Udemy in October 2014 and I have gained over 2,200 unique students. I am enjoying teaching and very passionate about helping people solve their problems, grow their business and improve their lives. I can proudly say that I have helped people across the 6 different continents.

My CV:

I finished my BSc Business and Management in one of the top UK universities with a first class degree. After getting the degree, I worked in one of the major corporation that is in Fortune 500 - General Motors. Hence, I am familiar to the corporate culture and the daily life of working in major corporations.

While working for the corporation I have come across many different people that I had to work with. I have also had the opportunity to attend many different business trips in various countries with different cultures, which has allowed me to understand how differently business operations are carried out in different countries.

Right now I work online as an entrepreneur, which has been mentioned before.

Ready to start learning?
Take This Course