How to Keep a Number Constant in Excel
Let’s say that you have a formula with cell references. Normally, if you drag them, all cells will change accordingly. Let’s take a…
Category: formulas
Apply Formula to an Entire Column in Excel
There are a few methods to apply the formula for the rest of the cells in a column that the formula should be…
Check if a Cell Contains a Substring
Case insensitive In order to check whether a cell contains a substring in a text, use the following formula.
|
1 |
=ISNUMBER(SEARCH(substring,string)) |
The following formula…
Pivot Table Count Unique Values
In order to count unique values in the Pivot Table, you have to do the following steps.
|
1 |
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) |
Leave a Cell Blank if the Condition Is False
The following formula will check cell A1, and if the condition is False (value != 2) it returns blank, otherwise (value = 2)…
Check if Value Exists in Another Column Using VLOOKUP
In order to get the value from the cell next to the matching cell, you have to use the vlookup function. This function…
Sumproduct
The SUMPRODUCT function is the combination of addition (SUM…) and multiplication (…PRODUCT). It gets one or more arrays and returns the sum of products of…
Timestamp
When you work with Excel, sometimes you may want to enter the current date or time near the list of items, such as…
Remove Leading and Trailing Spaces
There are a few different ways you can use to clean imported data from unwanted leading or trailing spaces. In this lesson, I…
Remove Blank Rows in Excel
If you have a large list containing empty rows, dealing with each row manually may not be the best idea. For that reason,…
