Keyboard Shortcuts for Formulas Tutorial
Free Online Microsoft Excel Tutorial
Excel 2010 Keyboard Shortcuts
* Shortcuts for Autosum
Part of working smarter and not harder in Excel means becoming a pro at using keyboard shortcuts to to enter formulas.
Shortcuts for Copying Formulas Down
Shortcuts for Insert Functions, Formulas and Arguments
Shortcuts for Array Formulas
Shortcuts for Calculate the Worksheet
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Keyboard Shortcuts for Formulas Test
Autosum is possibly the best known and most frequently used function in Excel. It allows you to quickly add up a column or row of consecutive values.
* Excel Keyboard Shortcuts for Autosum
The Autosum shortcut allows you to do this without lifting your hands off the keyboard.
1. Move your cursor to the cell in which you wish the total to appear. The selected cell needs to be either at the end of the row or the end of the column you wish to sum. See screenshot below. The yellow circled cells represent which cells you will be able to use the Autosum function in to sum up the columns in this example.
2. Press Alt+= to Autosum the columns.
3. Press Enter to complete the formula and display the results.
* Excel Keyboard Shortcuts for Copying Formulas Down
Should you have a formula that you need to copy down a column, you can achieve this by copying and pasting the formula or using the copy handle to drag the formula down.
However, there is a quicker way of doing this using a shortcut combination.
1. Select all the cells you wish to insert the formula into. For example, in the screenshot below, we wish to multiply column C with the values contained in Column D. We will select all the blank cells in Column E, where we want the formula to calculate:
2. Type the formula you require. In our example it would be =C2*D2 and press Ctrl+Enter.
3. This will automatically copy the formula into all the selected cells and display the calculation values.
* Excel Keyboard Shortcuts for Insert Functions, Formulas and Arguments
Working with formulas may be the most rewarding part of using Excel. There is nothing quite like the feeling when you manage to automate data analysis or number calculations and a formula or function that works exactly as expected. Excel provides assistance in the form of the Insert Function dialogue box, Function Arguments dialogue box and by displaying formula arguments in a cell.
1. To launch the Insert Function dialogue box, select the cell in which you wish to insert the formula.
2. Press Shift+F3 to launch the dialogue box (see screenshot below):
3. Use the search box to find a function that fulfils your requirements or select the function from the list.
The Functions Arguments dialogue box provides you with guidance as to all the arguments needed for a formula to work as expected and an explanation of each argument.
1. To launch the Functions Arguments dialogue box, select the cell in which you wish to insert the formula or function.
2. Type the equal symbol and the name of the formula or function. For example, =IF( and then press Ctrl+a to launch the Functions Arguments dialogue box (see screenshot below).
3. Use the boxes and explanations in this dialogue box to complete your formula.
If you need to quickly view the different arguments required to complete a formula or if you wish to insert the formula in a cell but not have it calculate yet (you may still be working on your data), you can use the Insert Arguments function.
1. Select the cell in which you wish the formula to appear.
2. Type the formula name and directly after typing the name press Ctrl+Shift+a to insert the function arguments into the cell and press Enter.
3. The cell will most likely display the Name? error when not selected, but the function arguments will appear in the formula bar. The arguments will be visible when selecting the cell.
* Excel Keyboard Shortcuts for Array Formulas
Array formulas are the superheros of Excel power. They allow you to use one formula to calculate sets of data or arrays.
For example, to calculate the Total Sales value for the worksheet in the screenshot below, instead of individually multiplying each Unit Price with the Number Sold and then totalling them up, we could use an array formula.
1. To insert an array formula, select the cell in which you wish the result to appear. For example, in the screenshot example above, this will be cell B:9.
2. Type the formula but instead of pressing enter to accept the formula, press Ctrl+Shift+Enter. In the example above we want to calculate the total for each product by multiplying the number sold with the unit price. The sum total of all the units sold must appear in the Total Sales box. Study the formula bar in the screenshot below of an array formula example used to calculate this value:
However, if you insert this formula on its own without pressing Ctrl+Shift+Enter to make it an array formula, the formula will only calculate the first value in the array. The curly brackets around the formula tells Excel to treat this as an array formula.
* Excel Keyboard Shortcuts to Calculate The Worksheet/ Workbook
If you have multiple formulas in a worksheet and you have made amendments to data or dependent formulas, you may want to update the worksheet to ensure the formulas are displaying the updated result. This becomes even more important if you have switched off the Workbook Calculation function to avoid the workbook freezing or crashing from using too much of your computer’s memory to continually recalculate the cells.
1. To calculate all worksheets in all workbooks that you have open at any given time, press F9.
2. To calculate only the active worksheet (the one in which your cursor is positioned), press Shift+F9.
* Excel Keyboard Shortcuts to Show/ Hide Formulas
When you have inherited a worksheet or workbook created by someone else and you are not quite sure about the formulas they have used, this function may be a lifesaver. It is also useful for when you want a quick overview of your worksheet or if you want to plagiarize formulas created by other users.
1. To display all the formulas in the worksheet instead of their values, press Ctrl+` (grave symbol).
2. To display values instead of formulas again, press Ctrl+` (grave symbol) again.
* Excel Keyboard Shortcuts for Absolute References
You make a cell reference absolute when you absolutely (excuse the pun!) do not want the reference to which the formula in a cell refers to change when adjustments are made to the worksheet, for example rows or columns that are inserted, etc.
When you make a cell reference absolute the formula will follow that cell’s position regardless of whether you insert more rows above it or columns adjacent to it.
1. To make a cell reference absolute you need to prefix the column reference and row reference contained in a cell formula with a $ symbol. Instead of typing this in, select the reference you wish to make absolute or position your cursor within that reference.
2. Press Ctrl+F4 on your keyboard.
For example: A cell reference such as =A1 when made absolute will display as =$A$1 in the formula bar.