TOP 100 EXCEL TIPS
by Vikram Soundankar
SHORTCUTS
| Question | Answer |
|---|---|
| How to hide a column? | Ctrl + 0 |
| How to hide a row? | Ctrl + 9 |
| How to autosum adjacent cells? | Alt + = |
| How to start a new line in cell? | Alt + Enter |
| How to insert a new worksheet? | Shift + F11 |
| How to insert a new column? | Ctrl + Shift + “+” |
| How to delete a column? | Ctrl + “-” |
| How to select an entire column? | Ctrl + Space |
| How to select an entire row? | Shift + Space |
| How to quickly copy a cell down? | Ctrl + D |
| How to quickly copy a cell right? | Ctrl + R |
| How to jump to the next sheet? | Ctrl + Page Down |
| How to jump to the previous sheet? | Ctrl + Page Up |
| How to navigate to cell A1? | Ctrl + Home |
| How to create a pivot table? | Alt + N + V |
| How to open a workbook? | Ctrl + O |
| How to replace data in a sheet? | Ctrl + H |
| How to create a chart? | Alt + F1 |
| How to insert a comment? | Shift + F2 |
| How to group rows? | Alt + Shift + → |
| How to remove duplicates? | Alt + A + M |
| How to merge cells? | Alt + H, M, C |
| How to unmerge cells? | Alt + H, M, U |
FORMULAS
| Question | Answer |
|---|---|
| How to sum a range of cells? | =SUM(range) |
| How to find an average of numbers? | =AVERAGE(range) |
| How to find the maximum value in a range? | =MAX(range) |
| How to find the minimum value in a range? | =MIN(range) |
| How to link cells from different sheets? | =SheetName!CellReference |
| How to concatenate two text cells? | =CONCATENATE(cell1, cell2) or cell1 & cell2 |
| How to join cells with a condition? | =TEXTJOIN(",",TRUE,range) |
| How to use INDEX to retrieve a value? | =INDEX(array, row_num, [column_num]) |
| How to round a number to nearest integer? | =ROUND(number,0) |
| How to extract left part of a text string? | =LEFT(text,num_chars) |
| How to extract right part of a text string? | =RIGHT(text,num_chars) |
| How to use a VLOOKUP function? | =VLOOKUP(value, table, col_index, [range]) |
| How to use an HLOOKUP function? | =HLOOKUP(value, table, row_index, [range]) |
| How to calculate the square root? | =SQRT(number) |
| How to generate a random number? | =RAND() |
| How to count cells with numeric data? | =COUNT(range) |
| How to count cells with any data? | =COUNTA(range) |
| How to get the current date? | =TODAY() |
| How to add days to a date? | =date + days |
| How to calculate percentage change? | =(new-old)/old |
| How to check if a cell is empty? | =ISBLANK(cell) |
| How to check if a condition is true? | =IF(condition, true, false) |
| How to sum only visible cells? | =SUBTOTAL(109, range) |
| How to sum visible & invisible cells? | =SUBTOTAL(9, range) |
FORMATTING
| Question | Answer |
|---|---|
| How to change cell color? | Home > Fill Color |
| How to apply bold text? | Home > Bold or Ctrl + B |
| How to insert a border around cell? | Home > Borders > All Borders |
| How to format a number as currency? | Home > Number Format > Currency |
| How to align text to center of a cell? | Home > Alignment > Center |
| How to wrap text in a cell? | Home > Wrap Text |
| How to merge cells together? | Home > Merge & Center |
| How to apply a format to a cell? | Home > Number Format > Short Date |
| How to change text color? | Home > Font Color |
| How to apply italics to text? | Home > Italics or Ctrl + I |
| How to apply a background color? | Home > Fill Color |
| How to rotate text in a cell? | Home > Alignment > Orientation |
| How to hide or show decimal places? | Home > Number Format |
| How to conditionally format cells? | Home > Conditional Formatting |
| How to copy formatting from another? | Format Painter |
| How to create a custom number format? | Home > Number > Custom |
| How to remove formatting? | Home > Editing > Clear Formats |
| How to change column width? | Right-click column > Column Width |
| How to change row height? | Right-click row > Row Height |
| How to hide a column? | Right-click column > Hide |
| How to unhide a column? | Select adjacent columns > Right-click > Unhide |
| How to insert a table hyperlink? | Insert > Link or Ctrl + K |
| How to change a chart’s font size? | Right-click chart > Font |
| How to adjust text orientation in cell? | Home > Alignment > Orientation |
ADVANCED
| Question | Answer |
|---|---|
| How to remove duplicate rows? | Data > Remove Duplicates |
| How to create a data validation list? | Data > Data Validation > List |
| How to find outliers in a list? | Use Z-score or IQR formula |
| How to look up data using INDEX & MATCH? | =INDEX(range,MATCH(lookup_value,lookup_range,0)) |
| How to create a named range? | Formulas > Name Manager > New Name |
| How to solve equations by changing cell values? | Data > What-If Analysis > Goal Seek |
| How to split text into columns? | Data > Text to Columns |
| How to protect a worksheet? | Review > Protect Sheet |
| How to create a drop-down list in a cell? | Data > Data Validation > List |
| How to calculate compound interest? | =FV(rate, nper, pmt, [pv], [type]) |
| How to switch data from rows to columns? | Paste Special > Transpose |
| How to track changes in workbook? | Review > Track Changes |
| How to freeze header row? | View > Freeze Panes > Freeze Top Row |
| How to create conditional formatting rules? | Home > Conditional Formatting > New Rule |
| How to define data validation criteria? | Data > Data Validation > Settings |
| How to calculate correlation between two sets? | =CORREL(range1,range2) |
| How to sort data in ascending/descending? | Home > Sort & Filter |
| How to remove duplicates from an entire workbook? | Use Power Query |
| How to create a waterfall chart? | Insert > Waterfall Chart |
| How to extract data from a pivot table with formula? | GETPIVOTDATA() |
| How to find the rank of the cell in an array? | =RANK.EQ(number,ref,[order]) |
| How to forecast a future point with linear regression? | =FORECAST.LINEAR(x, known_y’s, known_x’s) |