SOLVED

Formulas ($ and % signs, Decimals, Cell references)

%3CLINGO-SUB%20id%3D%22lingo-sub-2682981%22%20slang%3D%22en-US%22%3EFormulas%20(%24%20and%20%25%20signs%2C%20Decimals%2C%20Cell%20references)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2682981%22%20slang%3D%22en-US%22%3E%3CP%3E%5BB%5DJust%20a%20few%20basic%20issues%20when%20I%20use%20formulas%20in%20Excel%3A%5B%2FB%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20A%20%24%20sign%20sometimes%20appears%20in%20the%20cell%20even%20though%20the%20cells%20in%20the%20formula%20do%20not%20have%20%24%20signs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20How%20can%20you%20add%20a%20%25%20sign%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20How%20can%20you%20get%20rid%20of%20decimals%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BU%5DObviously%20you%20cannot%20just%20add%20or%20delete%20the%20above%20as%20cell%20results%20are%20automatic%20with%20formulas.%5B%2FU%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E4.%20I%20am%20mostly%20using%20relative%20cell%20references%20(as%20you%20probably%20know%2C%20formulas%20adjust%20as%20they%20are%20copied)%2C%20but%20want%20to%20copy%20a%20group%20of%20formulas%20and%20not%20have%20them%20adjust%20(absolute%20cell%20references).%20I%20looked%20this%20up%20and%20it%20said%20to%20select%20the%20reference%20you%20want%20to%20change%20in%20the%20formula%20bar%20and%20press%20F4%2C%20but%20I%20could%20not%20see%20where%20you%20select%20the%20reference.%20How%20do%20you%20do%20this%20or%20is%20there%20another%20way%20to%20do%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2682981%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2683034%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20(%24%20and%20%25%20signs%2C%20Decimals%2C%20Cell%20references)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2683034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137133%22%20target%3D%22_blank%22%3E%40peterholt110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E1%2C2%2C3%3A%3C%2FSTRONG%3E%20There%20are%20two%20ways%20to%20easily%20adjust%20number%20formats.%20The%20first%20is%20on%20the%20%22Home%22%20tool%20bar%2C%20the%20section%20that%20looks%20like%20this%20(except%20a%20lot%20smaller)%2C%20toward%20the%20center%20of%20the%20screen.%20The%20icons%20are%20pretty%20self-explanatory%2C%20and%20by%20clicking%20on%20the%20box%20showing%20%22General%22%20you%20get%20a%20more%20extensive%20set%20of%20choices.%20Change%20the%20number%20of%20decimal%20places%20by%20those%20two%20marked%20with%20an%20arrow%20in%20the%20image%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1629890764573.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305617iC6EED1B9EFE98163%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1629890764573.png%22%20alt%3D%22mathetes_0-1629890764573.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20other%20place%20you%20can%20accomplish%20the%20same%20is%20in%20the%20main%20menu%20up%20at%20the%20top%20of%20your%20screen%2C%20Format...Cells...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_2-1629891002931.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305620iD826B4C7B30B944D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_2-1629891002931.png%22%20alt%3D%22mathetes_2-1629891002931.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ewhich%20takes%20you%20to%20this%20dialog%20box%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1629890943448.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305618i6FAEF13A52F081DA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1629890943448.png%22%20alt%3D%22mathetes_1-1629890943448.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20You%20select%20a%20reference%20in%20a%20formula%20by%20just%20placing%20your%20cursor%20on%20it.%20Then%20pressing%20F4%20will%20cycle%20through%20the%20options%2C%20the%20different%20degrees%20of%20absolute%3A%3C%2FP%3E%3CUL%3E%3CLI%3EAbsolute%20in%20row%20and%20column%20%3CSTRONG%3E%24A%241%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EAbsolute%20in%20row%20but%20relative%20in%20column%20%3CSTRONG%3EA%241%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EAbsolute%20in%20column%20but%20relative%20in%20row%20%3CSTRONG%3E%24A1%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3Eand%20fully%20relative%20%3CSTRONG%3EA1%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

[B]Just a few basic issues when I use formulas in Excel:[/B]

 

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?

 

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

 


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?

1 Reply
best response confirmed by peterholt110 (Visitor)
Solution

@peterholt110 

 

1,2,3: There are two ways to easily adjust number formats. The first is on the "Home" tool bar, the section that looks like this (except a lot smaller), toward the center of the screen. The icons are pretty self-explanatory, and by clicking on the box showing "General" you get a more extensive set of choices. Change the number of decimal places by those two marked with an arrow in the image below.

mathetes_0-1629890764573.png

The other place you can accomplish the same is in the main menu up at the top of your screen, Format...Cells...

mathetes_2-1629891002931.png

which takes you to this dialog box

mathetes_1-1629890943448.png

 

4. You select a reference in a formula by just placing your cursor on it. Then pressing F4 will cycle through the options, the different degrees of absolute:

  • Absolute in row and column $A$1
  • Absolute in row but relative in column A$1
  • Absolute in column but relative in row $A1
  • and fully relative A1