Note: Please read the article carefully before proceeding! If in the process of using you encounter any errors, such as broken download links, slow loading blog, or not being able to access a certain page on the blog … then please inform me through the Contact page. Thanks!
Yes, in the process of using Excel to statistic, calculate the values included in the worksheet, the application of function formulas to the worksheet is quite obvious, and that is also one of the strengths that Excel having.
However, in some cases you may need to change a reference cell, or you want to lock / hide formula cells in Excel to keep data safe … while you do not remember the location of the formulas created on your long spreadsheet, what to do?
Okay, to assist you in finding the cells that contain formulas, according to the specific requirements of the Excel file, today, I will share with you a little trick in making a selection, and fill. The color of the formula cells is on the worksheet.
Support for setting, changing values, or locking formula cells on a spreadsheet with just a few mouse clicks … sounds very interesting, isn't it 🙂 then let's get started!
Method # 1: Make selections for cells with formulas in Excel with Go To Special
+ Step 1: First, open the Excel file that you are processing.
+ Step 2: Here you press the key combination
Ctrl + A, or scan / highlight to make a selection for the worksheet => Then press the key
Fn + F5) on the keyboard to open the dialog box
Go To => Then click
Special to customize extension.
+ Step 3: Then tick the box
Fomulas at the dialog box
Go To Special => then press
OK to make a selection of formula cells.
Finally we get the result as shown below.
Method # 2: Automatically color formula cells in Excel with VBA code
In addition to use
Go To Special To make a selection of Excel formula cells, you can color the values as follows, do:
+ Step 1: First, open the Developer tab => and select
Visual Basic. Or You can press the key combination
Alt + F11 to open cdoe VBA editor.
+ Step 2: At the dialog box
Microsoft Visual Basic for Applications, you open the tab
Insert => and select
Module as shown below.
+ Step 3: Now copy the following code …
Sub SelectFormulaCells ()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = “KutoolsforExcel”
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox (“Range”, xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.SpecialCells (xlCellTypeFormulas, 23)
Application.ScreenUpdating = False
For Each Rng In WorkRng
Rng.Interior.ColorIndex = 36
Application.ScreenUpdating = True
… and Paste into the dialog box
Module1 (Code) => then click Run, or
F5 on keyboard to execute.
+ Step 4: Then confirm the reference position in the KutoolsforExcel dialog box, to color the formula cell value.
And this is the result after we're done.
Okay, so I have just detailed instructions with you a little trick in automatically making selections and Coloring cells that contain formulas on Excel spreadsheets Alright then.
At this point, my tutorial on how to make selections and fill formula cells in my Excel file, here, please pause. Hope this tip will be helpful to you.
Good luck !
CTV: Luong Trung – Blogchiasekienthuc.com
Note: Was this article helpful to you? Do not forget to rate the article, like and share it with your friends and relatives!Read more :