#N/A...unsightly and it's nice when we can make it 'disappear'. Anna suggested a quick way to make those little 'eye-sores' go away. Follow along with Episode #1595 as Bill describes and demonstrates this easy to implement trick!
This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel Podcast, Replace #N/A.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. I don't do many live seminars anymore, but I was up in Boston and one of the people in that seminar was Anna. Anna's a marketing analyst and she was sitting in row 2 which, you know, that's where all the best tips and tricks come from, row 2.
So, thanks to Anna for this cool trick talking about how to get rid of the N/As, and I said, you know, hey, it's best to sort those to the top, sort ZtoA, and they'll come to the top. You can replace them then. Anna, said, wait, no. There's a there's a faster way to do that.
So, we have a VLOOKUP here. All of the non-matches are showing up as N/A, and Anna said, okay, first, of course, you have to convert this from formulas to values. So, CONTROL+C, and then I'm going to use the right-click menu key, V. That works in Excel 2010. So, now you see we have values up there, and I never thought this trick would have worked and here's why.
I'm going to switch over to VBA and take a look at the active cell. So, we do ALT+F11.
ACTIVECELL.VALUE. Press ENTER. You get 1370995, but if I choose that N/A and ask for the ACTIVECELL.VALUE, you see, it's not a number. It's an ERROR 2042. So, I didn't think I could ever use FIND AND REPLACE on this, but it turns out that you can.
So, CONTROL+H to bring up FIND AND REPLACE, and then #N ,…or / A, REPLACE WITH 0, and then REPLACE ALL, and because I'm typing it just as though it looks, I don't have to worry about the secret code for ERROR 2042 or whatever it was. Finds all the N/As and very quickly, without ever having to sort, you solve that problem. So pretty slick way.
Now, so, you know, normally when you get N/As, you have to go figure out why, but, here, these are forecasts, these are orders. Well, all the forecasts haven't come in yet but we want to be able to total things up. So, cool trick to replace the N/As using FIND AND REPLACE, CONTROL+H, just search for # N / A, and do REPLACE ALL.
Well, hey. I want to thank Anna for that great trick. I want to thank you for stopping by.
We’ll see you next time another netcast from MrExcel.
Learn Excel From MrExcel Podcast, Replace #N/A.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. I don't do many live seminars anymore, but I was up in Boston and one of the people in that seminar was Anna. Anna's a marketing analyst and she was sitting in row 2 which, you know, that's where all the best tips and tricks come from, row 2.
So, thanks to Anna for this cool trick talking about how to get rid of the N/As, and I said, you know, hey, it's best to sort those to the top, sort ZtoA, and they'll come to the top. You can replace them then. Anna, said, wait, no. There's a there's a faster way to do that.
So, we have a VLOOKUP here. All of the non-matches are showing up as N/A, and Anna said, okay, first, of course, you have to convert this from formulas to values. So, CONTROL+C, and then I'm going to use the right-click menu key, V. That works in Excel 2010. So, now you see we have values up there, and I never thought this trick would have worked and here's why.
I'm going to switch over to VBA and take a look at the active cell. So, we do ALT+F11.
ACTIVECELL.VALUE. Press ENTER. You get 1370995, but if I choose that N/A and ask for the ACTIVECELL.VALUE, you see, it's not a number. It's an ERROR 2042. So, I didn't think I could ever use FIND AND REPLACE on this, but it turns out that you can.
So, CONTROL+H to bring up FIND AND REPLACE, and then #N ,…or / A, REPLACE WITH 0, and then REPLACE ALL, and because I'm typing it just as though it looks, I don't have to worry about the secret code for ERROR 2042 or whatever it was. Finds all the N/As and very quickly, without ever having to sort, you solve that problem. So pretty slick way.
Now, so, you know, normally when you get N/As, you have to go figure out why, but, here, these are forecasts, these are orders. Well, all the forecasts haven't come in yet but we want to be able to total things up. So, cool trick to replace the N/As using FIND AND REPLACE, CONTROL+H, just search for # N / A, and do REPLACE ALL.
Well, hey. I want to thank Anna for that great trick. I want to thank you for stopping by.
We’ll see you next time another netcast from MrExcel.
