Compare two columns in Microsoft Excel and Use conditional formatting

Compare two columns in Microsoft Excel and Use conditional formatting:

Let’s say we have below data in excel:

Here, we are going to use conditional formatting in column C comparing column B and understand which is falling down, going up and neutral.

Let us use a simple formula here called “offset”.

To understand the functionality of offset, I have given the offset formula with a same reference to cell B2, and navigate from row 0, column 0,  then row 1, column 0,  then row 2, column 0, then row 3, column 0 as shown below for easy understanding using a concatenate function in cell E2
Formula I have used is:

=CONCATENATE(OFFSET(B2,0,0), " ", OFFSET(B2,1,0)," ",OFFSET(B2,2,0)," ",OFFSET(B2,3,0))

It gave the values from cell B2, B3, B4, B5 as shown below

By understanding the usage of Offset function, lets us use this formula in our excel.

Highlight the Cell C2 alone. And under HOME tab -> select Conditional formatting -> Icon Sets -> More Rules as below

It will open up the New formatting Rule window as below.  

Here select your Icon Style as shown below.
Change the Type to Number. 

For Green Icon, change Symbol to > as shown below and type the formula 

=offset($B$2,row()-2,0)

For Neutral Icon, change the symbol to >= as shown below and type the same formula

=offset($B$2,row()-2,0)

Note:  You can’t use B2.  You should use $B$2 here.

For red, any thing < will auto apply anyway. So you will have data as shown below:

Click OK.

You will see that, selected cell C2 reflects the expected result by comparing Cell B2. 

Now, to use same formatting, you need to highlight cell C2, and Doubleclick the format painter icon as shown below:

(NOTE:  You should not do single click..  But double click the format painter.  Very important).

Now just click on cell C3, you will see that the formatting applies to C3 cell as well.

Instead of doing each cell one at a time, by selecting C2 cell and double format painter and then to click Cell C4 and again repeating steps for C5 cell, we can now, 

Select both cell C2 and C3 and Double Click Format Painter icon as below:

Since we have copied 2 cells under format painter, 

Now, just drag over 2 cells (C4, C5) to use the format.  

Now you will see, all cells have required conditional formatting as expected: 

Thanks for spending your time in my blog:  Happy learning. 

Magic 9

Your mind wants you to forget the unpleasant.

That’s why, It really is easy to forget the unpleasant, if we simply refuse to recall it

You take a big step forward toward conquering fear when you refuse to remember negative, self-deprecating thoughts

Develop an understanding attitude with others. Tell yourself, when you talk to someone "Underneath he's probably a very nice guy. Most folks are."

Many criminals are captured not because any clues point to them but because they act guilty and self-conscious. Their guilt feeling puts them on the suspect list. So, have a desire to be right, think right, and act right

To think Confidently, act confidently

We can change our attitudes, by changing our physical actions. 

Confident action produces confident thinking

Magic 8

Much lack of self-confidence, can be traced directly to a mismanaged memory

Deposit only positive thoughts in your memory bank

Unsuccessful and successful people deal with these situations in directly opposite ways

Unsuccessful people take them to heart, so to speak. The unpleasant situation is the last thing they think about.

Successful people specialize in putting positive thoughts into their memory bank

Negative, unpleasant thoughts deposited in your mind affect your mind the same way.

Negative thoughts produce needless wear and tear on your mental motor

Negative thoughts create worry, frustration, and feelings of Inferiority

Negative thoughts put you beside the road while others drive ahead

When you're driving your car or eating alone or alone, recall pleasant, positive experiences. Put good thoughts in your memory bank

Just before you go to sleep,

Deposit good thoughts in your memory bank.

Count your blessings.

Recall the many good things you have to be thankful for: your wife or husband, your children, your friends, your health.

Recall the good things you saw people do today.

Recall your little victories and accomplishments.

Go over the reasons why you are glad to be alive

Destroy the negative thoughts, before those thoughts become mental monsters

A person can make a mental monster out of almost any unpleasant happening

Think of and write down three specific reasons you felt happy during past times

Whether the psychological problem is big or little, the cure comes when one learns to quit drawing negatives from one's memory bank and withdraws positives instead

When you remember situations of any kind, concentrate on the good part of the experience; forget the bad. Bury it.

If you find yourself thinking about the negative side, turn your mind off completely.

Magic 7

How old we are, is not important. It's one's attitude toward age that makes it a blessing or a barricade/obstacle

Make your team and family members feel, they are working for a team captain, not a dictator

Think, 'I’m still young," not 'I’m already old."

Practice looking forward

Life is actually longer than most people think

Invest future time in doing what you really want to do

Stop thinking "I should have started years ago.".  Start Now at least. Tell Yourself “I’m going to start now, my best years are ahead of me”

Accept the law of cause and effect

You'll find that, not luck but preparation, planning, and success-producing thinking precedes good fortune

Mr. Success, if receives a setback; he learns and profits,

But when Mr. Mediocre loses, he fails to learn

Success comes from doing those things and mastering those principles that produce success

Magic 6

We can't do much, to change the amount of native ability, but we can certainly change the way we use, what we have

Knowledge is power, when you use it constructively

The great scientist Einstein was once asked how many feet are in a mile. Einstein's reply was "I don't know”

Einstein taught us a big lesson. He felt it was more important to use your mind to think, than to use it as a warehouse for facts

Henry Ford was never interested in miscellaneous information. He knew that “the ability to know how to get information” from his executives is more important than using the mind as a garage for facts

People who can dream, they can develop the dream into a practical application

An idea man can make money; a fact man can't

Discover your superior talents. Analyze the skills you have.

My attitudes are more important than my intelligence

At work and at home, practice positive attitudes

See the reasons why you can do it, not the reasons why you can't

Use it to find ways to win, not to prove you will lose

Compare - How? Why?

If given a work to someone. How many people will ask…

1)      How to do the work?

2)      Why I need to do the work?

Here, work relates to anything. It can be Corporate work, Govt Work, Household work, Social work…. i.e., any work.

When we ask this question to a number of people,

Around 90 out of 100 people will ask How to do the work?

and 10 out of 100, will ask why I need to do the work?

Most people are interested to know how to achieve the work.

But fact says that, those 10 out of 10 people in Why category are guaranteed to complete the work.

But only 10 out of 90 people in How category are guaranteed to complete the work.

Below are 2 scenarios:

Scenario 1:

While passing on a hillside, you see a House, locked inside. An old lady standing outside starts screaming at you, to open the door or even break the door immediately without questioning. i.e., don’t ask why. But she starts explaining how to open the door using different methods/ways.

Highest probability is that, you won’t accept her command/wish to open/break the door.

How actually guarantees to complete a task/work assigned but fails here.

Because reason to do the work is unknown. Pressurizing also does not help to complete a work.

Lowest probability is that you may accept her command and use one of the methods in How to break the door, Without actually knowing the reason why you are breaking the door.

Chances are Old Lady may be telling the truth or she may be just playing a game with you for her personal benefit or the method she had suggested may not work to break the door.

Here, we need to accept the fact that only 10 out of 100 people may/will guarantee to complete a task/work without a reason.

Remaining 90 out of 100 are guaranteed to fail in completing the task/work. That’s why we wonder why people fail, even though they knew how to do the work.

Reason is – They do not find a good reason to do that.

Scenario 2:

While passing on a hillside, you see a House, locked inside. An old lady standing outside starts screaming at you, to open the door or even break the door. She didn’t tell on how to open the door with different methods/ways. But she pointed out a Big Smoke coming out of the window and a child crying inside.

Highest probability is that you will wish to open/break the door.

Even though How to complete a task/work is not explained, but you know the reason why to break the door. Human mind is good enough to find a way in how to break the door, because reason to do the work is known. It does not require Pressure from others to complete a work. If the old lady had some methods in How to break the door, it can be used here.

Very Low probability is that you may not understand the scenario either because you are unfit or irresponsible by nature.

Here, we need to accept the fact that 10 out of 10 people are guarantee to complete a task/work, since they knew the reason to do.

It’s a great responsibility for everyone to understand Why we need to do the work ,than How.