Sort Column But Exclude Rows On Bottom

[Data123]

I have a long set of columns and I would like to sort one and use the expand selection. However, I have a few rows at the very bottom of all the columns I want to exclude from the sort. These bottom rows are used to sum the totals of several columns. Is there a way to highlight those bottom rows to make them not sortable? Thank you!

 

[ExcelToDAX]

Assuming your column is in column A and you want to remove 2 rows at the bottom of that column, you could do this:

=DROP(FILTER(A:A,A:A<>""),-2)

 

[Data123]

Assuming your column is in column A and you want to remove 2 rows at the bottom of that column, you could do this:

=DROP(FILTER(A:A,A:A<>""),-2)

thank you! may i ask what the code or formula above will do?

 

[Alex Blakenburg]

Have you considered turning the top data section into a table and perhaps use Table totalling for your totals ?

 

[Data123]

Have you considered turning the top data section into a table and perhaps use Table totalling for your totals ?

Thanks and yes I have. Initially I did not want to do it for the top section, but I am considering it now. The columns are of many names and I add names and remove them often, would this be an issue with a table?

What are the negatives of making a spreadsheet into a table?

Also, what if I turned the bottom two rows into a table instead? If so I were then to highlight the top of a column and it happens to then highlight the bottom two rows, which were now a table would it sort the table also? Thanks.

 

[Alex Blakenburg]

Ideally we would need to see you data. I am not sure what you mean by you "add names and remove them often" mean when you seem ot be referring to column names.

For sort and filtering:
Excel uses a concept of Current Region. If you have a single cell selected the current region will pick up eveything around it until it hits a full blank row (going up/down) or fully blank column (going left/right).
So if you have total rows you don't want included just make sure there is a fully blank row before those rows.
You can see what it will do by usins Ctrl+<*> (the + usually involves Shift+<8>, this will hightlight the current region.

The below give more information and the first talks about the current region concept.

Excel Tables

Ten reasons to use Tables in Excel: Calculated Columns, Sorting, Filtering, Easy Formatting, Total Row, Header always appear, Autofill rows on data entry.

chrismenardtraining.teachable.com

How to Create Excel Tables and Fix Excel Table Problems

Create an Excel Table to organize your data. Quickly sort, filter, change formatting with Table Styles. Create pivot table from the Excel table.

contextures.com

 

[Data123]

Thank you for the info. Sorry for the confusion about names being added and removed. The cells are stock symbols so they can be replaced often. I hope this clears things up.

I do have a space between the column of stock symbols and the bottom rows where the total two rows are shown. I just tried again to sort the stock symbol column by highlighting the whole column and it highlights the bottom 2 rows with a space and brings them into the sort. Am I missing something that I did not do like adding more space or something else?

 

[Alex Blakenburg]

See if the image below helps.
By clicking any cell in the data range and using Ctrl+<*> ie Ctrl+shift+8 it will highlight the area that is going to be used as the default range for sorting, filtering, pivots and conversion to a table.
If it is not the range that you want to use for filtering it means that where I have a "frame" of blank cells around my data range, you have something in 1 or more of those cells.
That something could be a formula returning "" so that it looks to be empty but actually isn't.