Toggle Between Relative and Absolute Cell References in Excel With F4

BY STANLEY ZAROWIN

Q: My partner says there’s an F4 shortcut to creating absolute cell references in Excel formulas, but for the life of me I can’t make it work. What am I doing wrong?

 

A: Your partner is right, but while I can’t tell you what you’re doing wrong, I can tell you how to do it right.

 

When you type the cell reference in your formula, press F4. For example, in the formula =A6*B7, press F4 right after you type the 6 and the absolute reference $ will appear. Press F4 again after you type the 7—and voìla:

 

Wait, there’s more. If you continue to press F4 when your cursor is just to the right of A6, the formula will toggle between relative and absolute references through four options in this order: A6, $A$6, A$6, $A6.

 

SPONSORED REPORT

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out