Is there an Excel shortcut for "next nonblank cell"?

[Doctor Q]

I thought I remembered that some key combination or other shortcut in Excel would take me to the next non-blank cell in a column, but I can't seem to find it.

Did I imagine it or is there such a feature?

 

[balamw]

On Windows you can control arrow key to do that, but I don't know of a similar keystroke for Office:Mac.

B

 

[jimsmac]

On Windows you can control arrow key to do that, but I don't know of a similar keystroke for Office:Mac.

B

Yep, Command and arrow key, or Ctrl and arrow key both seem to work for me.

 

[Doctor Q]

Sounds like I'll have to junk my Macs and replace them with Windows PCs.

 

[Doctor Q]

I looked for a menu choice, rather than a shortcut, but I don't see that either.

My workaround is to sort on that column to separate blank from nonblank cells, find what I'm looking for, and then Undo. That's horribly crude. I sure hope there's a better way, because I don't like scrolling when I have a large spreadsheet, such as the one I'm working on today, with over 10,000 rows. It has sparse values in an "error" column and I'm trying to find the "next error".

 

[iMeowbot]

Yep, Command and arrow key, or Ctrl and arrow key both seem to work for me.

Those are both working for me too (on Mac).

Doctor Q, do you have any custom keyboard thingies that may be overriding the Excel defaults?

 

[Doctor Q]

Nope, no thingies on this Mac.

 

[WildCowboy]

I'm currently playing around with Excel 2004 on my Mac, and command-arrow and control-arrow seem to behave the same. Using the down arrow jumps me to either the bottom of a current block of non-blank cells or on to the beginning of the next block if I'm already at the bottom of a block. So say I have data in rows 1-10, 15-20, 22-23, and 27 within a column. If I have the box in row 1 selected, hitting command-down will successively take me to rows 10, 15, 20, 22, 23, and 27.

 

[Raid]

Hey Doctor Q I'm not exactly sure what you are looking for, but you might want to try the GoTo command (found in the Edit menu). If you select the column and then Edit>GoTo>Special you then have the option to find blank cells or cells that contain an error, etc.

If that's not it try using Data>AutoFilter and have it select all the cells that are comming up #value or #error! (or whatever the message is). the only other method I can think of to easily identify an error easily is with conditional formatting; it's possible to have the cell to appear red when there's an error.

I hope this helps!

 

[Applespider]

Those are both working for me too (on Mac).

They work for me on my Mac too.

I usually use Excel (PC) but took my Powerbook in the other day to do a presentation and was most impressed to find that they still worked in Excel Mac.

The fact that Excel for the Mac is crippled in terms of pivot tables annoyed me more!

 

[Nermal]

On Windows you can control arrow key to do that, but I don't know of a similar keystroke for Office:Mac.

I don't have Excel installed at the moment but I could have sworn that goes to the next blank cell.

 

[Doctor Q]

I'm currently playing around with Excel 2004 on my Mac, and command-arrow and control-arrow seem to behave the same. Using the down arrow jumps me to either the bottom of a current block of non-blank cells or on to the beginning of the next block if I'm already at the bottom of a block. So say I have data in rows 1-10, 15-20, 22-23, and 27 within a column. If I have the box in row 1 selected, hitting command-down will successively take me to rows 10, 15, 20, 22, 23, and 27.

I tried some more examples and I see that command-arrow works for cell with no formulas, but not for cells whose contents is computed to be blank, e.g.,

=if(some_condition,"error condition","")​

In my case, the condition is an error check and I'm trying to find the next error in a very large table without having to endlessly scroll or page down or use a crude workaround like sorting. I've tried other formulas like

=if(some_condition,"error condition")​

and

=if(some_condition,"error condition",NA())​

but none of them let command-down arrow go to other than the very end.

Hey Doctor Q I'm not exactly sure what you are looking for, but you might want to try the GoTo command (found in the Edit menu). If you select the column and then Edit>GoTo>Special you then have the option to find blank cells or cells that contain an error, etc.

If that's not it try using Data>AutoFilter and have it select all the cells that are comming up #value or #error! (or whatever the message is). the only other method I can think of to easily identify an error easily is with conditional formatting; it's possible to have the cell to appear red when there's an error.

Thanks for the tips. None of them quite solve my problem, which is to find the next cell below the current cell that has other than an empty string as its value, with as little trouble as possible.

I've noticed that you can do conditional formatting, but I've never used it because I'm usually more interested in content than presentation. But you are right that conditional formatting could also be used to make it easier to spot certain cells. However, I'd never use red, since I can't see red . In fact, that's sometimes a problem when others show me a spreadsheet they created, using red for negative numbers.

 

[Raid]

I tried some more examples and I see that command-arrow works for cell with no formulas, but not for cells whose contents is computed to be blank, e.g.,

=if(some_condition,"error condition","")​

Ah! Very true, that's because the cell is not blank as it contains the if statement, but displays a blank (the double quotes) when the statment is false.

In my case, the condition is an error check and I'm trying to find the next error in a very large table without having to endlessly scroll or page down or use a crude workaround like sorting.
<snip>
Thanks for the tips. None of them quite solve my problem, which is to find the next cell below the current cell that has other than an empty string as its value, with as little trouble as possible.

Good news you still have options!

If auto filter isn't quite what you're looking for, I'm going to assume that you still want to see all entries on your spreadsheet as you are going down to the next error. (SIDE NOTE: If all you want to see are the errors then autofilter is the easiest)... At any rate to be able to 'command-arrow' down the list you'll have to do a combination of things:

1. Copy and Paste>Special>values the column with your if statment (copy it into a new column if you don't want to get rid of the formula)
2. Use Autofilter to show all the blank cells in the recently copied column Data>Filter>Autofilter>Custom equals to in the first drop down list (default I think) and '(Blanks)' in the second drop down list (it's 2nd last on the list that appears)
3. Select all the Blank cells in the column you filtered on.
4. Select Edit>Clear>Contents (delete key works too) to get rid of all the double quotes.
5. Remove the filter by unselecting Data>Filter>AutoFilter
6. Go to the top of the spreadsheet and start using command-arrow down!

Writing out all the steps probably took me about 50 times longer to write than it would have to just do it... I do this kind of stuff all the time, though it is combersome to explain it's a quick and painless process. Good Luck

Oh and as for conditional formatting you can choose other colours, font styles, sizes, etc. And now that you mention it I do use red to indicate negitive numbers, but I always use the red and bracketed number format to show negitive values... now I've got a reason to do it!

 

[Applespider]

Just thought of a couple of options if you want to keep the formulae in place and attempt to fix them so don't want to paste special.

If you're unlikely to have a # in your results, you can just do a Find for the character # since all errors start with it. So long as you select 'values' rather than formulas, that should work and clicking Next would move you through. Or Find All would find all of em so you could work through them.

Alternatively, if there might be # in your values, you could put an if(iserror formula in where the 'true' statement is a word/character that's not going to come up in your normal results like "ERROR". So if your formula was say SUM(A13333) and it was throwing up a #VALUE message, you'd put in the following
=if(iserror(sum(A13333)),"ERROR",sum(A13333))

Then you could just do a Find (on values rather than formulas) for the word ERROR - which would let you just keep clicking the next button til it found the next ERROR.

EDIT: Giggling at vbulletin adding a in the middle of the formulas rather than a :

 

[Doctor Q]

Thanks, Raid. Your suggested steps give me an altenative to my "paste special, sort, undo" technique for finding errors. Both methods are straightforward but a bit of a bother.

EDIT: Giggling at vbulletin adding a in the middle of the formulas rather than a :

Thanks! You might want to try that "Disable smilies in text" checkbox in the Reply form. See? --->