how to search for negative values within a file

[bigdan]

Hello

As the subject says I want to search for only negative values within a file. Is there a way of doing so? I tried simply putting a - in the find values area, that didnt work.

Thanks

 

[ParamRay]

.
.

You could use data validation.

(1) Select the cells you in which you want to identify negative numbers;
(2) Create a new conditional formatting rule:

Home --> Styles --> Conditional Formatting --> Highlight Cells Rules --> Less Than;

(3) In the Less Than dialog box, enter 0 (zero) and click OK.

Now your cells containing negative values will be highlighted.

 

[Rick Rothstein]

As the subject says I want to search for only negative values within a file. Is there a way of doing so? I tried simply putting a - in the find values area, that didnt work.

Try it again, but this time click the "Options>>" button and remove the checkmark from the "Match entire cell contents" checkbox.

 

[bigdan]

I'm not very familiar with conditional formatting, though I do have some knowledge of it. Would this work for negative percentages as well, and would I then be able to get rid of the formatting? I just want to make sure I've changed all negative numbers, including percentages, from -$xxx.x to ($xxx.x)

 

[bigdan]

Try it again, but this time click the "Options>>" button and remove the checkmark from the "Match entire cell contents" checkbox.

Wow that seems to have worked! Except that I pressed Ctrl-F again and I didnt see match entire cell contents checked. Maybe that's why it worked, and earlier it was checked, but I know I certainly didnt uncheck it. Not sure how that works.

Anyway so I'd wanted to do this to make sure that all my negative numbers were in ($xxx) format rather than -$xxx format. When I searched this way I saw only one result, the one which had a minus sign in it. The other negative numbers, which had brackets around them, didnt end up showing up in the search. Is that whats supposed to happen? If so, how would I find all the negative numbers, not just the ones which have a visible minus sign? But this alone should be adequate.

 

[Rick Rothstein]

Wow that seems to have worked! Except that I pressed Ctrl-F again and I didnt see match entire cell contents checked. Maybe that's why it worked, and earlier it was checked, but I know I certainly didnt uncheck it. Not sure how that works.

It works in a very annoying manner as far as I am concerned. Once set, either manually or by VBA code, the two checkboxes retain that setting until changed. I don't mind the "sticky" nature of the setting so much as I mind the fact that you have to click a button to see the checkboxes in order to see what their setting is. If you look at the amount of information contained in panel displayed when you click the "Options>>" button, I see no reason why those settings do not simply appear on the main dialog panel.

Anyway so I'd wanted to do this to make sure that all my negative numbers were in ($xxx) format rather than -$xxx format. When I searched this way I saw only one result, the one which had a minus sign in it. The other negative numbers, which had brackets around them, didnt end up showing up in the search. Is that whats supposed to happen? If so, how would I find all the negative numbers, not just the ones which have a visible minus sign? But this alone should be adequate.

After you click the "Find All" button, click into the table of found cells that appears at the bottom of the panel, press CTRL+A and then click the Close button... all of the found cells will be selected, so you can call up the Cell Format dialog box and change the format as desired and all those selected cells will inherit that new cell format. (Click any non-selected cell to clear the multi-cell selection.)

 

[bigdan]

It works in a very annoying manner as far as I am concerned. Once set, either manually or by VBA code, the two checkboxes retain that setting until changed. I don't mind the "sticky" nature of the setting so much as I mind the fact that you have to click a button to see the checkboxes in order to see what their setting is. If you look at the amount of information contained in panel displayed when you click the "Options>>" button, I see no reason why those settings do not simply appear on the main dialog panel.


After you click the "Find All" button, click into the table of found cells that appears at the bottom of the panel, press CTRL+A and then click the Close button... all of the found cells will be selected, so you can call up the Cell Format dialog box and change the format as desired and all those selected cells will inherit that new cell format. (Click any non-selected cell to clear the multi-cell selection.)

Holy crap I cant believe I never knew about the Find All function! And to think I tell people in interviews my Excel skills are advanced haha.

Thanks a lot!