Repeat for as many names as you’d like to define. Type the name you’d like to create in the first box, then click Add. Now it’s a lot clearer what’s going on, and you no longer need to remember that cell C5 is the annual interest rate.Įxcel: Select the cell or range you’d like to name, then select Insert > Name > Define, which will pop up a new window. Here’s the same formula using named cells: =PMT(INT_RATE/12,TERM,LOAN_AMT). But really, it’s not easy to discern what this formula is doing. Just by reading it, you can probably guess that it returns a payment of some sort, and maybe you can tell that cell C5 contains an annual interest rate. If you name cells (and ranges), however, you can make the formula easier to read, as well as make reusing those cells in other formulas easier.Ĭonsider this formula as an example: =PMT(C5/12,C6,C7). It also means you need to remember the location of often-used cells, which can be tricky in a large spreadsheet. Referring to cells by location may be convenient, but it can also make it hard to figure out exactly what a given formula is doing. But while this method works, I find it more time-consuming than simply typing the dollar signs where I want them to be. But if you’re selecting cells with clicks and drags, Numbers has another way of switching between relative and absolute addressing.Ĭell references added via clicking and dragging appear in small colored bubbles, with a triangle to the right you click the triangle to pop up Numbers’ absolute/relative cell-addressing window. If you’re typing cell addresses directly, all three apps let you simply type the dollar sign manually. Similarly, A$10:A$20 would lock the rows copy this formula over one and down 50, and it would change to B$10:B$20. You can also lock only one direction: $A10:$A20 will always refer to column A, but if you copy the formula over one column and down 50 rows, it would change to $A60:$A70. Numbers’ absolute/relative cell-addressing window.
So instead of typing A10:A20, for example, you type $A$10:$A$20 to create a fixed formula that always refers to those cells, regardless of where you put it. All three apps use the same symbol for creating one: a dollar sign before the row and/or column symbols in a formula. An absolute address doesn’t change when copied to a new location.
If you don’t want the cell references to change when you copy or move a formula, all three apps offer a mode called absolute addressing. This is called relative addressing, as the functions’ contents are relative to where they’re placed it’s the default for formulas in all three apps. Spreadsheet apps are also quite smart if you copy =SUM(A10:A20) and paste it into the column to the right, it will automatically change to =SUM(B10:B20). You can enter these cell locations either by typing them or by clicking (or, for ranges, clicking and dragging) the mouse. As in Excel, you can create custom number formats that mix text and numbers-but you have to find the option first, as it’s buried in the Format > Numbers > More Formats submenu. Sheets: All number formats can be found in the Format > Number menu each formatting option appears in its own submenu. Numbers offers a bunch of specialized number formats, including Slider. (Numbers also includes special number formats such as Slider, Stepper, Pop-up Menu, and more these can be used to create intuitive data entry forms.)
You may need to set other values: For example, if you choose Numeral System, you’ll need to set values for Base, Places, and how to represent negative numbers. Select the option (Automatic, Number, and so on) you want to use from the pop-up menu.
Numbers: Click the Format icon (the paintbrush) in the toolbar, then select the Cell entry in the resulting sidebar. Your cells will still be treated as numbers for use in calculations, but they will display with the defined text. For example, a format of #,#0.00 "widgets" would format your number with a comma if needed, two decimal places, and the word widgets after the number.
The Custom option (recently added to Numbers as well) is especially useful, as you can combine text with your formatted number. All number formats are listed down the left edge of the dialog box select one, and its options appear on the right. You can also use the Format > Cells menu, then click Number in the dialog box that appears. Excel: Many often-used number formatting options are visible in the Home ribbon.