How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Tuesday

How to Highlight an Entire Column in Google Sheets

Posted by   on

To highlight an entire column in a table based on field labels (header row), we can use a custom conditional format rule in Google Sheets.


In such highlight rules, we may specify at least one condition.


The conditional format requirement may vary from user to user.


Because of this, I can’t give you any specific rules but can guide you in the right direction with the help of a few examples.


Let’s consider the timescale (header row of Bar/chart area) of a Gantt chart for applying a few highlighting rules.


We will test with different time units such as days (today), months, years, etc., in that timescale (header row).


Examples to Highlight an Entire Column in Google Sheets


1. Highlight an Entire Column Matching Today’s Date


I have a timescale in cell range B2:O2, and “Days” is the unit. The bar (plot) area is B3:O17.


How to match today’s date in the timescale and highlight the corresponding column up to row 17?


If that’s the scenario, we can use the below custom conditional format rule in Google Sheets.


=B$2=today()

You can follow the below steps to use the above formula to highlight the entire today’s column in the given range.



  1. Select B2:O17.

  2. Click on the menu Format.

  3. Select Conditional formatting > Single rule.

  4. Enter the given formula within the blank field that you can find under Format rules > Custom formula is.


Highlight an Entire Column Range Based on Today's Date
image # 1

Settings:-


Single Color Rule Settings in Sheets
image # 2

To highlight an entire column (column G) in Google Sheets, do as follows.


Select B1:O (step 1). The rest of the steps, even the formula, are the same.


Custom Rule to Highlight an Entire Column Based on a Date
image # 3

2. Highlight an Entire Column Matching Current Month


We may usually specify months in the header row in date format such as 1/1/2021, 1/2/2021, 1/3/2021, and so on instead of specifying months in text format such as Jan, Feb, Mar, etc.


Then we will format that row to mmm or mmmm from Format > Number > Custom number format to visually make them appear as text.


In that case, to highlight an entire column matching the current month, we can follow the below rule.


=eomonth(B$2,0)=eomonth(today(),0)

Note:- Here also I am considering the timescale range B2:O2 and bar area B3:O17.


The formula converts all the dates in B2:O2 to end of the month dates.


Then, matching it not with today’s date, but with the end of the month of today.


3. Conditional Formatting Matching Specific Number or Text


Imagine you want to match specific numbers such as years in the header row in the format 2020, 2021, 2022 or fruit names such as “Apple,” “Orange,” “Mago,” etc.


Then for the same above range, the rules to highlight an entire column in Google Sheets will be as follows.


Numeric Value:


=B$2=2021

Text Value:


=B$2="Apple"

4. Highlight an Entire Weekend Column In Google Sheets


I have already detailed how to conditional format weekends in Google Sheets.


Here is how to tune that formula to highlight an entire column matching the weekends – Saturday and Sunday.


=and(isblank(B$2)=false,OR(weekday(B$2)=7,weekday(B$2)=1)

The formula will highlight all the columns in the range that contains weekends entered as dates, not texts such as “Sunday,” in the header row.


To use a different weekend in the formula you may change the weekday number.


You May Like:- How to Utilise Google Sheets Date Functions [Complete Guide].


Additional Tips


Here are some additional tips.


We can include comparison operators and logical AND in the above rules.


Related:- AND, OR, or NOT in Conditional Formatting in Google Sheets.


Please refer to image # 1 above.


Problem 1:


How to highlight columns that fall in the date range 3/12/2021 and 13/12/2021?


Conditional Format Date Range Based on Header Row

Here is the rule.


=and(B$2>=date(2021,12,3),B$2<=date(2021,12,13))

It will highlight multiple column ranges, and that is D2:N17.


Problem 2:


How to highlight an entire column in a range matching a value in any row?


In all the above examples, I have used column range B2:O17 and matched a date, month, year, text, weekends, etc., in B2:O2.


Let’s forget about the header row (field labels) in B2:O2.


Let’s see how to highlight the entire column(s) matching a value anywhere in B2:O17.


=match("apple",B$2:B$17,0)

The above formula match “apple” in cells and highlight columns.


That’s all. Thanks for the stay. Enjoy!

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!