Learn how to use conditional formatting in Google Sheets. This guide covers everything from setting basic formatting rules based on numbers, text, dates, and other cell values, to using custom formulas for cases like deadlines or specific values. By setting up format rules tailored to your unique needs, you’ll save time, reduce errors, and make the most important details stand out.
What is Google Sheets conditional formatting?
Highlighting specific data with color is a great way to emphasize key information — many of us do this all the time.
But if you have data that changes frequently, like values that rise or fall above a certain number, or cells containing particular keywords, manually formatting each cell can be time-consuming and prone to errors.
Wouldn't it be great if such changes to formatting occurred automatically?
Conditional formatting in Google Sheets does just that. It automatically updates cell colors based on rules you set so that important cells stand out immediately.
Let’s explore some examples so you see how easily Google Sheets can apply and adjust formats based on data in real-time.
How to add a conditional formatting rule
Whatever conditional formatting rule you'd like to add in Google Sheets, it all starts the same — with a few clicks:
- Select the cells you want to apply the rule to.
- Go to Format > Conditional formatting in the Google Sheets menu:
- A panel will appear on the right side of the screen where you’ll set the details of the rule:
- edit the range to apply the rule to if needed
- set up the condition that best fits your needs
- choose how you want the cells to look like when the rule is met: set a background color or/and font color; make the text bold, italic, underlined or strikethrough.
- Once everything's set, click Done to save the rule.
You can add more rules by clicking Add another rule on the same pane. And you can easily modify or remove existing rules anytime:
With this general setup, you’re ready to create specific formatting rules for various use cases. Let’s jump right in 🙂
Set up simple formatting rule for numbers
Conditional formatting in Google Sheets is a fantastic way to visually highlight numerical data based on specific values. So let's suppose you want to color orders greater than $200 in total sales (column F):
- Select column F (a column with your numbers).
- Go to Format > Conditional formatting.
- Choose a condition from the dropdown menu: Greater than or equal to, and enter 200 in the provided field:
- Set the formatting style you prefer. For instance, use bold red text on a yellow fill color to make the cells stand out:
The style will be immediately applied to all cells meeting your condition: they all change colors accordingly.
As an alternative, for a more nuanced visualization, you can use a color scale:
- In the Conditional format rules sidebar, switch to the Color scale tab.
- Pick one of the existing sets of colors.
- Or customize colors for the minimum, maximum and (optionally) midpoint values. For example, set light green for the smallest numbers and dark green for the largest so that cells gradually darken as values increase:
These are simple yet effective ways to draw attention to numerical patterns in your data 🙂
Format cells in Google Sheets by multiple conditions
In some cases, you may want to apply different formats based on different conditions to the cells in the same column. Let's say you need to color orders over $200 in green and orders under $100 in red to draw attention to these specific amounts.
If color scale is not your best bet, you can try creating several formatting rules, each specific to one criterion:
- Greater than or equal to 200.
- Less than or equal to 100.
All your rules will be applied at the same time. Thus, multiple rules let you create a more tailored view of your data, spot key patterns and take action accordingly.
Google Sheets conditional formatting with custom formulas
While Google Sheets offers standard options to format cells, they may sometimes fall short of covering specific or complex cases where a custom approach is needed. That’s why there's this option to use custom formulas as conditions.
Custom formulas let you build your own rules using standard functions and operators. You'll be able to specify whether a cell meets a particular condition. The result of the formula should be either TRUE or FALSE — if TRUE, the formatting will be applied.
To get started, choose Custom formula is from the drop-down menu in the Conditional format sidebar:
Let me cover some useful examples so you see how this can work, from highlighting the highest values to marking minimum values while excluding zeros.
Custom formula to highlight max value
To color only the max number in a range, use this custom formula in conditional formatting in Google Sheets:
=F2=MAX($F$2:$F$50)
It checks if each cell in the range is equal to the maximum value and applies formatting accordingly:
Color top N values
If you want to color the top three (or other N) values in a range, create as many formatting rules as you need values, with the following custom formulas for each position:
=F2=LARGE($F$2:$F$50, 2)
you will need to replace 1 with 2, 3, etc. for each next value you'd like to color:
Highlight the lowest value
To highlight the lowest number in a range, use the MIN function in your custom formula:
=F2=MIN($F$2:$F$50)
Exclude zeros for minimum value
If you prefer don't count on zeros as lowest values, use MIN along with FILTER to exclude zeros and color the actual minimum value:
=F2=MIN(FILTER($F$2:$F$50, $F$2:$F$50>0))
Color bottom N values
For cases where you need to highlight N lowest numbers, use the SMALL function indicating the position of the number (1st or 2nd small, etc):
=F2=SMALL($F$2:$F$50, 1)
As you can see, custom formulas add flexibility to conditional formatting in Google Sheets. I’ll return to them often throughout this article to solve other specific formatting cases!
Conditional formatting if cell contains text
To conditionally format cells with text in Google Sheets, use the related rule: Text contains
In addition to just entering the word itself, you can incorporate wildcard characters to enable broader pattern matching for the search:
- Asterisk (*) matches any number of characters. Thus, d* pattern will find dark chocolates.
- Question mark (?) matches exactly one character. Thus, d??? pattern will find dark chocolates.
Tip. To find the actual asterisks and question marks in your cells, add a tilde (~) right before them. For example, Dark~? will look for cell that actualy contain string Dark?
Custom formulas with REGEXMATCH will also be a good fit here for highly specific searches:
=REGEXMATCH(D2:D50,"Dark")
Google Sheets conditional formatting with dates
Managing deadlines or tracking orders is yet another task you can cover with conditional formatting. Google Sheets lets you set up custom rules to highlight dates within specific time frames or to signal when deadlines have passed.
Example 1. Google Sheets conditional formatting if date is within 7 days
Let's highlight those arrival dates that are expected to happen within the next 7 days.
The arrival dates are in column G. If you break down the logic, you will need to check that the date in column G is either today or within the next 7 days. This custom formula will do the trick:
=AND(G2>=TODAY(), G2<=TODAY()+7)
If the starting date is not today though, simply replace TODAY with the DATE function specifying the date of interest:
=AND(G2>=DATE(2024,11,25), G2<=DATE(2024,11,25)+7)
As a result, all dates within the upcoming week from the intended dates will be instantly spotted providing a quick view of the closest order arrivals.
Example 2: Highlight past due dates
Identifying overdue dates is useful for tracking deadlines or incomplete tasks.
To create such a rule, you need a formula that will check for any date that is before the expected end date. Additionally, there may be cells without the actual end dates (blanks) meaning they've never been completed.
Both these criteria fit nicely into one custom formula for your format rule:
=OR(H2>G2,H2="")
Tip. If the end date is not in the table, do the same as in the example above and specify it using the DATE function:
=OR(H2>DATE(2024,10,26),H2="")
How to format blank cells
Highlighting cells based on text or numbers is helpful, but what about blank cells? Formatting based on blanks can be useful for tracking data completion or signaling about cells that still need attention.
If you only need to check whether a cell is blank, simply select Is empty or Is not empty in the conditional formatting rules.
But if you want to combine this with another condition, you'll need a custom formula. Here are 2 basic formulas for your conditional formatting in Google Sheets:
- To format blank cells: =""
- To format non-blank cells: <>""
Let's suppose you have an order status in column G and corresponding details in column H. If the status is Ready to Complete but the details are missing, you want to color that blank cell in column H blue to remind you to fill in the necessary information.
Here's a custom formula for this:
=AND(G2="Ready to Complete",H2="")
This formula checks if the status is Ready to Complete and if column H is empty. When both conditions are met, the formatting will apply.
Use this approach with empty/non-empty cells in other parts of your sheet to highlight missing values or track completion status.
Conditional formatting based on checkboxes in Google Sheets
Checkboxes can help you dynamically change formatting when checked (TRUE) or unchecked (FALSE). This is particularly useful for tracking tasks, to-do lists, and other datasets that rely on a checked/uncheked status.
You'll need custom formulas for this task:
- To format cells where a checkbox is checked, use a formula with TRUE:
=G2=TRUE
make sure to apply it to a column with checkboxes
- To format cells where a checkbox is unchecked, use a formula with FALSE:
=G2=FALSE
How to use Google Sheets conditional formatting to highlight entire row
Conditional formatting in Google Sheets can also format entire rows in your table. Let's see how it works by the example of checkboxes. I'll highlight all related data in a row whenever a checkbox is checked.
- Select the range you want to format. This should be your entire table (except for header) if you want to color the entire row: A2:G50
- In your custom formula, you’ll want to create an absolute reference to the column containing the checkbox (e.g., $G) so the formatting rule always refers to this one column. As for the row, let it remain flexible (2) so the rule is applied to each row individually:
=$G2=TRUE
This automatically formats entire rows whenever the checkbox in column G is checked.
Note. So remember, 3 things are key for formatting an entire row instead of a single cell:
- apply the format to the entire table
- use an absolute reference for the column ($G)
- and a relative reference for the row (2)
Google Sheets conditional formatting based on another cell
Did you know that you can format certain cells based on values in different cells? This lets you easily change the condition itself (in a cell) without updating the conditional formatting rule directly.
Suppose you want to color rows where Qty per order is less than 50 or more than 100:
- Start by entering those values in helper cells (for example, in column I next to your table):
- Open Conditional format rules and set up your whole table (but its header) as a range to format: A2:G50
- Then goes the formula for your first rule — the one that will highlight orders with quantities over 100:
=$E2>=$I$3
Note. Use absolute references ($) when referencing cells outside the table: $I$3. They will make sure that whatever you do with the table, the formula will still refer to this cell.
- Add a second rule to color order with fewer than 50 items. Click Add another rule at the bottom and change your custom formula to:
=$E2<=$I$2
This two-rule approach will highlight largest and the smallest orders based on different conditions, allowing quick updates just by changing values in column I.
Conditional formatting based on another sheet
Placing condition cells like I2 and I3 on a separate sheet can help keep the main sheet tidy. However, Google Sheets doesn’t support direct references to other sheets in conditional formatting rules. Named ranges also won't help here. To solve this riddle, use the INDIRECT function to reference cells across sheets.
Here’s how you set up conditional formatting based on another sheet:
- Move your condition cells (with the min and max order Qty.) to another sheet. Let’s say they’re now in Sheet2 in cells C2 and C3:
- In your rule settings, select your original table on Sheet1 as a range to format: A2:G50
- As for the custom formula, use INDIRECT to reference C2 in Sheet2:
=$E2<=INDIRECT("Sheet2!C2")
The INDIRECT function will convert your text string "Sheet2!C2" into a cell reference that your formatting rule can read:
- Similarly, for the upper limit, add another rule:
=$E2>=INDIRECT("Sheet2!C3")
With these formulas, you can update conditions on Sheet2, and the conditional formatting on your main sheet will adjust automatically. And no clutter on your main sheet 😉
How to remove conditional formatting in Google Sheets
Removing conditional formatting from your table is just as easy as adding it:
- Select any cell within the range where you’ve applied conditional formatting.
- Go to Format > Conditional formatting.
- On the sidebar that appears, you'll see all the rules applied to the range.
- Hover over the rule you want to remove and click the trash icon.
This will delete that specific rule without affecting other rules or formats.
With all these tips in mind, Google Sheets conditional formatting will play a key role in managing your data. From highlighting values, dates, and text patterns to setting up adaptable rules with custom formulas, you will bring clarity to your data. Now you’re ready to bring your data to life in Google Sheets! 😊
156 comments
Hi Natalia,
I'm so sorry to trouble you, but I'm stuck. I have shared a sample sheet with you. I am trying to highlight on sheet 1 the first week that the withdrawal on sheet 2 goes above £1000. Is that possible?
Many thanks.
Hi Laura,
I'm sorry I don't see any files from you. Please make sure you shared it with the correct email address: support@apps4gs.com
Please confirm here once you check the email. I'll look into it and try to help.
Hi Natalia,
I would like to colour my cells based true or false result in an if statement, but without taking into account the value if true or value if false. For example:
Column A Column B Column C
Row 1 1 a =if(A1 = 1, B1, "Bad") Since A1 = 1, C1 = B1 = a.
Row 2 1 b =if(A2 = 1, B2, "Bad") Since A2 = 1, C2 = B2 = b.
Row 3 1 b =if(A3 = 1, B3, "Bad") Since A3 = 1, C3 = Bad.
I would like to colour C1 and C2 based on the TRUE result of the correspondent if statements, without actually using the info "a" or "b".
Is this possible? If so, could you tell me how to do it?
Many thanks,
Joana
Just to correct and clarify the example dataset:
_________Column A______ Column B__________Column C (formula) ______Column C (result)
Row 1______1_______________a_______________ =if(A1 = 1, B1, "Bad")___________a
Row 2______1_______________b_______________ =if(A2 = 1, B2, "Bad") __________ b
Row 3______2_______________b_______________ =if(A3 = 1, B3, "Bad") __________Bad
Hello Joana,
If I understand your task correctly, you need to use the following custom formula in conditional formatting:
=AND($B1=$C1,ISBLANK($C1)=False)
As a range to color, select the entire column C. This formula will also ignore blank rows.
I want to highlight the cell for following query = "when duplicates found in column a & column b is not blank"
Hello Rahul,
I'm really sorry, I somehow missed your comment.
If you still need help with your task, please describe it in detail since the current description is not clear.
How to apply conditional formatting in a row if it has a sentence every row and you wanted to color it yellow if one row has this two names in a sentence?
Hello Angelica,
I believe the following parts of the blog post will answer your questions:
Format cells by the text they contain
Apply conditional formatting to entire rows
I have a list of order statuses in Column A - including "Ready to Complete" and "Incomplete" and numerical data in Column L.
I need a conditional formatting formula for:
If the status in Column A is "Ready to Complete" and the cell in Column L is blank than the blank cell turns blue
I've been able to do one or the other, but not both. I tried =$A6 = "Ready to Complete" - but it would trigger for Incomplete and not Ready to complete.
Hello Nicole,
you can combine both conditions using the AND function, like this:
=AND(A2="Ready to Complete",L2="")
Hi I'm trying to make names that come on the spreadsheet go bold if there is more than one, the names come onto the spreadsheet via form responses, so what I want to do is the moment 2 responses was submitted with the same username both names must go bold
Sorry this is the spreadsheet if it might help and the names I want to go bold is in column G on the requests tab, help will be much appreciated!! https://docs.google.com/spreadsheets/d/1333C8jB8Dlnd2m0zNPfhIiHVYQlTnOiCCQ1-hRbFwIw/edit#gid=1801551054
Hi Erzsi,
I can't access your spreadsheet. Please share it directly with support@apps4gs.com and confirm by replying to this comment when it's ready. I'll look into it.
Hello,
I am trying to use conditional formatting to highlight a whole column if there is an "X" in it.
The cells may or may not contain an "X" based if the Day & hour from another sheet match.
I've tried various text formulas and I have not been able to get any to work. I've also tried =REGEXMATCH.
The closest I have been able to get is using the =OR formula but it stops highlighting the column once it finds the "X"
Feel like if sheets had the option to uncheck "stop if true" then I wouldn't have this issue.
I shared the sample doc i made called "cond form stops when true"
Thanks for the great info, crossing my fingers you can help with this one
Hello Tom,
Thank you for sharing the spreadsheet right away! I've got it, will look into it and reply back asap.
Hello Tom,
The formula you need for your conditional formatting is:
=COUNTIF($D$4:$D$23,"X")>0
I've edited 4 conditional formatting rules in your spreadsheet: for columns D-G. Just change the rest of the rules accordingly and other columns will be coloured correctly as well :)
Wow !
Thanks for the quick response! I think that was one of the first I tried but I must not have had the absolute references.
Thank you!!
You're most welcome, Tom! Glad I could help :)
Hello, thanks for a great article, and especially for YEARS of replying to the questions people have in the comments section!
I have a Google Sheet that I use to track stock prices. I want two columns - one to track the %-value of a stock's price change, and the other to track the $-value of a stock's price change. I have set up conditional formatting for the % cells to go from +3% (green) to 0% (white) to -3% (red). I want to transfer that conditional formatting to the $ change cells, instead of setting the range directly for those cells. (A $10 change matters a lot when the stock is priced at $50, but it barely matters at all if the stock is priced at $3,000).
In the above examples, you taught how to do this with single color rules, but I cannot figure out how to do this with a color scale, or if it is even possible.
Hello Mike,
For us to be able to help you better, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) your example data 2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. You can replace any important information with some irrelevant data, just keep the format.
Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
We'll look into your task. Thank you.
Thank you for checking in on this. I made a sheet with just the relevant cells, and formatted the cells for % change. The cells for $ change I did not do any formatting. I published the google sheet to the web and emailed you a link to it :)
Mike,
I've got the table, thank you! I will look into it as soon as possible and try to come up with a solution.
Hi Mike,
I'm afraid the only option to make cells in column D replicate the color of cells in column C is to copy the formatting (coloring) itself. You can do that quickly for the entire column using the Paint format tool on the Google Sheets toolbar: https://support.google.com/docs/answer/161768?co=GENIE.Platform%3DDesktop&hl=en
how to highlighted the highest value cell in a multi range (A1:N40)?
Tks in advance
Hello Luky,
Create a conditional formatting rule with a custom formula like this (with the MAX function):
=A1=MAX($A$1:$N$40)
Hello,
Column H has numbers, Column I has numbers. I wish to have a formula that tells me if the number in Column I is greater or less Column H. What would I be best to do?
Hello!
If I understand your task correctly, the formula below to solve your task
=if(I1>=H1,"More","Less")
After that, you can copy this formula down along the column.
In my google sheets table, column A contains text and column F contains text and a total of 911 rows.
On each row, how can I highlight the row if the text in Column F is NOT EQUAL to column A?
E.g.
Cell A6 = "Hello" & Cell F6 = "Hello" then highlight row.
Cell A7 = "Hello" & Cell F7 = "Hi" then do not highlight.
and so on...
Hello!
Apply a conditional formatting rule to lines 1: 911 using the formula
=$A1<>$C1
I hope it’ll be helpful.
Apologies for combining the two, it's Google Sheets.
Thank you for replying.
If the issue still persists, please try clearing cache and cookies in your browser. If this doesn't help, please specify what menu items you follow exactly and where you're being redirected exactly. We'll see if there's anything we can advise you.
When using Excel Sheets on my tablet, clicking on 'conditional formatting' takes me to my files every time.
Any idea why that is and what the solution might be?
Hello,
I'm sorry, do you work with Excel Online or Google Sheets?
Hi Natalia
Thank you for your post as it is very helpful!
I use excel quite often and when I upload the spreadsheet to google drive, the formulas do not always work even though they work prior to uploading the spreadsheet to google drive.
I have 2 questions:
1st question, what is the conditional formatting formula to identify the 2nd lowest value in a column?
2nd question is a little more complicated. I have a spreadsheet which includes 2 columns, one of the columns consists of banks I work with, and the 2nd column is the interest rate they are offering. Sometimes more than one bank is offering the same interest rate.
On the same spreadsheet, I want to set it up so that when I enter an interest rate in a separate cell on this same spreadsheet, the bank or banks that are offering this same rate will automatically populate.
The formula below works before I upload the spreadsheet to google sheets, but doesn't work once the spreadsheet is uploaded to google drive.
=IF(ROWS($C$17:E20)<=$C$16,INDEX($K$2:$K$33,AGGREGATE(15,3,($L$2:$L$33=$D$16)/($L$2:$L$33=$D$16)*(ROW($L$2:$L$33)-ROW(P$1)),ROWS($C$17:E20))),"")
Thank you!
Hi Jimmy,
Thank you for your feedback.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here. We'll look into your task and try our best to help.
Hi, great article, very helpful, thank you.
I have a question. Well, a two-part question. Is it possible to set conditioning in a way that people will not be able to see the conditioning? For example, if I want to set questions that people answer, and if the answer is correct, it's one colour, and if the answer is incorrect, it's a different colour? I can do the colour thing, no problem. However, users are also able to see the formatting, so they would be able to see what the correct answer is just by checking the conditions of the formatting. Is there a way around this?
If there isn't. then is there a way to link the formatting to a separate sheet?
Thanks,
Mike
Hi Mike,
I'm sorry, I'm afraid it's impossible to link the conditional formatting to a separate document.
However, you can create the sheet with the correct answers, protect it from editing (so only you could edit it), and then hide the sheet. People without the permissions won't be able to unhide it.
You can also hide rows or columns with the answers. People without the permissions won't be able to unhide them as well.
Thanks, but I have also tried that too - I have one doc, with questions and spaces for answers on the first sheet, and a second sheet serving as a control sheet.
But now I don't know how to set the conditional formatting so that when the answer entered on to sheet 1 matches the answer on sheet 2 the answer shows in blue, and all other answers show in red.
Currently, all answers show in blue, regardless of what I try. So I'm doing something wrong :-)
Thank you for replying, Mark.
Sorry, it's hard to tell what you're may be doing wrong without seeing the data and the rules. For me to be able to help you better, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com). You can replace any important information with some irrelevant data, just keep the format.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.
Is it possible to shade the text of a cell if it's a formula? So if the cell contains a formula, it's output will be blue?
It doesn't need to be a specific formula, just for any cell that contains a formula, change it's color.
Sure,
assuming you want to check and colour cells in A2:A10, apply the conditional formatting to this range with a Custom formula as a rule and a formula like this:
=ISFORMULA(A2:A10)
Working on a Baseball League Google Sheet. Worksheet MPL contains all of the player data then there are individual Team Worksheets. Every player has a unique Numerical player code and this code is on every team sheet and on the MPL. The MPL has a column called (TYPE) that contains either nothing or an alpha value (UNC, UNL or MUP). There is no TYPE column on the Team Worksheets.
What I am trying to do is create a Conditional Format on the Team page that will respond to a Formula IF/AND query by formatting THE selected area specific colour.
Here is the formula I am using:
AND($G$8:$G$62=INDIRECT("Master Player List!$Z$1:$Z$1521"),INDIRECT("Master Player List!$C$1:$C$1521")="UNC")
Column Z on the MPL contains a unique number for every MLB Player
Column C is either blank or contains one of the following values UNC UNL or MUP
Column G on the target worksheet contains the same unique number for every MLB Player
Thanks for any help you can provide.
John
Hello John,
For us to be able to help you, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
Note. We keep that email for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.
Just sent the shared file to your site.
Thank you John,
We've got the file. One of our tech guys figured the easiest way would be to pull Col3 to each individual team sheet with your Query as well. Once you mention it in the formula, the column with types will appear as the last one – Q. Then, use formulas like this in your conditional formatting:
=$Q:$Q ="MUP"
If you don't need columns with types in those team sheets, you can hide them – the conditional formula rules will still work.
Thank you for your help. I was trying to find a way to do it without having to add 30 more columns to the Workbook. I have other similar cross sheet formulas that I am looking at but may have to just forget about it then.
I got the Answer..
Apply to range: B1:C
Format cells if... Custom Formula is...
Formula: =IF($A1<>"",B1="")
Format:
It may be helpful to someone..
Thank for your comments Rishabh.
Unfortunately, your solution doesn't work correctly.
I'd suggest creating 2 different rules in the following order:
I have a problem..
For example:
3 COLUMNS: A, B, C
Column C and Column B is to be highlighted if column A is filled.
Once C is also filled, the highlight on cell in column C goes away.
Once B is also filled, the highlight on cell in column B goes away.
Could you please help me with a condition for this..
please let me know what to do..
Hi there. Thank you for this great article.
My question is this:
I have two columns, A and B. Column A is my Due date and column B is my Shipped Date. All of column A has already four conditional formatting applied as follows:
Condition 1: Format cells if greater than =today()+7 - background green
Condition 2: Format cells if is between =today()+1 & =today()+6 - background yellow
Condition 3: Format cells if is between =today()+0 & =today()-14 - background orange
Condition 4: Format cells if less than =today()-14 - background red
Now I would like to strikethrough the text in any cell in column if there is a value in the corresponding cell in column B. How do I format that? Please help?
Thank you!
Hi Jon,
Formatting styles let you not only change the background color of cells but also alter the font in different ways. Simply create a new rule (for example, =B1:B10<>""), apply it to A1:A10, and choose Strikethrough in the Formatting style of the rule.
Thank you very much.