## Excel Formula Shortcuts

### 1. Basic Formula Usage

 Ctrl+` Toggle show formulas on/off = Start a formula Alt+= Insert the AutoSum formula Ctrl+A with formula present Edit formula in formula Bar Ctrl+Shift+U Expand/ collapse formula bar F4 After typing cell reference (eg =E4) makes reference absolute (=\$E\$4). Repeat if you want to toogle from absolute reference to partial or complete removal (\$E\$4 -> E\$4 -> \$E4 -> returning to E4. Shift+F3 with empty cell Display the 'insert function; dialog box Shift+F3 with formula present Edit arguments of formula at cursor position Alt+H,FD, U Select all Formulas (Home - Find - Formulas Ctrl+Shift+Enter with array formula Enter a formula as an array formula. Formula bar will show this as e.g. {=SUM(A1:A3*B1:B3)} whichis the sum of A1*B2+A2*B2+A3+B3. This is an easy and concise method. Remember that each time editing the formula will require the Ctrl+Shift+Enter. Some keyboards seem to distinguish between left and right Shift key Ctrl+` Show/ hide all formulas. This will automatically extend all column widths which reverses when presed again Ctr+' Duplicate formula from cell above F3 Paste named range in formula

### 2. Common Text Functions

 =LEFT (text, num_chars) Return X number of chars of text from left =RIGHT (text, num_chars) Return X number of chars of text from right =MID (text, start_num, num_chars) Return X number of chars of text from middle =LEN(text) Returns length of text =CONCATENATE (text, text2, …) Concatenates/ combines multiple cells or text fragments =FIND (find_text, within_text) Case-sensitive search of text within provided text. Add optional start positionas optional parameters. =SEARCH (find_text, within_text) Case-insensitive search of text within provided text. Add optional start positionas optional parameters. =TRIM(text) Remove extra spaces from beginning and end of text =UPPER(text) Convert text to all upper case =LOWER (text) Convert text to all olower case =PROPER (text) Convert text to all proper case (first letter capital, rest lower case)

### 3. Common Calculation Functions

 =SUM (value1,value2, …) Sum values =COUNT (value1,value2, …) Count cells =COUNTA (value1,value2, …) Count cells with content =MAX (number1, number2, …) Maximum value =MIN (number1, number2, …) Minimum value

### 4. Auditing Formulas

 Ctrl+[ Select direct precedents Ctrl+Shift+[ Select all precedents Ctrl+] Select direct dependents Ctrl+Shift+] Select all dependents Alt+M+P Formulas - Trace Precedents Alt+M+D Formulas - Trace Dependents Alt+M+AA Formulas - Remove all Arrow Traces

### 5. Shortcuts for Force Calculation

 F9 Calculate all worksheets in all open workbooks Shift+F9 Calculate the active worksheet Ctrl+Alt+F9 Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation Ctrl+Alt+Shift+F9 Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated

Created by max on 1/14/2021. Last updated by admin on 1/14/2021

## 0 Comments for '(Excel) - Excel Formula Shortcuts'   [post comment in main thread]

Email me replies