Related Posts
Hi everyone,
Need 11 likes..
Thanks in advance
Are you in a toxic environment?

Additional Posts in Excel Genius
Everyone: Excel is a smart app
Excel:

New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.






just do a ctrl + “~”
you’ll see the cells change to show the formulas if they have it.
This is super inefficient for checking 10,000 rows of data 😂
Subject Expert
3 ways (on top of my head)
1 - use =ISFORMULA() in the adjacent column, if there is a FALSE (or more), it means that the reference was hardcoded*
2 - go to Find and Select -> go to special -> check for constants (or formulas)*
3 - Go to Formulas in the tab bar, click "Show Formulas" and you can see on the Excel itself the formula (remember to turn it off after)
* 1 and 2 have the limitation of seeing an operation (like 2*3) as a formula instead of a constant
Re-write the formula and replace for entire column. We often try to find a problem but could instead just apply the solution.
Go to conditional formatting "use formula" option. Highlight cell if =not(isformula(F2)). Then apply it to your whole range. The cells that are hard-keyed will highlight with the formatting you choose.
F2 was a just a cell example fyi.
Can then sort by color to bring the non formulas to the top
If you want the whole column to contain your formula you could just carry it down from the first cell by double clicking on the + symbol when you hover on the bottom right corner of the cell. Or you could copy the first cell in the column and ctrl + shift + down arrow to highlight every populated cell below and paste
If you want to find and replace hard coding, ctrl F (find), search for $, make sure you select search formulas, replace with nothing.
Ctl+' or search "formula auditing mode"