SteveStrummerUK Posted September 30, 2020 Share Posted September 30, 2020 Two quick queries if I may for the Excel literate among you. In this example: 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? 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 More sharing options...
Michael Vogel Posted September 30, 2020 Share Posted September 30, 2020 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 1 1 Link to comment Share on other sites More sharing options...
craigb Posted September 30, 2020 Share Posted September 30, 2020 Link to comment Share on other sites More sharing options...
Canopus Posted September 30, 2020 Share Posted September 30, 2020 (edited) 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 1 Link to comment Share on other sites More sharing options...
craigb Posted September 30, 2020 Share Posted September 30, 2020 Whoops! Sorry Steve, didn't see question #2! 1 Link to comment Share on other sites More sharing options...
Sheens Posted September 30, 2020 Share Posted September 30, 2020 I no nothing about Excel Link to comment Share on other sites More sharing options...
SteveStrummerUK Posted October 3, 2020 Author Share Posted October 3, 2020 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 More sharing options...
SteveStrummerUK Posted October 3, 2020 Author Share Posted October 3, 2020 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 More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now