• Default Sort Header Row (2003)

    Author
    Topic
    #450370

    You guys always seem to come up with the easy answer. If you fix this for me, I will be in heaven and forever in your debt!!

    Excel has a “Sort” A>Z button on the Standard Tool bar — I ASSume that is the default, but MAYBE I added it years ago…

    Mine seems to exhibit variable behavior, and I am not sure WHY. Sometimes it “knows” that I have no header row, and other times it does not. I swear, I use it the same each time in the same stored “blank.xls” file. When it does what I don’t expect, I have to go to the Menu bar and select Data|Sort and tell it what to do. Again, I want it to ASSume no Header row.

    My question is: Is there some setting that forces the “Sort” button on the Standard Toolbar to ALWAYS use “No header row” as its default, or I am left with random chaos???

    Thanks for humoring me yet again.

    Viewing 1 reply thread
    Author
    Replies
    • #1106318

      Excel tries to be intelligent and guess whether the data have a header row. For example, if there is a column with numbers that has text in the first row, Excel will assume that the first row contains column headers (field names). Similarly, if the first row is formatted differently than the rest, for example bold vs non-bold, it’ll assume that the first row contains column headers. It doesn’t always guess correctly. As far as I know, there is no way to turn off this “artificial intelligence”.

    • #1106322

      You could use a macro to do the sort, or I came across this discussion which (eventually) ends up with:[indent]


      I just did a quick test and found the following (on Excel 2003)

      1) If the cell above the first column of your table is empty, then the default is “no header row”.

      2) If the cell above the first column has text then the default is “header row”, even if the rest of the cells above the table are empty.

      3) You can put an empty row above your table and hide it and the default will be “no header row”.


      [/indent]Any help?

      • #1106388

        To both:

        Sort of, but I swear I do the same thing routinely once a week. And I don’t always get the same result. One would expect Excel to be consistent.

        Let me explain (in case it helps). I use a Palm device (yeah, someone still uses them) to record the “work” I do all week. It is recorded in HandDbase (a database program). At the end of the week, I download this and convert into in an Excel sheet. I open this sheet, select all of the data I need, selectCopy to save this to the clip board. This saves a ‘block’ of data that is about 20×10 cells in size (rows x columns).

        Next, I open a “blank.xls” sheet (which I use as a template). This sheet is, in part, pre-formatted to accept my data. That is to say, it has column headers that label each column appropriately. I always paste my data starting in cell A2. Next I run a Macro that sticks in the borders, centers the columns I want centered, and adjusts the size of each column to the appropriate width. I am almost done.

        Lastly I want to alphabetize the list, so I select the area I need to alphabetize and I hit the Sort A>Z button. It works the way I want about half of the time. Now, there IS a header row built-in that I do NOT select — this header row is always the same and it exists un-touched in the “blank.xls” file.

        I have done this for at least 10 years with very little variation! It works sometimes, but not others. Any other thoughts?? Thanks for humoring me.

        • #1106391

          I can’t explain what you experience, but have you tried including the header row when sorting? If it is formatted differently from the data rows, it is very likely that Excel will see it as the header row and sort the data beneath it correctly.

          • #1106416

            Good thought. Yes, it is formatted differently. I’ll try it next time and get back. Thanks Hans.

          • #1106689

            Well… so much for artificial intelligence. The first row is formatted quite differently — Bold, Centered, different Font size, not enclosed by borders — yet it gets “sorted” like any other row if I select it with the other rows!!

            Oh well, it was worth asking. Thanks.

    Viewing 1 reply thread
    Reply To: Default Sort Header Row (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: