$ and % signs, Decimals, Cell references

Just a few basic issues when I use formulas in Excel:

1. A $ sign sometimes appears in the cell even though the cells in the formula do not have $ signs.

2. How can you add a % sign?

3. How can you get rid of decimals?

Obviously you cannot just add or delete the above as cell results are automatic with formulas.


4. I am mostly using relative cell references (as you probably know, formulas adjust as they are copied), but want to copy a group of formulas and not have them adjust (absolute cell references). I looked this up and it said to select the reference you want to change in the formula bar and press F4, but I could not see where you select the reference. How do you do this or is there another way to do it?

Originally Posted by Special-K

1. Maybe cells formatted as currency and dollar selected, e.g. I'm entering 13 in cell A1, its formatted as a currency with a dollar, result $13
2. Not enough info, example needed. Format the cell as a percentage is one way.
3. Not enough info, example needed. Assume 13.45 in cell A1, multiply by 100, in B1 = A1*100 result in B1 is B1 1345. Decimal point has been "got rid of". Or ormat the cell to have no decimal points but the number may display rounded up even though it retains the original value. Try it with 78.9
4. probably need to supply example spreadsheet with input and expected results.

Originally Posted by etaf

click in the formula bar and then onto the reference you want to change

so if you had F1:G4
and you wanted F1 and G4 to be absoluter
click between the F and 1 and press F4 , then click inbetween the G and 4 and press F4
all that does is add $

so
F1:G4
becomes
$F$1:$G$4

or you could just add the $

If you want the value to show as a number but with % at the end - format the cell to % - it will multiply the value by 100 and add a % sign

Not sure what point 1 means re - $

Thanks to both of you for your replies. I have now sorted out the basic issues. Just 1 thing came up when I was doing this: How can you format comma's (in numbers) into cells?

Originally Posted by etaf

click in the formula bar and then onto the reference you want to change

#AliGW


Is there any way to change a single or multiple formulas from relative to absolute cell reference at once? (i.e. Without adding a $ sign between each cell in the formula)

It depends Peter, the short answer is not easily. however if they are all based on a formula and copied you only need to change one of the formula and then copy it to the other cells.

I think if you have a sample sheet that is not huge, we could advise about how to change the formula. Most of these answers are very generic. Instructions are in the yellow banner

You can also type the $ where you want them in the formula, instead of using F4

We are also assuming that the values in the cells are not text strings, in which case the formating would make no difference and you would have to find and replace