OmniDocHow to lock cells with Excel formulas: Detailed explanation of $ symbol fixed reference
Explain clearly the role of the $ symbol in Excel formulas, explain how to use all lock and mixed reference to fix a cell, so that the value does not change when copying or pulling down the formula, and provide the F4 shortcut key and the verification method after completion.
Key points of this article
- The $ in the formula is added before the column label or row number, which means that the part will remain unchanged when copied. For example, $C$3 will fix the columns and rows at the same time.- Write $C3 when you only want to fix the columns, and write C$3 when you only want to fix the rows. This is suitable for one-way locking scenarios such as multiplication tables or unified multiplication coefficients in the same column.- Press F4 after selecting a reference to cycle between four reference forms, which is less likely to be missed than manually entering $ one by one.
- After writing the formula, check the results of the drop-down lines and the references in the edit bar to confirm that the fixed parts have not drifted.
I wrote the formula in Excel and pulled it down, but the result was messy: the unit price went to other cells, and the amounts were all 0 or wrong values. The problem is usually that the cell referenced by the formula is not locked. Locking cells relies on the $ symbol in the formula. This is also a confusion that many people often encounter when searching for "how to lock cells with excel formulas."
This article is written for people who frequently make quotations, payroll, inventory or performance statistics. We only talk about the $ symbol itself: what it means, how to choose among the three citation forms, how to use F4 to quickly add it, and how to check after writing. Contents such as batch filling and batch modification of formulas will not be expanded here.
After reading, you should be able to judge where $ should be added in a formula, and confirm that the results of each row and column after pull-down or horizontal pull-down point to the correct data.
How to lock cells with Excel formula? One sentence conclusion
Let’s get straight to the conclusion: add $ before the column number and row number to be fixed. If both columns and rows are fixed, write it as $C$3; if only the columns are fixed, write $C3; if only the rows are fixed, write C$3. The fixed part does not need to remain the same, it will change in position when copied.
| your situation | Recommended practices | reason |
|---|---|---|
| The entire formula points to the same value, such as tax rate, unit price, exchange rate | Lock all, such as $C$3 | No matter where you copy to, it always points to C3 |
| The rows should be changed and the columns should be fixed, just like the data in the same column are multiplied by the same coefficient. | Mixed references to locked columns, such as $C4 | The column is locked and the row number automatically changes when pulling down. |
| The columns should be changed and the rows should be fixed. For example, when copying horizontally, parameters in the same row should be referenced. | Mixed references to lock rows, such as C$3 | The row is locked, and the column number automatically changes when pulling horizontally. |
| Formulas only refer to adjacent cells in the same row, such as B4 | Relative reference, such as B4 | After copying, it will automatically correspond according to the position, no need to add $ |
| The referenced area range is fixed, such as B4 to B9 | Fixed area, such as $B$4:$B$9 | Area does not slide down when copying |
Before you start: Confirm these points first
- Confirm which value is fixed: First look at the structure of the formula and find out which value should be used in each row, such as tax rate or unit price. Only this type of value needs to be added with $; do not add data that changes line by line such as quantity and name that are different in each line.2. Confirm the direction of copying: Pulling down means copying vertically, pulling horizontally means copying horizontally. Both will cause different parts to change. Only add $ in the direction you want to fix. Missing or adding too much will get wrong results.
- Confirm the name of cross-sheet reference: When referencing other worksheets, such as Sheet2!$C$3, you must also add $, but you must also confirm that the name of the worksheet is written correctly. Fixed a wrong position and the result is still wrong.
- Confirm that the data is in a calculable table: If the original data is PDF, you can use the PDF editor of the OmniDoc desktop client (download.omnidoc.top) to export it to Excel, and then write formulas in the table. The scanned PDF must be OCRed first, and the recognized numbers must be checked one by one. There may be typos or misalignments in both OCR and export.
Steps to lock cells with $ in Excel formulas
The following example is: the unit price is placed in C3, column B is the quantity, and column D is the amount.
- Enter =B4C3 in cell D4 without adding $ first to confirm that this is the desired calculation relationship.2. Place the cursor on C3 in the formula, or select it with the mouse.3. Press F4, C3 will change to $C$3, and the formula will change to =B4$C$3, indicating that both rows and columns are fixed.4. Press F4 again to switch modes: $C3 only locks columns, C$3 only locks rows, and returning to C3 is a relative reference. On Mac keyboards you usually need to use the fn key, or use Command+T instead.5. Press Enter to confirm, and the result of D4 will be displayed.6. Double-click the fill handle in the lower right corner of D4, or drag down to copy the formula to other rows.7. Click D5 and look at the edit bar. The formula should be =B5$C$3, C3 remains unchanged, and B4 becomes B5.8. If you want to make a multiplication table, enter =$A4B$3 in B4, and then copy right and down: $A locks the column, B$3 locks the row, and each result will match the correct row header and column header.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether fixed cells remain unchanged | Click on a few lines of formulas to see if the references in the edit bar point to the same location. | Fill in the missing places with $, and remove the extra $. |
| Is the result after the pull-down reasonable? | See if the values change with the data, rather than being all the same or all 0 | If all are 0, it probably refers to a blank cell. Check the reference location. |
| Whether errors such as #REF!, #VALUE! etc. occur | Find the cell where the error is located and view its formula | Check whether the referenced cell has been deleted or has text mixed in it |
| Worksheet name referenced across tables | Check whether the worksheet name in the formula is consistent with the actual one | Correct the name and confirm that the referenced cell actually has data |
| Is it consistent with the hand calculation result? | Pick two or three lines at random and use a calculator to calculate them | In case of inconsistency, check the formula structure item by item, especially the position of $ |
When rows are inserted or deleted, the $ reference moves with the data, which is what locking does. However, if a row is inserted outside the referenced area, the area range will not be automatically expanded and needs to be checked again. If the data comes from OCR or format conversion, please check the important numbers again with the original file.
📊 Core comparison
| reference type | Writing example | Changes after copying | Typical scenario |
|---|---|---|---|
| relative reference | B4 | Both rows and columns change with position | Each row corresponds to its own data |
| All locked | $C$3 | Rows and columns remain unchanged | Unique fixed values such as tax rate and unit price |
| Mixed references (locked columns) | $C4 | Only rows change with position | Data in the same column is multiplied by a coefficient |
| Mixed references (row lock) | C$3 | Only columns change with position | Parameters referencing the same row when copying horizontally |
❓ Frequently Asked Questions (FAQ)
1. What is the use of $ in excel formula?
$ is used to fix the row or column referenced in the formula. The fixed part will not move when the formula is copied. Add $ before the column label to lock the column, and add it before the row number to lock the row. Adding $ in both places will completely fix it.
2. What is the fixed symbol for excel functions?
The symbol used for fixed references is $, and there is no other symbol that can replace it. It only affects the way the formula is referenced and does not change the value displayed in the cell.
3. When there are many formulas, can you add $ in batches at once?
It is recommended to write the formula in the first row correctly first, and then use the fill function to copy it downwards, instead of manually adding $ cell by cell. Changing a line and filling it in as a whole is usually less error-prone than changing it one by one.
4. After adding $, why is the result still wrong?
There are three common reasons: the wrong object is fixed, for example, C3 should be locked but it is locked to B3; the lock is in the wrong direction, the row needs to be locked but only the column is locked; and the referenced worksheet or range range is written incorrectly. Click on the formulas one by one and compare them with the checklist above, and you can usually find the problem.
📝 Summary
Adding $ before the column label or row number that you don't want to change will fix the specified cell when the formula is copied. After writing, use F4 to switch the reference form, and then look at the results of a few lines of drop-down, which can basically avoid most reference misplacements.
