Microsoft Excel – Tips & Tricks
Create a named range from selected cells in a worksheet
Select the range of cells you want to create a named range.
Right Click and select Define Name.
Type a desire name in Name box.
Create a drop-down list
Select a defined name range (was listed in name box) or cells in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then Data Validation.
Note: If you can’t click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try again,
On the Settings tab, in the Allow box, click List.
Check Ignore blank and In-cell dropdown boxes.
Click in the Source box, then select your cell range on the sheet or type = and type the defined range name.
Remove a drop-down list in a cell
Select the cell with the drop-down list.
Click Data > Data Validation.
On the Settings tab, click Clear All.
Edit or Delete a drop-down list
Select the worksheet that has the named range for your drop-down list.
Go to Formulas > Name Manager.
In the Name Manager box, click the range name you want to Edit or Delete.
Click Close, and then click Yes to save your changes.
Hide zero values in selected cells
These steps hide zero values in selected cells by using a number format. The hidden values appear only in the formula bar and are not printed.
1. Select the cells that contain the zero (0) values that you want to hide.
2. On the Home tab, click Format > Format Cells.
3. Click Number > Custom.
4. In the Type box, type 0;;;@, and then click OK.
To display hidden zero values
Select the cells with hidden zeros.
On the Home tab, click Format > Format Cells.
Click Number > General to apply the default number format, and then click OK.
Display zeros as blanks or dashes
Use the IF function to do this.
Use a formula like this to return a blank cell when the value is zero:
Here’s how to read the formula: If 0 is the result of (A2-A3), don’t display 0, display nothing (indicated by double quotes ””). If that’s not true, display the result of A2-A3. If you don’t want the cells blank but want to display something other than 0, put a dash “-“ or other character between the double quotes.
=IFERROR (value, value_if_error)
value = the formula to check for an error.
value_if_error = the value to return if an error is found.
= ISERROR (A1/B1, ” ”)
If A1/B1 has an error, return blank.
If Vlookup function has an error, iferror will returns “Not Found”.
Highlight entire rows based on a cell value
For example, there is a data table covered from cell A1 to F10, and we want to highlight all rows that has cell with value “Tom” in column C.
1. Select the entire data table (A1:F10 in this example).
2. Click to Home tab.
3. Under Styles tab, click to Conditional Formatting pull down and select New Rule.
4. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
5. In the formula field, enter the following formula: =$C1=”Tom” (The trick is dollar sign symbol to recognize the whole column to check the value “Tom”, never forget this).
6. Click the ‘Format’ button.
7. In the dialog box that opens, set the fill color in which you want the row to get highlighted.
8. Click OK.
How to highlight largest / lowest value in each row or column?
1. Select the data range that you want to highlight the largest value in each column or row.
2. Then click Home > Conditional Formatting > New Rule
3. Under New Formatting Rule window, click Use a formula to determine which cells to format, and then enter this formula =B2=MAX($B2:$E2) into the Format values where this formula is true text box.
(Note: B2 is the first cell in the column/row and E2 is the last cell in column/row).
4. Then click Format button, in the Format Cells dialog box, select one color you like under the Fill tab, click OK.
Calculates the number of days, months, or years between two dates
"d" : The number of complete years in the period.
"m" : The number of complete months in the period.
"y" : The number of complete years in the period.
Cell A1 has date 6/1/2001
Cell A2 has date 8/15/2002
Cell A3 has formula: DATEDIF(A1,A2,”d”) will return a value 440 i.e. 440 days between June 1, 2001 and August 15, 2002.
Calculates the date by adding months to current date
=EDATE(start_date,number of months)
Cell A1 has date 5/1/2000
Cell A2 has 30 months
Cell A3 has formula: EDATE(A1,A2) will return a value of 1/11/2002. Ensure both cells A1 and A3 has date format. If cell A2 has -30 months, cell A3 will return a value of 11/01/1997.
Display current date and time
Assign cell A1 with formula = Now()
Ensure cell A1 has format as custom
Right click to cell A1 and set custom format box as “m/d/yyyy, h:mm:ss AM/PM”
Cell A1 will return a value of 5/25/2020, 7:32:55 AM.
Calculate current age
1. Enter birth of date in cell A1.
2. Use below formula in any cells to display current age in years, months and days format:
=DATEDIF($A$1,TODAY(),"y")& " Years, " &DATEDIF($A$1,TODAY(),"ym")& " Months, "& DATEDIF($A$1,TODAY(),"md")& " days"
Print the top row on every page
1. On the Page Layout tab, click on Print Titles.
2. On the Sheet tab, in the Rows to repeat at top box, type "$1:$1" (if you want to print other row, use mouse to highlight and select specific row on spreadsheet).
Freeze to lock the first row
On the View tab, click Freeze Top Row.
When you do this, the border under row 1 is a little darker than other borders, meaning that the row above it is frozen.
If you want to unfreeze a row, on the View tab, click Unfreeze Panes.
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. Lookup values must appear in the first column of the table, with lookup columns to the right.
Lookup a value in a table by matching on the first column
= VLOOKUP (value, table, col_index, false)
value: The value to look for in the first column of a table.
table: The table from which to retrieve a value.
col_index: The column in the table from which to retrieve a value.
false: return exact match.
This function will search the value in 1st column equals to “Apple”, and return the related value in column #4 of table (B8:F16).
1. To display formulas in all cells, press CTRL + ~, or
2. On the Formulas tab, in the Formula Auditing group, click Show Formulas.
3. To display formula in particular cell, double-click that cell.
Split text into different columns
1. Select the cell or column that contains the text you want to split.
2. Select Data, then select Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited, then Next.
4. Select the Delimiters for your data. For example, Comma and Space. You can see a preview of your data in the Data preview window. Select Next.
5. Select the Column data format or use what Excel chose for you.
6. Select the cell or destination, which is where you want the split data to appear on your worksheet, click to Finish.