SOLVED

Not getting an option to sort largest to smallest, only A to Z

%3CLINGO-SUB%20id%3D%22lingo-sub-1356193%22%20slang%3D%22en-US%22%3ENot%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356193%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20problem%20with%20my%20Excel%20spreadsheet%20where%20I%20do%20not%20get%20an%20option%20to%20sort%20numerically%20in%20descending%20order%20-%20largest%20to%20smallest.%20I%20only%20get%20an%20option%20to%20sort%20alphabetically.%20The%20cell%20values%20are%20either%20blank%20or%20numeric.%20Each%20cell%20is%20using%20the%20following%20formula%20-%20%3DIFERROR(VLOOKUP(%24P10%2C%20May3List%2C%206%2C%20FALSE)%2C%20%E2%80%9C%E2%80%9D).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1356193%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356277%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645905%22%20target%3D%22_blank%22%3E%40RohitNaval%3C%2FA%3E%26nbsp%3BThen%20I%20suspect%20that%20your%20%22numbers%22%20are%20in%20fact%20texts.%20What%20do%20you%20get%20when%20you%20enter%20%3CSTRONG%3E%3DISNUMBER(%3CCELL%20reference%3D%22%22%3E)%26nbsp%3B%3C%2FCELL%3E%3C%2FSTRONG%3Ewhere%20the%20cell%20reference%20is%20a%20cell%20where%20you%20have%20the%20number%3F%20FALSE%3F%20Then%20it's%20not%20a%20number.%20If%20not%2C%20perhaps%20you%20can%20upload%20an%20example%20of%20your%20schedule.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356343%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356343%22%20slang%3D%22en-US%22%3EISNUMBER%20returns%20TRUE%20for%20cells%20where%20I%20have%20a%20number%20and%20FALSE%20where%20the%20cells%20are%20empty.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356349%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThat%20is%20exactly%20what's%20happening.%20Most%20of%20the%20cells%20are%20empty%20and%20Excel%20is%20defaulting%20to%20sort%20alphabetically.%20Any%20way%20to%20change%20that%20sort%20option%20to%20numeric%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20change%20the%20IFERROR%20formula%20from%20%22%22%20to%200%20and%20now%20the%20sort%20works%20numerically.%20However%20I%20have%20so%20many%20zeros%20showing%20now.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356354%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645905%22%20target%3D%22_blank%22%3E%40RohitNaval%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20hide%20zeroes%20through%20settings%20or%20by%20custom%20number%20format%2C%20here%20is%20how%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fwindows-and-office%2Fpro-tip-three-ways-to-hide-zero-values-in-an-excel-sheet%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fwindows-and-office%2Fpro-tip-three-ways-to-hide-zero-values-in-an-excel-sheet%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356321%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645905%22%20target%3D%22_blank%22%3E%40RohitNaval%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20you%20don't%20have%20blank%20cells%2C%20you%20have%20cells%20with%20empty%20texts%20returned%20by%20%3DIFERROR(...%2C%22%22).%20As%20soon%20as%20we%20have%20texts%20within%20the%20range%2C%20even%20empty%2C%20Excel%20suggests%20to%20sort%20alphabetically.%20With%26nbsp%3B%3DIFERROR(...%2C0)%20most%20probably%20that%20will%20be%20numeric%20sorting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356379%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356379%22%20slang%3D%22en-US%22%3EOh%20that's%20perfect.%20Thank%20you%20so%20much%20for%20that%20settings%20tip.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356388%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356388%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20I%20have%20you%2C%20when%20a%20cell%20shows%20a%20green%20flag%20in%20the%20upper%20left%20corner%2C%20what%20does%20that%20indicate%20and%20how%20do%20I%20get%20rid%20of%20it%3F%20I%20have%20to%20manually%20get%20rid%20of%20it%20by%20selecting%20'ignore%20error'.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356405%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20getting%20an%20option%20to%20sort%20largest%20to%20smallest%2C%20only%20A%20to%20Z%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645905%22%20target%3D%22_blank%22%3E%40RohitNaval%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreen%20%22flag%22%20is%20usually%20when%20one%20of%20this%20error%20appears%20if%20your%20settings%20are%20ON%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20795px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188520iA8F640DDF2897D0D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20switch%20above%20settings%20off%2C%20but%20I'd%20not%20recommend.%20You%20may%20select%20at%20once%20all%20such%20cells%20and%20ignore%20if%20that's%20only%20alert%20or%20correct%20if%20these%20are%20actually%20errors.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20229px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188521i99E2478AD36D1B4B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a problem with my Excel spreadsheet where I do not get an option to sort numerically in descending order - largest to smallest. I only get an option to sort alphabetically. The cell values are either blank or numeric. Each cell is using the following formula - =IFERROR(VLOOKUP($P10, May3List, 6, FALSE), “”).

10 Replies

@RohitNaval Then I suspect that your "numbers" are in fact texts. What do you get when you enter =ISNUMBER(<cell reference>) where the cell reference is a cell where you have the number? FALSE? Then it's not a number. If not, perhaps you can upload an example of your schedule.

best response confirmed by Grahmfs13 (Microsoft)
Solution

@RohitNaval 

In addition, you don't have blank cells, you have cells with empty texts returned by =IFERROR(...,""). As soon as we have texts within the range, even empty, Excel suggests to sort alphabetically. With =IFERROR(...,0) most probably that will be numeric sorting.

ISNUMBER returns TRUE for cells where I have a number and FALSE where the cells are empty.

@Sergei Baklan That is exactly what's happening. Most of the cells are empty and Excel is defaulting to sort alphabetically. Any way to change that sort option to numeric?

 

I did change the IFERROR formula from "" to 0 and now the sort works numerically. However I have so many zeros showing now. 

Oh that's perfect. Thank you so much for that settings tip.

While I have you, when a cell shows a green flag in the upper left corner, what does that indicate and how do I get rid of it? I have to manually get rid of it by selecting 'ignore error'.

@RohitNaval 

Green "flag" is usually when one of this error appears if your settings are ON

image.png

You may switch above settings off, but I'd not recommend. You may select at once all such cells and ignore if that's only alert or correct if these are actually errors.

image.png

 

@RohitNaval I tried filtering my numbers but it just went 1, 220, 34, 460 etc instead of smallest to largest. I fixed it by converting the column I was trying to filter into NUMBER and then doing text to column. For some reason it wasn't converting to numbers until I did text to column. After that it allowed me to filter smallest to largest (1, 34, 220, 460 etc).

@annabellas 

If you sort texts which option shall appear

image.png