how to search for negative values within a file

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
827
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
37,866
Office Version
  1. 2019
  2. 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
827
Office Version
  1. 2013
Platform
  1. Windows
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
827
Office Version
  1. 2013
Platform
  1. Windows

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
37,866
Office Version
  1. 2019
  2. 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
827
Office Version
  1. 2013
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top