How To Use Numbers Magic Fill

Hi, this is Gary with MacMost.com. Let's take a look at using Magic Fill in Numbers.

Numbers version 15.1 with an Apple Creator Studio subscription has a new feature in it called Magic Fill. This example here gives you a basic idea of what Magic Fill can do. You've got some names here. First name, Last Name. This maybe data you've gotten from somewhere else and it's put into your spreadsheet. Maybe you've got a hundred or a thousand of these names. Then for the first row you've filled in the first name and last name. Now, this is all the information that Magic Fill needs to complete the rest of the table. If you go to this cell here, select it, and then click again to fill it in you'll see Magic Fill has two options for you. One is to fill in the single item here. So it gets the name Kevin from this cell based on how the cell above this gets Megan from this cell. You can just select that and it will fill that one cell in. But you could also go to Magic Fill Columns and it will fill in the entire column. If we use that here it's going to show us what it added. You can see the striped cells here showing you it added those nine cells and what it put there. It gives you the opportunity to accept it or reject it. It also gives you an option whether to add as text or as formulas. We'll look at formulas here in a minute. But for now we can just accept this and we were able to fill in this entire column very easily.

The same thing here. If I click twice on this cell you could see it predicts what the value of this should be based on A3 and based on C2. I can say fill in the whole column with that and Accept. Now I've easily filled in first and last named columns here based on the info in the first column. 

Now another way to do this would have been to select the cells, like all of them like this, and then you can go to Table and then Magic Fill Cells. Or you can Control Click, right click, or two-finger click to bring up the Context Menu and find Magic Fill Cells there. You can use this and you get the same Accept or Reject. It's going to do both columns at the same time since we have them selected. I could also have selected the entire column like this and then used Magic Fill there as well. 

If you find these videos valuable consider joining the more than 3000 others that support MacMost Through Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

Now it's not just about getting, say, the first word or second word. It's a little smarter than that. So, for instance, I've got now last name comma first name here. If I wanted to grab that I could just give it the example that it needs, like this, and then it should figure it out. Not including the comma at all. So if I Magic Fill here you can see how it does it. Another trick is you can partially use Magic Fill now and then use it more later. So I can select just these cells and then I can use Magic Fill Cells like this and Accept. Now I can go and hover over the bottom of the selection. I can drag it down to continue filling in more rows. So this is handy if you have to add more like this. You can now select the rows like that and Magic Fill down one more. 

Now it's not always perfect. For instance here I want to remove the middle initial, which is only in some of these rows. So I've given it two examples and if I select this I see that I can get Magic Fill and will try to guess what I want. But it doesn't figure it out for all of them. Only three more of those names actually fit the pattern that was there before. This is actually good because I can actually accept this and then go to the rows where it can't match those and do those manually. So it still saves me some time. But then sometimes it is just impossible. Like you've got names like this where there is all sorts of different variations on the number of words and initials and things like that. You'll find here if you try to use Magic Fill it's not going to work. It's going to give you maybe just some here for the first column and it's going to get some of those wrong because it doesn't quite understand names. It's not really going any deeper than looking at the patterns in the text and trying to replicate those patterns for each row.  

But you can take data from multiple columns and combine them into one cell using the same thing. So, for instance, I can create a full name column here and give it an example. When I double click on it I get nothing here. It's telling me that it can't figure out any kind of Magic Fill function for it to do. But if I give it a second example then if I try to use Magic Fill here it will figure out what I wanted to do. So we needed two examples in this case to figure out it's going to combine these like that. If on the other hand I had tried to do something like this and the same thing here and cleared these out I could get it to Magic Fill in a different way, like that. So you can see it's a little bit more complex than just taking data from one cell to another. It can actually take data from multiple cells and combine them. 

It can also do conversions. So here I've got first, last and I want to do last, first. So let me give it one example here and then if I double click there I don't get anything so I'm going to give it a second example, like that, and now if I select the whole column and Magic Fill you'll see it got the idea. So it's actually converting from one to the other in this case. This is taking a cell that has multiple lines in it and it's a full address. Then I want it to look like this where it's taking the first name, last name, city, state, and zip out. If I try to do Magic Fill here it will kind of work, but not quite. So you can see here it got the state wrong. What's probably happening, as you can guess, is this is a two word city name whereas this is a one word city name. So let's fix this and give it the exact right thing, like that. Now that it's got two examples there I can select all the rest of the cells and use Magic Fill. It will get them all right. 

You can also do this in reverse. So here I have everything in its own column. I've given two examples here of how to combine the address. I've created the line breaks by using Option and Return by the way. Now if I select all of these and I go to Magic Fill Cells it figures it out and gives me these nice addresses. 

Here's another example of what it can do. Here I've got names and I want first name and last name but I don't want them to be all capital letters like this. Unfortunately the data came in like that. So I will give it an example that just has each name capitalized. If I try to use Magic Fill for this it doesn't get it. A second example is very often needed for it to get things right. Now if I do it you can see it gets it right. Gets the first name, last name, and does the case like I want. 

You can do various other things. Like, for instance, here are a bunch of email addresses. Say I wanted the domain name for each of those. So I'll just use this first one here as an example like that. Now I can Magic Fill and it figures out what I really want. Here are a bunch of file names. Let's see if it can get the idea with just one of those selected. You can see, no, it didn't. It just took the number of characters from the end. So let's give it another example here and let's see if now it gets what I want. It is getting closer but still doesn't quite seem to get it. So let's continue to give it some examples. Now let's see if it gets it. Now finally it seems like we've got the right thing. Even here where there's no extension because that's a folder. It doesn't put anything. Sometimes you've got to go more than just the two examples. It needs to have enough variation here and what's going on to figure out what you want. 

Here's one that surprised me that it worked. So I've got these little numbers here. Could be part numbers for something. There's letters and numbers. So if I give it some examples, like this, and I try to Magic Fill here it figures out that I want all the letters here on the left and the numbers on the right. This one doesn't have any numbers so it is not sure what to do with it. Probably if I gave it an example like this it would figure it out. 

Now let's go back for a minute and take a look at the other options you've got when you do Magic Fill. If I select all of this and I do Magic Fill I can accept it here and it just puts those names in there. But if I do it and I add another name then you can see it doesn't fill this in automatically. I have to manually go ahead and drag down on the autofill there to do Magic Fill. 

But look what happens if instead of that I use the Option here to Magic Fill as formulas. So I accept this and it looks the same. But if I look at the actual value at the bottom left you can see now it has converted these to formulas. In this case it is looking for the text before the first space. Then this one is the is the text after the first space. It's doing it for each one of these. It's got a formula in each one, not the actual hard coded thing. That's important because if I were to change something here, like that, you can see it updates. It wouldn't update if there was actually the name that was in there. Furthermore, if I were to add another row here, like that, and then press return you could see it fills it in. That's because this cell and this cell have that formula in it that's automatically put in when you add another row by pressing a return there. So it is often more useful to have the formula, but also this gives you a peek behind the curtain. You can see exactly what it's doing. If we go over here, for instance, and we say, yeah Magic Fill this but give us the formulas like that. Now I can select one of these and I can see what it's doing. It's giving us the text before the comma. In this case the text after the space. Interesting. So you can see how it might mess it up if there's a missing space there for instance. 

Let's look at this one here. If I use Magic Fill here and switch to As Formulas you can see here what the formula is going to be before you even insert it since it's just one column. So it's going to concatenate the B column, put a comma, and then a space, and then put the A column there. Here if I go ahead and use Magic Fill with the formulas it's using a regular expression to look for the characters before the first number. Here we're choosing a similar regular expression to just get the numbers on the end. 

So Magic Fill in Numbers is an interesting feature. It will work in a lot of situations and when it does it can save you a lot of time. But it won't work in all situations. Whenever there's a little variety in the data then you might run into some issues. If you're good with formulas you probably don't need it. You can do this all by creating your own formulas, but it doesn't mean it can't save you time in some situations. Try a few examples of your own in a sample Numbers document so you get the hang of it, so you know how to use it when you need it. Hope you found this useful. Thanks for watching.