Say that you need to add up all of the red cells. This is easier in Excel 2007 using Filter by Color. Episode 816 will show you how to do this in Excel 2007 as well as a clever trick using find by format to do the same thing in Excel 2003.
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.
Well, today, I have a cool trick, brand new in Excel 2007 but an amazing way to do the same thing in Excel 2003.
We have a list of invoices here, and someone has gone through and flagged some invoices in red and they say add up the red ones.
Well, gee, that's tough to do, but, in Excel 2007, what you can do is right-click one of the red cells and go to FILTER and say FILTER BY SLECTED CELL’S COLOR or FONT COLOR.
So, I'll choose COLOR.
That will get me all the red cells, and then, once you've applied a filter to the data set, go to the cell immediately below the data set, the first visible cell -- so, in this case, row 25 -- and use ALT+=, and instead of creating the SUM function, like ALT+= usually does, it's going to create the version that shows the total of just the selected cells -- so, 499619.
[ =SUBTOTAL(9,B2:B24) ] Now, what if you haven't upgraded to Excel 2007?
What if you’re back in Excel 2003?
Well, there's a cool thing to do where you choose all of column B, and we'll go to EDIT, FIND, and I'm going to choose the OPTIONS.
OPTIONS allows me to look for a certain format.
So, I'll click the FORMAT button, and say CHOOSE FORMAT FROM CELL, and basically go click on one of my red cells.
Now, I'm going to do FIND ALL and that gets me a list down here at the bottom of the dialog box of all of those cells.
What I'm going to do is press CONTROL+A. That will select only those red cells, and then we’re going to look down in the quick sum area down here in the status bar.
It'll show me that the total of those selected red cells is 499619 -- exactly the same answer we got in Excel 2007.
So, easy way to do it in 2007, but certainly a clever way to do it in Excel 2003.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, today, I have a cool trick, brand new in Excel 2007 but an amazing way to do the same thing in Excel 2003.
We have a list of invoices here, and someone has gone through and flagged some invoices in red and they say add up the red ones.
Well, gee, that's tough to do, but, in Excel 2007, what you can do is right-click one of the red cells and go to FILTER and say FILTER BY SLECTED CELL’S COLOR or FONT COLOR.
So, I'll choose COLOR.
That will get me all the red cells, and then, once you've applied a filter to the data set, go to the cell immediately below the data set, the first visible cell -- so, in this case, row 25 -- and use ALT+=, and instead of creating the SUM function, like ALT+= usually does, it's going to create the version that shows the total of just the selected cells -- so, 499619.
[ =SUBTOTAL(9,B2:B24) ] Now, what if you haven't upgraded to Excel 2007?
What if you’re back in Excel 2003?
Well, there's a cool thing to do where you choose all of column B, and we'll go to EDIT, FIND, and I'm going to choose the OPTIONS.
OPTIONS allows me to look for a certain format.
So, I'll click the FORMAT button, and say CHOOSE FORMAT FROM CELL, and basically go click on one of my red cells.
Now, I'm going to do FIND ALL and that gets me a list down here at the bottom of the dialog box of all of those cells.
What I'm going to do is press CONTROL+A. That will select only those red cells, and then we’re going to look down in the quick sum area down here in the status bar.
It'll show me that the total of those selected red cells is 499619 -- exactly the same answer we got in Excel 2007.
So, easy way to do it in 2007, but certainly a clever way to do it in Excel 2003.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
