CONDITIONAL FORMATTING IN EXCEL

Conditional formatting in Excel is a feature that allows you to highlight cells based on specific conditions or rules. This feature enables you to visually distinguish between different types of data, making it easier to analyze and understand. Conditional formatting can also be used to highlight cells that meet certain criteria, such as values that are above or below a certain threshold, dates that fall within a specific range, or text that matches a particular pattern.

1. Here, in “sheet 1”, we have a question in the green box which says, “Find the sales Rep. whose generated revenue is >11,000”.

2. First of all, select the cells from July to December, by clicking and dragging from top to bottom.

3. Then go to your “Home” tab.

4. Then click on “conditional formatting”, and a menu will drop down.

5. On the menu drop down, click on “Highlight cells Rules” and another menu will dropdown.

6. On the “Highlight cell Rule” menu, click on “Greater than”.

7. Then a message box will appear.

8. In the message box, type in the criteria in the empty white space. Type in “11,000” which is the criteria.

9. By the left hand side of the message box click on it and a menu will drop down. There you can choose whatever color you want your text and cells to look like. Some number of suggested options will appear in the menu drop down, it will be left for you to make your choice.

10. But, for the purpose of the class we will go with “Green fill with Dark Text” as our choice of color.

11. Then you will click on “Ok” on the message box.

12. Then it will be applied.

13. Next, we move to “sheet 2”. Here, the question in the green box says, “Format cells using 3 color scales”.

14. First of all, highlight the cells from July  to December, by  clicking and dragging from top to bottom, just like we did in the previous example.

15. Then, go to your “Home” tab.

16. Then, click on “Conditional Formatting”, and a menu will drop down.

17. On the menu drop down click on “New Rule”.

18. A message box will appear.

19. Check on the first section of the  message box where it is written “Format all cells based on their values” and click on it.

20. Still check the second section of the of the message box, you will see a box written in it “2 color scale”, then  click the arrow head sign beside it.

21. A menu will drop down after clicking the arrow head sign.

22. On the menu, there are a number of suggested options for us to choose but for the purpose of the class and as the question instructs, we will choose “3 color scale”.

23. The press “Ok” om the message box.

24. And it will be applied.

25. You can also “Manage” the rues of the we just worked on by highlighting it.

26. Then go to your “Home” tab.

27. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

28. On the menu drop down click on “Manage Rules”.

29. A message box will appear.

30. Click on the box that has red, yellow, and green on it, and make sure the blue color background appears.

31. On the message box click on “Edit Rule”.

32. The message box will re-appear, giving you room for edition.

33. Click the arrow head sign beside the colored boxes (i.e the red, yellow, and green) , and different kind of colors will appear for you to choose from. When you are done, click “Ok” on the message box and it will apply.

34. Next, we move to “sheet 3”. Here the question in the green box says, “Show GREEN if Revenue above is Average and YELLOW if the Revenue is below Average”.

35. First of all highlight the cells from July to December by clicking, holding and dragging from top to bottom.

36.  Then, go to your “Home” tab.

37. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

38. On the menu drop down click on “New Rule”.

39. A message box will appear.

40. On the first section of the message box, under “Select a Rule type” click on the fourth one on the list which says “Format only values that are above or below average”.

41. On the second section, click on the box carrying an arrow head sign beside it and a menu will drop down.

42. On the menu drop down select “Above “and click on it.

43. Still on the message box click on “Format”.

44. A message box will appear carrying different types of colors on it, for you to choose from.

45. We will choose “Green” from the numerous colors displayed, as the question implies.

46. Then click on “ok” on the message box.

47. A message box will still appear.

48. Then press “ok” on the message box that just appeared.

49. And it will apply.

50. Go ahead and still highlight the data set from July to December by clicking, holding, and dragging from top to bottom.

51. Go to your “Home” tab.

52. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

53. On the menu drop down click on “New Rule”.

54. A message box will appear.

55. On the first section of the message box, under “Select a Rule Type” click on “Format only values that are above or below average”.

56. Go to the second section of the message box and click on the first box carrying an arrow head sign, and a menu will drop down. (Check image).

57. On the drop down menu select “below” and click on it.

58. Then click on “Format” on the message box.

59. A message box will appear carrying different kinds of color for you to choose from.

60. But, for the purpose of the class and as the question implies we will choose “YELLOW” by clicking a yellow color from the numerous colors displayed.

61. Then click “ok” on the message box.

62. A message box will re-appear, this time, showing you the color in which you just selected.

63. Then, click “ok” on the message box.

64. And it will be applied.

65. Next, we move to “sheet 4”. Here, the question in the green box says, “Find the top 10 and bottom 10”.

66. First of all, highlight the cells from July to December by clicking, holding and dragging from top  to bottom.

67. Then, go to your “Home” tab.

68. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

69. On the menu drop down select “Top/Bottom Rue” and another menu will appear.

70. On the “Top/Bottom Rule” menu click on “Top 10 items”. And it will apply for Top 10.

71. In the same way, highlight the data set again after getting the Top 10. Highlight the cells from July to December by clicking, holding, and dragging from top to bottom.

72. Then, go to your “Home” tab.

73. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

74. On the menu drop down click on “Top/Bottom Rule” and another menu will appear.

75. On the menu that just appeared click on “Bottom items”. And it will apply for Bottom 10.

76. Alternatively, we can also do this by highlighting the data set from  July to December by clicking and dragging from top to bottom.(no image)

77. Then, go to your “Home” tab.

78. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

79. On the menu drop down click on “New Rule”.

80. A message box will appear.

81. On the first section of the message box, under “Select a Rue Type” click on “Format only Top or Bottom values”.

82. Then, click on “Format” on the message box.

83. A message box will appear showing different kinds of colors displayed on it, for you to choose from.

84. For the purpose of the class we will choose Navy blue color by clicking on it.

85. Then click “ok” on the message box.

86. A message box  will Still re-appear showing the color you just selected.

87. Then, click “ok” on the message box.

88. And it will apply.

89. So, in the same process we will still highlight the data set from July to December by clicking, holding and dragging from top to bottom.

90. Go to your “Home” tab after highlighting the data.

91. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

92. On the menu drop down click on “New Rule”.

93. A message box will appear.

94. On the first section of the message box under “Select a Rule Type” click on “Format only Top or Bottom values”.

95. On the second section of the message box click on the box with an arrow head sign written in  it “Top” and a menu will drop down showing you two options which are (Top and Bottom).

96. Click on “Bottom” from the suggested options on the menu, because we have already done for “Top”.

97. Then, click on “Format” on the message box.

98. A message box will appear showing different kinds of colors to choose from.

99. For the purpose of the class we will choose Red.

100. Then, click “ok” on the message box.

101. A message box will still re-appear showing the color you just selected.

102. Then, click “ok” on  the message box.

103. And it will be applied.

104. Next, we move to “sheet 5”. Here, we have question in the green box which says, “Apply Data Bars “.

105. You can apply this Data bars for the whole revenue by highlighting it from July to December.

106. Then, go to your “Home” tab.

107. On your “Home” tab click on “Conditional Formatting” and a menu will drop down.

108. On the menu drop down click on “Data Bars” and another menu will appear showing different data bars with different colors.

109. You can click on any of your choice.

110. Or, you can just apply it on a single column by highlighting it from top to bottom. For the purpose of the class we will choose “July” column and work with it.

111. Then, go to your “Home” tab.

112. On your “Home’ tab click on “Conditional Formatting” and a menu will drop down.

113. On the menu drop down click on “Data Bars” and another menu will appear showing different data bars with different colors for you to choose from.

114. You can click on any of your choice and it will apply.

115. Next, we move to “sheet 6”. Here, we have a question in the green box which says, “Show if the revenue is INCREASING STABLE or DECREASING using icons”.

116. First of all, select or highlight the data from July to December by clicking and dragging from top to bottom.

117. Then, go to your “Home” tab after highlighting the data.

118. On your “Home’ tab, click on “Conditional Formatting” and a menu will drop down.

119. On the menu dropdown click on “Manage Rules”.

120. A message box will appear.

121. On the message box, click on “New Rule”.

122. Another message box will re-appear.

123. On the first section of the message box, under “Select a value Type” click on “Format all cells based on their values”.

124. On the second section of the message box click on the first box with an arrow head sign beside it and a number of suggested options like (2-color scale, 3-color scale, Data bars, Icon set) will appear.

125. For the purpose of the class we will select “3-color scale” and click on it.

126. Then click “ok” on the message box.

127. A message box will still appear showing the colors you just selected.

128. Then, click “ok” on the message box.

129. And it will apply.

130. Go back to your “Home” tab.

131. On  your “Home” tab click on “Conditional Formatting” and a menu will drop down.

132. On the menu drop down click on “Icon sets” and another menu will drop down showing different kinds of icon sets.

133.  From then “icon sets” menu click on the n”3-triangle” icon sets.

134. And it will apply.