The Mail application that ships with macOS and OS X is solid, feature-rich and spam-eliminating software that is also an easy-to-use email client. Optimized to work on the Mac, the Mail app is trouble free and full featured. It can handle all your email accounts in one place. The best email apps for mac. ![]() Conditional Formatting can be very useful when presenting data in a spreadsheet, or for allowing information to jump out to you easily. In the following video lesson I go over the following uses of it: – Formatting based on numerical values (greater than, less than, between, etc). – Formatting based on text contained in cells. – Quick overview of Data Bars, Color Scales, and Icon Sets – How to use custom formulas with Conditional Formatting to highlight entire rows or other cells (extremely useful / versatile). FYI, the formula at time 7:30 is =$H4=”Y”, it seems some people have trouble reading it. Hi Ben, thanks for doing these! Some analysis requires the identification of dates that fall within a certain time period. The figure demonstrates how you can apply conditional formatting that highlights dates based on a start date and end date. As you adjust the start and end dates, the conditional formatting adjusts with them. ![]() Not sure why some people are complaining about free information Anyways, I’m having a conditional formatting issue I was hoping you could help me with. I am trying to highlight rows where one column is greater than the other, for instance if O6 > M6 I want all of row 6 to be highlighted. I tried making a rule with the formula =$O6>$M6, but it is not working. Both are filled with numbers, but the numbers in M are from a formula, so I’m wondering if that might be the issue. Please let me know what you think. Hey Ravi, Yes, it is certainly something you can accomplish with conditional formatting. You’ll just need to use the AND() function to check for multiple criteria. In your case, we’ll actually use 3 conditional statements, as follows: =AND(A1>=25, A1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. Click Insert > Module, and paste the following code in the Module Window. Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function 3. Then save the code, and apply the following formula: Count the colored cells: =colorfunction(A,B:C,FALSE) Sum the colored cells: =colorfunction(A,B:C,TRUE) A: is the cell with the particular background color you want to calculate the count and sum. B:C: is the cell range where you want to calculate the count and sum. Hi, A while ago I worked on a spreadsheet where I had a list of shop names in the first column, then the next 8 columns were each allocated to a month and the data for each shop name in each month was either Over (shaded RED), Under (shaded BLUE) or N/A (no shading, where no data was available for that shop that month). I wanted to be able to highlight in RED any shop names that had 4+ months noted as Over, and in BLUE any shops that had 4+ months noted as Under but did not know how to do conditional formatting so scrolled down the list manually scanning and highlighting as I saw clumps of red or blue. There were hundreds! Is there a formula that might have worked to be able to “average” the 8 months for each shop name and decipher if it was mostly Over or mostly Under and highlighted the shop name automatically in the corresponding colour? Would I have somehow needed to create an extra column that kind of “averaged” out the Overs and Unders (not sure if you can average text?) Thanks so much for your help! All the best, L. I’ve been using your tutorials to improve my Excel skills and am finding them so valuable – thank you for sharing your knowledge!!! I just graduated with my BBS in Public Accounting. I had taken my excel courses at the very beginning of my studies. Since then, I havent been using excel much. So I remember the basics, but I don’t remember the formulas. I would have had such a huge problem if it were not for your workbook and videos. I thank you so much! Thanks to you I can now refresh myself on excel and start to focus on building my Accounting career. I truly appreciate all the hardwork you have put into this. It helps so much!!!
0 Comments
Leave a Reply. |