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.
More info: https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
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
= New CTC / Current CTC - 1
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 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
Refer: https://www.wallstreetprep.com/knowledge/excel-shortcuts/
Adjustment of Rows and Columns
- Autofit Row Height: To quickly adjust a row’s height to fit its contents, select the row(s) and press Alt + H + O + A. The shortcut automatically resizes the row(s) to display all the contents without truncation.
- Autofit Column Width: Similar to row height, you can swiftly adjust a column’s width to fit its contents by selecting the column(s) and pressing Alt + H + O + I. The shortcut ensures all data within the selected column(s) is fully visible.
- Specific Height or Width: To change the size of rows or columns, try the following shortcuts:
– For row height, select the row(s), press Alt + H + O + H. Enter the desired height in points (e.g., 20), and press Enter.
– For column width, select the column(s), press Alt + H + O + W. Enter the desired width in points (e.g., 25), and press Enter.
IF Function
The IF function is a premade function in Excel, which returns values based on a true or false condition.
It is typed =IF and has 3 parts:
=IF(logical_test, [value_if_true], [value_if_false])
For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
How to Lock Cells in Excel
Here's a more detailed breakdown:
- Select the cells: Highlight the specific cells, columns, or rows you want to lock.
- Open Format Cells: Right-click on the selected area and choose "Format Cells" or use the shortcut Ctrl+1 (or Cmd+1 on Mac).
- Access the Protection tab: In the Format Cells dialog box, click on the "Protection" tab.
- Lock the cells: Check the "Locked" box. This doesn't immediately lock the cells; it just sets the formatting property.
- Protect the worksheet: Go to the "Review" tab on the Excel ribbon and click "Protect Sheet".
- Set a password (optional): You can optionally set a password to prevent unauthorized users from unprotecting the sheet.
- Confirm the protection: Click "OK" to apply the sheet protection and lock the specified cells.
Now, only the cells you unlocked will be editable, while the locked cells will be protected from changes. You can also choose to allow users to select locked cells, or format them, by making those selections in the "Protect Sheet" dialog.
How to Lock a Formula in Microsoft Excel Using $ Sign
Understanding Absolute vs. Relative References:
Relative references: (like A1) change when the formula is copied. If you copy a formula from A1 to A2, a relative reference to B1 in the original formula will become B2 in the copied formula.
Absolute references: (like $A$1) remain fixed when the formula is copied.
Mixed references: (like $A1 or A$1) lock either the column or the row. $A1 will keep the column fixed as A, but the row will change if copied. A$1 will keep the row fixed as 1, but the column will change.
- To make a cell reference absolute, place a dollar sign ($) before both the column letter and the row number (e.g., $A$1).
- To lock only the column, place the dollar sign before the column letter (e.g., $A1).
- To lock only the row, place the dollar sign before the row number (e.g., A$1).
Use slicers to filter data
- Click anywhere in the table or PivotTable.
- On the Insert tab, select Slicer.
- In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK.
- A slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.
Get geographic location data in Excel
- Type some text in cells. For example, type a country, state, province, territory, or city name into each cell.
- Then select the cells.
- Go to the Data tab, then from Data Types, select Geography.
- If Excel finds a match between the text in the cells, and our online sources, it will convert your text to the Geography data type. You'll know they're converted if they have this icon: Linked record icon for Geography
- Select one or more cells with the data type, and the Insert Data button Add Column button will appear. Select that button, and then click a field name to extract more information. For example, pick Population.
- Select the Insert Data button again to add more fields. If you're using a table, type a field name in the header row. For example, type Area in the header row and the Area column will appear with data.
TRANSLATE Function in Excel
- Replace "text to translate" with the actual text you want to translate, or use a cell reference to the cell containing the text.
- Replace "source language code" with the two-letter code of the source language (e.g., "en" for English, "es" for Spanish). If you leave it blank, Excel will try to detect the language automatically.
- Replace "target language code" with the two-letter code of the desired target language.
SUMIF function
- range: The range of cells that you want to evaluate against the criteria.
- criteria: The criteria that determines which cells in the range should be summed. This can be a number, text, date, or logical expression.
- sum_range: The range of cells to sum. If omitted, the range argument is used.
SUBSTITUTE function
- Text Required - The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text Required - The text you want to replace.
- New_text Required - The text you want to replace old_text with.
- Instance_num Optional - Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
