Select your Top Menu from wp menus

Google Sheets – Conditional Formatting Based on Another Cell

► ALRIGHT, I’M GUESSING YOU MADE IT TO THIS
► VIDEO BECAUSE YOU MAY ALREADY KNOW HOW TO
► DO CONDITIONAL FORMATTING IN GOOGLE SHEETS,
► BUT YOU’RE TRYING TO FORMAT BASED ON ANOTHER
► CELL’S VALUES.
► I AM GOING TO TAKE THIS DATA THAT I HAVE AND
► I’M GOING TO TRY TO KEEP IT SIMPLE AT FIRST
► AND JUST CONDITIONALLY FORMAT ONE COLUMN BASED
► ON THE VALUES IN THE COLUMN NEXT TO IT.
► WE’LL WALK THROUGH THIS SET OF STEPS HERE.
► YOU GO TO FORMAT AND YOU GO TO CONDITIONAL
► FORMATTING.
► I’VE ALREADY DONE THAT, THAT’S WHY THIS BOX
► IS OVER HERE WAITING FOR ME.
► I’M GOING TO PRESS “+” TO GET A NEW RULE.
► OUR RANGE IS JUST GOING TO BE COLUMN A BECAUSE
► THAT’S THE RANGE WE ARE GOING TO HAVE FORMATTED.
► I AM GOING TO SPECIFY COLUMN A BY SAYING JUST
► COLUMN A, START AT A AND DON’T STOP, JUST
► GO ALL THE WAY DOWN.
► THAT’S HOW YOU SPECIFY THAT, WITH THAT SYNTAX.
► AND THEN, TO FIND THE CUSTOM FORMULA, YOU
► DROP THIS BOX DOWN AND THEN GO DOWN TO “CUSTOM
► FORMULA IS”.
► HERE IT IS EXPECTING YOU TO TYPE IN A FORMULA
► JUST LIKE YOU WOULD IN A SPREADSHEET.
► YOU START IT WITH AN “=” SIGN AND ENVISION
► US STARTING IN A.
► SO, WHEN YOU ARE IN A, YOU’RE GOING TO BE
► LOOKING AT WHAT’S THE VALUE IN B.
► I’VE CHOSEN FOR THIS EXAMPLE TO HIGHLIGHT
► EVERY DATE ON WHICH JOAN MAKES A SALE.
► WE ARE GOING TO SAY, LOOK, IF B IS EQUAL
► TO JOAN – I’M GOING TO STOP RIGHT HERE TO
► SHOW YOU A FEW THINGS.
► YOU HAVE TO SURROUND THE WORD JOAN IN QUOTES
► BECAUSE THAT’S WHAT YOU HAVE TO DO WHEN YOU
► ARE SPECIFYING A STRING IN A SPREADSHEET AS
► OPPOSED TO A NUMBER.
► THE SECOND THING IS, SEE THE RECTANGLE AROUND
► THE FORMULA RIGHT NOW IS RED SO IT IS TELLING
► ME IT’S NOT READY YET, IT IS TRYING TO HELP
► ME OUT.
► IT SAYS, “IT?S NOT A VALID FORMULA”.
► IF I CLOSE THE QUOTE, IT TELLS ME IT’S READY
► TO GO.
► IT DOESN’T SAY IT’S RIGHT, IT JUST SAYS IT’S
► A VALID FORMULA.
► LET’S JUST CHANGE THE FORMATTING A LITTLE
► BIT TO YELLOW SO WE CAN SEE IT BETTER – EH,
► WE ACTUALLY CAN’T SEE THAT ANY BETTER, LET’S
► GO BACK TO GREEN.
► THAT’S GIVING YOU A PREVIEW OF WHAT THIS IS
► GOING TO DO.
► JOAN MADE THREE SALES.
► YOU SAY DONE, LOOK AT THE DATA.
► THERE WE GO.
► THIS IS FORMATTING BASED ON THE DATA OVER
► HERE.
► NOW, AFTER LOOKING AT THE RESULTS, IT MAY
► NOT BE EXACTLY WHAT YOU WANT.
► YOU MAY NEED TO HIGHLIGHT THE ENTIRE ROW SO
► IT’S EASIER TO SEE SO WE’LL WALK THROUGH THAT
► AS WELL.
► YOU’RE FORMULA IS OVER HERE.
► IT IS WAITING FOR YOU TO SEE IF YOU WANT TO
► CHANGE ANYTHING.
► IT MESSED WITH MY RANGE, I WANTED IT TO BE
► INFINITE.
► I’M NOT SURE WHY IT CHANGED THAT, BUT WE’LL
► CHANGE IT BACK.
► HAVING SAID THAT, WE’RE ACTUALLY GOING TO
► DO A FEW THINGS.
► WE’RE GOING TO DO THAT ALL THE WAY OVER TO
► G.
► THEN, WE’RE GOING TO CHANGE…IF YOU HIT DONE
► NOW, IT DOESN’T CHANGE ANYTHING.
► YOU WOULD THINK IT WOULD DO THE ENTIRE ROW,
► BUT IT DOESN’T.
► WHAT YOU NEED TO DO IS YOU GO BACK IN HERE
► AND YOU HAVE TO CHANGE THE FORMULA.
► WHAT’S HAPPENING IS THIS FORMULA INCREMENTS
► IF YOU WILL JUST LIKE ANY OTHER SPREADSHEET
► FORMULA.
► THIS CONDITIONAL FORMATTING WORKS FROM THE
► LEFT TO THE RIGHT.
► AT FIRST, IT’S GOING TO LOOK AT JANUARY
► AND SEE IF THIS IS JOAN.
► IT’S NOT GOING TO HIGHLIGHT IT.
► SINCE YOU EXPANDED THE RANGE, IT’S GOING TO
► LOOK AT B AND SEE IF THIS IS JOAN.
► IT IS GOING THROUGH THE ENTIRE SPREADSHEET,
► BUT THE FORMULA ISN’T FIXED SO IT IS MOVING
► WHERE IT LOOKS.
► WHEN IT GETS TO A, IT SAYS THIS IS JOAN,
► SO I’M GOING TO HIGHLIGHT.
► BUT THEN WHEN IT GETS TO B, IT SAYS THIS
► ISN’T JOAN.
► COME OVER TO YOUR CUSTOM FORMULA AND SAY – CONTINUE
► LOOKING AT THE SAME COLUMN, DON’T MOVE.
► I WANT THE TO MOVE BECAUSE I WANT IT TO
► GO DOWN A ROW EVERY TIME BUT WE WANT TO FIX
► THE COLUMN.
► JUST LIKE YOU WOULD IF YOU WERE INSIDE A SPREADSHEET,
► USE A DOLLAR SIGN TO INDICATE THAT VALUE SHOULDN’T
► MOVE WHEN THE FORMULA MOVES.
► SO YOU SEE, IT’S MAGICALLY WORKING OVER HERE
► NOW.
► THIS IS JUST WHAT WE WANTED, RIGHT?
► JOAN MADE THREE SALES, YOU CAN SEE RIGHT AWAY
► FROM LOOKING.
► YOU CAN EVEN SEE SOME OTHERS.
► WE’VE COVERED TWO THINGS HERE.
► I THINK HIGHLIGHTING COLUMNS BASED ON VALUES
► THAT ARE OUTSIDE THE COLUMN AND THEN MAKING
► IT LOOK A LITTLE BETTER BY HIGHLIGHTING THE
► ENTIRE ROW.
► THE VALUES IN THERE ARE JUST ONE OF THE CELLS.
► THIS GETS PRETTY EASY WHEN YOU JUST USE CUSTOM
► FORMULAS.
► SO, I HOPE THAT WAS HELPFUL.
► YOU CAN TAKE THIS INTO YOUR NEXT PRESENTATION
► AND WOW EVERYONE.
► YOU’LL JUST BE AMAZING;)
► HAVE FUN WITH IT.
► THANKS!

Video Link

About The Author

Related posts

Leave a Reply

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