A must-have for Excel experts: the wonderful use of the F4 key, a secret weapon to improve efficiency!

This article will reveal the powerful functions of the F4 key in Microsoft Excel under Windows system, helping you quickly master this shortcut key to improve productivity.

1. Switching formula reference type

Reference types in Excel include relative references, absolute references, and mixed references. The F4 keys can be conveniently switched between these types, especially when creating formulas.

Suppose you need to calculate the price of seven products and add a 20% tax.

In cell E2, you may enter the following formula:

 <code>=SUM(D2 (D2*A2))</code>

After pressing Enter, the price containing 20% ??tax can be calculated.

However, if you copy this formula directly to other cells in column E, the reference in the formula will also move down accordingly - for example, cell E3 will refer to A3, E4 will refer to A4, and so on. This is because Excel uses relative references by default, and the reference position of the formula is relatively fixed relative to the position of the cell where the formula is located.

To solve this problem, you need to use the F4 key to set the reference of cell A2 to an absolute reference. This way, even if the formula is copied to another cell, the reference to A2 will not change.

First, select cell E2 and press F2 to edit the formula. Then, use the arrow keys to move the cursor before, between, or after the cell reference to be modified (here A2).

Press F4 once to switch the reference type to absolute reference. "A2" will become "$A$2", indicating that both column and row references are locked. When copying the formula to another cell, the reference to A2 will remain unchanged.

2. Repeat the last operation

Without entering formulas, the F4 key has another powerful function: repeat the last operation.

For example, you need to insert blank columns between existing data columns.

First, move to cell B1, press the menu key (also known as the application key), and press the i and Enter keys to open the Insert dialog box.

Next, press C and Enter to insert a new column to the left of column B.

Now, you have added a blank column. You can repeat this process, but the keyboard shortcut sequence is relatively verbose. At this time, the F4 key comes in handy.

First, press the right arrow key twice to move from cell B1 to cell D1.

Then, press F4 to repeat the last operation (insert a new column on the left).

Reusing the "right arrow x2 > F4" sequence allows you to quickly add new columns between existing data columns.

Suppose you need to set the width of these blank columns to 1 unit. First, use the left arrow key to return to cell B1. Then, press Alt > O > C > W > 1 > Enter to change the column width.

Similarly, this shortcut key sequence is also relatively complex. But since F4 can repeat the last operation, just use the right arrow key to move to column D, press F4, then move to column F, press F4, and so on.

3. Execute and repeat search query

Ctrl F Opens the Find and Replace dialog box's Find tab.

After entering the search criteria in the Find Content field, you can repeatedly press Enter to find the matching cell.

However, if you want to make a manual change between each search result, you have to press Esc to close the Find and Replace dialog, make the change, and press Ctrl F again to restart it.

Instead, after entering the search query, press Esc to close the Find and Replace dialog box, and then press Shift F4 to perform the search without restarting the dialog box. Each time you press Shift F4 afterwards, the search continues, even if you make a significant change to the content of the spreadsheet.

4. Close the active Excel workbook or window

Ctrl F4 closes the active workbook (if automatic save is enabled), or starts the Save As dialog box (if the workbook is not saved).

This closes the active workbook, but the Excel window remains open. To completely close the active Excel window, press Alt F4.

Mastering the F4 keys and other shortcut keys will greatly improve your Excel work efficiency! You can also customize the quick access toolbar to easily and quickly access common functions.

The above is the detailed content of Don't Ignore the Power of F4 in Microsoft Excel. For more information, please follow other related articles on the PHP Chinese website!