Creating Simple Database Lists In Mac Numbers

Not all Numbers spreadsheets need to have complex formulas and perform calculations. Some of the most useful spreadsheets simply help you keep track of members of a group or items you own. Create a simple database like this is easy and can come in handy, especially if you save it to iCloud Drive for mobile access as well.

Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how to use Numbers to keep track of a simple list of items. Like members in an organization or perhaps the books that you own.
MacMost is supported by more than 600 viewers just like you. 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 know when I usually talk about Numbers I'm talking about functions and formulas and calculations that you could do. But it doesn't have to be that complex. Numbers is the best app for keeping track of lists of items. Let's say you want to keep track of members in an organization. It could be a team, it could be your company, it could be a church group. You just want to have a list where there are names, contact information, and other notes about the members. Or maybe you want to keep track of all of the DVD's you own or books you own. You can use Numbers to do this and you don't need to get into anything complex like functions or formulas at all.
So the first example is going to be a simple membership list. We'll start with a blank template here and use the default table to get started. Now tables are made up of cells. You select a cell by clicking the cells. There are columns and rows. The columns have a letter name and the rows have a number name. So each cell is identified by a letter and a number like B2 or or D7. Notice the first row, row 1, is darker than the rest. This is called a Header row. We use that to label the rows. So, for instance, the first row could be Name, the second row could be Phone, the third row maybe Joined or the Date Joined, and then maybe address, and whether or not the member is Active.
The first column is also darker than the other columns. This is called a Header Column. Usually we have the identifier for the row there. In the case of members this would be the person's name. If we were keeping a list of say DVD's you own it would be the title of the DVD. So let's enter in a name here. When I'm done typing in a cell I can hit the Return or Tab. Return will go to the cell below. The Tab will go to the cell to the right. So when you're entering data across in a row you're going to use Tabs to go all the way to the end. Let me add some sample data.
So I've got the phone number here, I've got the date here, address, and then a number, zero for not active and one for active. Now notice a few things. If I click on the name here and I look at the bottom left this is text and it shows what the text is. If I click here it shows text and what the text is. If I click here it shows Actual which means an actual value in this case a date. So it doesn't represent that as text, those characters. It actually knows that means October 17, 2009. This is text and this is actual. It knows it's a number.
Now I have some extra columns here. I can use the circle with two lines at the end to shrink the table and get rid of the extra columns. Likewise I could use this circle here at the bottom left corner of the table to shrink all the way up to the top so I can get rid of the extra rows. The idea here is the each row is going to represent a different member. When you talk about data bases this is the same as a Record.  Let me enter in some more data. With this cell selected I could hit Return and get a new line and I can start typing in new data.
So now I've got a good set of sample data in here. At the top you can see it says Table 1. There's a special field here at the top of a table that allows you to give it a name. So I'm going to select that and name this table Members. Now some of the dates here look different. I've got 02 here for the year and 2018 here for the year. I want there to be a consistent format for all of these. I'm going to select all of these cells. I can do that by selecting the first cell and Shift selecting say the last cell. But an easier way to do it is to click on the letter at the top of the column to select the entire column. But I don't want this Header cell here. So instead double click on it and it selects only the value cells, not the Header cell. 
Now I can go over to the right sidebar. If you don't see it click Format and then click Cell. You'll see Data Format. It says Multiple because indeed there are multiple formats. But if I select that I can say I want specifically to use Date and Time and I want the Date to be this format here. In Time none. If I put a time there you can see all of them are set to 12 a.m. because the actual value there is a specific time so I want it to be None and just show the actual dates.
I can also use Formatting for other columns for other things. For instance for column E here if I double click on that I select all the values there and instead of using Automatic or None as the format to get zeroes and ones I can select Checkbox. Now what a checkbox gives me is a check where there's a one and no check when there's a zero. To make it even easier I can now click on it to change the value. So instead of having to enter a one or a zero I can do that. The checkbox is one of the special formats that you could use here. It's particularly useful in cases like this.
Now you can also use the Sidebar to change other types of formatting. So for instance if I wanted to change the look of some text I'd select a cell here and go to Text and change the font, whether it's bold or not, the colors, make it italics, all sorts of things. That's for that one cell. If I want to do it for the entire column I just double click on the letter there at the top. Now I have all of those selected and I can make the change. So I can change the text color, say, to something like that. I could change other things like make it centered. Even centered vertically inside the cells.
So this works just like you're working in a word processing app. Now you may notice that these addresses here are multiple lines. But that doesn't work if you actually try to type it. Here's what you get if you try to type it. I'm going to hit Delete to delete the value in there. Then type the street and if I hit return to go to the next line to enter the city you could see it just advances to the next cell below it. Instead of return you want to hold the Option key down and hit return. That will give you a line break inside of the cell. Now you can type a second line and continue to type if you need to. You can type whole paragraphs of things in there. 
I showed you how to select an entire column by clicking on the column letter at the top. You can also select a row by clicking on the number here to the left. Click it once to select and once you've selected it you can click and drag that row up or down to move it. You can also select multiple rows by clicking and dragging this to select a range. You can click once and then Shift click somewhere else to select a range. Once you have a range selected you could click on any of them and move the entire bunch as a group.
Another handy thing to do is to go to Format, Table and turn on alternating row color. This changes slightly the color of each row and it's easier for us to visually then to look across a row of data. Now you can also move columns. So I can move Column C here and I can drag and move that over so Joined is after Address. So it makes a little more sense. I can also Sort pretty easily. If I move my cursor over the top here you can see these little arrows that appear. If I click on one of those arrows I have a lot of options. So I can sort Ascending by column A. It would have made more sense if I had used the last name, first name for the names or a separate column for last name and first name so I can sort by last name. I can sort by date as well. So I can sort here Ascending and see the members by date they joined. If you need to insert something, like add an extra column, there's also an option for that here. Add Column Before or Add Column After. You can also delete a column if you want. The same thing for rows. I can click here and I can add a row above or below this row or Delete a row. 
Here's another example. I've created a very similar looking table. I've titled the table My Books and instead of names I have book titles. Then I've got other columns for Author, Format, date Read, My Rating, and then a column for Notes. I've done some things here with formatting. For instance for  Dates. These dates here are real dates like December 17, 2015 but they display as month and year. So if I look at the Format for all of these I could see I've got it set to Date and Time but the Format set to Month and Year. For Ratings instead of checkboxes I've done Star ratings. So you've got that as an option right there under Checkbox. You can easily set a number between 1 and 5 by clicking in here on the spot with the star. For Format here I've chosen a pop-up menu as the format. You can create this little list here. That way you can easily just click in here and then choose one of the options. 
Notes is a handy column to have and I certainly could have added that to the member database as well. Just add any extra text you need. I can now sort this list by Date Read but I could also sort it by things like Rating. Sorting by Rating Descending would give me my favorite books. 
One great thing about creating Numbers documents for this stuff is that you can get Numbers for iOS as well. So you can have Numbers on your iPhone and on your iPad and then you save this document to iCloud Drive and now you have access to it everywhere that you go. So if you want to see a list of books you own while you're in a bookstore you can bring up your Number spreadsheet. If you want to get contact information for a member in your organization you can bring up that document and look in there on your iPhone while you're out.

Comments: 7 Responses to “Creating Simple Database Lists In Mac Numbers”

John Bedrossian

5 years ago

Very good presentation on data base. Could you show us how to generate mailing labels from a data base. It would be very useful. Thank you.

Gary Rosenzweig

John: That's tough to do. You may want to search the Mac App Store for "labels" to see if anything there works. You can do it easily in the Contacts app, but that assumes you have all of those people in that app. Otherwise, you need to copy and paste from Numbers into Pages and then put a lot of work (and trial and error) into formatting them perfectly to fit your labels. I wouldn't do that unless you really like working on that kind of thing.

thank you Gary, I used to print labels in MS Access, but i don't have that on the mac.

Marilyn

My MacBook Pro doesn't have Pages. Where do I get it? Did Microsoft for Mac override it?

Marilyn: You can get it in the Mac App Store. Microsoft Word is a different app from a different company. One app doesn't overwrite another like that.

Dick Cournoyer

I use Orion Print & Label . It’s not on the Mac App Store and costs about $15

Procyon

4 years ago

You can easily print labels or other address documents directly from Contacts.

Comments Closed.

Creating Simple Database Lists In Mac Numbers

Comments: 7 Responses to “Creating Simple Database Lists In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information