excel gets my goat sometimes
November 19, 2009 4:50 PM Subscribe
excel filter: why wont it let me have header rows below row 1?
I dont use excel much; but whenever I do, I'm always amazed at why its virtually impossible to label any rows or columns.
You'd think that labelling rows or columns would be one of the most basic things that one should be able to do in a spreadsheet program especially if one wants to keep one's rows and columns logical.
I know that you can label row 1 and column 1 and then exclude them when you sort. (And even that it seems to me they didnt make it automatic, you have to go into advanced sort window and click on 'i have header rows' or something.)
But what if you have 5 rows of numbers, and then you want to add more label rows?
Is there basically no way to do that? COuldnt they, for pete's sake, just give an option to make any row a "comment" row that isnt included in sorting and calculations? Like a "REM" statement in programming, to be able to right click a row and say "ignore this row in sorting and calculation"? Why hasnt MS done that yet? It seems so obvious and so useful a function.
Or is there a way to do that and I'm just dumb?
I dont use excel much; but whenever I do, I'm always amazed at why its virtually impossible to label any rows or columns.
You'd think that labelling rows or columns would be one of the most basic things that one should be able to do in a spreadsheet program especially if one wants to keep one's rows and columns logical.
I know that you can label row 1 and column 1 and then exclude them when you sort. (And even that it seems to me they didnt make it automatic, you have to go into advanced sort window and click on 'i have header rows' or something.)
But what if you have 5 rows of numbers, and then you want to add more label rows?
Is there basically no way to do that? COuldnt they, for pete's sake, just give an option to make any row a "comment" row that isnt included in sorting and calculations? Like a "REM" statement in programming, to be able to right click a row and say "ignore this row in sorting and calculation"? Why hasnt MS done that yet? It seems so obvious and so useful a function.
Or is there a way to do that and I'm just dumb?
Best answer: I'm not sure what you're asking -- to sort any part of a spreadsheet and have "labels rows" ignored in the sort process? Are these header rows always at the top of the rows you want sorted , or maybe even in the middle of them?
You can sort any contiguous group of rows just by clicking in it and clickng the AZ or ZA button. To not include a header row at the top of them, just temporarily insert a blank row, sort, and delete the blank row.
These rows don't have to be at the top of the spreadsheet, and you don't have have to sort entire rows. Excel automatically detects entirely blank rows and columns, and assumes the group you want to sort is marked off by blank rows and columns. You can also highlight a specific block of cells to sort instead of relying on blank rows and columns.
If you are looking to sort a bunch of rows but have the sort ignore a header row in the middle of them, I have no idea how to do that in any software. Not sure how I would even do that manually.
posted by msalt at 5:04 PM on November 19, 2009
You can sort any contiguous group of rows just by clicking in it and clickng the AZ or ZA button. To not include a header row at the top of them, just temporarily insert a blank row, sort, and delete the blank row.
These rows don't have to be at the top of the spreadsheet, and you don't have have to sort entire rows. Excel automatically detects entirely blank rows and columns, and assumes the group you want to sort is marked off by blank rows and columns. You can also highlight a specific block of cells to sort instead of relying on blank rows and columns.
If you are looking to sort a bunch of rows but have the sort ignore a header row in the middle of them, I have no idea how to do that in any software. Not sure how I would even do that manually.
posted by msalt at 5:04 PM on November 19, 2009
Best answer: Don't sort the whole sheet, just the range you want sorted.
To make it a little more automatic, you could highlite the table area you want sorted, even all the way down to the bottom of the page, and then name that, by typing a name into the little name box above and to the left of the sheet.
Then, when you want to sort everything below your (several) label rows, you can just select the range name from the dropdown there.
posted by pompomtom at 5:05 PM on November 19, 2009
To make it a little more automatic, you could highlite the table area you want sorted, even all the way down to the bottom of the page, and then name that, by typing a name into the little name box above and to the left of the sheet.
Then, when you want to sort everything below your (several) label rows, you can just select the range name from the dropdown there.
posted by pompomtom at 5:05 PM on November 19, 2009
* No, I don't know how to fucking do it with the fucking ribbon shit.
Close your eyes. Pretend the bloody ribbon thing isn't there. Pretend you're using a sensible version of Excel. Continue using Alt-W-F (or whatever it is, not on the work machine just now to test).
posted by pompomtom at 6:37 PM on November 19, 2009
Close your eyes. Pretend the bloody ribbon thing isn't there. Pretend you're using a sensible version of Excel. Continue using Alt-W-F (or whatever it is, not on the work machine just now to test).
posted by pompomtom at 6:37 PM on November 19, 2009
Response by poster: thanks for the responses -- I suppose I could use the method where I highlight the specific cells I want sorted -- but -- I guess i'm incredulous that we cant simply comment out rows. Because if I sort a lot on a particular spreadsheet, doesnt it basically mean that every single time I have to laboriously go thru and use the control key and shift keys and highlight only the rows I need sorted? Seems like an awful lot of work to do each time before a sort.
I suppose one could make a macro for that - which seems to me a complex solution to a simple problem. I know an excel expert wouldnt care, but for casual users this is, I think, something that comes up often and its amazing that there is no simple way to exclude a row I guess.
posted by jak68 at 6:49 PM on November 19, 2009
I suppose one could make a macro for that - which seems to me a complex solution to a simple problem. I know an excel expert wouldnt care, but for casual users this is, I think, something that comes up often and its amazing that there is no simple way to exclude a row I guess.
posted by jak68 at 6:49 PM on November 19, 2009
Response by poster: msalt, this is interesting what you said: "These rows don't have to be at the top of the spreadsheet, and you don't have have to sort entire rows. Excel automatically detects entirely blank rows and columns, and assumes the group you want to sort is marked off by blank rows and columns."
Ima try it right now...
posted by jak68 at 6:51 PM on November 19, 2009
Ima try it right now...
posted by jak68 at 6:51 PM on November 19, 2009
Response by poster: msalt, what you suggested did not work for me, even when I sorrounded my headers with blank rows.
What I started with:
What I ended with:
What I would have liked to have seen (ie, unmolested headers, with the sort being done successfully):
posted by jak68 at 6:59 PM on November 19, 2009
What I started with:
What I ended with:
What I would have liked to have seen (ie, unmolested headers, with the sort being done successfully):
posted by jak68 at 6:59 PM on November 19, 2009
Response by poster: blah, looks like the links didnt post: trying again:
what I started with
what I ended with
what I would have liked to have seen.
posted by jak68 at 7:01 PM on November 19, 2009
what I started with
what I ended with
what I would have liked to have seen.
posted by jak68 at 7:01 PM on November 19, 2009
Response by poster: pompomtom: this is a very handy tip, I did not know this, thanks!
"To make it a little more automatic, you could highlite the table area you want sorted, even all the way down to the bottom of the page, and then name that, by typing a name into the little name box above and to the left of the sheet. "
I guess it makes it a little easier than having to re-select everything. Still, excel sucks ass, in my estimation!
posted by jak68 at 7:03 PM on November 19, 2009
"To make it a little more automatic, you could highlite the table area you want sorted, even all the way down to the bottom of the page, and then name that, by typing a name into the little name box above and to the left of the sheet. "
I guess it makes it a little easier than having to re-select everything. Still, excel sucks ass, in my estimation!
posted by jak68 at 7:03 PM on November 19, 2009
If your startwith file had a blank column between the first column of numbers and the second, you could just click onto any one cell in column A and then click the A-Z button to sort only that column and only until it hit a blank row.
In your endwith example, it sorted all columns in descending Col A order because (it looks like) you had all of column A and B selected. As msalt said "Excel automatically detects entirely blank rows and columns, and assumes the group you want to sort is marked off by blank rows and columns."
posted by nelvana at 7:25 PM on November 19, 2009
In your endwith example, it sorted all columns in descending Col A order because (it looks like) you had all of column A and B selected. As msalt said "Excel automatically detects entirely blank rows and columns, and assumes the group you want to sort is marked off by blank rows and columns."
posted by nelvana at 7:25 PM on November 19, 2009
Response by poster: oh i see, so there has to be blank columns as well? I'll try it again...
posted by jak68 at 7:59 PM on November 19, 2009
posted by jak68 at 7:59 PM on November 19, 2009
Response by poster: update, i just tried it with a blank column, and it still isnt doing what i want. In other words, I see your point that, if I select a cell within a grouping thats been cordoned off by blank rows and columns, and then click sort, it sorts just that whole group.
BUT -- thats not really what I want -- I'd like to be able to sort an *entire* column while leaving untouched a *variety of* header rows (a header row every five rows, all down the line).
It seems the only way to do this is to laboriously select the ranges of cells each time? (or create a shortcut to do that, as pompomtom says, but no way around that?) So in other words no way to mark off a row or column as a 'comment' so its not included in a row or column sort.
posted by jak68 at 8:04 PM on November 19, 2009
BUT -- thats not really what I want -- I'd like to be able to sort an *entire* column while leaving untouched a *variety of* header rows (a header row every five rows, all down the line).
It seems the only way to do this is to laboriously select the ranges of cells each time? (or create a shortcut to do that, as pompomtom says, but no way around that?) So in other words no way to mark off a row or column as a 'comment' so its not included in a row or column sort.
posted by jak68 at 8:04 PM on November 19, 2009
Response by poster: altho i agree that grouping them off like that makes things a bit easier too, like pompomtom's shortcut, it just means i can click within each grouping (so the labor depends on how many groupings i have) and then click sort. I suppose thats easier than selecting cells.
thanks, i guess one of these two shortcuts will have to do then, unless anyone has any other ideas :-D
posted by jak68 at 8:06 PM on November 19, 2009
thanks, i guess one of these two shortcuts will have to do then, unless anyone has any other ideas :-D
posted by jak68 at 8:06 PM on November 19, 2009
Somewhat easier than laboriously selecting the ranges of cells each time -- select them once, then click in the range box (which normally shows the address of the single cell you're in, such as A3) and type the name of your choice. Hit enter, and the range is defined by that name.
you can now choose it from the drop-down list or type it and the range (with all the control and shift fanciness) will be selected. Next level: do that, then record a macro that does it and assign it a simple keystroke.
posted by msalt at 9:55 PM on November 19, 2009
you can now choose it from the drop-down list or type it and the range (with all the control and shift fanciness) will be selected. Next level: do that, then record a macro that does it and assign it a simple keystroke.
posted by msalt at 9:55 PM on November 19, 2009
This thread is closed to new comments.
If you have, say, two or five or ten rows at the top of your columns that you do not want included in the sort, what you can do is highlight all the rows you do want sorted, or even just the specific cells you want sorted, and then sort using whatever parameters you want. It'll only sort what's highlighted at that point, leaving everything else untouched.
posted by Askr at 4:58 PM on November 19, 2009