Learn how to highlight row if cell contains partial text. Essentially, highlight row if cell contains text in Excel.
Or highlight row if cell contains partial text. This also works in Google Sheets.
Highlighting a row in a spreadsheet can be a useful way to draw attention to certain data that meets specific criteria. One way to do this is by using conditional formatting to highlight a row if a cell within that row contains partial text. This can be done in a few simple steps, and they are listed below.
Highlight Titles
1) Select table
2) Home -- Style -- Conditional Formatting
3) New Rule...
4) Use a formula to determine which cells to format
5) =AND(ISNUMBER(SEARCH($E$1,$B1)),$E$1"")
6) Format
7) Fill tab
8) Choose a colour
9) OK
10) OK
excel highlight row if cell contains text,highlight row if cell contains partial text google sheets,excel conditional formatting if cell contains multiple specific text,excel conditional formatting if cell contains specific text,conditional formatting excel based on text,conditional formatting if cell contains partial text google sheets,highlight partial text in excel cell,how to highlight cells in excel based on value of another cell
Or highlight row if cell contains partial text. This also works in Google Sheets.
Highlighting a row in a spreadsheet can be a useful way to draw attention to certain data that meets specific criteria. One way to do this is by using conditional formatting to highlight a row if a cell within that row contains partial text. This can be done in a few simple steps, and they are listed below.
Highlight Titles
1) Select table
2) Home -- Style -- Conditional Formatting
3) New Rule...
4) Use a formula to determine which cells to format
5) =AND(ISNUMBER(SEARCH($E$1,$B1)),$E$1"")
6) Format
7) Fill tab
8) Choose a colour
9) OK
10) OK
excel highlight row if cell contains text,highlight row if cell contains partial text google sheets,excel conditional formatting if cell contains multiple specific text,excel conditional formatting if cell contains specific text,conditional formatting excel based on text,conditional formatting if cell contains partial text google sheets,highlight partial text in excel cell,how to highlight cells in excel based on value of another cell
Category
📚
LearningTranscript
00:00Here's how you can create a feature in Excel that highlights the row containing
00:03the relevant text that you have entered. And to top it off, sort the table so that
00:08the highlighted row is always at the top. Let's start off by highlighting the row
00:11first. Click anywhere on your table and press ctrl A. This will highlight the
00:17whole table for you. Click on home tab and make sure you're in style and
00:21conditional formatting, new rule. In this new rule here you're gonna use formula
00:27and this is the formula you'll be using. Next click on format, make sure you're on
00:34the fill tab and select the color that you want to highlight the row with. I'm
00:38gonna say I'm gonna highlight with yellow and click OK and click OK. Next go to the
00:44cell E1 here and after that enter maybe a text called FO and hit enter and if you
00:50scroll down you'll see any of this title that has an FO in it will be highlighted.
00:54Let's say try DHE as in the. You can see any of those title with the the will be
01:00highlighted. Next let's sort the table so that the highlighted row is always at the
01:05top. Select anywhere on your table and press ctrl shift L to enable filter. Now
01:12go to developer. If your developer tab is not available, right click anywhere on the
01:17ribbon and click on customize the ribbon and then make sure that the check mark is
01:22available next to the developer and click OK. I already have my developer tab on here
01:27so the next thing you have to do is click record macro and then give the
01:32macro a name and click OK. After that click the filter, after that select sort by
01:38color and select the yellow color which is your highlighting color and after that
01:43stop your macro recording. Once that's done you have to create a button to sort so
01:49click on insert you are still on the developer button and then draw a button
01:54somewhere on your spreadsheet and associate the action of the button with
01:59a macro one which you just created and click OK. Once that's done click anywhere
02:04on the spreadsheet. If for some reason this thing is turned on like that make sure
02:08you click on it to disable it. Now if you type say AM and hit enter you can see
02:14they are not sorted yet but if you click the button you can see that the
02:17highlighted row is always at the top. Here's an SH and button. You can see that
02:22the sorted highlighted rows are always sorted at the top. The last feature I want
02:29to add would be to be able to enter alphabet like that and the table
02:33highlight by itself without me having to press an enter key like this. So to do
02:38that you're gonna have to introduce a new control called text box. Under develop,
02:42control, insert. Under active X control there's a text box here and let's drop a
02:47text box somewhere around here. Next you're gonna right click on the text box
02:51and click on property from the context menu. Now we're gonna link this text box
02:56to this cell E1. So on the link cell you're gonna press E1 like this and hit
03:02enter. And we're gonna make this text box looks exactly like a cell. So we're gonna go
03:08border style here. I'm gonna change it to border style single and this border color
03:13here you're gonna change it to scroll bar. And then once that's done let's close
03:17this and now I can see it looks exactly like a cell. So what you do is that just
03:22as a test let's click the design mode. So if you type anything in here you can see
03:28the table dynamically changes as soon as you make any of those changes on your
03:33spreadsheet. So the final thing is the cosmetic thing. Click on design again and
03:38now you can drag as soon as you click on design and overlay it on top of the cell
03:43E1. Now click the design mode as off and now as soon as you enter any characters
03:50let's say the you see how the tables get highlighted by itself as soon as I enter
03:56anything and of course your sort button you click on it it will sort it for you.