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!

Read more:

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.

automatic-format-in-excel-format-in-excel (1)

+ Step 2: Here you press the key combination Ctrl + A, or scan / highlight to make a selection for the worksheet => Then press the key F5 (or Fn + F5) on the keyboard to open the dialog box Go To => Then click Special to customize extension.

READ  Review Samsung Galaxy M30s: Bad design but good use - Knowledge sharing blog

automatic-calculation-in-excel-format (2)

+ Step 3: Then tick the box Fomulas at the dialog box Go To Special => then press OK to make a selection of formula cells.

automatic-format-in-excel-format-excel (3)

Finally we get the result as shown below.

automatic-format-in-excel-format (4)

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.

automatic-calculation-in-excel-format (5)

+ Step 2: At the dialog box Microsoft Visual Basic for Applications, you open the tab Insert => and select Module as shown below.

automatic-format-in-excel-format-in-excel (6)

+ Step 3: Now copy the following code …

Sub SelectFormulaCells ()
‘Updateby20140827
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
next
Application.ScreenUpdating = True
End Sub

… and Paste into the dialog box Module1 (Code) => then click Run, or F5 on keyboard to execute.

automatic-calculation-in-excel-format (7)

+ Step 4: Then confirm the reference position in the KutoolsforExcel dialog box, to color the formula cell value.

automatic-format-in-excel-format-excel (8)

And this is the result after we're done.

automatic-format-in-excel-format-in-excel (9)

Epilogue

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.

READ  How to collapse text in Word automatically - Knowledge sharing blog

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 :

Leave a Reply

Your email address will not be published. Required fields are marked *