Jump to content

Microsoft Excel - Advice Please (#3)


SteveStrummerUK

Recommended Posts

Two quick queries if I may for the Excel literate among you.

In this example:

XP0lp42.jpg

  1. I notice in some of my Excel documents, there seems to be an 'auto-copy' feature (which works a bit like predictive text). In the example above, I just need to type the 'T' in the cell arrowed and Excel automatically suggests 'Texaco', as per the cell above.
    Is there any way to enable/disable this feature, either globally or on a per-document basis?
     
  2. I'm in 'Accountancy' mode for the money values to be listed in the TOTAL column; is it possible to have Excel display zero pounds/pence as £0.00, or is this only possible in 'Currency' mode?

 

Many thanks in advance for any help/advice ☺️

Link to comment
Share on other sites

8 hours ago, SteveStrummerUK said:

2. I'm in 'Accountancy' mode for the money values to be listed in the TOTAL column; is it possible to have Excel display zero pounds/pence as £0.00, or is this only possible in 'Currency' mode?

Select the range of cells you want to format, right-click and select Format Cells. Then from the dialogue select the Number tab. In the left-hand list box change Category to Custom, and in the Type field enter £* 0.00;£* (0.00) with a space between the asterisk and the first zero or left parenthesis. The asterisk followed by a space results in the Pound being left-aligned, so skip the asterisk(s) if that’s not what you want. Finally, I have assumed that you want negative numbers to be displayed in parentheses, accounting style. If that’s not what you want, skip everything after and including the semi-colon.

Edit: I just realized that you perhaps wanted negative numbers in red, just like in your question. If so, enter £* 0.00;[Red]£* (0.00) or £* 0.00;[Red]£* -0.00 depending on your preferences.

Disclaimer: 1) I don’t have an English version of Excel, 2) You may have something more recent but I’m still running Excel 2010, and 3) I normally use a decimal comma instead of  a decimal point. Still, I think my localized instructions should work. Otherwise, I'm sure someone will correct me. :)

Edited by Canopus
  • Thanks 1
Link to comment
Share on other sites

On 9/30/2020 at 8:11 PM, Canopus said:

Select the range of cells you want to format, right-click and select Format Cells. Then from the dialogue select the Number tab. In the left-hand list box change Category to Custom, and in the Type field enter £* 0.00;£* (0.00) with a space between the asterisk and the first zero or left parenthesis. The asterisk followed by a space results in the Pound being left-aligned, so skip the asterisk(s) if that’s not what you want. Finally, I have assumed that you want negative numbers to be displayed in parentheses, accounting style. If that’s not what you want, skip everything after and including the semi-colon.

Edit: I just realized that you perhaps wanted negative numbers in red, just like in your question. If so, enter £* 0.00;[Red]£* (0.00) or £* 0.00;[Red]£* -0.00 depending on your preferences.

Disclaimer: 1) I don’t have an English version of Excel, 2) You may have something more recent but I’m still running Excel 2010, and 3) I normally use a decimal comma instead of  a decimal point. Still, I think my localized instructions should work. Otherwise, I'm sure someone will correct me. :)

Absolutely brilliant - does exactly what I wanted!

Thanks Canopus ?

Link to comment
Share on other sites

On 9/30/2020 at 12:54 PM, Michael Vogel said:

Hey Straummy - give this a try

 

Click File > Options.

Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off.

HTH

Thanks for the clarification Mike - I was pretty sure that was the answer but need to check (works like a dream on my Office 365 desktop version).

To be honest, I was asking for a friend who's recently upgraded from Excel 2010 to v2017-ish. He's tried this and it still doesn't work in his version. Could there be something else he needs to do?

 

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...