25 Things You Didn’t Know You Could Do With Mac Numbers

Here are a variety of functions, features and tips for Mac Numbers that many Numbers users don't know about.

Video Transcript: Hi, this is Gary with MacMost.com. Today let me show you some things that you might not know that you can do in Numbers.
MacMost is brought to you thanks to a great group of more than 800 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So let's start off with Stock Quotes. Stock Quotes has been around for awhile in Numbers but some people still don't know they are there. All you need to do to get a stock quote is use the Stock function. Just give it the symbol, like APPL for Apple, and you get the previous market day's close for that stock. So it's now a live to the minute stock value. It's just the daily value. If you look at the function help for Stock you'll see there's a lot more that you can do with it. It just isn't price. You get things like Change, Open, High, Low, Market Cap, Volume, Yield. All sorts of things. Also there's a StockH function to give you stock history. So in addition to the stock value and what you want, like Close or Open or Volume, you also give a date. Make sure it's a date that the market was actually open. So not a Saturday or Sunday or holiday. 
Now you can also do Currency Conversions. So you can use the Currency function to convert from one currency to another. So in this case USD, for US dollars, to EUR or Euros. It shows that the one US dollar is 0.82 Euros accurate to the current day. If you look at the Help here you'll see that the values come from Yahoo Finance. So if you don't know the correct three letters used for a currency go to the Yahoo Finance website and look it up there. The same thing for stocks. If you need to get some sort of fund or a foreign stock find out how Yahoo Finance represents that stock and use that same thing in the Stock or StockH function. There's also, as you can see here, a currency convert function. That one takes a value that already has a currency in front of it, like this one here, Euros and it converts it to another currency, USD here, and it gives us our one dollar back. 
Now a lot of people don't know that you can also work with Durations in Numbers. Durations are represented with a single letter to represent the unit. So, 4h 38m is
 4 hours and 38 minutes. You can add and subtract durations. So you can add these two durations together to get 7 hours and 23 minutes. Here we're adding three weeks to two days and 17 hours plus 10d and 10h to get 4 weeks, 6 days, 3 hours. Notice that this is just simple addition here. No special functions are needed. To create these durations you just need to type them exactly like this. Type 4h space 38m and you'll get a duration. Now you can also subtract dates to get a duration. It doesn't make sense to add dates but it does make sense to subtract them. So if you subtract 
June 4, 2021 from March 28, 2021 you get a duration value. 68 days. Here I'm subtracting NOW, which is a function that will return the time right now, from June 5, 1975 and I get 16687 days. So if that's your birthday that's how old you are in days.  
Now you can also Add and Subtract durations from a date. So you can take a date like March 28, 2021, add ten days to it, just simple addition, no function needed and you'll get the date ten days from that current date. 
There are some special functions though to deal with Work Days. Workdays would be weekdays, basically, not including the weekends. But you could also exclude certain holidays. So between January 1st and December 31st there are 261 work days. This function is three words. NETWORKDAYS. Not network days. You could see here it takes a start date and an end date but also a list of excluded dates. So these would be the extra holidays. It uses a range for that. So, for instance, in the example here you could see it's taking two cells there that include two different dates to exclude. But you could create a whole little table with all of your company's holidays and then include that in here as a range and it would also exclude those days. 
Now the Workday function, which you could see here, that will allow you to take a date, add a certain number of workdays to it to give you a final date. So in other words ten workdays from March 1st is March 15th. You could also include extra holidays in here just like with NETWORKDAYS.
Now there are two different ways to get random numbers in Numbers. One is to use the RAND function which gives you a floating point value between zero and one. But you could also use RANDBETWEEN and give two values and it would give a number, in this case between one and six. Now these numbers will change every single time you change a cell in the table. It recalculates everything which means generating new random values. But you could always select a cell, copy it, and then Paste Formula Results to make that number permanent.
Now here's a really interesting function. ROMAN. It will convert a regular number to Roman Numerals. So 378 is CCCLXXVIII. 2021 is MMXXI. 
Here's an interesting function. Hyperlink. Hyperlink allows you to create a link from a bunch of characters. So you can actually combine things and dynamically create links. So in this case I'm going to combine HTTPS:// with the value of this cell and then append a slash there and then the value of this cell. Then I'm going to give it the text of  link with an exclamation point. So you can see here this is what it looks like. If I click on that it will launch the browser and go to that page. I can now change this link by changing one of these two values here. Here's another example. I can create a hyperlink that goes to the Goggle Finance Page for a stock. All it's doing here is inserting the value of this cell here. The rest of this is just taken by going to Goggle and looking at how their finance page URL's are formed. I also then take the same value there and attach the word Stock INFO to it to make the text. So here I've a nice link that goes to Goggle Finance for Apple's stock. If I change this to something else then this link would go to that stock. 
You can get the most common value in a set by using MODE. So in this case here at the bottom I've got example data. I've got in this first column here a bunch of numbers, just between one and six. So MODE is taking the entire column A there and it's telling me the most common value found there is 2. It could also get the RANK. So the Rank will take a look at all the values there and tell you where a specific value is ranked. So in this case it's looking at Column B and the first value and saying where that ranks in all the values. So here you can see I've got all these values here in Column B. The first one is 550. So 550 actually ranks seventh. It's the 7th largest value in that set of values. 
Now Merge can also do conversions. So you can just use the Convert function and then a variety of different units. So in this case taking the number 100 and converting it from miles to kilometers gets me about 161 kilometers. Here I'm converting 24 degrees Celsius to Fahrenheit. Here I'm converting 8 cups to gallons. If you look at the help here for the Convert function you'll see all the different units and exactly how to represent them. There are a ton of different things from distance to energy to speed. All these different things that you could use to convert from one unit to another. 
So here's some more math. You have least common multiple and the greatest common divider. So here between 18 to 12 the greatest common divider is 6. Between 8 and 6 the least common multiple is 24. You see you include a range here. So my range just says two values. But you can have a set of twenty values and it will still find the least common multiple or greatest common divider in that set. 
Here's the Choose function. This allows you to show a value based on a little list of values. So here I have the planets and I'm looking at this value here for the fourth value in this list is Mars. If I were to change this to five it would change to Jupiter. I've actually made this the cell format stepper with the minimum of one and the maximum of eight. So I could increase or decrease this to get a value that's here. 
To create a link what you do is select text inside of a cell. You can go to Format and then Add Link, Edit Link, or Remove Link here. Or just Command K. You can link to a webpage. You can start an email from a Link. But you could also, in Numbers, link to a sheet. So you can have a link like this that goes to a sheet. You can see my second sheet here is called data. If I click on this link it jumps to the Data Sheet. So if you had a summary sheet at the beginning and then you had a whole bunch of different sheets with a bunch of data on them you could include links to jump to the proper sheet so you could quickly get to that set of data. 
Now some other things about sheets. You can name sheets. I still see a lot of people that have sheet 1, sheet 2, sheet 3. But if you click on this little button here for Sheets, you can rename a sheet and call it whatever you want. So come up with a descriptive name that will help you find what you need inside of your document. Now when you go to a Sheet like this one, click here, and then you say Show Sheet Option, you go to the Format Sidebar. One of the things that you can do in the Format Sidebar is set a background color for the sheet. That could really help you differentiate the different types of data. As a matter of fact you could even use that same color, maybe, in cells here to make it easier to visualize where all of your data is stored inside the document. Notice that you can also jump to a table in a sheet. So I could click here to jump to Sheet 1 but I could click here and it shows me all the tables in the sheet. So if I want to jump right to the part in the sheet that has the example data table, I click here and it opens up that sheet and jumps right to example data.
Now here's something that's pretty cool. You can have fractions inside of Numbers. So, here I've got what you would expect, decimal places. 0.5, 14.125. I could actually show those as fractions. You don't use Functions at all. It's not even a formula. It's a Format. So here I have exactly the same values but instead I have them shown as fractions because under Format, Cell I've chosen the data to format Fraction. I can set an accuracy or lock it into quarters or eighths or sixteenths or something like that. You could see it represents this as ½ for one-half. It represents 14.125 at 14 1/8.  
So a few more tips. One is that you might know that you can resize a column by dragging the line in-between. But you can have a column width be automatically set to the size of the data in it by double clicking that line. You can see how it snaps to the size that's needed. 
You can transpose a table. Let's look at this sample data here. I can select this table here, go to Table and at the bottom Transpose Rows and Columns. You could see how it's going to switch the rows and columns in that table. It's really handy for when you've entered a bunch of data and realize that you've kind of done it in the wrong way. 
Now getting around in a large table can be difficult. But if you have a keyboard that has the Page Up, Page Down, Home, and End keys you can use those to get around. But, of course, most keyboards especially MacBooks don't have these keys. But you actually still have them. You just need to use the fn key. fn and down arrow is page down. fn and up arrow is page up. fn and the right arrow is end. fn and the left arrow is Home. So that makes it much easier to jump around in a large sheet. 
You may already notice that when you select a bunch of cells at the bottom you'll get some quick calculations. Like here's the sum. Here's the average. The minimum/maximum and the count, the number of cells selected. But there's a little button over here. Click that and you have a whole bunch of different functions you could add to this list. So, for instance, if you wanted to add the Median value you could simple click there like that. Then the median value is shown here. You could also uncheck some of these if you don't need to see those and make more room.
So this just scratches the surface. I'm not really big into statistics and I'm definitely not that knowledgeable about financial things. There are a whole bunch of different functions that allow you to calculate interest rates and earnings and things like that. So take a look through all the functions in the Functions Help, especially in the finance and statistics categories, to find out more things that you can do. 
One final thing I want to show you. That's how I do this column here that shows the formula. You can see here's the formula in the cell and I have that represented here. I'm using the formula text function for that. Use Formula Text. Give it a cell and it will show a text representation of the formula that's in that cell. It makes it really easy to do tutorials like this for any situation where you want to visually see the formula that you're using. I'l be sure to include this entire Numbers spreadsheet with this post at MacMost.com so you can checkout all of the different functions and how they're setup.

Download the example file.

Comments: 10 Responses to “25 Things You Didn’t Know You Could Do With Mac Numbers”

Eduard Nebbeling

3 years ago

Thank you very much for this topic, it's really helpful when working in Numbers.

Sue Dickie

Very timely. I just looked at the Stocks template yesterday and couldn’t figure out how they had done some calculations. After poking around, I found ‘Previous gain’ in a hidden row. Your video has now shown me more things that I wasn’t aware of, and some will be helpful. As an aside, I wish Apple annotated their templates to explain how they were pulled together.

John R Carter Sr

You just blew my mind with this.
I'm using Numbers at the very basic level of just showing tabular data.
I knew that there were many formulas available, and I've used some of the math functions. But this goes beyond anything I would need to do! Still, fun to know.

Ray Johnston

Very helpful, Gary. Do you have a way to generate a value for "yesterday" which also skips over holidays? I'd like to use that in a spreadsheet to look at yesterday's closing stock value, since todays closing value won't be correct until after the close.

Gary Rosenzweig

Ray: Look at WORKDAYS in the help. You can use a value like -1 and then add your holidays in to get the previous work day.

Ray

Tried that: =WORKDAY("1/1/21",-1,"1/1/21") returns 12/30/20, not 31. Also tried =STOCK(TSLA, previous close), but that seems to return value for two days ago, according to =STOCKH(TSLA,close,"2/18/21") {today's the 20th}.

Ray: WORKDAY(“1/1/21″,-1,”1/1/21″) probably returns 12/30/20 because you are eliminating 1/1/21 and also going back one more day as well. The other one makes sense because you are asking for the "previous" close, not the current price.

Thanks, Gary. For my personal finance tracking, those answers work well enough. I think I'll use the =STOCK( ticker,previous close) function for just a quick look, and perhaps the =WORKDAY( date,-1) function (no holidays) and =STOCKH( ticker,close,date ) for trend tracking.

Joe Pustai

After watching your Hidden Mac Numbers Tips I successfully inserted several links (to other places in the same workbook). This was simple and easy to do and worked well for my rather extensive financial spreadsheet (thank you). As of today I can no longer insert a link. The three link options are showing up but are no longer selectable (Control K will not work either). I even updated my OS and my Numbers is the most recent but I cannot seem to be able to link any more.... how can I fix that?

Joe: Are you selecting some text in a cell? You can't just select the cell.

Comments Closed.

25 Things You Didn’t Know You Could Do With Mac Numbers

Comments: 10 Responses to “25 Things You Didn’t Know You Could Do With Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information