Starting in Excel 2007, you could sort by color. In today's Episode, #1317 of the "Learn Excel from MrExcel" podcast series, Bill shows us how to sort a database where the sales reps have been identified by cell color.
Chapter 15 ... This episode is the video podcast companion to the book, "Rev Up to Excel 2010: Upgraders Guide to Excel 2010" by Bill Jelen a.k.a. "MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Considering Upgrading to Excel 2010? Want some help in making an informed decision? Check out Bill Jelen's Blog Post: "Upgrade to Excel 2007 or Excel 2010?"
Chapter 15 ... This episode is the video podcast companion to the book, "Rev Up to Excel 2010: Upgraders Guide to Excel 2010" by Bill Jelen a.k.a. "MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Considering Upgrading to Excel 2010? Want some help in making an informed decision? Check out Bill Jelen's Blog Post: "Upgrade to Excel 2007 or Excel 2010?"
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Rev up to Excel 2010, chapter 15.
Color Sort.
First heard about this feature back in Excel 2007.
I thought it was really, really silly.
But you know what, people do crazy things for their Excel spreadsheets things that I would never do.
Here's someone coded the sales rep by color.
So, all the red cells are Angie and then all the orange cells are Betty and yellow cells are Chad.
This is horrible, I mean, how do you add anything up or sort or do anything?
Well, the beautiful thing about Excel 2007 and Excel 2010 is we can go to data, sort and we can say, hey! Look, we want to sort by amount but we don't want to sort on the values.
We want to sort on the cell color.
Now, you have choices here cell color, font color, or the cell icon, that was added in by conditional formatting and oh! By the way, cell color could work with conditional formatting commands.
So, if your cells being color through conditional formatting and it work and we're going to do here, we want to put the red on top.
All right! Now, this is going to be a little bit tedious.
Luckily, we're allowed more than three levels because we're going to need a bunch of levels.
I'm going to copy that level and say they want red on top and then orange, copy that level and then yellow, copy that level, red, orange, yellow, green.
Copy, blue, copy and then finally the purple.
All right! So, there's the order they want everything to do and then click [ ok ] and you'll see that they sort the data now into the proper sequence.
Now, of course if we had control over the person who was doing this, we would tell them to stop.
If they're sending is this is a one-time thing then we would go through and add a real sales rep column here.
If they're sending us to this every day, and we don't have control over them.
Well, we get a Résumé up and try and find another place to work because this is going to drive us crazy.
Now, you write a macro or something like that.
But anyway sorting my color, nice feature that came along in Excel 2007 also there on Excel 2010.
Hey, let me undo, undo if you just need to bring all the red cells to the top, you can right-click and under sort, put selected cell color on top and it brings all the red to the top.
So, if you just need to bring one particular color to the top, you can do that or even filter by that one color.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Rev up to Excel 2010, chapter 15.
Color Sort.
First heard about this feature back in Excel 2007.
I thought it was really, really silly.
But you know what, people do crazy things for their Excel spreadsheets things that I would never do.
Here's someone coded the sales rep by color.
So, all the red cells are Angie and then all the orange cells are Betty and yellow cells are Chad.
This is horrible, I mean, how do you add anything up or sort or do anything?
Well, the beautiful thing about Excel 2007 and Excel 2010 is we can go to data, sort and we can say, hey! Look, we want to sort by amount but we don't want to sort on the values.
We want to sort on the cell color.
Now, you have choices here cell color, font color, or the cell icon, that was added in by conditional formatting and oh! By the way, cell color could work with conditional formatting commands.
So, if your cells being color through conditional formatting and it work and we're going to do here, we want to put the red on top.
All right! Now, this is going to be a little bit tedious.
Luckily, we're allowed more than three levels because we're going to need a bunch of levels.
I'm going to copy that level and say they want red on top and then orange, copy that level and then yellow, copy that level, red, orange, yellow, green.
Copy, blue, copy and then finally the purple.
All right! So, there's the order they want everything to do and then click [ ok ] and you'll see that they sort the data now into the proper sequence.
Now, of course if we had control over the person who was doing this, we would tell them to stop.
If they're sending is this is a one-time thing then we would go through and add a real sales rep column here.
If they're sending us to this every day, and we don't have control over them.
Well, we get a Résumé up and try and find another place to work because this is going to drive us crazy.
Now, you write a macro or something like that.
But anyway sorting my color, nice feature that came along in Excel 2007 also there on Excel 2010.
Hey, let me undo, undo if you just need to bring all the red cells to the top, you can right-click and under sort, put selected cell color on top and it brings all the red to the top.
So, if you just need to bring one particular color to the top, you can do that or even filter by that one color.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
