Lock & Protect Formula Cells
January 30, 2021
Have a lot of formulas and you thought find and select in Excel is just limited to cell values? We can quickly identify cells with formulas with this trick!
Whenever you are auditing an Excel worksheet and need to know where all the formulas are located, a great way is to highlight the formula cells in a distinctive color. This is how it is done:
STEP 1: Select all the cells in your Excel worksheet by clicking on the top left hand corner of your worksheet.
STEP 2: Press the CTRL+G shortcut which will open up the Go To dialogue box and select the Special button.
STEP 3: Select the Formula radio button and press OK.
STEP 4: This will highlight all the formulas in your Excel worksheet and you can use the Fill Color to color in the formula cells.
And now all your cells containing formulas are now highlighted!
You can practice this cool trick by downloading this workbook:
Say you have a data set and want to make sure that each column contains what it is supposed to.
For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text.
You can easily check this by highlighting that column and pressing CTRL+G to bring up the Go To dialogue box (or by choosing from the menu Home > Find & Select > Go To…)
Then you need to choose Special > Constants and select the constant that you want to find in your column.
In our example you will need to only select the Text box and de-select the other boxes and press OK. This will highlight the cells that contain text and you can begin to format these cells.
See how this is done by watching the tutorial below.
In Excel you can have a data set that comes from an external source which isn’t always formatted to your liking.
One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to analyze the data.
To find these annoying blank cells in Excel you will need to highlight all your data set (CTRL+*) and bring up the Go To shortcut: CTRL+G > Special > Blanks.
Then you can fill in the blank cells with a color red and filter by that same color to drill down to these blank cells and take some formatting action.
I show you how easy this can be done below.
STEP 1: Make sure your entire table is selected. We will select all the blank cells.
STEP 2: Press Ctrl + G to open the Go To Window. Click Special.
Select Blanks. Click OK.
STEP 3: The blank cells are now selected. Go to Home > Font > Fill > Color Red
STEP 4: Let us try it out! Try filtering the Customer column by selecting Filter by Color > Color Red
Our filtering has worked to show the blank cells!
January 30, 2021
June 30, 2020
October 19, 2017
April 18, 2016
October 16, 2015
June 18, 2015
May 28, 2015
October 8, 2014