Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shading a range of cells | Excel Discussion (Misc queries) | |||
sort columns, but not shading | New Users to Excel | |||
Automatic Shading of Cells | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |