Conditional-Formatting-in-Microsoft-Excel-and-Google-Sheets.png

Conditional Formatting in Microsoft Excel and Google Sheets

Spread the love

Conditional formatting is a functionality of Ms. Excel.

Its applications are vast, covering both individuals and businesses. It’s the applicability that makes Ms. excel stand out as one of the most powerful tools from Microsoft Office Suite.

And Google also allows users to tap into the same functions whenever they are connected to the internet.

For one, it’s easily accessible as part of MS Office apps. Secondly, Excel is highly adaptable to various operations based on data.

This piece will walk you through one of Ms. Excel’s applicability: Conditional formatting.

First, What is Conditional Formatting?

According to excel-easy.com,

“Condition Formatting in Ms Excel enables you to highlight cells in a different color depending on the cell’s value.”

It’s the formatting that helps you to analyze a set of data and gain more insights.

Next, with conditional formatting, a user can make faster decisions than doing it manually.

A Real-Use-Case of C Formatting

Mr. John is a high school physics teacher. At the end of the year, his class of 15 students required to make choices – to continue with physics or drop it. The catch: Only those with more than 50% will take on the subject.

So, Mr. John has two options:

  1. Sit down with a pen and paper and do the manual bit. Or,
  2. Use conditional formatting to separate the students using a spreadsheet

We’ll go for option 2 for this tutorial

Here’s a list of Mr. John’s class showing both marks obtained and gender.

 

list-of-studends-and-marks-for-condition-formatting.png

 

 

Process: Mr. John will use conditional format rules to know the batch of students who’ll proceed with Physics to the next class

Conditional formatting works with color codes. We take green for those continuing and red for those who’ll drop- based on the score as above.

  1. Next, select the column with marks/ score and click on the Home tab. Proceed to select the Conditional formatting option, and use the first option(Greater Than)

 

condituional-formatting-select-column-select-hometab-select-conditional-formatting-option.png

 

 

Proceed to the dialog box and set passmark as 49%.

 

 

set-rules-conditional-formatting-passmarl-49.png

 

 

Note: You can go with the default color or use the drop-down button to choose your color of preference.

 

 

Select-custom-color-condtitional-.png

 

 

When you select custom, the color dialog box appears. Make your choice.

Try a custom color like green below.

 

 

select-custom-color-from-ciolor-pallete.png

 

 

Here’s the final window showing all the students above 49% as being shaded in green.

 

 

show-conditional-format-as-green-cells.png

How Does Conditional Formatting Help Mr. John?

It saves him time and the effort required to do the sorting of records for his students’ scores in physics.

 

As you can see, with a simple hands-on approach to everyday scenarios, Conditional formatting helps solve complex problems in record time.

 

The case would not be any different in case Mr. John was to use Google Sheets. The results would be similar, and he could share the same on a real-time basis.

 

Parting Note

Conditional formatting using Ms. excel and Google Sheets is one of a host of humongous solutions at your disposal.

 

If you take the time to learn and practice a trick a day, that’ll help you master the ways around Ms. Excel.

 

Learning a trick a day makes the entire difference. Start today!

 

Leave a Reply

Your email address will not be published. Required fields are marked *