Using Locked Tables In Mac Numbers

You can't lock individual cells in Numbers, but you can lock entire tables. By designing your spreadsheet well, or making alterations to an existing spreadsheet, you can only have exactly the cells you want as editable.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Today let me show you how you can get around the fact that you can't lock individual cells in Numbers.
MacMost is brought to you thanks to a great group of more than 700 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts. 
So I often get asked how can you lock individual cells in Numbers. This is something you could do in Excel but in Numbers you could only lock entire tables. Now being able to have multiple tables in Numbers is one of its most important features and something a lot of people just don't use. Let me show you an example of how you can use multiple tables and the ability to lock those tables to create really good spreadsheets. 
We're going to use one of the templates here as an example instead of building something from scratch. I'm going to use the Mortgage Calculator Template that comes with Numbers. So here you've got something that has various different elements like text boxes. You've got two spreadsheets here and you even have a chart. The only numbers that you should really be editing in this spreadsheet are these four numbers here. You adjust these four numbers and then it will give you new calculations here and an entire new spreadsheet here with all sorts of different numbers in it. This chart will also change as well. What would be nice is if you could lock everything except these four cells so only those are editable.
So there's no function to say select these cells and lock them. You either lock the entire table by going to Arrange, Lock and now nothing in the table can be edited or you have the entire table unlocked. But you can see here we have multiple tables. So we can actually lock some parts of the spreadsheet and not others. Since there's nothing editable here, this is all calculations based on the numbers here. So if I select something in this table and I go to Lock it now I can't accidentally mess this table up. The same with this chart. I can go here and lock that. So how can I lock everything in this table except for these four cells?
Well, I'm going to show you three different ways to do it.
Let's do the quickest way first although not the most elegant. That is to create a separate table that holds these four cells. Now I could create a new table by clicking here, create a new table. Make it one column by four rows. Then adjust all my formulas so that say instead of this formula here being B1 x B2 it actually refers to the new table. There's a really easy way to do that. You can separate cells from a table. All you need to do is select them. So I'm going to Shift Click to select these four cells. Then click and hold and drag. You can see it will drag these four cells away. I drop it there and I get that new table. These four cells are left blank. But the cool thing is that all of the formula references have moved to that new table. So if I click here you can see it says Table 1, A1; Table 1, A2 which is over here. So I've done all that work with a simple drag and drop procedure.
Now let's go to this table here, I'm going to go to Format, Table and turn on Table Name. Then I'm simple going to drag it on top of this table. It should lock directly in place. You can zoom in on it and kind of move it just so it's perfectly there so all the lines lineup perfectly like I just got it there. Now everything looks the same but I can go and select this table here and then go to Lock it. This table which was just floating on top of it and a separate element, that's unlocked. So now I've succeeded in having everything here locked except for these four cells and I've done it rather quickly and easily.
Now if you think it's messy to have one table sitting over another you can simply break this up into three tables. So we've got this table here already to go. Let's go and break this apart. I'm going to use Arrange, Unlock. Option Command L. Select these. Click, Hold and drag them away. I'll do the same with these. Now let's Delete this table here and I'll move these in place. Just have them all kind of snap to each other. I can make adjustments. I probably want to get rid of the Table Name for this table here and adjust it and put it right there. I can adjust the formatting. I can add a name for this here or maybe just create a text box that goes across the top. Now I've got three separate tables and I can select these two tables here and Lock them and leave this third one unlocked. I get the same result.
Now let's look at a third method which I like the best because it will create the best looking result and if you're starting something from scratch it's a great way to design something. That's to not actually mess with these tables much at all but to create specific spaces where it's obvious to the user of the spreadsheet they can enter in new data. 
So I'm going to select all three of these elements here just move them down and create some extra space. I'm going to create a new table here and just make it a plain table with no header or footer rows. I'm going to shrink it to one cell like that and drag it over here. I'm going to make it just a little bit wider and let's set this to be the purchase price. Then I'm going to select here and copy the value, it's just the value number in here, and paste it in here so I have a good starting point. So that's the starting point purchase price.  Now I'm going to select it and Copy and click off of it and Paste so I get a new copy of it here and put another one cell table there and I'll call this Down Payment and I will select here, Copy and Paste. Not only does it paste in the value but also the cell formatting so this has little arrows here to allow you to adjust amounts.
Let's go and do the same thing and create two more here. I'm going to Option Drag to duplicate these. Now let's bring these a little closer to each other so they all fit. Now I want to make sure the values from these cells are used, not the value from these cells. Now I could just eliminate these. I could select them, delete them. But I would have to first update all the formulas here. So there are a lot of different formulas. Not only these three but formulas down in here that have to be updated to refer to these. An easier way to do it if I've already created the spreadsheet is to simply have the values here refer to these values. So, for instance, this value here I can replace by hitting the equals key, enter a new formula, and the formula is simply just a quick reference to this value. 
Now you could see if I change it here and you can see it changes there and that changes everything else as well. So now what I need to do here is delete this value, since it has special cell formatting so I need to delete it before I use the equals key, then refer to this. Then I'll do equals and refer to that, and then here I'll do Delete and then equals and refer to that. So now each one of these refers to one of these cells up here. So now I can go and select this table, select all three of them in fact, and Lock them and now you can't change the values in any of those. But you can change these four values and it's kind of clear here at the top. You can even add extra text here to say Alter These Four Values and See The Results Below. 
Anything I change here, like I go and change the Down Payment to 11%, you can see it changes there and then it changes all those values. If I wanted to I could even go further. Let's unlock this table here. I can select these four rows here and then go to Table and then Hide Four Rows. It actually hides those four so I just get this now. You can, of course, as I said before, just change all the formulas here to refer to these cells. But that's a little bit harder to do since this spreadsheet is already setup. But if you're creating something from scratch like this then you would just design it to actually use these four cells here and there's no need to have four hidden rows there repeating the same values.

Comments: 2 Responses to “Using Locked Tables In Mac Numbers”

Gene

4 years ago

Good video. Another technique I use to let the user know what cells are editable and which are not is color fill the cells. Red no, green yes. Doesn't prevent it but it shows intent.

Elmi

This was extremely helpful, thank you! I've been working in Excel and Google Sheets forever, and I struggle to replicate some of the things in Numbers that I take for granted in the other apps. This helped a lot.

Comments Closed.

Using Locked Tables In Mac Numbers

Comments: 2 Responses to “Using Locked Tables In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information