Jump to: Basic Formula Usage, Common Text Functions, Common Calculation Functions, Auditing Formulas, Shortcuts for Force Calculation

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 |

=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) |

=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 |

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 |

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

Advertising for Consideration:

Ads for Consideration:

Please kindly review and accept Terms of Use and Cookie & Policy Policy

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