Go To Special series. Select Constants and Formulas

Select Constants and formulas:

Welcome to the second post of this series – Select constants and formulas. Click here to read to navigate to go to special and also to select comments.

What are constants and formulas?

Well, constants are values that are unchanging in nature. They are not dependent on any other constant or variable.

Formulas on the other hand are derived based on the value of referred cells. Formulae copied and pasted as values are constants as well for selectionJ. Having said that, let’s explore the options to select constants and formulas by using “Go to special”.

Data types of constants and formulas can be number, text, logical and error.

Number can be any numeric value

Text can be any text (string) value.

Logicals are TRUE and FALSE.

Errors are all the error types in excel. #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

Tip: If you select a range and then go to special and select your criteria, it selects the criteria only from your selected range.

Select Constants and formulas.

In the below example, I have four employees and their details of salary, bonus and their contract type (Boolean, Permanent or not). Our company policy, at the year end permanent employees get 15% on annual salary as bonus and contract employees get a fixed amount of 100,000.

In this example, Emp Id’s are constants. Emp Name is formula based (Vlookup driven from the mapping table).  Salary is constant and bonus is 15% on salary for permanent employees and a fixed amount of 100000 for contract employees. Below screenshot to show what are formulas and constants.

Extra Tip: Press Ctrl+` to get to this view (to see all formulas).

Excel Shortcut: Press F5, Alt+S+O to select constants. Press F5, Alt+S+F to select formulas.

When you click on constants or formulas on the go to special dialog box, you will see four check boxes (Numbers, text, logicals and error) are activated. Now select the check box for the data type you want to select. If not, by default, constants of all data types are selected.

Share your experience:

Do you use this option more often? Share your experiences, how do you deal with this type of data. Have another tip do this stuff? Also, do not forget to leave your comments on this post. I learn a lot from you all.

I’ll be posting soon, second post of this series “Go to Special – Blanks”.