#### Sort Treats Empty Cells As Largest Value??

```I have several projects that sort data from greatest to least
(descending).  That is, nothing is considered greater than a million.
Of about 4000 rows between a third and a half are blank (no count).
I need the maximum values/count at the top of the list, but Excel puts
these empty cells at the top of the list.  As a result I have to do a
lot of moving of large groups of data around to put the empty/blank
cells at the bottom of these lists -- a real time consuming pain.

Is there a way to have Excel treat empty/blank cells as having lower
values than cells with values?

I assume I could fill all empty cells with zeros but this will clutter
the reports up a lot, so I'd rather not do that.

jim
```
 0
jimsnews (12)
8/11/2005 4:00:21 PM
excel 39879 articles. 2 followers.

2 Replies
682 Views

Similar Articles

[PageSpeed] 14

```jim evans <jimsnews@houston.rr.com> wrote in
news:3tsmf19ddqajl5oqpkdrhk1qd8nk6qi1qh@4ax.com:

>
> I have several projects that sort data from greatest to least
> (descending).  That is, nothing is considered greater than a million.
> Of about 4000 rows between a third and a half are blank (no count).
> I need the maximum values/count at the top of the list, but Excel puts
> these empty cells at the top of the list.  As a result I have to do a
> lot of moving of large groups of data around to put the empty/blank
> cells at the bottom of these lists -- a real time consuming pain.
>
> Is there a way to have Excel treat empty/blank cells as having lower
> values than cells with values?
>
> I assume I could fill all empty cells with zeros but this will clutter
> the reports up a lot, so I'd rather not do that.
>
> jim
>

Do you have a formula in your sort column producing "" when the result is
to be nothing?
Then you get the standard Excel sort rule applied and "empty" cells come
first.

Are there any zeros that you need to be visible?
If not, let the formula result be 0 and suppress the display of zeros.
Then you wil get the sort result you wish.

--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)
```
 0
Dodo
8/11/2005 4:25:28 PM
```use a database and you can sort things a lot easier
and you wont have to have multiple copies of the same data.. sorted in
different directions

```
 0
aaron.kempf (776)
8/11/2005 5:01:26 PM

Similar Artilces:

legacyExchangeDN value
I have 3 Exchange Routing Groups, each with an Exchange 2000 or Exchange 2003 server. I've used 'exchdump' to determine the value of the legacyExchangeDN attribute. Depending on which Exchange server I run exchdump on, it gives me a different legacyExchangeDN value. Is this how it's supposed to be? Different howso? legacyExchangeDN value for what object? The server? The Routing Group? -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "cpu" <nosp@mthanks.zzz.zzz> wrote in...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

New Views are not displaying Customized calculated fields value
I created a new view based on some native fields along with some custom fields. None of the custom field values are calculating and showing up in the view: The custom fields do not seem to be complicated. here they are Earned Work hours = INT([Work]/60)*([Physical % Complete]/100) Productivity = IIf([Actual Work]=0,0,([Earned Work Hours]/([Actual Work]/60))*100) WorkHours Forecast = IIf([Actual Work]=0,([Work]/60),([Work]/60)/([Productivity]/100)) The view is set at the Security Category of "My Projects" Thanks, I am assuming that you have this problem in P...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Empty frames
While surfing the Washington Post site, I came across an empty video box with a tiny box in the upper left hand corner of the frame. Inside the tiny box were three little dabs of red, green and blue colors. On another site, I found an empty picture frame with the same tiny box. How can these be opened? I have Windows XP, SP 3, Windows Media Player 11, IE 8. Thank you. Web pages can contain all kinds of multimedia content. One is Adobe Flash. Empty boxes or frames can appear either because your PC doesn't have the correct software add-on to render the content or your secur...

Sort behaves unexpectedly
Over the years I've come to count on being able to sort be a column header. That is, all of the rows below the headers have data and at least two rows above the headers are empty. I have a spreadsheet now where rows 1, 2 and 3 have random info in them. Rows 4 and 5 are empty. Row six has column labels, and rows 7-45 have data. If I put my cursor on one of the headers (e.g., "name"), and click sort a/z, it sorts only the rows in the group, but it sorts the header row into the rest of them. Rows 1-3 aren't affected. How come? I've never had this happen before. ...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Default picklist values not appearing on convert from lead to acco
Hi, Converting a lead to an account. I have several picklist fields (on the account) which should bring up a default value. When I create a new account from scratch they all appear. When I convert a lead to an account none of the default values are there. There are no offending mappings or scripts that would explain this. Has anyone else seen this or can offer any guidance. Ta Pete. Default values are not set on enties which are converted or created trough the webservice. The only way to have a default value set for any path is to use the PreCreate Callout. -- Patrick Verbeeten (MC...

pivot table question
My data table has a 'costs' and 'date' column. I'm interested in getting data from the last 7 days and having it in the field list so that I can use it as part of a formula. Does anyone know how I can do this? Excel 2007 PivotTable Filter last 7 days. http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_03_09a.xlsx Great. Thanks so much! ...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the \$ symbol at the beginning of the type and add text at the end of the type to look like this \$#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the \$ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

MS RMS 1.2 or QuickSell barcode type database values
Hello, a member of the newsgroups was kind enough to send me a copy of the QSCImp.exe, so I could transition my database over and it worked very well. However, rather than hand set the 10,000 items I have with the barcode type (as I use Interleaved 2 of 5, UPCA, EAN13, UPCE and Code 39), I was hoping that someone had the nomenclature that would allow me to pre-set the Barcode type based on my barcode number values. I know my 11 digits are UPCA, so with a little bit of IF/THEN I could easily set a database field value of UPCA. Could someone enlighten me as to what RMS/QuickSell is looking for ...