Conditional Formatting - MR. RENAUD'S CLASS



Conditional FormattingMicrosoft Excel offers you the ability to make a cell ‘look’ a particular way based on what is entered into the cell.For example, if 10 is entered into a cell you could have that cell’s background turn blue or have the number 10 turn pink.The ‘Conditional Formatting’ option is located under the ‘Home’ menu, in the Style section. 172021512319000208597525527000In order to set a new rule, you must click on ‘New Rule’ at the bottom of the drop down menu. Then select the, Format only cells that contain, option.You will see a window that will allow you to Edit the Rule Description:You will be able to select the cell(s) you want to format and the constraints that will apply to those cells. You will also be able to format using colour.The options you can use for conditional formatting are:BetweenNot betweenEqual toNot equal toLess thanGreater thanLess than, equal toGreater than, equal toConditional formatting allows you to format the cell’s appearance based upon either the ‘cell value’ or the ‘cell formula’.Once you format one cell, you can copy that format to other cells on your spreadsheet by using the Format Painter, which is on your Home menu in the Clipboard section. In order to use the Format Painter, you would do the following:Select the cell that contains the formatting you wish to copyDouble click on Format PainterClick once on all other cells that you wish to copy the formatting to.CONDITIONAL FORMATTING EXAMPLEExample:Open a blank Excel WorkbookSave as: Conditional Formatting ExampleType Mackenzie across row 1 (one letter in each column)Type Manitoba going down column A (one letter in each row)center698500Highlight the M by clicking on it onceSelect Home Conditional Formatting New Rule Format only cells that containEnter the following parameter:Condition 1Cell value is equal to mFormat to fill in orange.Click OK.Condition 2Go back into the Conditional Formatting optionSet a second condition for the letter mCell value is not equal to mFormat to fill in redClick OK.-6794514795500Repeat the steps above for the rest of the letters:Use the formatting brush to achieve this by doing the following:5715000127000Click once on the m that is now orangeDouble click the formatting paint brush on your found under the clipboard section of the Home menu.Select each letter on your worksheet by clicking on them once (they should all turn red)Once you are done select ESC.You will now have to go to each individual cell and change the conditions in the conditional formatting option to reflect the letter in that cell.Start with the letter a in MackenzieClick on it onceSelect Conditional Formatting Manage Rule Edit RuleThe first condition will be highlighted in the Rule Manager.Change the m to an a and click OK.Select the second condition listed in the Rule Manager.Change the m to an a again and click OK.Click OK to exit. The letter a should now be orange.-190531432500Repeat for all lettersOnce you have finished call the teacher over to check your work. IF StatementsIF statements are used in order to instruct Excel to select specific cells only if they meet the criteria indicatedUse the Insert Function button next to the formula bar to help you insert your formulaExample Use the insert function button next to the formula bar to open all of the possible functions45720050990500Select IF from the list of functions and click ok. A window like the one below will open.on the line that reads Logical test highlight the cell that you want Excel to evaluate by clicking once and then enter your parameters:equal to (=)greater than (>) less than (<)on the line that reads Value if true, type what you would like Excel to populate if the selected cell meets the parameters:true, yes, great job, etcon the line that reads Value if false, type what you would like Excel to populate if the selected cell does not meet the parameters:30861008001000false, no, try again, etcSelect okFill down or across to copy your IF statement to adjacent cells. You Try Example:Open a blank Excel WorkbookSave as: IF Statement ExampleReplicate the spreadsheet that you see to your right.Insert an IF statement that gives the following commands:If, the value of the cell is greater than 4True, GREAT JOB!False, TRY AGAINOnce you are finished have the teacher check your work. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download