Hello, Excellers. Welcome back to another #FormulaFriday #ExcelTips blog post in my 2021 Excel series. Today let’s take a look at how to quickly find your cells that contain formulas. Have you ever inherited a workbook? Have you ever been asked to update an Excel workbook?. Do you need to see which cells contain Excel formulas?. Also, do you need to quickly check the referencing of your formulas to ensure they are correct?.
So, I would say that no-one wants to manually check each cell, do they?. Imagine doing that with hundreds or even thousands of Excel cells. No thank you!. Or even worse, accidentally changing cells then have to find where the formulas are. Here is a fast way to look at the worksheet and easily identify the formula cells.
Check Cells For Formulas.
All you need to do is follow the simple steps below:
- Formula Tab.
- Show Formulas.
Yes, that is all there is to this Excel tip. Once you hit Show Formulas the cells with formulas go from this
Then to this.
There is an Excel shortcut as well, for your shortcut lovers out there. Just hit Ctrl+~.
Phil says
Maybe not faster, but using the “Goto Special” is a very common way as well:
When in your spreadsheet, press F5 and in the dialogue window click the “special…” button in the lower left corner. Then select there content you want (here: formulas) and Excel will select all cells with formulas (like if you CTRL+clicked every single one of them).
You can then color them in a different fill and look through them, filter for the color or whatevery you want to do with it (e. g. Right Click the right border of the cell, move right and while holding down the right mouse button returning left immediately and then “copy as values”)