Don’t Ignore the Power of F4 in Microsoft Excel

If you're utilizing Microsoft Excel connected a Windows PC and bask utilizing keyboard shortcuts to amended your productivity, you should decidedly larn the galore ways successful which F4 tin prevention you tons of time.

Toggling Between Reference Types successful Formulas

There are 3 types of references successful Excel—relative references, implicit references, and mixed references—and you tin usage the F4 cardinal to toggle betwixt these erstwhile generating a formula.

Let's accidental you're moving retired the outgo of 7 products successful your store, and you request to adhd the mandatory 20% taxation to those costs.

To bash this, successful compartment E2, you mightiness type:

=SUM(D2+(D2*A2))

and property Enter to adhd the 20% taxation to the baseline cost.

However, if you transcript this look to the remaining cells successful file E, arsenic you determination down each row, the references successful the look volition besides determination down a row—for example, the look successful compartment E3 volition notation compartment A3, E4 volition notation A4, and truthful on.

This is because, by default, Excel references are relative, meaning they usage the aforesaid comparative positioning betwixt wherever you're typing the look and the compartment they notation to if you transcript the look to a antithetic location.

To hole this, you request to marque the notation to compartment A2 implicit utilizing the F4 key. In different words, adjacent erstwhile you transcript the Cost+Tax look to different cells successful file D, you tin usage F4 to marque definite the notation to compartment A2 doesn't change.

First, prime compartment E2, and property F2 to edit the formula. Then, usage the Arrow keys to determination the cursor straight before, successful the halfway of, oregon straight aft the compartment notation you privation to hole (in this case, it's the notation to compartment A2).

Now, property F4 erstwhile to toggle the notation benignant to an implicit reference. Instantly, you volition announcement a dollar motion ($) placed earlier the file and enactment references, turning "A2" into "$A$2." This means that some the file and the enactment notation successful this lawsuit are locked, truthful erstwhile you transcript the look to different cells, the A2 notation volition stay intact.

After pressing Enter, you're acceptable to duplicate the look containing the implicit notation to the remaining rows. Press the Up Arrow cardinal to caput backmost to compartment E2. Then, property Ctrl+Shift+End to prime that compartment and the remaining cells, past Ctrl+D to autofill the formula, harmless successful the cognition that the notation to compartment A2 volition stay constant.

The amusive of utilizing F4 to toggle betwixt references doesn't halt there. In this example, you request to enactment retired however overmuch your employees gain each twelvemonth by adding their yearly salaries to their fixed bonuses. In different words, you privation to adhd the worth successful B2 to the worth successful D2 for the 2023 fig for worker A, and past you privation to adhd the worth successful C2 to the worth successful D2 for their 2024 figure.

To make this calculation, you request the notation to the values successful file D (the fixed bonus) to stay absolute, portion references to the rows (each employee) and columns B and C (each year's totals) request to beryllium relative. This operation of implicit and comparative references is called a mixed reference, and you tin make this script utilizing the F4 key.

To execute this, successful compartment F2 type:

=SUM(B2+D2

Now, with your cursor inactive flashing aft the notation to compartment D2, property F4 3 times truthful that lone the file notation (D) has the dollar motion placed earlier it.

After closing the parentheses and pressing Enter, spell backmost to compartment F2, and property Ctrl+C to transcript the formula. Then, property the Right Arrow to determination to compartment G2, and property Ctrl+V to paste the formula. Notice however the wage file notation switches comparative to the compartment presumption (B2 has changed to C2), portion the fixed bonus file notation ($D2) remains the same.

Next, prime cells F2 and G2 utilizing Shift+Arrows, property Ctrl+Shift+End to besides prime the remaining cells, past property Ctrl+D to duplicate the formula, harmless successful the cognition that the enactment references are relative, since they aren't prefixed with the $ symbol.

So, successful summary, erstwhile typing a formula, you tin property F4 repeatedly to rhythm done absolute, mixed, and implicit references.

Repeating the Last Action

When you're not typing a formula, F4 has a antithetic usage altogether that tin prevention you a large woody of time.

In this example, you've been asked to insert a blank file betwixt each existing file of figures.

The archetypal measurement to achieving this utilizing lone your keyboard is to determination to compartment B1, property the Menu cardinal (also known arsenic the Application key), past property one and Enter to unfastened the Insert dialog box.

Next, property C and Enter to insert a caller file to the near of file B.

Now that you've added an bare file betwixt what were antecedently columns A and B, you could repeat this process for each caller column, though the keyboard shortcut series is long-winded. This is wherever the powerfulness of F4 truly comes successful handy.

First, property the Right Arrow doubly to determination from compartment B1 to compartment D1.

Then, property F4 to repetition the past enactment you conscionable performed (insert a caller file to the left).

Then, usage the aforesaid Right Arrow x 2 > F4 series to repeatedly and rapidly adhd caller columns betwixt each existing file of information successful your spreadsheet.

Next, let's ideate you've been told that the blank columns you conscionable added request to beryllium 1 portion wide. To bash this utilizing your keyboard, first, usage the Left Arrow to caput backmost to compartment B1. Then, property Alt > O > C > W > 1 > Enter to alteration the file width accordingly.

Again, this keyboard shortcut series is hard to retrieve and execute quickly. However, since F4 lets you repetition the past enactment you performed, simply usage the Right Arrow to caput to file D, property F4, past determination to file F, property F4, and truthful on. It took maine little than 5 seconds to use this file width alteration to the archetypal 14 blank columns I created earlier.

You tin usage F4 to repetition assorted different erstwhile actions successful Excel, similar changing a cell's colour oregon font formatting, adding oregon removing borders, oregon adjacent duplicating the formatting of a signifier oregon bars successful a chart.

Performing and Repeating a Find Query

Many radical cognize that pressing Ctrl+F opens the Find tab of the Find And Replace dialog box.

Then, aft typing the applicable hunt criteria successful the Find What field, you tin property Enter repeatedly to find each compartment that matches those criteria.

The contented with this method is that if you privation to marque manual changes to your spreadsheet betwixt each hunt effect utilizing lone your keyboard, you person to property Esc to adjacent the Find And Replace dialog box, marque your changes, past property Ctrl+F to motorboat it again.

Instead, erstwhile you've typed the hunt query, property Esc to adjacent the Find And Replace dialog box, and past property Shift+F4 to execute the hunt without relaunching the Find And Replace Dialog box. Each clip you property Shift+F4 subsequently, the hunt continues arsenic if the dialog container were inactive open, adjacent if you marque important changes to the spreadsheets' content.

The quality of utilizing this keyboard shortcut is that Excel remembers the past hunt query until you adjacent the workbook.

Closing the Active Excel Workbook oregon Window

The last uses of the F4 cardinal impact closing the Excel workbook oregon window.

When you property Ctrl+F4, the progressive workbook volition either adjacent (if you person AutoSave activated) oregon motorboat the Save As dialog container (if your workbook isn't saved).

Notice that portion this closes the progressive workbook, the Excel model remains open, acceptable for you to unfastened a caller (Ctrl+N) oregon saved (Ctrl+O) worksheet. If you privation to adjacent the progressive Excel model altogether, property Alt+F4.

---

Using F4 and different keyboard shortcuts isn't the lone mode to save clip erstwhile moving successful Microsoft Excel. For example, you could personalize the Quick Access Toolbar truthful that your most-used features, similar spell check, paste special, conditional formatting, and frost panes, are accessible astatine a azygous click of your mouse.