how to search for negative values within a file

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
779
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 5, 2009
Messages
779
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

Well-known Member
Joined
Oct 5, 2009
Messages
779

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 5, 2009
Messages
779
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,588
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top