VLOOKUP function
Use VLOOKUP when you need to find things in a table or a range by row.
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
For example:
=VLOOKUP(B2,Desktop!$B:$D,2,0)
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
- The value you want to look up, also called the lookup value.
- The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
- The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
- Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or approximate match.
More info: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
COUNTIF function
Use COUNTIF, to count the number of cells that meet a criterion.
=COUNTIF(Where do you want to look?, What do you want to look for?)
For example:
=COUNTIF(D13:D2444, "=1")
=COUNTIF(A2:A5,"apples")
=COUNTIF(A2:A5,A4)
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)
=COUNTIF(B2:B5,">55")
=COUNTIF(B2:B5,"<>"&B4)
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")
=COUNTIF(A2:A5,"*")
=COUNTIF(A2:A5,"?????es")
More info: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
COUNTIFS function
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
For example:
=COUNTIFS(D13:D2444,">=1", D13:D2444,"<=3")
=COUNTIFS(B2:D2,"=Yes")
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
=COUNTIFS(B5:D5,"=Yes",B3:D3,"=Yes")
The COUNTIFS function syntax has the following arguments:
- criteria_range1 Required. The first range in which to evaluate the associated criteria.
- criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".
- criteria_range2, criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
Get data from other sheets in your spreadsheet
= followed by the sheet name, an exclamation point, and the cell being copied
For example:
=Sheet1!A1
='Sheet number two'!B4
Note: If a sheet name contains spaces or other non-alphanumeric symbols, include single quotes around it (as in the second example).
Display numbers as phone numbers
Excel provides a special number format that lets you format a number as a phone number. For example, you can format a 10-digit number, such as 9873367686, as +91 9873367686.
- Select the cell or range of cells that you want to format.
- On the Home tab, click the Dialog Box Launcher next to Number.
- In the Category box, click Custom.
- In the Type list, click on 0.
- Add +91 before 0.
Calculating New CTC based on %
= Current CTC * (1 + Hike)
Calculating Hike % on new CTC
Remove Carriage/Line Breaks Returns manually
Please find the steps for eliminating line breaks using Find and Replace:
- Select all cells where you want to remove or replace carriage returns.
- Press Ctrl+H to open the Find & Replace dialog box.
- In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot.
- In the Replace With field, enter any value to replace carriage returns. Usually, it is space to avoid 2 words join accidentally. If all you need is deleting the line breaks, leave the "Replace With" field empty.
- Press the Replace All button and enjoy the result!
Calculate Age in Excel
- Enter the date of birth in a cell. For this example, we'll use cell A1.
- In the cell where you want to display the age, enter the following formula: =(TODAY()-A1)+1
- Press Enter. The age will be displayed in the cell.
Replace TODAY() with any dates you want to calculate age
How to Adjust Column Width in Excel
- Select the Column(s) to Adjust
- Open the “Home” Tab
- Click on “Format” to Open the Menu
- Press “Column Width” in the Cell Size Group
- Enter the Specific Width and Click the “OK” Button
Adjust Column Width Shortcut = Alt + H + O + W
How to Center Across Selection in Excel?
- Select the Cell Range to Center the Text Across
- Press “Ctrl + 1” to Open the Format Cells Box
- Open the “Horizontal:” Drop-Down Menu in the “Alignment” Tab
- Click the “Center Across Selection” Option and Press the “Enter” Key
How to Unhide Columns in Excel?
Unhide Columns Shortcut = ALT → H → O → U → L
How to Highlight Duplicate Values in Excel
- Open the “Home” Tab
- Click on “Conditional Formatting” (Styles Group)
- Select “Highlight Cells Rules”
- Click on Duplicate Values…
Highlight Duplicate Values Shortcut = ALT → H → L → H → D
How to Change the Number of Decimal Places?
Increase Number of Decimal Places → Alt + H + 0
Decrease Number of Decimal Places → Alt + H + 9
