Microsoft Excel - Advice Please (#3)

[SteveStrummerUK]

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

In this example:

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 ☺️

[Michael Vogel]

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

[craigb]

[Canopus]

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 September 30, 2020 by Canopus

[craigb]

Whoops!  Sorry Steve, didn't see question #2!

[Sheens]

I no nothing about Excel

[SteveStrummerUK]

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 😊

[SteveStrummerUK]

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?