Intermediate Formulas in Numbers

You already know what Numbers is. You already know how to build basic spreadsheets with the built-in templates, or how to use basic functions and formulas in Numbers. But, to be honest, you have just scratched the surface of what Numbers can really do.

For the low price of $20, or free with a new Mac, Numbers is a compelling alternative to Excel and other spreadsheet apps. And yet, when it comes to serious number or data crunching, most people automatically assume you must have Excel.

Nothing could be further from the truth. Numbers is a powerful spreadsheet app, and with its recently re-added AppleScript support, you can do far more with Numbers than you've ever imagined. In this tutorial, I will demonstrate some deeper data and number crunching techniques with Numbers, including:

Working with multiple sheets
The Vlookup function
IfError statements to keep things clean
Using Applescript to add a prompt

Put all that together, and you can turn a Numbers spreadsheet into a customized contact database app, or a database to track almost anything else you'd like. Let's get started!

The Setup

I'll begin this tutorial with a basic list of fake data. This list will contain 300 people, with their name, age, and email addresses listed in respective columns. You can make your own spreadsheet with your own sample data, or go ahead and add your real contacts info, to use with the tutorial. Your spreadsheet should look a bit like this:

Setting up a workbook with two spreadsheets

Working With Multiple Sheets

Many beginner users only use one sheet while working in Numbers. They try to pack all of their data, numbers, charts, and anything else they need into one limited space. This will keep you scrolling up and down and side to side all day.

There is a better way: multiple sheets. Multiple sheets give you the freedom to keep your data separate from your charts or other info, and can help you keep your sanity while working with larger datasets.

You can create multiple sheets from within the same workbook by clicking on the + sign in the top-left corner of the spreadsheet. Then, it's wise to re-label each sheet, so you won't lose track of its purpose. Simply double click on each sheet's name (currently set to Sheet 1 and Sheet 2), then replace the names with data and search tool, respectively.

Breaking your workbook up into separate concerns and properly naming each tab creates a much more scalable design that will stand the test of time. It is important to think about the amount of data that your workbook will grow into, not just the amount of data it will currently hold. That will keep your spreadsheet ready to serve you far into the future.

The Vlookup Function

Now that the Search Tool tab has been created, let's create a simple search interface. The goal is for the user to only have to enter a name to find a contact's information. Upon doing so, the sheet will return the name, age, and email of the person they entered.

After some basic styling, swapping of sheet order, and removing of cells, your search tool sheet should look something like this:

The Vlookup function will automatically fill in cells B4, C4, and D4. To begin building your search tool, place your cursor in cell B4 and press the = key. The function menu will automatically come up. Begin typing Vlookup until Numbers shows that as the guessed function. Hit Return, and the formula template will fill in and ask you to provide the four parameters it requires:

search-for: This is value you want Vlookup to search for (example: "Alex Spencer").
columns-range: This is the area/table you want to run the search on (example: Columns A through E).
return-column: This is the column number you want returned (example: Column 2).
close-match: Whether you want the closest match to return, or require an exact match.

In the case of this tutorial, you want search-for to be whatever is put in cell B1. You then want the search range to be Data Sheet's Name:Email. For column range, we want the person's name to appear. Names are listed in the first column, so enter 1. I would recommend setting close-match to exact-match/false. The resulting formula inside B4 should look like this:

Repeat this process, placing Vlookup formulas in cells C4 and D4. The only part of the formulas you will change is the return-column. Cell C4 is expecting the age of the person, and ages are listed in the second column. So your return-column should be set to 2. If you guessed that return-column would be set to 3 in the Vlookup for cell D4, you guessed right!

NOTE: You will see red warning signs in the vLookup cells after you created the formulas. This is because your search cell (B1) is probably empty.

The IfError Formula

Those red warning signs can be alarming. So let's create some statements that say, "If this Vlookup formula returns an error, make the value of this cell blank. But if it doesn't return an error, show the result of the Vlookup."

Double click on the cell B4 to expose the full formula, then copy all of it by highlighting it and pressing Command-C. Once you have it copied, clear the cell back to the = sign and start a new function called IfError. IfError only requires two parameters:

any-expression: This is the formula or expression you want evaluated to see if it returns errors.
if-error: This is what you wish to return if there is an error. If there isn't an error, the expression/formula will simply run.

The expression you want Numbers to evaluate is the Vlookup formula you just copied. So paste that in as the first parameter. If there is an error, you probably just want a blank cell. So put in " " as the second parameter. Once both parameters are put in, the formula will look like this:

Repeat this process for cells C4 and D4. Goodbye scary red error signs!

Lastly, select all of the cells on the sheet. Go to the Arrange tab in the Format inspector. Then click the Lock button to prevent anyone from changing any of your formulas.

Using Applescript

Locking every cell on the Search Tool data sheet may keep your formulas safe, but it also makes entering a name to search impossible. To fix this issue, you will need to leverage the wizardry of AppleScript.

AppleScript has been around for years, but support for AppleScript in Numbers has just recently returned. If you haven't ever used AppleScript before, you can just follow along with this tutorial to make the AppleScript you'll need for your Numbers database, but I'd recommend learning more about with our Beginner's Guide to AppleScript Part 1 and Part 2.

To get started adding AppleScript to your Numbers spreadsheet, launch the Automator application. Then, when a file picker comes up, choose New Document, then select Service when asked what type of document to create.

Your cursor should be sitting in the Actions search bar. Start a search for AppleScript. When it comes up, choose Run AppleScript. The window should now look like this:

In the editor window, place the following where you see (* Your script goes here *):

      tell application "Numbers"
        display dialog "Please enter a name:" default answer "first_name last_name"
        set theName to text returned of result
    
        tell table 1 of sheet 1 of document of the front window
          set value of (cell "B1") to theName
        end tell
      end tell
    

Even though AppleScript reads better than any other scripting language out there, let's go through this line by line.

Line 1 is starting all of the instructions we want to do the Numbers application.
Line 2 is creating a pop-up dialog and asking for an input from the user. It also makes the default input "first_name last_name".
Line 3 creates a variable and sets its value to whatever the user enters in the dialog.
Line 4 is blank.
Line 5 chooses the table, sheet, and window you want AppleScript to target within Numbers.
Line 6 sets the value of the target cell to the variable that captured the user input.
Lines 7 and 8 close their respective tell blocks.

In the menu above the script editor, change Service receives to no-input in Numbers. Your final window should look like this:

Lastly, save this service as Run Search. This will allow your users to access and run the service from within Numbers. So, when it comes time to run a search, your user will go to Numbers, then Services, then Run Search.

That'll open a dialog asking for the name you want to lookup — just type the name, then press Return or click OK.

Entering a name in the prompt

And now, you'll see your spreadsheet in action, bringing up the contact info from your original spreadsheet based on your search.

That's only an example of the power of Numbers, with its formulas, functions, and multiple sheets, combined with AppleScript and some simple coding. You could use it to make a similar spreadsheet to quickly find any data you want to organize, or you could use these basic tools and other functions and more to turn Numbers into a customized app for anything you need to accomplish.

Conclusion

There is no doubt in anyone's mind that Numbers is a terrific application for creating basic spreadsheets or charts. But it's far more than that. In this tutorial, you've seen how Numbers can be turned into a customized contacts database app, complete with simple lookup to find the info you need. With those tools and more of the power under the hood in Numbers, combined with its newly added support for Applescript, there are endless possibilities for what you can do with Numbers.

Go try it out for your own data and number crunching needs, I'm sure you'll be able to find a ton of ways to put Numbers to use that you'd never imagined before!