Making Numbers Formulas Easier To Read

You can use a trick to force a line return in Numbers formulas so they are easier to read and edit. The same trick can be used in some web forms and other apps to add line returns when a regular Return keystroke doesn't work.

Video Transcript: So here's a handy little tip if you use Numbers a lot. Especially if you use complex long formulas in Numbers.  You'd get things that look like this. If I click here and look at the formula it gets to be pretty long and this is actually a pretty short one. It could go for several lines of really complex nested ands and ifs and all sorts of things in here. 
Here I've got a simple formula, more or less, where I'm multiplying the quantity times a price here in this lookup table. So I'm taking the product name and using that in lookup to lookup based on the product , the price. So apple matches to apple here which gives me the price of 25 cents. So it's the quantity times 25 cents and then I multiply that by an IF statement, if there's a check in this checkbox, this discount column I've got .75 (in other words 75% of the price) or 1 if there's no checkmark there. In other words a 25% discount if I check on that. So I can test this out by checking it and you can see I get 25% discount here. I can change the price of apples to say 26 cents and you can see all the apple prices updated a little bit. 
So that's all great and it works fine except that when I go to edit this formula it gets a little long. It would be nice to be able to break this up on lines. It turns out you can. If you put your cursor somewhere where you want to break a line and you hit Return, of course, it's just going to enter the formula. But instead if you were to hit Option, Return it actually puts a line there. So I'm going to do that before and after the multiplier there. I'm going to do it before that after the multiplier here. Now you can see it's a lot easier for me to understand what's going on in this formula. 
All I need to do now is copy, paste that over all of these, it's the same formula as the one line version but now it's in all of them. Anytime I go to look at that formula there I can see it's broken up on multiple lines. The short version of the formula is down at the bottom. That's not going to show those line breaks because there is only room for one line there. But when I'm actually working on the formula I get this break up here. 
You know the Option Return is a very useful thing. You can use it on a lot of different occasions. For instance, a lot of sites with forms for filling something in like little chat windows and things like that. They automatically submit what your typing if you hit Return. But if you hit Option Return it actually puts a line return there and you can break up what you're saying on multiple lines. 
So it's kind of a handy thing to try out. It works here in Numbers and it works in a variety of other places. It certainly makes for a lot nicer looking formula.

Comments: 3 Responses to “Making Numbers Formulas Easier To Read”

Kelly

6 years ago

Is there a way to use this with the iOS version?

Gary Rosenzweig

Kelly: There is no Option+Return on the iPad, but it appears you can add extra spaces with the spacebar and use that to separate things a bit.

John Scibal

Once again Gary......"Who Knew?" You are incredible.

Comments Closed.

Making Numbers Formulas Easier To Read

Comments: 3 Responses to “Making Numbers Formulas Easier To Read”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information