How to keep formula cell reference constant in Excel?
When you use relative cell reference in a formula, it will automatically adjust when you use the Fill Handle to apply it to a different location or copy and paste it to another cells. See below screenshot shows.
In many cases, you would like the cell reference staying constant rather than adjusting automatically. In this tutorial, we will show you how to keep formula cell reference constant in Excel.
Method B: Keep formula cell reference constant with Kutools for Excel
Easily Keep Formula Cell Reference Constant With Several Clicks:
The Kutools for Excel's Convert Refers utility helps you easily convert all formula references in bulk in a selected range or multiple ranges to a specific type of formula reference. Such as convert relative to absolute, absolute to relative and so on. Download the full feature 30-day free trail of Kutools for Excel now!
To keep cell reference constant in formula, you just need to add the $ symbol to the cell reference with pressing the F4 key. Please do as follows.
1. Select the cell with the formula you want to make it constant.
2. In the Formula Bar, put the cursor in the cell which you want to make it constant, then press the F4 key.
In this case, I don’t want the cell reference A1 to be adjusted with the formula moving, so I put the cursor on A1 in the formula, and then press F4. See screenshot:
Then you can see the cell reference A1 is constant in the formula.
The Convert Refers feature of Kutools for Excel can help you easily making cell reference constant in Excel.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
1. After installing Kutools for Excel, please click Kutools > Convert Refers to activate the Convert Formula References feature.
2. When the Convert Formula References dialog box pops up, select the cell or the range you want to make the references constant, then select the To absolute option, and finally click the OK button.
Then all the relative cell references in selected range are changed to constant references immediately.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 years agoFinancial ratios are one of the most common tools of managerial decision making. A ratio is a comparison of one number to another—mathematically, a simple division problem. Financial ratios involve the comparison of various figures from the financial statements in order to gain information about a company's performance. It is the interpretation, rather than the calculation, that makes financial ratios a useful tool for business managers. Ratios may serve as indicators, clues, or red flags regarding noteworthy relationships between variables used to measure the firm's performance in terms of profitability, asset utilization, liquidity, leverage, or market valuation.