Sometimes a manager demands that you insert tiny little columns between the columns of your data set. While I hate this, use the trick in Episode 852 to ensure that the data set sorts correctly.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I've got one of my major pet peeves here today.
I hate the manager who says, "Look, I want tiny little columns between my columns because it makes the underscores under the headings look better." That's just a recipe for disaster-- someone's going to sort part of this data set and not part of this data set.
It violates the rules that I put in my book-- no blank rows no blank columns-- but every once in a while, what are you going to do?
Your manager makes you do this.
Well, a great preemptive strike to solve this problem is to select all of those blank cells-- all those blank cells.
So, I just go to Special, Blank cells, and we're actually going to put something in those cells-- we're going to put the word "Blank", or really you can put anything-- Ctrl+Enter, and then I'm going to select those cells and change the font color to white.
Okay, so now it looks like the cells are completely blank, but, in fact, they're filled in, and we can still sort with a single click because we have a nice contiguous block of data.
Again, I hate the tiny little blank columns, want to avoid those tight little blank columns; but if your manager absolutely makes you do it, putting the blank up in the heading and turning it white will prevent it from accidentally getting half the data sorted and not all of the data sorted.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
I've got one of my major pet peeves here today.
I hate the manager who says, "Look, I want tiny little columns between my columns because it makes the underscores under the headings look better." That's just a recipe for disaster-- someone's going to sort part of this data set and not part of this data set.
It violates the rules that I put in my book-- no blank rows no blank columns-- but every once in a while, what are you going to do?
Your manager makes you do this.
Well, a great preemptive strike to solve this problem is to select all of those blank cells-- all those blank cells.
So, I just go to Special, Blank cells, and we're actually going to put something in those cells-- we're going to put the word "Blank", or really you can put anything-- Ctrl+Enter, and then I'm going to select those cells and change the font color to white.
Okay, so now it looks like the cells are completely blank, but, in fact, they're filled in, and we can still sort with a single click because we have a nice contiguous block of data.
Again, I hate the tiny little blank columns, want to avoid those tight little blank columns; but if your manager absolutely makes you do it, putting the blank up in the heading and turning it white will prevent it from accidentally getting half the data sorted and not all of the data sorted.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
