How to keep formula cell reference constant in Excel?
When you use a cell reference in a formula, it will automatically adjust when you move 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.
Click Kutools > Convert Refers. The Kutools for Excel's Convert Formula References utility helps you easily convert all formula references in bulk in selected range such as convert all relative to absolute at once in Excel.Download the full feature 60-day free trail of Kutools for Excel now!
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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 60 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.
Note: With this To absolute feature, all the cell references will be changed to constant references.
Excel Productivity Tools
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.