In your reply in Negative Values on the 28th you stated:-
<The formatting options only apply to the cells selected at the time the
options are applied. If you want a whole row/column/worksheet to be
affected, all of that row/column/worksheet must be selected.>
But you did not specify how to do the whole worksheet. In Formatting the
dropdown menu does not cater for changes to presentation of negative values
for a worksheet, so which way can I format it?
HI Zygy,
Please check your original thread:
http://excelforum.com/showthread.php...32#post1572732
I think you know how to do this (?), but once your range is selected go to Format - Cells - Number, select Currency (in left column) and choose the format you want from the negative values area.
hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
Thank you for the answer, but I tried that and it does not work. In any case
that is supposed to change only the selected cell, it does not change the
negative values occurring in other places, therefore I must have it for a
whole sheet. Macroport stated that it is possible, but he did not say which
way!
It would be a good idea if persons making suggestions to solve a problem
ALWAYS stated how to achieve this as it is done by the professionals.
"broro183" <[email protected]> wrote in
message news:[email protected]...
>
> HI Zygy,
>
> Please check your original thread:
>
> http://excelforum.com/showthread.php...32#post1572732
>
> I think you know how to do this (?), but once your range is selected go
> to Format - Cells - Number, select Currency (in left column) and choose
> the format you want from the negative values area.
>
> hth
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile:
> http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=537425
>
Hi Zygy,
To format the whole sheet:
.. click on the grey rectangle in the upper left corner (ie just above row 1
and to the left of column A)
.. use Format|Cells|Number and choose the format you want.
Note that the above applies to a single worksheet. If you want to apply the
formatting to all worksheets in a workbook:
.. select the first worksheet in the workbook,
.. Click on the grey rectangle in the upper left corner
.. click on the tab for the last worksheet in the workbook
.. use Format|Cells|Number and choose the format you want.
Cheers
PS: It's usually best to continue a line of discussion in the original
thread.
"Zygy" <[email protected]> wrote in message
news:[email protected]...
> Thank you for the answer, but I tried that and it does not work. In any
case
> that is supposed to change only the selected cell, it does not change the
> negative values occurring in other places, therefore I must have it for a
> whole sheet. Macroport stated that it is possible, but he did not say
which
> way!
>
> It would be a good idea if persons making suggestions to solve a problem
> ALWAYS stated how to achieve this as it is done by the professionals.
>
> "broro183" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > HI Zygy,
> >
> > Please check your original thread:
> >
> > http://excelforum.com/showthread.php...32#post1572732
> >
> > I think you know how to do this (?), but once your range is selected go
> > to Format - Cells - Number, select Currency (in left column) and choose
> > the format you want from the negative values area.
> >
> > hth
> > Rob Brockett
> > NZ
> > Always learning & the best way to learn is to experience...
> >
> >
> > --
> > broro183
> > ------------------------------------------------------------------------
> > broro183's Profile:
> > http://www.excelforum.com/member.php...o&userid=30068
> > View this thread:
http://www.excelforum.com/showthread...hreadid=537425
> >
>
>
Many thanks for the reply. I had a warning from Rob Brocket that if I do
that there will be "quite an increase in your file size", so I tried to
format only a used column as he suggested. When I followed his instruction
as in:-http://excelforum.com/showthread.php?p=1572732#post1572732 my Tool
Bar changed so now I am missing £ icon, decimal point etc and all the £
disappeared in the whole column, although now the negative balances appear
automatically in read, but without the £ in front of the figures!
Where did I go wrong and how can I retain the £ both in the toolbar and in
all the figures in a formatted column?
"macropod" <[email protected]> wrote in message
news:[email protected]...
> Hi Zygy,
>
> To format the whole sheet:
> . click on the grey rectangle in the upper left corner (ie just above row
> 1
> and to the left of column A)
> . use Format|Cells|Number and choose the format you want.
>
> Note that the above applies to a single worksheet. If you want to apply
> the
> formatting to all worksheets in a workbook:
> . select the first worksheet in the workbook,
> . Click on the grey rectangle in the upper left corner
> . click on the tab for the last worksheet in the workbook
> . use Format|Cells|Number and choose the format you want.
>
> Cheers
> PS: It's usually best to continue a line of discussion in the original
> thread.
>
>
> "Zygy" <[email protected]> wrote in message
> news:[email protected]...
>> Thank you for the answer, but I tried that and it does not work. In any
> case
>> that is supposed to change only the selected cell, it does not change the
>> negative values occurring in other places, therefore I must have it for a
>> whole sheet. Macroport stated that it is possible, but he did not say
> which
>> way!
>>
>> It would be a good idea if persons making suggestions to solve a problem
>> ALWAYS stated how to achieve this as it is done by the professionals.
>>
>> "broro183" <[email protected]> wrote
> in
>> message news:[email protected]...
>> >
>> > HI Zygy,
>> >
>> > Please check your original thread:
>> >
>> > http://excelforum.com/showthread.php...32#post1572732
>> >
>> > I think you know how to do this (?), but once your range is selected go
>> > to Format - Cells - Number, select Currency (in left column) and choose
>> > the format you want from the negative values area.
>> >
>> > hth
>> > Rob Brockett
>> > NZ
>> > Always learning & the best way to learn is to experience...
>> >
>> >
>> > --
>> > broro183
>> > ------------------------------------------------------------------------
>> > broro183's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30068
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=537425
>> >
>>
>>
>
>
Hi Zygy,
I guess you didn't choose a format with the '£' in front before applying the
'Custom' format.
The simplest approach would be to redo the formatting exactly as I
suggested - including choosing a format with the '£' in front, required # of
decimal points, thousands separator, etc. - for both +ve and -ve values.
As for your toolbar changing so you've lost the £ icon, decimal point etc,
there's nothing in Rob's advice that would cause that. If you've lost the
whole formatting toolbar, you can put it back again via View|Toolbars and
checking the 'Formatting' option. If you've only lost some of the icons from
that toolbar, you can recover all of them via
View|Toolbars|Customise|Toolbars, selecting the 'Formatting' option and
clicking 'Reset'.
Cheers
"Zygy" <[email protected]> wrote in message
news:[email protected]...
> Many thanks for the reply. I had a warning from Rob Brocket that if I do
> that there will be "quite an increase in your file size", so I tried to
> format only a used column as he suggested. When I followed his instruction
> as in:-http://excelforum.com/showthread.php?p=1572732#post1572732 my Tool
> Bar changed so now I am missing £ icon, decimal point etc and all the £
> disappeared in the whole column, although now the negative balances appear
> automatically in read, but without the £ in front of the figures!
>
> Where did I go wrong and how can I retain the £ both in the toolbar and in
> all the figures in a formatted column?
>
> "macropod" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Zygy,
> >
> > To format the whole sheet:
> > . click on the grey rectangle in the upper left corner (ie just above
row
> > 1
> > and to the left of column A)
> > . use Format|Cells|Number and choose the format you want.
> >
> > Note that the above applies to a single worksheet. If you want to apply
> > the
> > formatting to all worksheets in a workbook:
> > . select the first worksheet in the workbook,
> > . Click on the grey rectangle in the upper left corner
> > . click on the tab for the last worksheet in the workbook
> > . use Format|Cells|Number and choose the format you want.
> >
> > Cheers
> > PS: It's usually best to continue a line of discussion in the original
> > thread.
> >
> >
> > "Zygy" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thank you for the answer, but I tried that and it does not work. In any
> > case
> >> that is supposed to change only the selected cell, it does not change
the
> >> negative values occurring in other places, therefore I must have it for
a
> >> whole sheet. Macroport stated that it is possible, but he did not say
> > which
> >> way!
> >>
> >> It would be a good idea if persons making suggestions to solve a
problem
> >> ALWAYS stated how to achieve this as it is done by the professionals.
> >>
> >> "broro183" <[email protected]>
wrote
> > in
> >> message news:[email protected]...
> >> >
> >> > HI Zygy,
> >> >
> >> > Please check your original thread:
> >> >
> >> > http://excelforum.com/showthread.php...32#post1572732
> >> >
> >> > I think you know how to do this (?), but once your range is selected
go
> >> > to Format - Cells - Number, select Currency (in left column) and
choose
> >> > the format you want from the negative values area.
> >> >
> >> > hth
> >> > Rob Brockett
> >> > NZ
> >> > Always learning & the best way to learn is to experience...
> >> >
> >> >
> >> > --
> >> > broro183
> >>
> ------------------------------------------------------------------------
> >> > broro183's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=30068
> >> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=537425
> >> >
> >>
> >>
> >
> >
>
>
Many thanks for the very comprehensive reply. I did chose
the -£5,300.00 in red, the last one in the menu. Maybe I started the
formatting the wrong way. I clicked on the letter of the column, then I went
to Format>Cells>Currency and made my selection as above. But then all the £
signs disappeared from the column.
What did I do wrong? I am a self taught operator of Excel, so you must make
allowances for ignorance!
"macropod" <[email protected]> wrote in message
news:[email protected]...
> Hi Zygy,
>
> I guess you didn't choose a format with the '£' in front before applying
> the
> 'Custom' format.
> The simplest approach would be to redo the formatting exactly as I
> suggested - including choosing a format with the '£' in front, required #
> of
> decimal points, thousands separator, etc. - for both +ve and -ve values.
>
> As for your toolbar changing so you've lost the £ icon, decimal point etc,
> there's nothing in Rob's advice that would cause that. If you've lost the
> whole formatting toolbar, you can put it back again via View|Toolbars and
> checking the 'Formatting' option. If you've only lost some of the icons
> from
> that toolbar, you can recover all of them via
> View|Toolbars|Customise|Toolbars, selecting the 'Formatting' option and
> clicking 'Reset'.
>
> Cheers
>
>
> "Zygy" <[email protected]> wrote in message
> news:[email protected]...
>> Many thanks for the reply. I had a warning from Rob Brocket that if I do
>> that there will be "quite an increase in your file size", so I tried to
>> format only a used column as he suggested. When I followed his
>> instruction
>> as in:-http://excelforum.com/showthread.php?p=1572732#post1572732 my Tool
>> Bar changed so now I am missing £ icon, decimal point etc and all the £
>> disappeared in the whole column, although now the negative balances
>> appear
>> automatically in read, but without the £ in front of the figures!
>>
>> Where did I go wrong and how can I retain the £ both in the toolbar and
>> in
>> all the figures in a formatted column?
>>
>> "macropod" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Zygy,
>> >
>> > To format the whole sheet:
>> > . click on the grey rectangle in the upper left corner (ie just above
> row
>> > 1
>> > and to the left of column A)
>> > . use Format|Cells|Number and choose the format you want.
>> >
>> > Note that the above applies to a single worksheet. If you want to apply
>> > the
>> > formatting to all worksheets in a workbook:
>> > . select the first worksheet in the workbook,
>> > . Click on the grey rectangle in the upper left corner
>> > . click on the tab for the last worksheet in the workbook
>> > . use Format|Cells|Number and choose the format you want.
>> >
>> > Cheers
>> > PS: It's usually best to continue a line of discussion in the original
>> > thread.
>> >
>> >
>> > "Zygy" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Thank you for the answer, but I tried that and it does not work. In
>> >> any
>> > case
>> >> that is supposed to change only the selected cell, it does not change
> the
>> >> negative values occurring in other places, therefore I must have it
>> >> for
> a
>> >> whole sheet. Macroport stated that it is possible, but he did not say
>> > which
>> >> way!
>> >>
>> >> It would be a good idea if persons making suggestions to solve a
> problem
>> >> ALWAYS stated how to achieve this as it is done by the professionals.
>> >>
>> >> "broro183" <[email protected]>
> wrote
>> > in
>> >> message news:[email protected]...
>> >> >
>> >> > HI Zygy,
>> >> >
>> >> > Please check your original thread:
>> >> >
>> >> > http://excelforum.com/showthread.php...32#post1572732
>> >> >
>> >> > I think you know how to do this (?), but once your range is selected
> go
>> >> > to Format - Cells - Number, select Currency (in left column) and
> choose
>> >> > the format you want from the negative values area.
>> >> >
>> >> > hth
>> >> > Rob Brockett
>> >> > NZ
>> >> > Always learning & the best way to learn is to experience...
>> >> >
>> >> >
>> >> > --
>> >> > broro183
>> >>
>> ------------------------------------------------------------------------
>> >> > broro183's Profile:
>> >> > http://www.excelforum.com/member.php...o&userid=30068
>> >> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=537425
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
I solved the problem. To retain the £ in front of the figures in a cell it
is necessary to select it from the Symbols menu when Formatting! Thank you
all for the contribution to my problem!
"Zygy" <[email protected]> wrote in message
news:[email protected]...
> Many thanks for the very comprehensive reply. I did chose
> the -£5,300.00 in red, the last one in the menu. Maybe I started the
> formatting the wrong way. I clicked on the letter of the column, then I
> went to Format>Cells>Currency and made my selection as above. But then all
> the £ signs disappeared from the column.
>
> What did I do wrong? I am a self taught operator of Excel, so you must
> make allowances for ignorance!
>
> "macropod" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Zygy,
>>
>> I guess you didn't choose a format with the '£' in front before applying
>> the
>> 'Custom' format.
>> The simplest approach would be to redo the formatting exactly as I
>> suggested - including choosing a format with the '£' in front, required #
>> of
>> decimal points, thousands separator, etc. - for both +ve and -ve values.
>>
>> As for your toolbar changing so you've lost the £ icon, decimal point
>> etc,
>> there's nothing in Rob's advice that would cause that. If you've lost the
>> whole formatting toolbar, you can put it back again via View|Toolbars and
>> checking the 'Formatting' option. If you've only lost some of the icons
>> from
>> that toolbar, you can recover all of them via
>> View|Toolbars|Customise|Toolbars, selecting the 'Formatting' option and
>> clicking 'Reset'.
>>
>> Cheers
>>
>>
>> "Zygy" <[email protected]> wrote in message
>> news:[email protected]...
>>> Many thanks for the reply. I had a warning from Rob Brocket that if I do
>>> that there will be "quite an increase in your file size", so I tried to
>>> format only a used column as he suggested. When I followed his
>>> instruction
>>> as in:-http://excelforum.com/showthread.php?p=1572732#post1572732 my
>>> Tool
>>> Bar changed so now I am missing £ icon, decimal point etc and all the £
>>> disappeared in the whole column, although now the negative balances
>>> appear
>>> automatically in read, but without the £ in front of the figures!
>>>
>>> Where did I go wrong and how can I retain the £ both in the toolbar and
>>> in
>>> all the figures in a formatted column?
>>>
>>> "macropod" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > Hi Zygy,
>>> >
>>> > To format the whole sheet:
>>> > . click on the grey rectangle in the upper left corner (ie just above
>> row
>>> > 1
>>> > and to the left of column A)
>>> > . use Format|Cells|Number and choose the format you want.
>>> >
>>> > Note that the above applies to a single worksheet. If you want to
>>> > apply
>>> > the
>>> > formatting to all worksheets in a workbook:
>>> > . select the first worksheet in the workbook,
>>> > . Click on the grey rectangle in the upper left corner
>>> > . click on the tab for the last worksheet in the workbook
>>> > . use Format|Cells|Number and choose the format you want.
>>> >
>>> > Cheers
>>> > PS: It's usually best to continue a line of discussion in the original
>>> > thread.
>>> >
>>> >
>>> > "Zygy" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> Thank you for the answer, but I tried that and it does not work. In
>>> >> any
>>> > case
>>> >> that is supposed to change only the selected cell, it does not change
>> the
>>> >> negative values occurring in other places, therefore I must have it
>>> >> for
>> a
>>> >> whole sheet. Macroport stated that it is possible, but he did not say
>>> > which
>>> >> way!
>>> >>
>>> >> It would be a good idea if persons making suggestions to solve a
>> problem
>>> >> ALWAYS stated how to achieve this as it is done by the professionals.
>>> >>
>>> >> "broro183" <[email protected]>
>> wrote
>>> > in
>>> >> message news:[email protected]...
>>> >> >
>>> >> > HI Zygy,
>>> >> >
>>> >> > Please check your original thread:
>>> >> >
>>> >> > http://excelforum.com/showthread.php...32#post1572732
>>> >> >
>>> >> > I think you know how to do this (?), but once your range is
>>> >> > selected
>> go
>>> >> > to Format - Cells - Number, select Currency (in left column) and
>> choose
>>> >> > the format you want from the negative values area.
>>> >> >
>>> >> > hth
>>> >> > Rob Brockett
>>> >> > NZ
>>> >> > Always learning & the best way to learn is to experience...
>>> >> >
>>> >> >
>>> >> > --
>>> >> > broro183
>>> >>
>>> ------------------------------------------------------------------------
>>> >> > broro183's Profile:
>>> >> > http://www.excelforum.com/member.php...o&userid=30068
>>> >> > View this thread:
>>> > http://www.excelforum.com/showthread...hreadid=537425
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks