Sorting data in Excel: style information and extra cells left behind.

August 6, 2015 12:20 PM

When I attempt to sort my Excel spreadsheets, some style information is left behind. It also doesn't sort empty cells. I want to fix this.

I have an Excel spreadsheet that has several columns and many rows of data. Each row is a data point, and the columns code different properties of this data point. I would like to sort by data properties I've coded, but when I try to sort using the filter function on the header row, some style information for the cells is left behind - borders stay where they are, while fill colors and fonts sort as expected.

Also, only the cells columns containing entries move, meaning that . All the empty columns to the right stay put.

How do I fix this annoying problem? I want all of the style information to move along with its cell, and I want cells to the right to sort as well.

I'm using Office 2011 on a MacBook Pro OS 10.9.5

posted by Kutsuwamushi to Computers & Internet (7 answers total)

You can get around this problem by using the Cell Styles function in Excel. Use that to create different styles (font, border, fill, etc.) for your various cells, instead of just highlighting those cells and changing how they and their borders look. Once you've formatted them using Cell Styles, everything will move with the cells.
posted by schroedingersgirl at 12:36 PM on August 6, 2015

Here are instructions for Cell Styles on a Mac.
posted by schroedingersgirl at 12:36 PM on August 6, 2015

To deal with the blank columns, you need to highlight all the columns you want sorted. You can also just highlight the entire sheet by clicking on the square above 1 and next to A. Sometimes this has a triangle in it and sometimes it is empty. Borders don't sort by default, but you may be able to get around that by using Conditional Formatting.
posted by soelo at 12:37 PM on August 6, 2015

Response by poster: Sorry, I should have been more specific about how I formatted the cells and what I'm doing:

I used custom Cell Styles to format the cells, so that's not the solution.

Highlighting the entire sheet versus not highlighting the entire sheet makes no difference when it comes to sorting the empty columns to the right (or the style stuff).

Borders don't sort by default

Holy crap I didn't even realize this could be by design, and now I'm finding results on Google that also suggest the conditional formatting fix. But Conditional formatting doesn't work for the type of data I have...
posted by Kutsuwamushi at 12:46 PM on August 6, 2015

If the column is empty, how do you know it isn't sorting? I don't understand how an empty column could be sorted, so I think I am missing something in your explanation.
posted by soelo at 1:46 PM on August 6, 2015

Response by poster: I selected the entire row by clicking on the row number before applying the formatting. All of the columns are formatted, including those to the right that haven't been used yet. I want it to do this because I might add more columns.

When I highlight the header row and click the "Filter" button, I get the little drop-down arrows - but only on the cells that have an entry. If I use one of those arrows to sort, only the columns that have those little drop-down arrows sort. I know the columns to the right aren't sorting because all of their style information gets left behind - font, fill color, etc.
posted by Kutsuwamushi at 2:25 PM on August 6, 2015

Oh, that makes sense. What you can do is use Format Painter on a column that has the colors that you want and paste them over the empty column. Another option is to not color the whole row and when you want to add a column, insert it to the left of your last column. It should keep the colors that way.
posted by soelo at 3:18 PM on August 6, 2015