shading used cells only
 

i havea spread sheet document with over 25K entries spreading six rows. how
can i shade only the cells hat have numbers in them and leave the blank cells
with out shade with out doing it each numbered cell at a time. there has to
be a faster way

shading used cells only
 

Select the range of cell. Assuming A1:F25000

FormatConditional FormattingFormula is.

=A1<""

Format to a nice color and OK.


Gord Dibben MS Excel MVP

On Mon, 26 Jun 2006 11:57:02 -0700, mrharpo2u
wrote:

i havea spread sheet document with over 25K entries spreading six rows. how
can i shade only the cells hat have numbers in them and leave the blank cells
with out shade with out doing it each numbered cell at a time. there has to
be a faster way

shading used cells only
 

[ 1 ] Are all the numbers the results of formulas,
OR
[ 2 ] Are all the numbers keyed in,
OR
[ 3 ] Both?

Hit <F5, and click on "Special"

In the "Go To Special" window,

If the answer above is "1"
Click on "Formulas" then <OK,

If the answer above is "2"
Click on "Constants", then <OK,

Then, while the cells are *still* selected, format them to a color you like.

If the answer above was "3", then you'll just have to repeat the "Go To" and
format twice.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mrharpo2u" wrote in message
...
i havea spread sheet document with over 25K entries spreading six rows.
how
can i shade only the cells hat have numbers in them and leave the blank
cells
with out shade with out doing it each numbered cell at a time. there has
to
be a faster way

shading used cells only
 

Select all of the cells (select the rows is probably quickest way) then use
Format | Conditional Formatting
in the first dialog that comes up, choose
[Cell Value Is]
Then choose [not between]
and enter 0 into both of the parameters so that the line reads:
Cell Value Is | not between | 0 | and | 0
sounds kind of lame, but keeps you from having to write a less than zero and
a greater than zero condition.
Then click the [Format...] button and the [Patterns] tab and choose the
color for your shading.

Click [OK] to make it permanent.

The only problem you'll have with this is that cells with zero in them will
not get shaded.

If your numbers never go outside of a certain range (say they are all
positive) then you can try other variations of Conditional Formatting to just
catch the ones you're interested in, including zero. For example, if numbers
are never negative, then you could choose the [greater than or equal to]
option and zero as the value.
"mrharpo2u" wrote:

i havea spread sheet document with over 25K entries spreading six rows. how
can i shade only the cells hat have numbers in them and leave the blank cells
with out shade with out doing it each numbered cell at a time. there has to
be a faster way

shading used cells only
 

Misread original.

Use =ISNUMBER(A1) for the CFFormula is:


Gord Dibben MS Excel MVP

On Mon, 26 Jun 2006 12:32:43 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Select the range of cell. Assuming A1:F25000

FormatConditional FormattingFormula is.

=A1<""

Format to a nice color and OK.


Gord Dibben MS Excel MVP

On Mon, 26 Jun 2006 11:57:02 -0700, mrharpo2u
wrote:

i havea spread sheet document with over 25K entries spreading six rows. how
can i shade only the cells hat have numbers in them and leave the blank cells
with out shade with out doing it each numbered cell at a time. there has to
be a faster way