Excel Tips: Auto Highlight Paid & Not Paid Using Conditional Formatting #excel #exceltips #fyp #fypシ, by Adil Computer Center on YouTube. 1 min.
- Video length
- 1 min
- Time to do it
- 5 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Open your Excel sheet and add a column for status, such as column C. Type Paid in the first row and Not Paid in the next few rows so you can test the colours.
Select the status cells you want to control. Go to the Data tab, click Data Validation, and choose List from the Allow menu.
In the Source box type Paid,Not Paid with a comma and no spaces, then click OK. Each cell now has a small drop down arrow.
Select the rows you want to colour, starting from the top left cell of your data. Keep the whole table selected, not just the status column.
On the Home tab click Conditional Formatting, then New Rule, then Use a formula to determine which cells to format.
Type a formula like =$C2="Paid" and change C to your status column letter and 2 to your first data row number.
Click Format, open the Fill tab, pick a green colour, and click OK twice to close both boxes.
Add a second rule the same way with =$C2="Not Paid" and a red fill. Then test it by changing a drop down value and watching the row change colour.
Tips and common mistakes
Keep the dollar sign in front of the column letter so the rule always checks the status column and not the cell next to it.
Select the full row range, not just the status cell, if you want the whole line to change colour.
If nothing changes, check the row number in your formula matches the first row of your selected range.
Use Home, Conditional Formatting, Manage Rules to edit or delete a rule later.
Questions
Can I do this in Google Sheets instead?
Yes. Select your range, open Format, then Conditional formatting, choose Custom formula is and enter the same =$C2="Paid" formula with your own colours.
Why is my whole column turning one colour?
Your formula probably points at a single fixed cell without the dollar sign. Anchor the column with $ and reference the first data row only.
Can I add more statuses like Pending?
Yes. Add Pending to the data validation list and create a third rule with its own formula and an amber fill.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
