I have a formula in Excel that needs to be run on several rows of a column based on the numbers in that row divided by one constant. When I copy that formula and apply it to every cell in the range, all of the cell numbers increment with the row, including the constant. So:

```B1=127
C4='=IF(B4<>"",B4/B1,"")'
```

If I copy cell C4 and paste it down column C, the formula becomes

```=IF(B5<>"",B5/B2,"")
=IF(B6<>"",B6/B3,"")
etc.
```

when what I need it to be is

```=IF(B5<>"",B5/B1,"")
=IF(B6<>"",B6/B1,"")
etc.
```

Is there a simple way to prevent the expression from incrementing? Mar 11 in Database 41 views

## 1 answer to this question.

In Excel, you can use a feature called 'locked reference,' and you can lock a range with \$ symbols. You'd use the following as an example:

=IF(B4<>"",B4/B\$1,"")
This freezes the 1 in B1 so that it doesn't change when you copy it to the next row.

When you copy \$B\$1 down a row or across a column, the range does not change. answered Mar 15 by
• 13,560 points

## Remove time from date field in Excel formula

Use the Find And Replace function to ...READ MORE

## What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

## How to enter a series of numbers automatically in Excel

Excel, unlike other Microsoft Office programmes, does ...READ MORE

## How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE