Contents
An example from life. There is a large table with a bunch of formulas and values. It is known that when entering data into the table, the operator made a mistake (it doesn’t happen to anyone) and in some places entered values from the keyboard instead of the formula into the cells. The task is to find all the cells with formulas and check if there is a constant where the calculation should be.
Or. They sent you a large spreadsheet. You want to understand how it works, what and how it counts. Seeing where the formulas are in the cells and where the values entered from the keyboard would be very helpful, right?
Press the key F5, then in the window — button Highlight (Special). A very useful (it is a pity that it is so deeply “buried”) dialog box will open, with which you can select cells according to a certain attribute:
For example:
- only cells with constants (i.e. with values entered from the keyboard)
- only cells with formulas and functions
- only visible cells (no hidden ones, for example, when filtering or in collapsed totals)
- only empty cells, etc.
In the latest versions of Excel 2007/2010, this window is available on the tab Home (Home) in group Editing (Edit) – drop down list Find and select (Find & Select). The team is called Selecting a group of cells (Go to special):
:
- How to see both formulas and their results in cells at the same time
- Colormap for highlighting cells with different content types in PLEX add-on