Click the Formulas tab In the Formula Auditing group click on Show Formulas. Then the selected cell reference is locked. Select the cell with the formula you want to make it constant.
To resolve the error select any cell in the spill range so you can see its boundaries.
Select the cells and on the left-hand side you will see one small icon click on that icon and choose the option Convert to Numbers Now we must see the result of the formula. In the Formula Bar put the cursor in the cell which you want to make it constant then press the F4 key. Click the cell that you want to insert the formula go to the Formulas tab and from the tools ribbon click Insert function Look for and click the function on the list and then click the Ok button. Go to File Options.