I've checked around this site and am unable to find my issue (of which I have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

Hi there! It sounds like you're having trouble sorting all the columns in your large spreadsheet. I'd be happy to help you out.

Here's what you can do to sort all the columns in your spreadsheet:

1. Click on any cell within your data range (A3:BI193).
2. Go to the Data tab in the ribbon at the top of the screen.
3. Click on the Sort button.
4. In the Sort dialog box, make sure that the My data has headers box is checked.
5. In the Sort by dropdown menu, select the column you want to sort by (e.g. Column A).
6. In the Then by dropdown menu, select the next column you want to sort by (e.g. Column B).
7. Continue selecting columns in the Then by dropdown menu until you've selected all the columns you want to sort by.
8. Make sure that the Sort On dropdown menu is set to Values.
9. Make sure that the Order dropdown menu is set to either Smallest to Largest or Largest to Smallest, depending on how you want to sort your data.
10. Click the OK button to sort your data.

If you follow these steps, Excel should recognize all the columns in your data range and sort them accordingly. Let me know if this helps!

I never let excel guess at the range to be sorted. I always select the entire
range to sort, then use data|sort.

Excel can get confused when there are gaps in rows or columns and figure that
the range stops before the gap.



Gillian72 wrote:

I've checked around this site and am unable to find my issue (of which I have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

--

Dave Peterson

ps. you can make sorting the data easier...

In xl2003, you can apply data|filter|autofilter to the range and use the
dropdown arrow to sort your data.

Debra Dalgleish has a technique at her site that adds invisible rectangles in
the headers and then sorts the data by that field when you click on one of those
rectangles.

http://contextures.com/xlSort02.html

Gillian72 wrote:

I've checked around this site and am unable to find my issue (of which I have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

--

Dave Peterson

When you add columns to the end, Excel does not always understand that the
new columns are an extension of what you already had. When you insert
columns, it has no issues.
There are a couple of ways you can approach this. If you are using Excel
2003, you can highlight your headers from A:BI and go to Data-List-Creat
List. When you add columns to the right of a list, Excel does know to add
the new columns to the list.
Or you can name the range you want sorted. If you select columns A:BJ and
give it a name like Entire_List then anytime you want to sort, just hit
Ctrl+G and select the name Entire_List. Now you can sort by the entire list,
and since we added column BJ (currently unused) to the range, then adding
columns after BI is faking Excel into thinking your inserting the columns in
front of column BJ.
Hope that helps.
-KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Gillian72" wrote:

I've checked around this site and am unable to find my issue (of which I have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

I get the same problem from time-to-time.

The probable cause is that you have added columns / rows outside the range
of used cells. I find it useful to add the requisite number of columns /
rows inside the range of used cells, copy the last column / row to all the
new columns / rows (this also copies all the formats and formulae) and then
add / change data in the new columns / rows. If you do that with your
present spreadsheet for columns presently ?BB through ?BI you should
resolve your present problem and ensure it doesn't re-occur.

Regards.

Bill Ridgeway
Computer Solutions

"Gillian72" wrote in message
...
I've checked around this site and am unable to find my issue (of which I
have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from
BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why
the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

Dave and KC,
Thank you so much for your quick options! Based on what you wrote, both
would work - by selecting the data first (definitely the safest) as well as
creating a list. After playing around a bit, I think I figured out why I was
having issues and it's thanks to you! Columns BB & BC were totally blank
except for the title. I entered in some dashes into these columns in the
first record, went to Data - Sort, and it grabbed all the columns. I will
probably continue to select the data but just wanted to put that out there in
case anyone else was experiencing this. I didn't test it on other columns
(such as A) but it appears to be working for me.
Thanks again!!!
:)

"KC Rippstein" wrote:

When you add columns to the end, Excel does not always understand that the
new columns are an extension of what you already had. When you insert
columns, it has no issues.
There are a couple of ways you can approach this. If you are using Excel
2003, you can highlight your headers from A:BI and go to Data-List-Creat
List. When you add columns to the right of a list, Excel does know to add
the new columns to the list.
Or you can name the range you want sorted. If you select columns A:BJ and
give it a name like Entire_List then anytime you want to sort, just hit
Ctrl+G and select the name Entire_List. Now you can sort by the entire list,
and since we added column BJ (currently unused) to the range, then adding
columns after BI is faking Excel into thinking your inserting the columns in
front of column BJ.
Hope that helps.
-KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Gillian72" wrote:

I've checked around this site and am unable to find my issue (of which I have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!

Bill,
Thanks for your response. Strangely enough, I tried this as well and my
sorting was well, out of sorts.... I just posted another reply indicating
that I think my problem stemmed from having some blank columns in the first
record which may have stopped the sorting. I think your way would work for
me from now on as long as I make sure there's data (real or fake) in that
first record.
Thanks again!!!

"Bill Ridgeway" wrote:

I get the same problem from time-to-time.

The probable cause is that you have added columns / rows outside the range
of used cells. I find it useful to add the requisite number of columns /
rows inside the range of used cells, copy the last column / row to all the
new columns / rows (this also copies all the formats and formulae) and then
add / change data in the new columns / rows. If you do that with your
present spreadsheet for columns presently ?BB through ?BI you should
resolve your present problem and ensure it doesn't re-occur.

Regards.

Bill Ridgeway
Computer Solutions

"Gillian72" wrote in message
...
I've checked around this site and am unable to find my issue (of which I
have
many. Sorry. Too personal.) Anyway, I work daily in a large spreadsheet
that has a growing number of rows and columns to BI (recently added from
BA).
Now when I perform a sort, it only sorts from A - BA, with columns BC - BI
staying the same and all the data getting muck a muck. I can sort by
highlighting the specific cells (A - BI and 3 - 193). I'm wondering why
the
Data - Sort doesn't automatically recognize the new columns. If I don't
highlight anything and go to Data - Sort, it has a range to BA. If I'm
clicked on a cell such as BG, it highlights only a segment of the sheet
(looks like column 88 to something and BD to BI).
If anyone can assist, that would be great.
Thanks!