In English it reads, search the formula contained in cell C12 for the address of the cell at the intersection of ROW 5 and COLUMN C and make it a relative cell reference*. Here is the conditional formatting formula we’ll use to check if any of the cells in the range C5:C10 are referenced in the formula in cell C12 (which is =C5+C6): = SEARCH( ADDRESS(ROW(C5),COLUMN(C5), 4,1), FORMULATEXT($C$12) ) in the SEARCH function is an optional argument, meaning you can simply omit it. Tip: Function arguments in square brackets are optional. SUBSTITUTE(text, old_text, new_text, ) – replaces existing text with new text.FORMULATEXT(reference) – returns the formula as a string/text.COLUMN() – returns the column of a cell reference.ROW() – returns the row number of a cell reference.ADDRESS(row_num, column_num,, , ) – creates a cell reference as text.SEARCH(find_text, within_text, ) – returns the numeric starting position of text within text.These conditional formatting formulas use the following functions (it looks like a long list, but don’t be put off): We repeat for the formula in cell C13 and format in pink. If they are, the cell fill color is set to green. Note: This is quite advanced but if you take your time and refer to the workbook I’m confident you can grasp it.įor this technique we use a Conditional Formatting formula to check if any cells in C5:C10 are referenced in the formula in cell C12. It gives a quick visual check that you can toggle off by unchecking the ‘Show Formatting’ button: In the image below, you can see the cells in column C that relate to the totals in cells C12 and C13 by way of color coding: Highlight Cells Referenced in Formulas with Conditional FormattingĪnother tool we can use in Excel 20 is Conditional Formatting, it also comes with limitations, but first let’s look at the application. With this technique you get a nice color coded visual of the cells involved:īut that’s only good for one cell at a time. Trace precedents and dependents arrows are useful for reasonably simple formulas, but they can quickly turn into pick-up sticks in more complex scenarios:Īnother easy way to highlight cells referenced in a formula is to press F2 to edit the cell containing the formula in question.
These arrows can be turned off by clicking the ‘Remove Arrows’ button on the Formulas tab. For example, in the image below we can see Trace Precedents arrow for cell F4:Īnd here is the Trace Dependents arrow for the same cell: We can use tools like Trace Precedents and Dependents (on the Formulas tab of the ribbon) to get a visual indication of what cells are involved in a formula. xlsx file please ensure your browser doesn't change the file extension on download. Excel for Customer Service Professionalsĭownload the Excel Workbook.
0 kommentar(er)
