Numbers ’08 User’s Guide
K Apple Inc. © 2008 Apple Inc. All rights reserved. Under the copyright laws, this manual may not be copied, in whole or in part, without the written consent of Apple. Your rights to the software are governed by the accompanying software license agreement. Apple, the Apple logo, AppleWorks, ColorSync, iMovie, iPhoto, iTunes, Keynote, Mac, Mac OS, Numbers, Pages, Quartz, and QuickTime are trademarks of Apple Inc., registered in the U.S. and other countries.
1 Contents Preface 16 Welcome to the Numbers User’s Guide Chapter 1 18 18 20 21 21 21 22 22 23 24 25 25 26 28 28 29 29 30 Numbers Tools and Techniques Spreadsheet Templates The Numbers Window Spreadsheet Viewing Aids Zooming In or Out The Sheets Pane Print View Alignment Guides The Styles Pane The Toolbar The Format Bar The Inspector Window Formula Tools The Media Browser The Colors Window The Font Panel The Warnings Window Keyboard Shortcuts and Shortcut Menus Chapter 2 31 31 31 33 33 34 34 35 35
Chapter 3 4 36 36 37 37 38 38 39 39 40 41 41 42 42 42 43 Saving a Copy of a Spreadsheet Closing a Spreadsheet Without Quitting Numbers Using Sheets to Organize a Spreadsheet Viewing Sheets Adding and Deleting Sheets Reorganizing Sheets and Their Contents Changing Sheet Names Dividing a Sheet into Pages Setting a Spreadsheet’s Page Size Using Headers and Footers Arranging Objects on a Page Setting Page Orientation Setting Pagination Order Numbering Pages Setting Page Margins 44 44 45 45 45 48 48 49 49 50
62 63 63 64 65 67 68 68 69 69 70 70 70 71 72 72 73 73 74 74 74 75 76 76 77 77 78 Using the Date and Time Format Using the Fraction Format Using the Scientific Format Using the Text Format Using a Checkbox and Other Control Formats Monitoring Cell Values Adding Images or Color to Cells Autofilling Table Cells Working with Rows and Columns Adding Rows Adding Columns Rearranging Rows and Columns Deleting Table Rows and Columns Using a Table Header Row or Column Using a Footer Row Hiding Rows and Columns Resiz
86 87 87 88 88 88 89 89 90 90 91 91 91 92 93 93 94 94 95 96 96 Chapter 6 6 98 98 101 102 103 103 103 104 104 105 105 105 105 106 106 107 107 107 108 108 Performing Instant Calculations Adding a Quick Formula Performing a Basic Calculation Using Column Values Performing a Basic Calculation Using Row Values Removing a Formula Using the Formula Editor Adding a New Formula with the Formula Editor Editing a Formula with the Formula Editor Using the Formula Bar Adding a New Formula with the Formula Bar Editing
Chapter 7 109 109 110 110 112 112 113 113 113 113 114 114 114 115 115 115 115 116 116 116 117 118 Using Axis Titles Showing Data Point Labels Formatting the Value Axis Placing Labels, Gridlines, and Tick Marks Formatting the Elements in a Data Series Formatting Titles, Labels, and Legends Adding Descriptive Text to a Chart Formatting Specific Types of Charts Pie Charts Selecting Individual Pie Wedges Showing Series Names in a Pie Chart Separating Individual Pie Wedges Adding Shadows to Pie Charts and Wedg
126 126 126 126 127 128 128 129 129 130 131 131 132 133 133 133 134 135 135 135 136 136 136 137 137 137 138 139 140 141 141 141 142 143 143 143 144 144 145 145 146 8 Changing the Paragraph Background Color Using the Font Panel Creating Shadows on Text Using the Font Panel Adding Accents and Special Characters Adding Accent Marks Viewing Keyboard Layouts for Other Languages Typing Special Characters and Symbols Using Smart Quotes Using Advanced Typography Features Adjusting Font Smoothing Setting Text Alig
Chapter 8 146 146 147 148 Checking for Spelling Mistakes Finding Misspelled Words Working with Spelling Suggestions Finding and Replacing Text 149 149 150 150 150 151 151 151 152 152 153 153 154 154 154 155 156 156 158 158 159 159 160 160 160 161 162 164 164 164 165 166 166 167 167 168 168 Working with Shapes, Graphics, and Other Objects Selecting Objects Copying or Duplicating Objects Deleting Objects Moving Objects Moving an Object Forward or Backward Aligning Objects Aligning Objects Relative to Each
168 169 169 170 171 171 172 172 173 173 173 174 176 177 177 177 178 Editing Single and Double Arrows Editing a Star Editing a Polygon Using Media Placeholders Working with Images Importing an Image Masking (Cropping) Images Cropping an Image Using the Default (Rectangular) Mask Masking an Image with a Shape Unmasking an Image Removing the Background or Unwanted Elements from an Image Changing an Image’s Brightness, Contrast, and Other Settings Using PDF Files as Graphics Using Sound and Movies Adding a
Chapter 12 192 192 Predefining Sheets for a Custom Template Step 5: Save a Custom Template 193 193 195 196 198 198 199 200 201 202 204 205 205 205 206 206 207 207 208 208 209 209 209 210 210 210 211 211 212 212 213 213 214 214 215 215 216 216 217 Dictionary of Functions About Functions Date and Time Functions Financial Functions Logical Functions Information Functions Reference Functions Numeric Functions Trigonometric Functions Statistical Functions Text Functions Function Descriptions ABS ACCRINT ACCR
217 218 218 219 220 220 221 221 222 223 224 224 225 226 226 227 227 228 228 229 229 230 230 230 231 231 232 232 233 233 234 234 235 236 236 237 237 238 238 239 240 12 COS COSH COUNT COUNTA COUNTBLANK COUNTIF COUPDAYBS COUPDAYS COUPDAYSNC COUPNUM COVAR DATE DATEDIF DAY DB DDB DEGREES DISC DOLLAR EDATE EVEN EXACT EXP FACT FALSE FIND FIXED FLOOR FORECAST FV GCD HLOOKUP HOUR HYPERLINK IF INDEX INDIRECT INT INTERCEPT IPMT IRR Contents
240 241 241 241 242 242 243 244 244 244 245 245 245 246 246 247 248 248 248 249 249 250 250 250 251 251 252 252 253 253 254 254 255 255 256 256 257 257 258 258 259 ISBLANK ISERROR ISEVEN ISODD ISPMT LARGE LCM LEFT LEN LN LOG LOG10 LOOKUP LOWER MATCH MAX MAXA MEDIAN MID MIN MINA MINUTE MIRR MOD MODE MONTH MROUND NOT NOW NPER NPV ODD OFFSET OR PERCENTILE PI PMT POISSON POWER PPMT PRICE Contents 13
260 260 261 262 262 262 263 263 264 264 264 265 266 266 267 267 268 268 269 270 270 270 271 271 271 272 272 273 273 274 274 275 275 276 277 277 278 278 279 279 280 14 PRICEDISC PRICEMAT PROB PRODUCT PROPER PV QUOTIENT RADIANS RAND RANDBETWEEN RANK RATE REPLACE REPT RIGHT ROMAN ROUND ROUNDDOWN ROUNDUP ROW ROWS SEARCH SECOND SIGN SIN SINH SLN SLOPE SMALL SQRT STDEV STDEVA STDEVP STDEVPA SUBSTITUTE SUM SUMIF SUMPRODUCT SUMSQ SYD T Contents
280 281 281 282 282 283 283 283 284 284 284 285 286 286 287 288 289 289 Index TAN TANH TIME TIMEVALUE TODAY TRIM TRUE TRUNC UPPER VALUE VAR VARA VARP VARPA VDB VLOOKUP WEEKDAY YEAR 290 Contents 15
This full-color PDF document provides extensive instructions for using Numbers. Before using this document, you may want to look at the Numbers tutorial in iWork ’08 Getting Started. It’s a quick way to prepare yourself to be a self-sufficient Numbers user. iWork ’08 Getting Started also provides additional resources for getting acquainted with Numbers, such as a tour of its features and how-to videos.
The following table tells you where to find information in this guide. In Numbers Help, you can find information by browsing or searching.
1 Numbers Tools and Techniques 1 This chapter introduces you to the windows and tools you use to work with Numbers spreadsheets. When you create a Numbers spreadsheet, you first select a template to start from. Spreadsheet Templates When you first open the Numbers application (by clicking its icon in the Dock or double-clicking its icon in the Finder), the Template Chooser window presents a variety of spreadsheet types from which to choose.
Pick the template that best fits your purpose. If you want to start from a plain spreadsheet, without preformatting, pick the Blank template. After selecting a template, click Choose to work with a new spreadsheet based on the selected template. Templates contain predefined sheets, tables, and other elements that help you get started. Sheets let you divide information into groups of related objects. You might use one sheet for data from 2006 and another sheet for data from 2007.
The Numbers Window The Numbers window has elements that help you develop and organize your spreadsheet. The Sheets pane: See an overview of the tables and charts on the sheets of your spreadsheet. View the results of calculations for values in selected cells, and drag calculations into The toolbar: Customize it to include the tools you use most often. The Styles pane: Select a predefined table style to quickly format a table. The Formula Bar: Create and edit formulas in table cells.
 Use buttons in the toolbar to quickly add tables, charts, text boxes, media files, and other objects. For example, click the Tables button in the toolbar to add a new table that’s been preformatted for the template you’re using. All templates contain several preformatted tables for you to choose from. See “The Toolbar” on page 24 to learn how to customize the toolbar so it includes the tools you use most often.  Use the Format Bar to quickly format a selected object.
Print View When you want to print a sheet or make a PDF of it, you can use Print View to visualize the layout of objects on a sheet on individual pages. Footer area Header area Click to view pages in landscape (horizontal) orientation. Click to view pages in portrait (vertical) orientation. Click to choose a page zoom level that lets you see more or fewer pages. Click to show or hide Print View. Slide to shrink or enlarge all the sheet’s objects.
The Styles Pane The styles pane lets you quickly apply predefined formatting to tables in a spreadsheet. Table styles define such attributes as color, text size, and cell border formatting of table cells. To apply a table style, simply select the table and click a style in the Styles pane. Switching from one table style to another takes only one click. See “Using Table Styles” on page 79 for details.
The Toolbar The Numbers toolbar gives you one-click access to many of the actions you perform as you work in Numbers. As you discover which actions you perform most often, you can add, remove, and rearrange toolbar buttons to suit your working style. To see a description of what a button does, hold your pointer over it. The default set of toolbar buttons is shown below. Add a sheet. Add a table. Sort and filter rows. Add a chart, text box, shape, or comment. Add a formula.
The Format Bar Use the Format Bar, displayed beneath the toolbar, to quickly change the appearance of tables, charts, text, and other elements in your spreadsheet. The controls in the Format Bar vary with the object selected. To see a description of what a Format Bar control does, hold the pointer over it. Here’s what the Format Bar looks like when a table or table cell is selected. Show or hide a table’s name. Format text in table cells. Format cell values. Arrange text in table cells.
Here are ways to open an Inspector window: m Click Inspector in the toolbar. m Choose View > Show Inspector. m To open another Inspector window, press the Option key while clicking an Inspector button. After an Inspector window is open, click one of the buttons at the top to display a different inspector. Clicking the second button from the left, for example, displays the Sheet Inspector.
 Using the Function Browser is the fastest way to add a function. A function is a predefined formula that has a name (such as SUM and AVERAGE). To open the Function Browser, click the Function Browser button in the Formula Bar. Click to open the Function Browser. “Using Functions” on page 96 tells you how to use the Function Browser.
The Media Browser The Media Browser provides access to all the media files in your iPhoto library, your iTunes library, and your Movies folder. You can drag an item from the Media Browser to your spreadsheet or to an image well in an inspector. Click a button to view the files in your iTunes library, your iPhoto library, your Aperture library, or your Movies folder. Drag a file to your spreadsheet. Search for a file. Here are ways to open the Media Browser: m Click Media in the toolbar.
The Font Panel Using the Mac OS X Font panel, accessible from any application, you can change a font’s typeface, size, and other options. Use the Format Bar for quick font formatting, but use the Font panel for advanced font formatting. To open the Font panel: m Click Fonts in the toolbar. For more detailed information about using the Font panel and changing the look of text, see “Using the Font Panel to Format Text” on page 123.
Keyboard Shortcuts and Shortcut Menus You can use the keyboard to perform many of the Numbers menu commands and tasks. To see a comprehensive list of keyboard shortcuts, open Numbers and choose Help > Keyboard Shortcuts. Many objects also have shortcut menus with commands you can use on the object. Shortcut menus are especially useful for working with tables and charts. To open a shortcut menu: m Press the Control key while you click an object.
2 Working with a Numbers Spreadsheet 2 This chapter describes how to manage Numbers spreadsheets. You can create a Numbers spreadsheet by opening Numbers and choosing a template. You can also import a document created in another application, such as Microsoft Excel or AppleWorks 6. This chapter tells you how to create new Numbers spreadsheets, as well as how to open existing spreadsheets and save spreadsheets.
To create a new spreadsheet: 1 Open Numbers by clicking its icon in the Dock or by double-clicking its icon in the Finder. If Numbers is open, choose File > New from Template Chooser. 2 In the Template Chooser window, select a template category in the left column to display related templates, and then select the template that best matches the spreadsheet you want to create. If you want to begin in a spreadsheet without any predefined content, select Blank. 3 Click Choose.
Importing a Document You can create a new Numbers spreadsheet by importing a document created in Microsoft Excel or AppleWorks 6. Numbers can also import files in comma-separated value (CSV) format, tab-delimited format, and Open Financial Exchange (OFX) format. From AppleWorks, you can only import spreadsheets. Here are ways to import a document: m Drag the document to the Numbers application icon. A new Numbers spreadsheet opens, and the contents of the imported document are displayed.
Saving Spreadsheets When you create a Numbers spreadsheet, all of the graphics are saved with the spreadsheet so they display correctly if the spreadsheet is opened on another computer. Fonts, however, are not included as part of the spreadsheet. If you transfer a Numbers spreadsheet to another computer, make sure the fonts used in the spreadsheet have been installed in the Fonts folder of that computer. You can choose whether to save audio and movie files with a Numbers spreadsheet.
You can also send a spreadsheet to iWeb. For more information, see “Sending a Spreadsheet to iWeb” on page 187. Undoing Changes If you don’t want to save changes you made to your spreadsheet since opening it or last saving it, you can undo them. Here are ways to undo changes: m To undo your most recent change, choose Edit > Undo. m To undo multiple changes, choose Edit > Undo multiple times. You can undo any changes you made since opening the spreadsheet or reverting to the last saved version.
Saving Search Terms for a Spreadsheet You can store such information as author name and keywords in Numbers spreadsheets, and then use Spotlight to locate spreadsheets containing that information. To store information about a spreadsheet: 1 Click Inspector in the toolbar, and then click the Document Inspector button. 2 In the Spotlight fields, enter or change information.
If you’ve made changes since you last saved the spreadsheet, Numbers prompts you to save. Using Sheets to Organize a Spreadsheet Like chapters in a book, sheets let you divide information into manageable groups. For example, you might want to place charts in the same sheet as the tables whose data they display. Or you may want to place all the tables on one sheet and all the charts on another sheet.
Adding and Deleting Sheets There are several ways to add and delete sheets. Here are ways to create and remove sheets: m To add a new sheet, click the Sheet button in the toolbar. You can also choose Insert > Sheet. A new sheet containing a predefined table is added at the bottom of the Sheets pane. You can move the sheet by dragging it to a new location in the Sheets pane.
Changing Sheet Names A name distinguishes each sheet in the Sheets pane. The sheet name is assigned by default when you add a sheet, but you can change it to a more descriptive name. Here are ways to change a sheet’s name: m In the Sheets pane, double-click the name and edit it. m In the Sheet Inspector, edit the name in the Name field. You can also change the names of a sheet’s tables and charts. See “Naming Tables” on page 49 and “Using a Chart Title” on page 107 for instructions.
Here are ways to show or hide Print View: m Click View in the toolbar, and then choose Show Print View or Hide Print View. m Choose File > Show Print View, View > Show Print View, File > Hide Print View, or View > Hide Print View. m Click the page icon next to the page zoom control in the lower left of the canvas. When you use Print View, the zoom level you choose from the pop-up menu in the lower left determines how many pages you can view in the window at one time.
Using Headers and Footers You can have the same text appear on multiple pages in a sheet. Recurring information that appears at the top of the page is called a header; at the bottom it’s called a footer. You can put your own text in a header or footer, and you can use formatted text fields. Formatted text fields allow you to insert text that is automatically updated. For example, inserting the date field shows the current date whenever you open the spreadsheet.
To avoid showing header rows or columns when a table spans pages, deselect “Repeat header cells on each page” in the Table Inspector or choose Table > Don’t Repeat Header Rows or Table > Don’t Repeat Header Columns. m Move objects from page to page by dragging them or by cutting and pasting them. Setting Page Orientation You can lay out pages in a sheet in a vertical orientation (portrait) or a horizontal orientation (landscape).
Setting Page Margins In Print View, every sheet’s page has margins (blank space between the sheet’s edge and the edges of the paper). These margins are indicated onscreen by light gray lines, visible when you use layout view. To set the page margins for a sheet: 1 Select the sheet in the Sheets pane. 2 Click View in the toolbar and choose Show Print View and Show Layout. 3 Click Inspector in the toolbar, and then click the Sheet Inspector button.
3 Using Tables 3 This chapter tells you how to add and format tables and cell values. Several other chapters provide instructions that focus on particular aspects of tables: Â To learn about using styles to format tables, see Chapter 4, “Working with Table Styles,” on page 79. Â To learn about using formulas in table cells, see Chapter 5, “Using Formulas and Functions in Tables,” on page 83. Â To learn about displaying table values in charts, see Chapter 6, “Using Charts,” on page 98.
Working with Tables Use a variety of techniques to create tables and manage their characteristics, size, and location. Adding a Table While most templates contain one or more predefined tables, you can add additional tables to your Numbers spreadsheet. Here are ways to add a table: m Click Tables in the toolbar and choose a predefined table from the pop-up menu. You can add your own predefined tables to the pop-up menu. See “Defining Reusable Tables” on page 49 for instructions.
m Use the Table Inspector to access table-specific controls, such as fields for precisely controlling column width and row height. To open the Table Inspector, click Inspector in the toolbar, and click the Table Inspector button. Add a table name. Add and remove a header row, a header column, and a footer row. Merge or split selected cells. Adjust the size of rows and columns. Set the style, width, and color of cell borders. Add color or an image to a cell.
m Use table styles to adjust the appearance of tables quickly and consistently. See “Using Table Styles” on page 79 for more information. m Use the reference tabs and handles that appear when you select a table cell to quickly reorganize a table, select all the cells in a row or column, add or delete rows and columns, and more. Drag the Table handle to move the table. Reference tab letters can be used to refer to columns. Click the Column handle to add one column. Drag it to add multiple columns.
Resizing a Table You can make a table larger or smaller by dragging one of its selection handles or by using the Metrics Inspector. You can also change the size of a table by resizing its columns and rows. Here are ways to resize a table that’s selected: m Drag one of the square selection handles that appear when a table is selected. See “Selecting a Table” on page 51 for instructions. To maintain a table's proportions, hold down the Shift key as you drag to resize the table.
m To move a table more precisely, click any cell, click Inspector in the toolbar, click the Metrics Inspector button, and then use the Position fields to relocate the table. m To copy a table and move the copy, hold down the Option key, click and hold at the edge of an unselected table, and drag. Naming Tables Every Numbers table has a name that’s displayed in the Sheets pane and can optionally be displayed above the table.
3 Define the table’s geometry. To resize the table, see “Resizing a Table” on page 48 and “Resizing Table Rows and Columns” on page 73. To define columns and rows, see “Working with Rows and Columns” on page 69. To split or merge, and resize table cells, see “Splitting Table Cells” on page 74 or “Merging Table Cells” on page 74. 4 Add and format any content you want to reuse. See “Working with Content in Table Cells” on page 54 for instructions.
To copy a table from one iWork application to another: 1 Select the table you want to copy, as “Selecting a Table” on page 51 describes. 2 Choose Edit > Copy. 3 In the other application, set an insertion point for the copied table, and then choose Edit > Paste. Selecting Tables and Their Components You select tables, rows, columns, table cells, and table cell borders before you work with them. Selecting a Table When you select a table, selection handles appear on the edges of the table.
When a cell is selected, use the Tab, Return, and arrow keys to move the selection to an adjacent cell. Selecting “Return key moves to next cell” under Table Options in the Table Inspector sometimes changes the effect of the Return and Tab keys. If “Return key” option is selected If “Return key” option isn’t selected The next cell to the right Press Tab. If you press Tab when the last cell in a column is selected, a new column is added.
To select an entire row or column: 1 Select any table cell so that the reference tabs are showing. 2 To select a column, click its reference tab (above the column). To select a row, click its reference tab (to the left of the row). Selecting Table Cell Borders Select cell border segments when you want to format them. A single border segment is one side of a cell. A long border segment includes all adjacent single border segments.
You can also use the Cell Borders buttons in the Table Inspector to select a border segment. m To select and deselect segments by clicking them in a table, use border selection mode. Choose Allow Border Selection from the Borders pop-up menu in the Format Bar or choose Table > Allow Border Selection, and then select the table you want to work with. The pointer changes shape when it’s over a horizontal or vertical segment. The pointer appears to straddle the segment.
m To replace everything in the cell, select the cell and then begin typing. If “Return key moves to next cell” isn’t selected in the Table Inspector, you can also select the cell and then press Return or Enter, which selects everything in the cell, and then start typing. m To insert content within existing content, select the cell, click to set the insertion point, and begin typing. m To undo changes made to a table cell since selecting the cell, press Esc.
m To control font attributes, use the text formatting buttons in the Format Bar. Choose a typeface. Choose the text color. Choose a font. Choose the font size. You can also use the Font panel (click Fonts in the toolbar). m To check spelling, follow the instructions in “Checking for Spelling Mistakes” on page 146. m To find and optionally replace text in cells, follow the instructions in “Finding and Replacing Text” on page 148.
Working with Dates in Cells Unless a cell has a date and time format associated with it, when you type a value that can be interpreted as a date or time value, Numbers might reformat the value. For example, if you type 1/22/45 or 1-22-45 into a table cell with the Automatic cell format and leave the cell, the value changes to Jan 22, 2045. If you enter a 2-digit year value greater than or equal to 50, Numbers adds the prefix “19”; otherwise Numbers adds the prefix “20.
m If cell values aren’t visible because rows are too narrow, you can use the Fit button next to the Row Height controls in the Table Inspector to make values visible. Select a cell, row(s), or the table, and then click the Fit button. As row content is added or removed, row height automatically changes to match content height. You can also resize a row by clicking the bottom border of its reference tab and dragging down, or by using the Row Height controls in the Table Inspector.
m After you define a cell format, you can associate the format with multiple cells by using autofilling. See “Autofilling Table Cells” on page 68 for instructions. Using the Number Format Use the number format to format the display of a number’s decimal places, thousands separator, and negative values.
Using the Currency Format Use the currency format to format the display of monetary values. To define a currency format that displays two decimal places, a thousands separator, and negative numbers with the negative symbol, select one or more cells and then click the Currency Format button in the Format Bar. Use the Decrease Decimal Places and Increase Decimal Places buttons located nearby to change the number of decimal places.
Using the Percentage Format Use the percentage format to display numeric values followed by the % symbol. If the value is used in a formula, its decimal number version is used. For example, a value that displays as 3% is used as 0.03 in a formula. If you type 3% in a cell formatted using the automatic format and then apply the percentage format to the cell, the value displayed is 3%.
5 To specify how to display negative values, choose an entry from the pop-up menu adjacent to the Decimals field. 6 To specify whether to use a thousands separator, select Thousands Separator. If a cell you’re formatting already contains a value, the value is assumed to be a decimal value, and it’s converted into a percentage. For example, 3 becomes 300%. Using the Date and Time Format Use the date and time format to display date and/or time values.
Using the Fraction Format Use the fraction format to control the display of values smaller than 1. For example, 27.5 displays as 27 1/2 when the format is Halves and as 27 4/8 when the format is Eighths. To define a fractions format, select one or more cells, choose Fractions from the More Cell Formats pop-up menu in the Format Bar, and choose a format from the submenu. More Cell Formats button You can also use the Cells Inspector.
To define a scientific format that displays two decimal places, select one or more cells and then choose Scientific from the More Cell Formats button in the Format Bar. More Cell Formats button For more control over the number of decimal places, use the Cells Inspector. To define a scientific format for one or more cells using the Cells Inspector: 1 Select the cell or cells. 2 Click Inspector in the toolbar, and then click the Cells Inspector button. 3 Choose Scientific from the Cell Format pop-up menu.
Using a Checkbox and Other Control Formats You can add a checkbox, slider, stepper, or pop-up menu to a cell: Â A checkbox is for cells whose value indicates one of two states, such as on or off, or yes or no. Â A slider is useful for quickly making large changes to numbers so you can see the effects of the changes on other cells in the table or on a chart. Â A stepper lets you increase or decrease numbers in specific increments. Â A pop-up menu lets you predefine the values a cell can contain.
m To add a slider, click the More Cell Formats button in the Format Bar and then choose Slider in the pop-up menu. A slider with default settings, visible in the Cells Inspector, is created. More Cell Formats button You can also open the Cells Inspector and choose Slider from the Cell Format pop-up menu. In the Cells Inspector, change default settings if desired: Minimum and Maximum: Indicates the lowest and highest cell values.
m To add a stepper, click the More Cell Formats button in the Format Bar and then choose Stepper in the pop-up menu to create a stepper with default settings. You can also open the Cells Inspector and choose Stepper from the Cell Format pop-up menu. In the Cells Inspector, change the default settings if desired: Minimum and Maximum: Indicates the lowest and highest cell values.
8 To add another rule, click + and repeat steps 5 through 7. If more than one rule is defined for a cell and the cell’s content satisfies the conditions of multiple rules, the colors associated with the higher rule in the list of rules are applied to the cell. To stop using a rule, click its – button to delete the rule, or deselect Fill Color and Text Color to disable the rule. To stop using a list of rules, deselect “Highlight cells using the following rules.
To create new values based on numeric patterns, select two or more cells before dragging. For example, if two selected cells contain 1 and 2, the values 3 and 4 are added when you drag through the adjacent two cells. And if two selected cells contain 1 and 4, the values 7 and 10 are added when you drag through the adjacent two cells (values are incremented by 3). Autofilling doesn’t set up an ongoing relationship among cells in the group.
Adding Columns You can add new columns to a table. Here are ways to add columns: m To add a single column, hover your pointer over a column’s reference tab to see its menu arrow. Click the arrow and then choose Add Column Before or Add Column After from the pop-up menu. You can also choose these commands from the Table menu. m You can use the Tab key to add a column to the right side of the table when “Return key moves to next cell” under Table Options in the Table Inspector is selected.
Here are ways to delete rows or columns: m Select the row(s) or column(s) or a cell in them, and then choose Table > Delete Row or Table > Delete Column. m To delete a single row or column, hover your pointer over the row or column reference tab to see the menu arrow, and then choose Delete Row or Delete Column from the pop-up menu.
m Select the table and then choose one of the following from the Table menu: Add Header Row, Delete Header Row, Add Header Column, or Delete Header Column. m If a table spans pages and you want to repeat headers at the top of each page, select “Repeat header cells on each page” in the Table Options area of the Table Inspector. The cells in a header row or header column are referred to as header cells. Header cells play an important role in making formulas in table cells easier to read and create.
Resizing Table Rows and Columns Resize all rows and columns so they’re equal in size, or resize only specific rows and columns in a table. Here are ways to resize rows and columns: m To make all rows the same size, select the table and choose Table > Distribute Rows Evenly. m To make all columns the same size, choose Table > Distribute Columns Evenly. m To resize a single row, drag the bottom border of the row’s reference tab up or down.
Working with Table Cells You can split and merge table cells, resize them, format them, hide them, add comments, and more. Merging Table Cells Merging table cells combines adjacent cells into one, eliminating the borders so that they behave as a single cell. To merge table cells: 1 Select a group of two or more adjacent table cells. The group of cells you choose must form a rectangle, and they must be all body cells or all header cells. 2 Choose Table > Merge Cells.
Formatting Table Cell Borders You can change the line thickness and color of table cell borders. Or you can hide the cell border of any cell. To format table cell borders: 1 Select the cell border segments you want to format. See “Selecting Table Cell Borders” on page 53 for instructions. 2 Use the controls in the Format Bar or in the Table Inspector. Click to choose a line thickness. Click to choose a stroke color. Click to choose a stroke style. Stroke pop-up menu: Lets you choose a stroke style.
Copying and Moving Cells You can use commands or dragging to move or copy table cells. Here are ways to copy and move cells: m To move cells within a table or to another table, select a cell or several adjacent cells, and then drag the selection by its border until the destination cells are selected. Any values in the destination cells are replaced. m To copy cells within or between tables, drag selected cells while holding down the Option key. Any values in the destination cells are replaced.
m To delete a comment, click the X in the upper right of the comment box. m To include comments when you print a sheet, show any comments you want to print, and then choose File > Print Sheet. Reorganizing Tables You can use sorting and filtering to reorganize the data in tables. Â Sorting: Lets you arrange values in some or all the cells in a column in ascending or descending order. Rows containing cells being sorted are reordered.
The following table describes how different types of data are sorted in ascending or descending order.
4 Working with Table Styles 4 This chapter explains how to use table styles to quickly and consistently manage the appearance of tables. You can use different visual characteristics to distinguish different tables in your Numbers spreadsheet. For example, you can format a table containing inputs and assumptions using a blue background and matching borders, but format a table containing calculation results using a gray background and matching borders.
Applying Table Styles After applying a table style to a table, you can change any of the formatting defined by the style. Such a change is called an override. If you later assign a different style to the table, you can keep or remove any overrides you created. Here are ways to apply a table style: m To replace a table’s existing style and remove any overrides, click the arrow to the right of the style you want to apply and then choose Clear and Apply Style. You can also choose Format > Reapply Table Style.
m To format text in one or more cells, select the cells. To modify the color, alignment, and spacing of text within a cell, use the Format Bar or the Text Inspector (click Inspector in the toolbar and click the Text Inspector button). To modify font attributes, use the Font panel (click Fonts in the toolbar). If you want to use the changes you make to redefine the table style for the whole spreadsheet, click the arrow to the right of the style and then choose Redefine Style from Table.
Creating New Table Styles You can create a new table style by reformatting a table and saving the formatting as a table style. To create a new table style: 1 Use the instructions in “Modifying Table Style Attributes” on page 80 to achieve the visual effects you want. 2 Select the table, click the arrow to the right of any style in the Styles pane, and then choose Create New Style. 3 Type a unique name for your new table style, and click OK.
5 Using Formulas and Functions in Tables 5 This chapter tells you how to perform calculations in table cells using formulas and functions. See Chapter 3, “Using Tables,” on page 44 for instructions for adding and formatting tables and data in them and Chapter 12, “Dictionary of Functions,” on page 193 for complete information about individual functions. Using Formulas A formula is a mathematical expression that uses operations to derive a value.
Here’s a formula that adds the values in nine cells of the first column: SUM(A2:A10). There is one argument, A2:A10. The colon means the function should use the values in cells A2 through A10. You don’t need to understand more than this to use formulas in tables. Templates (such as the Loan Comparison and Mortgage templates) and formula editing tools (such as the Formula Editor and the Formula Bar) make basic formulas easy to use.
3 If the value in a Quantity cell or in a Unit Price cell changes, the formula associated with the Cost cell updates the value it displays. To see this happen, type a value in the Quantity and Unit Price cells in any row and notice how the value in the adjacent Cost cell is updated. 4 Double-click the cell in the Cost column that’s in the row containing Subtotal. In this formula, two cells are identified by using reference tab identifiers.
6 The values in the Cost column are formatted for display using a cell format (currency). To see the cell format settings, close the Formula Editor by clicking outside the table. Then click the table, click any cell in the Cost column, click Inspector in the toolbar, and then click the Cells Inspector button. A $ is displayed in front of numbers in the cells. Numbers are displayed using two decimal places.
2 To perform another set of instant calculations, select different cells. If you find a particular calculation very useful and you want to incorporate it into a table, you can add it as a formula to an empty table cell. Simply drag sum, avg, or one of the other items in the lower left to an empty cell. The cell doesn’t have to be in the same table as the cells used in the calculations.
Here are ways to perform basic calculations using values in a column: m To use values in a range of adjacent cells, select the cells, click Function in the toolbar, and choose a calculation from the pop-up menu. Numbers places the formula and its result in the first empty cell beneath the selected cells. If there is no empty cell, Numbers adds a row to hold the result.
Here are ways to open the Formula Editor: m Select a table cell and then type the equal sign (=). m Double-click a table cell that contains a formula. m Select a table cell, click Function in the toolbar, and then choose Formula Editor from the pop-up menu. m Select a table cell and then choose Insert > Function > Formula Editor. When the Formula Editor opens, it appears over the selected cell.
3 To save changes, press Return, press Enter, or click the Accept button in the Formula Editor. You can also click outside the table. To close the Formula Editor and discard changes, press Esc or click the Cancel button in the Formula Editor. Using the Formula Bar The Formula Bar, located beneath the Format Bar, lets you create and modify formulas in a selected cell. Cancel button Discard changes. Click to open the Function Browser. Text field View or edit a formula. Accept button Save changes.
Editing a Formula with the Formula Bar You can use the Formula Bar when you want to edit a formula. To edit a formula: 1 Select a table cell that contains a formula. The formula is displayed in the text field of the Formula Bar, and its arguments are highlighted in color. 2 Click in the Formula Bar, and make changes as required. You can use the arrow keys to move the insertion point around in the text field. 3 To save changes, press Return, press Enter, or click the Accept button in the Formula Bar.
Here are ways to add a cell reference at the insertion point in the text field: m To create a reference to a specific cell by using your mouse, click the cell. You can also type a reference to a specific cell. Use the column letter followed by the row number (A4) in the reference tabs or use the column name followed by the row name (2006 Class Enrollment). If the other table is on a different sheet, click the sheet in the Sheets pane before selecting the cell.
If the table is large and you want to move the formula to a cell that’s out of view, select the cell, choose Edit > Mark for Move, select the other cell, and then choose Edit > Move. For example, if the formula =A1 is in cell D1 and you want to move the same formula to cell X1, select D1, choose Edit > Mark for Move, select X1, and then choose Edit > Move. The formula =A1 appears in cell X1.
To view error and warning messages: m Click the icon. A message window summarizes each error and warning condition associated with the cell. To have Numbers issue a warning when a cell referenced in a formula is empty, choose Numbers > Preferences and in the General pane select “Show warnings when formulas reference empty cells.
Understanding the Arithmetic Operators Arithmetic operators in table cell formulas perform arithmetic operations and return numerical results. Here are the arithmetic operators (examples presume that cell A2 contains 20 and that B2 contains 2): m The operator + returns the sum of two values. For example, A2 + B2 returns 22. m The operator – returns the difference between two values. For example A2 – B2 returns 18. m The operator * returns the product of two values. For example, A2 * B2 returns 40.
Understanding the Comparison Operators Comparison operators compare two values and return TRUE or FALSE. Here are the comparison operators (examples presume that cell A2 contains 20 and that B2 contains 2): m The operator = returns TRUE if two values are equal. For example, A2 = B2 returns FALSE. m The operator <> returns TRUE if two values aren't equal. For example, A2<>B2 returns TRUE. m The operator > returns TRUE if the first value is greater than the second value. For example, A2 > B2 returns TRUE.
To use the Function Browser to add a function: 1 Select the cell in which you want to add a function. 2 Click the Function Browser button in the Formula Bar, or choose Insert > Function > More Functions. The Function Browser opens. 3 Select a function. To find a function, scroll through the list on the right, optionally subdividing the list by selecting a function category on the left.
6 Using Charts 6 You can convert table data into attractive charts. This chapter describes how to create and format charts. Numbers provides tools for creating visually appealing charts to present some or all of the data in one or more tables. By default, the appearance of charts is coordinated with the template you’re using, but you can adjust colors, textures, shadows, labels, and more to emphasize various chart elements. About Charts You can turn data that appears in one or more tables into charts.
There are several ways to represent the data in this table in a chart. In a bar chart, for example: Â You can plot 2007 employee counts for Region 1 and Region 2 in side-by-side bars, followed by pairs of bars for 2008, 2009, and 2010. Such a chart would have two data series and four data sets. Region 1 and Region 2 are called data series; each region is represented by a series of data values.
When you select a chart, the cells plotted in it are highlighted in the related table. In addition, colored squares appear next to columns and rows in the table to show which color represents each data series in the chart. Click this button to change the chart orientation. These squares indicate which color represents each data series. A button in the upper left of the table lets you switch between using columns and rows as data series. When the button looks like this, the columns are data series.
Data series are represented differently in different kinds of charts: Â In column charts and bar charts, a data series is represented by a series of bars in the same fill (color or texture). Â In a line chart (also called a graph), a data series is represented by a single line. Â In an area chart, a data series is represented by an area shape. Â In a pie chart, only a single data set (the first data point in each series) is represented on the chart.
Here are ways to add a chart: m To add a chart based on a table, select the table, click Charts in the toolbar, and choose a chart type from the pop-up menu. On the pop-up menu, 2D charts are on the left, and 3D charts are on the right. The values in the table are plotted in the chart. If the table is empty, the chart is blank until you add values to table cells.
Changing the Plotting Orientation A data series button in the upper left of the table lets you switch between using columns and rows as data series. Click this button to change the chart orientation. The button looks like this when columns are data series. To change the plotting orientation: 1 Select the chart. 2 Click the data series button. Changing the Data Plotted in a Chart When you change the data in a chart’s related table cells, the chart is automatically updated.
m To add cells based on a new row or column, select the table and insert the row or column between cells that are already plotted. When you add data to the new cells, the data will be plotted in the chart. Adding Data to a Chart from Multiple Tables You can add data to a chart from cells that are in more than one table. To chart data in multiple tables: 1 Create a chart based on data from one table. 2 Select cells in another table. 3 Drag the selected cells into the chart.
Replacing a Data Series You can change the row or column on which a data series is based to a different row or column. To replace a data series: 1 Select the chart. 2 Select the series you want to move, either by clicking the data series in the chart or by clicking the data series label in the table. 3 Drag the data series in the table to the row or column you want to use as a replacement. Deleting a Chart You can delete a chart by deleting its related table(s), or you can delete only the chart.
If you switch to a scatter chart, each point in the chart requires two values. If the chart is based on an odd number of rows or columns, the last row or column isn’t plotted. See “Scatter Charts” on page 117. If you switch to a bar, column, area, or line chart, each series in the new chart corresponds to a row or column in the table. If you switch to a 3D version of a chart, the Chart Inspector provides controls for managing object depth, lighting style, and more. See “3D Charts” on page 118.
Here are ways to work with a chart’s legend: m To show the legend, select the chart and then select Show Legend in the Chart Inspector. m To edit the labels in the legend, edit the series labels in the related table or select the legend and edit the text directly in the legend. m To format the legend’s text, see “Formatting Titles, Labels, and Legends” on page 112. m To hide the legend, select the chart and then deselect Show Legend in the Chart Inspector. You can also select the legend and press Delete.
Here are ways to rotate 2D charts: m Select the chart, hold down the Command key, move the pointer toward a selection handle until it changes to a curved, double-headed arrow, and then drag a selection handle. m To rotate a chart in 45-degree increments, press the Shift and Command keys while dragging a selection handle. m Click Inspector in the toolbar, click the Metrics Inspector button, and then drag the Rotate wheel or use the Angle controls to set the angle of the chart.
To format the border stroke and color, select the chart and use the line style controls on the Format Bar. Choose a line width. Choose a line color. Choose a line style. Using Axis Titles You can add a title to a chart’s X-axis and Y-axis. The title can be formatted. Here are ways to work with an axis title: m To add an axis title, select the chart and then select Show Category Title from the XAxis or Show Value Title from the Y-Axis pop-up menu in the Axis pane of the Chart Inspector.
Formatting the Value Axis You can set the values and units to be displayed along the value axis, the axis on which you read the data point values. For column charts (vertical bars), line charts, and area charts, the Y-axis is the value axis. For horizontal bar charts, the X-axis is the value axis. For scatter charts, both axes are value axes. (Pie charts don’t have a value axis. To read about formatting pie charts, see “Pie Charts” on page 113.) To format the value axis: 1 Select a chart.
For vertical bar charts (column charts), line charts, and area charts, the Y-axis is the value axis and the X-axis is the category axis. For horizontal bar charts, the X-axis is the value axis. For scatter charts, both axes are value axes. For 3D charts, the X and Y axes are the same as their 2D counterparts. To format axis markings: 1 Select the chart. 2 Click Inspector in the toolbar, click the Chart Inspector button, and then click Axis.
Formatting the Elements in a Data Series You can use a variety of visual effects to enhance the appearance of data series elements, such as bars, wedges, and area shapes. To format data series elements: 1 To format all data series, select the chart. To format elements in a single data series, select the chart and then click one of the elements in the series. For all chart types except pie charts, selecting one element (such as a bar) of a series automatically selects the full series.
2 You can use the Format Bar to format font characteristics and style. For additional formatting options, use the Format menu or the Font panel. See “Using the Format Menu to Format Text” on page 121 and “Using the Font Panel to Format Text” on page 123 for instructions. Adding Descriptive Text to a Chart You can add descriptive text to a chart, such as when you want to provide background information for a particular data point. To add text: 1 Create a text box.
Showing Series Names in a Pie Chart On pie charts, you can display series names. To show series names in a pie chart: 1 Select the chart or individual pie wedges. 2 Click Inspector in the toolbar, click the Chart Inspector button, and then click Series. 3 Choose Show Series Name from the Data Point Label pop-up menu. 4 Choose Inside or Outside from the Data Point Label pop-up menu. Separating Individual Pie Wedges To make pie wedges more visually prominent, you can separate them from the pie.
Adjusting the Opacity of Pie Charts You can change the opacity of the chart and individual chart elements, such as the legend. See “Adjusting Opacity” on page 158 for more information. Rotating 2D Pie Charts You can use various techniques for rotating a 2D pie chart. Here are ways to rotate a pie chart: m Select the chart, click Inspector in the toolbar, click the Chart Inspector button, click Series, and then use the Rotation Angle wheel or field.
You can also move the pointer near a bar edge until it becomes a double-headed arrow, and then drag to make the bars thicker or thinner. Adding Shadows to Bar and Column Charts You can add shadows to each bar in the chart or to each group of bars. If you want to add shadows to individual bars, it’s a good idea to separate them first. See “Adjusting Spacing of Bar and Column Charts” on page 115 for instructions. To add shadows: 1 Select the chart.
Here are ways to format area charts and line charts: m To format symbols, select a data series (area shape or line), click Inspector in the toolbar, click the Chart Inspector button, and then click Series. Choose a symbol from the Data Point Symbol pop-up menu. Choose a symbol to use for data points. Fill data point symbols with color or images. Use the options on the Symbol Fill pop-up menu to add color or images to the symbols.
3D Charts All the chart types except scatter charts can be displayed as 3D charts. Here are 2D and 3D versions of a pie chart. And here are 2D and 3D versions of a line chart. To define 3D scene settings: 1 Select a 3D chart. 2 Click Inspector in the toolbar, click the Chart Inspector, and then click 3D Scene. 3 Modify the chart’s viewing angle, lighting style, and depth as desired using the 3D Scene controls. Viewing angle arrows: Drag an arrow to adjust the angle.
7 Working with Text 7 This chapter describes how to add and modify the appearance of text, including lists. In Numbers spreadsheets, you can place text in text boxes, table cells, and shapes. Adding Text Add text by typing it in a table cell, text box, or shape. Â To learn about adding text to a text box or shape, see “Using Text Boxes and Shapes to Highlight Text” on page 141. Â To learn how to add text to table cells, see “Working with Text in Cells” on page 55.
Selecting Text Before you format or perform other operations on text, you need to select the text you want to work with. Here are ways to select text: m To select one or more characters, click in front of the first character and drag across the characters you want to select. m To select a word, double-click the word. m To select a paragraph, click three times quickly in the paragraph.
Using the Format Bar to Format Text The Format Bar makes changing the size and appearance of text in a text box, shape, or comment quick and easy. When text in a text box, shape, or comment is selected, you can change the font style and size, adjust the color of the text, make text bold or italics or underlined, align text, set line spacing, and format the text into columns. Align text. Set color of text. Change font typeface, style, and size. Make text bold, italics, or underlined.
Underlining Text Using the Menus You can underline text and then format the underline to change the underline style or color. To underline text: 1 Select the text you want to underline, or click where you want to type new text. 2 Choose Format > Font > Underline. To modify the underline style or color, click Fonts in the toolbar and use the Text Underline button in the Font panel. See “Using the Font Panel to Format Text” on page 123.
Changing Text Capitalization Using the Menus You can quickly make blocks of text all uppercase or lowercase, or format text as a title. To change text capitalization: 1 Select the text you want to change, or click where you want to type new text. 2 Choose Format > Font > Capitalization and choose an option from the submenu. Choose All Caps to change the text to capitals. Choose Small Caps to change the text to smaller capitals with larger capitals for uppercase letters.
You can change the appearance of any text in your spreadsheet by selecting it and then selecting options in the Font panel. When you make formatting changes in the Font panel, the selected text changes right away, so you can try different formatting options and quickly see what looks best. Here is a summary of what the text effects pop-up menus do, from left to right: Â The Text Underline pop-up menu lets you choose an underline style (such as single or double).
A preview of the selected font appears in the preview pane at the top of the Font panel. If you don't see a preview pane, choose Show Preview from the Action pop-up menu in the lower-left corner of the Font panel. 3 Adjust the font size using the size slider or other size controls. 4 Adjust the typography settings of the selected font by choosing Typography from the Action pop-up menu.
Changing the Paragraph Background Color Using the Font Panel You can use the Font panel to add a color behind a paragraph. To modify the background color of a selected paragraph: 1 Click Fonts in the toolbar. 2 Click the Document Color button in the Font panel (the fourth button from the left), and then select a color in the Colors window. See “Using the Colors Window” on page 161 for instructions. Creating Shadows on Text Using the Font Panel You can use the Font panel to create and format shadows on text.
3 Choose Show Keyboard Viewer from the Input menu on the right side of the menu bar (the one that looks like a flag or alphabetical symbol). The Keyboard Viewer shows the characters for your keyboard. (If you've selected a different keyboard layout or input method in the Input menu, it shows the characters for the selected keyboard layout.) For example, if U.S. is chosen in the Input menu, you see the characters that appear on a U.S. keyboard in the Keyboard Viewer.
Typing Special Characters and Symbols Using the Mac OS X Character Palette, you can insert special characters, such as mathematical symbols, letters with accent marks, arrows and other “dingbats,” and more. You can also use this palette to enter Japanese, Traditional Chinese, Simplified Chinese, and Korean characters, as well as characters from other languages. To insert special characters or symbols: 1 Place the insertion point where you want the special character or symbol to appear.
Using Advanced Typography Features Some fonts, such as Zapfino and Hoefler, have advanced typography features, which let you create different effects. If you are using a font in a text box that has different typography effects available, you can change many of the effects in the Font submenu of the Format menu. For example, you may be able to adjust the following: Â Kern: Place characters closer together or farther apart.
Setting Text Alignment, Spacing, and Color The primary tools for adjusting alignment, spacing, and color are the Format Bar and the Text Inspector. You can make some horizontal alignment adjustments (such as centering text or aligning it on the left) by using the Format menu. Color, spacing, and alignment controls are also available on the Format Bar when text is selected. When text in a text box, comment, or shape is selected, you can set the color of text, align text, and set line spacing. Align text.
Aligning Text Horizontally You can change the alignment of text in a column, table cell, text box, or shape so that text is aligned to the left or right border, centered, or aligned on both left and right (justified). To align text left, center, right, or justified: 1 Select the text you want to change. 2 Click Inspector in the toolbar, click the Text Inspector button, and then click Text. 3 Click one of the five horizontal alignment buttons, located to the right of the color well.
Adjusting the Spacing Between Lines of Text You can increase or decrease the distance between lines of text. To adjust spacing: 1 Select the text you want to change. 2 Click Inspector in the toolbar, click the Text Inspector button, and then click Text. 3 Move the Line slider left to decrease spacing or right to increase it. To specify a precise line spacing value, type a point value in the Line field, or click the Up Arrow or Down Arrow next to the field.
Adjusting the Spacing Before or After a Paragraph You can increase or decrease the spacing before or after paragraphs. To adjust the amount of space before or after a paragraph: 1 Select the paragraphs you want to change. 2 Click Inspector in the toolbar, click the Text Inspector button, and then click Text. 3 Drag the Before Paragraph or After Paragraph slider. You can also specify a precise value (5 pt, for example) in the text boxes.
Here are ways to change text color: m Click the Text Color color well in the Format Bar. In the color matrix that appears, select a color by clicking it or click Show Colors to open the Colors window for additional color options. m Click Inspector in the toolbar, click the Text Inspector button, click Text, and then click the color well. The Colors window opens. “Using the Colors Window” on page 161 provides instructions for using the Colors window.
Setting a New Tab Stop You use the horizontal ruler to add a new tab stop. To create a new tab stop: 1 Click View in the toolbar and choose Show Rulers. 2 Click the horizontal ruler to place a tab symbol where you want to set the tab stop. 3 Control-click the tab symbol and choose an option from the shortcut menu. Choose from among these tab types. Left Tab: Aligns the left side of text with the tab stop. Center Tab: Places the center of text at the tab stop.
Setting Indents You can modify the amount of space between text and the inside border of a text box, shape, table cell, or comment. Setting Indents for Paragraphs You can change indentation by dragging the indentation controls on the text ruler. To set indents using the rulers: 1 Click View in the toolbar, and then choose Show Rulers.
3 Drag the Inset Margin slider to the right to increase the space between text and the inside border of the object, or type a number in the Inset Margin box and press Return. You can also click the arrows to increase and decrease space. Specify how much space you want around text inside a text box, shape, or table cell. Setting Indents for Lists To manage the indentation of bullets, numbers, and text that accompanies them in bulleted lists, numbered lists, and ordered lists, you use the Text Inspector.
Using Bulleted Lists Although you can use automatic list generation to create a simple bulleted list, using the Text Inspector gives you many options for formatting bulleted lists. See “Generating Lists Automatically” on page 137 for information about automatic list generation. To add and format a bulleted list using the Text Inspector: 1 Place the insertion point where you want the list to begin. 2 Click Inspector in the toolbar, click the Text Inspector button, and then click Bullets.
 To return to regular text at the end of your list, press Return and choose No Bullets from the Bullets & Numbering pop-up menu. You may also need to adjust the indent level. If you’re working in a table cell and “Return key moves to next cell” is selected in the Table Inspector, press Option-Return instead of Return. Using Numbered Lists Although you can use automatic list generation to create a simple numbered list, using the Text Inspector gives you many options for formatting numbered lists.
 To start a new numbered sequence in a list, click “Start at” and specify the number you want the sequence to begin with. If you want items in your list to have labeled subtopics (like in an outline), use an ordered list instead of a numbered list. Using Ordered Lists (Outlines) Ordered lists (or outlines) provide different numbering styles for each indent level in a list, allowing you to create a hierarchy of information.
 To return to regular text at the end of your list, press Return and choose No Bullets from the Bullets & Numbering pop-up menu. You may also need to adjust the indent level. If you’re working in a table cell and “Return key moves to next cell” is selected in the Table Inspector, press Option-Return instead of Return.  To add an existing paragraph to a numbered list, click the paragraph, choose a numbering style, and then click “Continue from previous.
Use the Columns pop-up menu in the Format Bar to quickly divide text into 1 through 4 columns in a selected text box or rectangular shape. For more options, use the Text Inspector. To create and format columns using the Text Inspector: 1 Select the text box or rectangular shape with text you want to divide into columns. 2 Click Inspector in the toolbar, click the Text Inspector button, and then click Columns. 3 To indicate how many columns you want, use the Columns field.
4 To add text to a shape that’s part of a group, double-click in the text area of any shape in the group. If you resize the group, everything resizes except for the text. Of course, you can still select text and change its font size. See “Grouping and Locking Objects” on page 159 for details about grouping objects. Formatting a Text Box or Shape You can change the amount of space between text and the inside border of a text box, shape, or table cell.
4 Type the webpage’s address in the URL field. The Hyperlink Inspector button Type the URL that you want to link to. Select to disable all hyperlinks so you can easily edit them. Linking to a Preaddressed Email Message You can add a hyperlink that you can click to create a preaddressed email message in your default mail application. To add hypertext that links to an email message: 1 Select the text that you want to turn into a hyperlink.
m Click outside the hyperlink text, and use the arrow keys to move the insertion point into the text. Inserting Page Numbers and Other Changeable Values You can insert such values as page numbers, page count, and date and time in a spreadsheet by using formatted text fields, which are automatically updated by Numbers when they change.
To automatically convert the letters in “1st,” “2nd,” “3rd,“ and so on into superscripts, select “Superscript numerical suffixes.” To set Numbers to automatically detect that something you’ve typed is an email address or a URL, select “Automatically detect email and web addresses.” Email and web addresses you type automatically become hyperlinks for Mail or Safari. To use automatic list generation, select “Automatically detect lists.
The first misspelled word found is highlighted. You can correct it or choose the same menu command again to continue checking the spreadsheet. To go through the text more quickly, press Command-semicolon (;) to continue checking the spreadsheet. m To check spelling and view suggestions for misspelled words, choose Edit > Spelling > Spelling. The Spelling window opens, and you can use it as “Working with Spelling Suggestions” describes.
Finding and Replacing Text You can find every instance of a word or phrase in your spreadsheet, and optionally change it to something else. Here are ways to find and replace text: m Choose Edit > Find > Find, click Simple or Advanced to set up find/replace criteria, and then click a button to conduct find/replace operations. Simple: In the Find field, type the text you want to find, and type any text you want to replace it with in the Replace field.
8 Working with Shapes, Graphics, and Other Objects 8 This chapter describes techniques for adding and modifying images, shapes, sound, and other objects. An object is an item you can add to a spreadsheet and then manipulate. Images, shapes, movies, sound files, tables, charts, and text boxes are all objects. Images include photographs or PDF files. Movies and sound are for spreadsheets that will be viewed onscreen.
Copying or Duplicating Objects The technique you use to copy an object depends on where you want to place the copy. When the copy will be far from the original or in another sheet or spreadsheet, copying and pasting is generally easier. When you're working with an object that is near the original, duplicating is generally easier. Here are ways to copy objects: m To copy and paste an object, select it, and then choose Edit > Copy. Click the sheet in which you want the copy to appear. Choose Edit > Paste.
m To precisely align objects by their edges or centers, you can use alignment guides. See “Using Alignment Guides” on page 152 for details. m Select the object and choose Edit > Cut. Place the insertion point where you want the object to appear, and then choose Edit > Paste. Avoid dragging an object by the selection handles because you may inadvertently resize the object.
Middle: Moves objects vertically so that their centers align horizontally to the first object you select. Bottom: Positions objects so that their bottom edges align horizontally to the first object you select. You can also align objects relative to one another by dragging them and using alignment guides to determine when the objects are correctly positioned. See “Using Alignment Guides” for more information.
Creating New Alignment Guides You can create your own alignment guides to help you place objects. To create an alignment guide: 1 If you’re editing text in an object or if you’re editing the content of a table cell, select an object or click the canvas. 2 Click View in the toolbar, and then choose Show Rulers. 3 Place the pointer over a ruler, click, and drag onto the page. An alignment guide appears. 4 Drag the guide where you want it on the page.
Modifying Objects You can resize objects, change their orientation, modify their border styles, add shadows and reflections to them, and adjust their opacity. Resizing Objects You can resize an object by dragging its handles or typing exact dimensions. Here are ways to resize objects: m To resize an object by dragging, select the object and then drag one of its selection handles. To resize an object in one direction, drag a side handle instead of a corner handle.
You can also click Inspector in the toolbar, click the Metrics Inspector button, and then drag the Rotate wheel or use the Angle controls to set the angle of the object. m To rotate a shape but keep its text horizontal, after rotating the shape choose Format > Shape > Reset Text and Object Handles. Changing the Style of Borders For shapes, chart elements, and text boxes, you can choose a line style and color for the object’s border, or you can specify no border.
Framing Objects Enclose your images, shapes, text boxes, movies, and media placeholders with graphical borders, known as picture frames. Here are ways to work with picture frames: m To add a picture frame, select the object, click Inspector in the toolbar, and then click the Graphic Inspector button. Choose Picture Frame from the Stroke pop-up menu, and then click the thumbnail to choose one. Click the Color well to choose a color for the picture frame.
To quickly add or remove a shadow, you can select the object and then select or deselect Shadow in the Format Bar. The Graphic Inspector gives you more extensive control over shadow formatting. Select the checkbox to add a shadow to a selected object. Change the shadow color in the color well. Change the angle of the shadow with the Angle wheel. Offset, Blur, and Opacity values can change the look of the shadow. To add a shadow to an object using the Graphic Inspector: 1 Select the object.
Adding a Reflection You can add a reflection to an object that reflects vertically downward. You can add or remove a reflection by selecting an object and then selecting or deselecting Reflection in the Format Bar. Using the Graphic Inspector, you can also control the amount of reflection. To add a reflection to an object using the Graphic Inspector: 1 Select the object. 2 Click Inspector in the toolbar, and then click the Graphic Inspector button.
For shapes, you can set opacity for fill and stroke colors separately from object opacity. If you move the Opacity slider in the Colors window to modify a fill or stroke color, that opacity value becomes maximum object opacity. Then, when you change the object opacity in the Graphic Inspector, you are changing it relative to the opacity you set in the Colors window. This circle is set to 100% opacity in the Graphic Inspector. This circle is set to 100% opacity in the Graphic Inspector.
Locking and Unlocking Objects You can lock objects to avoid inadvertently moving them as you work. After you lock individual or grouped objects, you can’t move, delete, or modify them until you unlock them. A locked object can be selected, copied, or duplicated; when you copy or duplicate a locked object, the new object is also locked. To lock objects: 1 Hold down the Command (or Shift) key as you select the objects you want to lock. 2 Choose Arrange > Lock.
To invert the gradient, click the double-headed arrow next to the color wells. Click each color well to select colors. Flip the gradient orientation or set its direction by using the arrow buttons or the Angle wheel, or by typing a value. Click the double-headed arrow to invert the gradient. Instructions for using the Colors Window follow. Using the Colors Window You use the Colors window to select color for objects. Click a button to view different color models.
To select a color: 1 Open the Colors window by clicking Colors in the toolbar or clicking a color well in one of the inspectors. 2 Click anywhere in the color wheel. The selected color is displayed in the color box at the top of the Colors window. 3 To make the color lighter or darker, drag the slider on the right side of the Colors window. 4 To make the color more transparent, drag the Opacity slider to the left or enter a percentage value in the Opacity field.
4 Choose an image scale from the pop-up menu. Scale To Fit: Resizes the image to fit the object’s dimensions as well as possible. If the object’s shape is different from the original image’s, parts of the image may not appear; blank space may also appear around the image. Scale To Fill: Makes the image appear larger or smaller, sizing it to leave minimum space around the image, even if the object and image have different shapes.
Using Shapes Numbers comes with predrawn shapes. You can also create your own custom shapes. Adding a Predrawn Shape You can insert predrawn shapes, such as triangles, arrows, circles, and rectangles, to use as simple graphics. Here are ways to add a predrawn shape: m Click Shapes in the toolbar, and then choose a shape from the Shapes submenu. You can also choose Insert > Shape > shape type. m You can also draw a shape.
4 Repeat step 3 to create additional points as required. Every time you add a new point, Numbers connects it with the preceding point using a line with the current stroke and with the first point using no stroke, and fills the shape with the default fill color for the template you’re using. You can change the fill color later using the Fill settings in the Graphic Inspector. To delete a segment you’ve just created, press Delete. Pressing Delete again removes the preceding segment, too.
Manipulating Points of a Shape You can change the contour of a shape by adding, moving, or deleting its points. First you need to make the shape editable, as described in “Making Shapes Editable” on page 165. Click a point and drag it to change the shape. Here are ways to manipulate a shape’s points: m To add a point, make the shape editable, press the Option key, and hold the pointer over the shape’s border. The pointer changes into a pen tip with a plus sign (+).
3 To expand or contract the curve, drag the circular control or one of the control handles. 4 To change the angle of the curve, move the control handles clockwise or counterclockwise. You achieve different effects when you move the handles together or independently of each other. Experiment until you achieve the desired effect. To move the control handles together, press the Option key, and then drag one or the other of them. To move only one control handle, press the Command key before dragging a handle.
Editing Specific Predrawn Shapes Some predrawn shapes have special built-in editing controls. Editing a Rounded Rectangle The rounded rectangle has a circular control that lets you change the corners. Drag to straighten or round the corner. To edit a rounded rectangle: m Select the shape, and drag its circular control to the left to straighten the corners and to the right to round them. Editing Single and Double Arrows The arrows have three special controls. Drag to change the tail length.
Editing a Star The star shape has a slider for increasing and decreasing the number of points in the star and a circular control for changing the angles between points. Drag to change the angles between points in the star. View the number of points currently in the star. Drag to increase or decrease the number of points in the star. Here are ways to edit a star: m When you select a star shape, the slider appears. Drag the slider to increase or decrease the number of points in the star.
Using Media Placeholders Some Numbers templates contain media placeholders. You can drag your own images, movies, and audio files to these placeholders, and your media file is automatically sized and positioned for you. You can easily replace an item in a media placeholder by dragging a new file to it; you don’t have to delete the old file first. You can recognize a media placeholder by letting the pointer rest over it; a help tag appears instructing you to drag your own image into its place.
m To convert a placeholder image into an ordinary image, select the image, choose Format > Advanced, and then deselect “Define as Media Placeholder” in the submenu. To replace an ordinary image, you must first delete it. m To delete a media placeholder, select it and press the Delete key. m To create a media placeholder, follow the instructions in “Creating Media Placeholders for a Custom Template” on page 191.
Masking (Cropping) Images You can crop images without actually changing the image files by masking parts of them. Cropping an Image Using the Default (Rectangular) Mask You can use a rectangular mask to define the boundaries of an image. To crop an image using the default (rectangular) mask: 1 Import the image you want to mask (see “Importing an Image” on page 171 for instructions). 2 Select the image, and then choose Format > Mask. A resizable mask appears over the image, and some controls appear.
Masking an Image with a Shape You can use a shape to define the boundaries of an image. To mask an image with a shape: 1 Select the image and choose Format > “Mask with Shape” > shape. You can also Shift-click to select a shape and an image, and then click Mask in the toolbar (or choose Format > “Mask with Selected Shape”). 2 Drag the image to position the part you want to show. To move the mask, click the dotted edge of the mask and drag it.
3 Click the color you want to make transparent, and then drag slowly over it. Dragging selects the contiguous area that uses the same color. As you drag, the selection grows to include the area that uses the same color. You can control how much of the image is selected by dragging less or more. 4 Repeat step 3 as many times as you like. Dragging more here... ...selects more of the image. 5 Press Return or click outside the image when you’re done.
2 Choose View > Show Adjust Image to open the Adjust Image window. Adjust the lightness. Adjust the contrast of light and dark tones. Change the color intensity. Introduce more warmth or coolness. Change the amount of red or green tones. Sharpen or soften the focus. Adjust shadows and highlights. Understand the relationship between shadows and highlights. Change the levels of dark and light tones. Restore original settings. Enhance colors automatically. 3 Use the controls to make adjustments.
4 To restore the original settings, click Reset Image. To save any changes you made, save the spreadsheet. The settings at the time you save the spreadsheet are visible anytime you open the Adjust Image window. Using PDF Files as Graphics If you intend to greatly enlarge or reduce a graphic’s dimensions, consider converting it to a PDF file before bringing it into Numbers. PDF files don’t lose their crispness when they are significantly resized.
Using Sound and Movies You can add sound, movies, and Flash files to your spreadsheet if it will be viewed onscreen as a Numbers file. The movie or sound file plays when you double-clicks its icon. Note: Some media files are protected under copyright law. Also, some downloaded music may be played only on the computer where the download occurred. Make sure you have permission to use the files you want to include.
m Choose Insert > Choose, select the movie file, and then click Insert. m To add a movie file using a media placeholder, see “Using Media Placeholders” on page 170. Adjusting Media Playback Settings In the QuickTime Inspector, you can change such settings as when to start and stop a movie, and the frame to display until the movie starts playing. To set media playback preferences: 1 Click Inspector in the toolbar, and then click the QuickTime Inspector button. 2 Click the movie or sound object to select it.
9 Adding Address Book Data to a Table 9 This chapter tells you how to add contact information stored in Address Book and vCards to a Numbers table. Address Book fields are a rich source of names, phone numbers, addresses, and other data for people. You can use Address Book data to quickly create tables that consolidate information you need for business contacts, personal friends, holiday correspondents, and so on.
Mapping Column Names to Address Book Field Names The following table summarizes the Address Book fields that Numbers supports. The synonyms are alternative names you can use to add a particular Address Book field value.
Address Book field names Field name synonyms Home AIM Home IM, Home IM handle, Home IM name, Home IM address, Home chat, Home chat handle, Home chat name, Home chat address Other AIM Other IM, Other IM handle, Other IM name, Other IM address, Other chat, Other chat handle, Other chat name, Other chat address Yahoo Work Yahoo Home Yahoo Other Yahoo Address Street address (first available street address) Street City (first available city) Town State (first available state) Zip Zip code, Postal code
Adding Address Book Data to an Existing Table You can add data from Address Book or from a vCard to an existing table if the table’s header row contains names that match the field names listed in “Mapping Column Names to Address Book Field Names” on page 180. To add contact data to an existing table: 1 Identify the Address Book or vCard fields whose values you want to copy into the table. 2 Make sure the table has a header row whose cells contain supported Address Book or vCard field names.
10 Sharing Your Numbers Spreadsheet 10 This chapter describes the various ways you can distribute your Numbers spreadsheet so others can view or use it. In addition to printing or faxing your spreadsheet, you can share your spreadsheet electronically. By exporting to other document formats, you can collaborate with others who use Excel and other applications. You can also send a document directly to iWeb to attach it to a blog entry.
Type the number of copies you want to print in the Copies field, and then select Collated if you want each group of pages to print together in order before printing the next group. If you want to print a batch of page 1, then page 2, then page 3, and so on, deselect the checkbox. 6 Use the Copies & Pages pop-up menu in the Print dialog to select printing options. You can print your sheet in reverse order, print only odd or even pages, print a cover page (separator page), and more.
4 To print double-sided, choose Layout from the pop-up menu below the Presets pop-up menu, and then select a binding option. Long-Edge Binding: Prints the pages for lengthwise binding. Short-Edge Binding: Prints the pages for binding on the short side of the paper. Not all printers can print double-sided. Check the documentation that came with your printer.
For more information about ColorSync and Quartz filters, see ColorSync Utility Help by opening the ColorSync application (located in Applications/Utilities), and then choosing Help > ColorSync Utility Help. The ColorSync Utility icon Exporting to Other Document Formats You can share your Numbers spreadsheets across different platforms by exporting them to a file in Excel, PDF, or comma-separated values (CSV) format. To export a spreadsheet to another file format: 1 Choose File > Export.
Sending a Spreadsheet to iWeb If you use (or intend to use) iWeb to create a website, you can send a Numbers spreadsheet directly to your iWeb blog or podcast. The spreadsheet becomes an attachment that your website visitors can download. To send a spreadsheet to iWeb: 1 Make sure you have iWeb ’08 or later installed. 2 Open your Numbers spreadsheet, and then choose File > Send to iWeb > PDF or choose File > Send to iWeb > Numbers Document. PDF creates a PDF version of your spreadsheet.
11 Designing Your Own Numbers Spreadsheet Templates 11 This chapter is for designers who want to create their own spreadsheet templates. This chapter assumes you are already familiar with the features of Numbers described in earlier chapters. Designing a Template The templates that come with Numbers provide ideas for designing the look of charts, tables, and other objects. To create your own templates, you can do something as simple as adding your company logo to an existing template.
Step 2: Define Reusable Tables A template has one or more reusable tables, which let you add tables that have already been styled and sized. They can also contain predefined content, such as formulas and text. When using a template, you add a reusable table by clicking Tables in the toolbar and choosing a table from the pop-up menu. You can use or redefine the reusable tables in a Numbers template, or you can create your own. “Defining Reusable Tables” on page 49 provides instructions.
Defining Default Attributes for Text Boxes and Shapes The default attributes of text boxes and shapes determine their fill, stroke, shadow, and opacity when they’re first inserted on a page. Default attributes also determine the appearance of text you place in the boxes or shapes. To define default text boxes and shapes: 1 Add the objects to a sheet. Click Text Box in the toolbar to add a text box, and click Shapes in the toolbar and choose any shape.
Step 4: Create Initial Spreadsheet Content You can use predefined content to model the look of a finished spreadsheet or to provide instructions and other productivity aids for template users. Predefining Tables and Other Objects for a Custom Template Add tables, charts, text boxes, images, shapes, and other initial content to your templates to help users be more productive and to make the objects in the spreadsheet visually appealing.
Predefining Sheets for a Custom Template Add multiple sheets to a template when it contains content you want to separate into groups. Here are ways to set up sheets for a template: m You can add sheets to or remove sheets from a Numbers template. See “Adding and Deleting Sheets” on page 38 for details. m To move sheets around and reorder their content, see “Reorganizing Sheets and Their Contents” on page 38.
12 Dictionary of Functions 12 This chapter describes functions you can use in cell formulas. This chapter describes each of the functions you can use in a table cell formula. For information about using functions in table cell formulas, see “Using Formulas” on page 83 and “Using Functions” on page 96. About Functions A function is a named operation that you can include in a formula to perform a calculation or to manipulate data in a table cell.
The following table defines terms that are commonly used in function descriptions. Term or symbol Meaning italic An argument you replace with an actual value or a reference to a cell containing a value. commas and semicolons The syntax descriptions for functions use commas to separate arguments. If your International System Preferences are set up to use the comma as a decimal separator, separate arguments using a semicolon instead of a comma. [ brackets ] Surround an optional argument.
Date and Time Functions The date and time functions perform calculations on date, time, or numeric expressions. Numbers includes these date and time functions. Function Description DATE (page 224) Combines individual year, month, and day numbers into a single date. DATEDIF (page 225) Finds the time interval between two dates. DAY (page 226) Extracts the day number from a date. EDATE (page 229) Finds the date some number of months before or after a given date.
Financial Functions These functions operate on numbers and require numeric expressions as arguments. Unless otherwise stated, these functions return numeric values. Numbers provides these financial functions. 196 Function Description ACCRINT (page 205) Calculates the total accrued interest for a security that pays periodic interest. ACCRINTM ()page 206 Calculates the total accrued interest for a security that pays interest at maturity.
Function Description NPV (page 254) Calculates the net present value of an investment based on a series of future cash flows and a fixed interest rate. PMT (page 257) Calculates fixed, periodic payments given a fixed interest rate, a number of periods, the present value, the future value, and the type of payments. PPMT (page 258) Calculates the principal portion of a payment for a loan or investment based on periodic fixed payments and a fixed interest rate.
Logical Functions The logical functions evaluate the logical truth or falsehood of expressions and return TRUE, FALSE, or a value assigned by the function. Numbers provides these logical functions. Function Description AND (page 208) Returns TRUE if all arguments are true and FALSE otherwise. FALSE (page 231) Returns the value FALSE for compatibility with older spreadsheet applications. IF (page 236) Returns one of two values depending on whether a specified expression is TRUE or FALSE.
Reference Functions Reference functions help you find data in tables and retrieve data from cells. Numbers provides these reference functions. Function Description ADDRESS (page 207) Constructs a cell address string from separate row, column, and table identifiers. AREAS (page 208) Counts how many cell ranges are in the argument. CHOOSE (page 213) Uses an index value to retrieve a particular value from a set of values.
Numeric Functions The numeric functions calculate commonly used mathematical values. Numbers provides these numeric functions. 200 Function Description ABS (page 205) Calculates the absolute value of a number. CEILING (page 212) Rounds a number away from zero to the nearest multiple of a specified factor. COMBIN (page 215) Finds the number of different ways you can combine a number of items into groups of a specific size. EVEN (page 229) Rounds a number away from zero to the next even number.
Function Description SIGN (page 271) Returns 1 when the argument is positive, –1 when the argument is negative, and 0 when it’s zero. SQRT (page 274) Calculates the square root of a number. SUM (page 277) Calculates the sum of a set of numbers. SUMIF (page 278) Calculates the sum of a set of numbers that satisfy a specified condition. SUMPRODUCT (page 278) Calculates the sum of the products of corresponding numbers in two or more ranges.
Statistical Functions Statistical functions perform statistical calculations and require numeric expressions as arguments. The numeric expression can be a number, cell reference, or range. Unless otherwise stated, statistical functions result in a number. Numbers provides these statistical functions. 202 Function Description AVEDEV (page 210) Calculates the average difference (deviation) of data points from their arithmetic mean (average).
Function Description PERCENTILE (page 256) Finds the value within a range of values that corresponds to a particular percentile. POISSON (page 257) Calculates event rate probabilities based on the Poisson distribution. PROB (page 261) Calculates the probability of a range of values if you know the individual probability of each value. RANK (page 264) Finds the rank of a number within a range of numbers.
Text Functions The text functions help you work with strings of characters. Numbers provides these text functions. 204 Function Description CHAR (page 212) Returns the character that corresponds to a numeric Unicode character code. CLEAN (page 213) Removes most common nonprinting characters (Unicode character codes 0–31) from text. CODE (page 214) Returns the Unicode code number of the first character in a string. CONCATENATE (page 216) Concatenates strings.
Function Descriptions The function descriptions in this section are arranged in alphabetical order by function name. ABS The ABS function calculates the absolute value of a number. The result is either a positive number or zero. ABS(number) Â number: A number, a numeric expression, or a reference to a cell containing a numeric expression. Examples If cell A1 contains 5, ABS(A1) returns 5. ABS(8-5) returns 3. ABS(5-8) returns 3. ABS(0) returns 0. If cell A1 is empty, ABS(A1) returns 0.
3 uses actual/365 (calendar days for each month but 365 days per year). 4 uses 30E/360 (European 30/360). Examples If A1:A7 contain 1/1/2000, 3/1/2000, 7/1/2000, 0.10, 1000, 2, 0: ACCRINT(A1, A2, A3, A4, A5, A6, A7) returns 50. ACCRINTM The ACCRINTM function calculates the total accrued interest for a security that pays interest at maturity. ACCRINTM(issue, settle, rate, par, [day-count]) Â issue: The date when the security was issued.
Notes To see the resulting angle in degrees instead of radians, use the DEGREES function. Examples ACOS(SQRT(2)/2) returns 0.785398163397448, which is approximately pi/4. ACOS(0.54030230586814) returns 1. DEGREES(ACOS(.5)) returns 60, the degree measure of an angle that has a cosine of 0.5. ACOSH The ACOSH function calculates the inverse hyperbolic cosine of a number. ACOSH(number) Â number: A number greater than or equal to 1. Examples ACOSH(10.0676619957778) returns 3. ACOSH(COSH(5)) returns 5.
 table: Optional; the name of the table. If the table is on another sheet, you must also include the name of the sheet. Examples ADDRESS(3,5) creates the address $E$3. ADDRESS(3,5,2) creates the address E$3. ADDRESS(3,5,3) creates the address $E3. ADDRESS(3,5,4) creates the address E3. ADDRESS(3,3,,, “Sheet 2 :: Table 1”) creates the address Sheet 2 :: Table 1 :: $C$3. AND The AND function returns TRUE if all arguments are true and FALSE otherwise (logical conjunction). AND(expression, [expression, . . .
Examples AREAS(A1:F8) returns 1. AREAS(C2:C8 B6:E6) returns 1. AREAS((A1:F8, A10:F18)) returns 2. AREAS((A1:C1, A3:C3, A5:C5)) returns 3. ASIN The ASIN function returns the arcsine (the inverse sine) of a number. While the SIN function takes an angle and returns the sine, the ASIN function takes a sine and returns the corresponding angle. The result is expressed in radians, in the range –pi/2 to pi/2 (– 90 to 90 degrees). ASIN(sin) Â sin: A sine value in the range –1 through 1.
Notes To see the resulting angle in degrees instead of radians, use the DEGREES function. Examples ATAN(1) returns the angle measure 0.785398163 radians (45 degrees), which has a tangent of 1. DEGREES(ATAN(1)) returns 45. ATAN2 The ATAN2 function calculates the angle, relative to the positive x-axis, of the line passing through the origin and the specified point. The angle is expressed in radians, in the range –pi through pi. ATAN(x, y) Â x: The x-coordinate of the point the line passes through.
Examples AVEDEV(2,2,2,4,4,4) returns 1. AVEDEV(2,2,2,2,3,3,3,3,4,4,4,4) returns 0.6666667. AVERAGE The AVERAGE function calculates the average (arithmetic mean) of a set of numbers. AVERAGE(number, [number, . . .]) Â number: One or more numbers, numeric expressions, or references to cells containing numbers or numeric expressions. Notes To calculate the average, AVERAGE divides the sum of the numbers by the number of numbers. Table cells containing text values or Boolean values (TRUE or FALSE) are ignored.
Examples AVERAGEA(A1:A4) returns 2.5 if cells A1 through A4 contain 4, a, 6, b. The text values are counted as zeros in the sum of 10 and included in the count of values (4). Compare with AVERAGE(A1:A4), which ignores the text values completely for a sum of 10, a count of 2, and an average of 5. AVERAGEA(A1:A4) returns 4 if cells A1 through A4 contain 5, a, TRUE, 10. The text value counts zero and TRUE counts 1 for a sum of 16 and a count of 4. AVERAGEA(A1:A4) returns 0.
Notes Not all numbers in a character are associated with a printable character. You can use the Character Palette, which you can enable in International preferences, to view entire sets of characters and their codes. You can use the CODE function to find the numeric code for a specific character. Examples CHAR(98.6) returns "b", which is represented by the code 98. CODE("b") returns 98. CHOOSE The CHOOSE function uses a specified index value to retrieve a value from a set of values.
Examples Suppose you copy what you believe to be the text "a b c d e f" from another application and paste it into cell A1, but instead see "a b c ? ?d e f". You can try using CLEAN to remove the unexpected characters: CLEAN(A1) returns "a b c d e f". CODE The CODE function returns the Unicode code number of the first character in a specified string. CODE(text) Â text: A text expression or reference to a cell containing a text expression.
COLUMNS The COLUMNS function counts how many columns are in a range of cells. COLUMNS(cell-range) Â cell-range: A range of cells in a table. Notes If you select an entire row for cell-range, Numbers returns the total number of cells in the row, which changes when you resize the table. Examples COLUMNS(B3:D10) returns 3, the number of columns in the range (columns B, C, and D). COLUMNS(5:5) returns the total number of columns in row 5.
CONCATENATE The CONCATENATE function joins (concatenates) strings. CONCATENATE(string, [string, . . .]) Â string: A string or a reference to a cell containing a string. To include a string in an argument, place quotation marks around it. Notes You can also use the & operator to concatenate strings. Examples If cell A1 contains Lorem and cell B1 contains Ipsum, CONCATENATE(B1,", ",A1) returns "Ipsum, Lorem". CONCATENATE("a", "b","c") returns "abc". "a"&"b"&"c" returns "abc".
CORREL The CORREL function calculates the correlation between two data sets based on linear regression analysis. CORREL(y-range, x-range) Â y-range: A range of cells containing the dependent variable (y). Â x-range: A range of cells containing the independent variable (x). Examples Given the following table: CORREL(D2:D7, E2:E7) returns 1. CORREL(B2:B7, A2:A7) returns 0.977265. COS The COS function calculates the cosine of an angle expressed in radians.
COSH The COSH function calculates the hyperbolic cosine of a number. COSH(number) Â number: Any real number. Examples COSH(0) returns 1. COSH(1) returns 1.543. COSH(5) returns 74.21. COSH(10) returns 11,013.233. COUNT The COUNT function counts the number of arguments and referenced cells that contain numbers, numeric expressions, or dates. COUNT(value, [value, . . .]) Â value: One or more numbers.
COUNTA The COUNTA function counts the number of arguments and referenced cells that are not empty. COUNTA(value, [value, . . .]) Â value: A reference to one or more cells or an argument containing a value. Notes To count only cells or arguments that contain numbers or dates, use the COUNT function. Examples Given the following table: COUNTA(A1:E1) returns 5. COUNTA(A2:E2) returns 5. COUNTA(A3:E3) returns 5. COUNTA(A4:E4) returns 5. COUNTA(A5:E5) returns 2. COUNTA(1, "a", TRUE, FALSE) returns 4.
COUNTBLANK The COUNTBLANK function counts the number of cells in a range that are empty. COUNTBLANK(range) Â range: A single range of cells. Examples Given the following table: COUNTBLANK(A1:E1) returns 0. COUNTBLANK(A2:E2) returns 1. COUNTBLANK(A3:E3) returns 2. COUNTBLANK(A4:E4) returns 1. COUNTBLANK(A5:E5) returns 5. COUNTIF The COUNTIF function counts the number of cells in a range that satisfy a condition. COUNTIF(test-range, condition) Â test-range: The range of cells to be tested.
COUPDAYBS The COUPDAYBS function counts the number of days from the beginning of the coupon period in which settlement of a security occurs to the settlement date. COUPDAYBS(settle, maturity, frequency, [day-count]) Â settle: The date of the settlement, usually the date when the security was purchased. Â maturity: The date the security matures (expires). Â frequency: The number of coupon payments each year. 1 means annual payments. 2 means semiannual payments (twice per year).
4 means quarterly payments (four per year). Â day-count: Optional; specifies the number of days per month and days per year used in the calculations (sometimes called the day count basis). 0 or omitted uses 30/360 (30 days for each month, 360 days in a year). 1 uses actual/actual (the number of days shown on a calendar). 2 uses actual/360 (calendar days for each month but 360 days per year). 3 uses actual/365 (calendar days for each month but 365 days per year). 4 uses 30E/360 (European 30/360).
2 uses actual/360 (calendar days for each month but 360 days per year). 3 uses actual/365 (calendar days for each month but 365 days per year). 4 uses 30E/360 (European 30/360). Examples If A1:A4 contain 9/1/2000, 1/1/2001, 4, 1: COUPDAYSNC(A1, A2, A3, A4) returns 30 (the number of days from September 1 to the next coupon date, October 1, on the basis of 30 days in September).
Examples If A1:A4 contain 2/1/2000, 1/1/2001, 4, 0: COUPNUM(A1, A2, A3, A4) returns 4. COUPNUM(DATE(2000,6,1), A2, A3, A4) returns 3. COUPNUM(DATE(2000,9,1), A2, A3, A4) returns 2. COUPNUM(DATE(2000,11,1), A2, A3, A4) returns 1. COVAR The COVAR function calculates the covariance of two data sets. COVAR(range1, range2) Â range1: A reference to the cells containing the first data set. Â range2: A reference to the cells containing the second data set. The dimensions of range1 and range2 must be the same.
DATEDIF The DATEDIF function finds the number of days, months, or years between two dates. DATEDIF(start-date, end-date, method) Â start-date: The starting date. Â end-date: The ending date. Â method: Specifies whether the time difference is expressed in years, months, or days and how the calculation handles dates in different years or months: "D" counts the days between the dates. "M" counts the months between the dates. "Y" counts the years between the dates.
DAY The DAY function extracts the day number from a date. DAY(date-time) Â date-time: Any valid Numbers date. Examples DAY("4/6/88 11:59:22 AM") returns 6. DB The DB function calculates the depreciation of an asset for a specified period using the fixed-declining balance method. DB(cost, salvage, life, period, [first-year-months]) Â cost: The initial cost of the asset. Â salvage: The salvage value of the asset. Â life: The number of periods over which the asset is depreciating.
DDB The DDB function calculates the depreciation of an asset based on a specified depreciation rate. If you don’t specify a rate, it defaults to the double-declining balance method. DDB(cost, salvage, life, period, [factor]) Â cost: The initial cost of the asset. Â salvage: The salvage value of the asset. Â life: The number of periods over which the asset is depreciating. Â period: The period for which you want to calculate the depreciation. Must use the same time units as life.
DISC The DISC function calculates the discount rate of a security. DISC(settle, maturity, price, value, [day-count]) Â settle: The date of the settlement, usually the date when the security was purchased. Â maturity: The date the security matures (expires). Â price: The cost of the security per $100 par value. Â value: The redemption value per $100 par value. Â day-count: Optional; specifies the number of days per month and days per year used in the calculations (sometimes called the day count basis).
Examples DOLLAR(2323.124) returns $2,323.12. DOLLAR(2323.125) returns $2,323.13. DOLLAR(99.554, 0) returns $100. DOLLAR(12, 3) returns $12.000. DOLLAR(-12, 3) returns ($12.000), with parentheses indicating a negative amount. DOLLAR(123, -1) returns $120. EDATE The EDATE function finds a new date some number of months before or after a given date. EDATE(date, month-offset) Â date: The starting date. Â month-offset: The number of months before or after date.
EXACT The EXACT function returns TRUE if the argument strings are identical in case and characters. EXACT(string1, string2) Â string1: A text expression. To include the string as the argument, place quotation marks around it. Â string2: The second text expression. Examples EXACT("toledo", "toledo") returns TRUE. EXACT("Toledo", "toledo") returns FALSE. EXACT(toledo", "tol" & "edo") returns TRUE. EXP The EXP function calculates e to the power of a number.
FALSE The FALSE function returns the Boolean value FALSE. It is included so you can use spreadsheets created with some older spreadsheet applications. You can specify a Boolean value of FALSE by simply typing “false” (without quotation marks) into a cell or function argument. FALSE() Â No arguments (but you must include the parentheses). Notes You can type the word FALSE into a cell or formula instead of using the FALSE() function. Examples FALSE() returns the Boolean value FALSE.
FIXED The FIXED function rounds a number to the specified number of decimal places and then converts the result to text. FIXED(number, [places], [commas]) Â number: A number. Â places: Optional; a number specifying the number of decimal places you want in the result. Â commas: Optional Boolean value; specifies whether to use position separators in the whole portion of the resulting number. FALSE or 0 leaves out position separators in the resulting number.
Examples FLOOR(0.25,1) returns 0. FLOOR(1.25,1) returns 1. FLOOR(5,2) returns 4. FLOOR(73,10) returns 70. FLOOR(-0.25,-1) returns 0. FLOOR(9,2.5) returns 7.5. FORECAST The FORECAST function uses linear regression analysis of known value pairs to find the y (dependent) value that corresponds to a chosen x (independent) value. FORECAST(x, y-values, x-values) Â x: The x value for which you want to find a corresponding y value. Â y-values: A range of cells containing the known y values.
 present-value: Optional; the present value of the investment, specified as a negative number.  when-due: Optional; specifies whether payments are due at the beginning or end of each period: 0 (or omitted) means payments are at the end of each period. 1 means payments are at the beginning of each period. Examples FV(5%,10,-300,-3000,0) calculates the value in 10 years of a $3000 initial investment, with an added payment of $300 at the end of each year, at an annual rate of 5%.
 exact-match: Optional Boolean value; determines whether an exact match is required: TRUE, 1, or omitted selects the column with the largest top-row value that is less than the search value if there is no exact match. FALSE or 0 returns an error if there is no exact match. Examples Given the following table: HLOOKUP(20,A1:E4,2) returns "E." HLOOKUP(39,A1:E4,2) returns "E." HLOOKUP("M",A2:E4,2) returns "dolor." HLOOKUP("C",A2:E3,2) returns "lorem." HLOOKUP("blandit",A3:E4,2) returns "5.
HYPERLINK The HYPERLINK function creates a clickable link that opens a webpage or new email message window. HYPERLINK(url, [link-text]) Â url: A standard universal resource locator. Â link-text: Optional; the text that appears as a clickable link in the cell. If you omit this argument, the URL is used as the link text. Examples HYPERLINK("http://www.apple.com","Apple") creates a link with the text Apple that opens the default web browser to the Apple homepage. HYPERLINK("mailto:janedoe@example.
INDEX The INDEX function retrieves the value in the cell located at the intersection of the specified row and column within a range of cells. You specify the location by indicating how many rows down and how many columns to the right the cell is in relation to the upper-left corner of the range. INDEX(cell-range, [row], [column], [area]) Â cell-range: A range or list of ranges containing the values you want to return.
Examples If cell A1 contains 99 and A20 contains A1, then: INDIRECT(A20) returns 99, the contents of cell A1. INT INT returns the nearest integer that is less than or equal to the number. INT(number) Â number: The number you want to convert to an integer. It can be a number, a numeric expression, or a reference to a cell containing a numeric expression. Notes Other functions related to rounding numbers: CEILING, ROUND, ROUNDUP, ROUNDDOWN, TRUNC. Examples INT(1.49) returns 1. INT(1.50) returns 1. INT(1.
Examples Given the following table: INTERCEPT(A2:F2, A1:F1) returns 1. SLOPE(A2:F2, A1:F1) returns 2. INTERCEPT(A5:F5, A4:F4) returns 2.392. IPMT The IPMT function calculates the interest portion of a payment for a loan or investment at a specified period based on fixed, periodic payments and a fixed interest rate. IPMT(rate, period, num-periods, present-value, [future-value], [when-due]) Â rate: The interest rate for each period. Â period: The period for which you want to find the interest.
IRR The IRR function calculates the internal rate of return for an investment that is based on a series of potentially irregular cash flows rather than fixed-interest income. The rate returned is the rate that makes the net present value of a series of cash flows 0. IRR(flows-range, [estimate]) Â flows-range: A range of cells that contain cash flow values. Positive values represent income. Negative values represent expenditures. All values must represent equal time intervals.
ISERROR The ISERROR function returns TRUE if the expression in the function’s argument evaluates to an error and FALSE otherwise. ISERROR(cell) Â cell: A reference to a table cell that contains a formula. Examples If cell A1 contains =QUOTIENT(3,0): ISERROR(A1) returns TRUE. (You can’t divide by 0.) ISEVEN The ISEVEN function returns TRUE if the value is even (leaves no remainder when divided by 2) and FALSE otherwise.
Examples ISODD(3) returns TRUE. ISODD(3.75) returns TRUE. ISODD(2) returns FALSE. ISPMT The ISPMT function calculates the interest due for a particular payment period of an investment. ISPMT(rate, period, num-periods, present-value) Â rate: The interest rate for each period. Â period: The period for which you want to find the interest. Â num-periods: The number of periods in the life of the investment. Â present-value: The present value of the loan or investment.
Examples Given the following table: LARGE(A2:E2, 1) returns 50, the largest value in the range. LARGE(A2:E2, 2) returns 40, the second-largest value in the range. LARGE(A2:E2, 5) returns 10, the fifth-largest value in the range. LARGE(A2:E2) displays an error; you must specify n. LARGE(A2:E2, 0) displays an error; n must be greater than 0. LARGE(A2:E2, 6) displays an error; you can’t find the sixth out of five values. LCM The LCM function finds the least common multiple of the specified numbers.
LEFT The LEFT function retrieves the specified number of characters from the left end of a string. LEFT(text, [length]) Â text: A text expression. Â length: Optional; the number of characters to retrieve. If omitted, the leftmost character is retrieved. Examples LEFT("one two three", 2) returns "on". LEFT("abc") returns "a". LEN The LEN function counts the number of characters in a string. The count includes all spaces, numbers, and special characters. LEN(text) Â text: A text expression.
LOG The LOG function calculates the logarithm of a number with a specified base. LOG(number, [base]) Â number: A positive number. Â base: Optional; a positive number not equal to 1. If omitted, 10 is used. Examples LOG(8, 2) returns 3. LOG(100, 10) and LOG(100) both return 2. LOG(5.0625, 1.5) returns 4. LOG10 The LOG10 function calculates the base-10 logarithm of a number. LOG10(number) Â number: A positive number. Notes To find the logarithm for a base other than 10, use the LOG function.
Examples Given the following table: LOOKUP("C", A1:F1, A2:F2) returns 30. LOOKUP(40, A2:F2, A1:F1) returns D. LOOKUP("B", A1:C1, D2:F2) returns 50. LOWER The LOWER function converts uppercase characters in text to lowercase. LOWER(text) Â text: A text expression or reference to a cell containing a text expression. Examples LOWER("UPPER") returns "upper". LOWER("Lower") returns "lower". LOWER("MiXeD") returns "mixed". MATCH The MATCH function finds the position of a value within a row or column range.
Notes When searching for text, case is ignored. MATCH works only on a range that is part of a single row or column; you can’t use it to search a two-dimensional table. Examples Given the following table: MATCH(40, A1:A5) returns 4. MATCH(40, E1:E5) returns 1. MATCH(35, E1:E5, 1) returns 3 (30 is the largest value less than or equal to 35). MATCH(35, E1:E5, -1) returns 1 (40 is the smallest value greater than or equal to 35). MATCH(35, E1:E5, 0) displays an error (no exact match can be found).
MAXA The MAXA function finds the largest number in a set of values. In addition to numeric values, text values and logical FALSE are counted as 0 and logical TRUE is counted as 1. MAXA(value, [value, . . .]) Â value: A series of numbers or numeric expressions, or references to cells containing numbers, numeric expressions, text, or Boolean values. Notes Text entered directly in the arguments results in an error. Examples MAXA(1, 2, 3, 4) returns 4.
Examples MID("lorem ipsum dolor sit amet",7, 5) returns "ipsum". MID("1234567890",4,3) returns "456". MID("shorten", 5, 20) returns "ten". MIN The MIN function finds the smallest number in the list of arguments. MIN(number, [number, . . .]) Â number: A series of numbers, numeric expressions, or references to cells containing numbers or numeric expressions. Notes Cells that don’t contain numbers are ignored. Text entered directly in the arguments results in an error. Examples MIN(5, 5, 5, 5, 6) returns 5.
MINUTE The MINUTE function extracts the minutes from a date or time. MINUTE(date-time) Â date-time: Any valid Numbers date or time value. Examples MINUTE("4/6/88 11:59:22 AM") returns 59. MIRR The MIRR function calculates the modified internal rate of return of a series of periodic cash flows. MIRR stands for modified internal rate of return. MIRR(cash-flows, finance-rate, reinvest-rate) Â cash-flows: One or more future cash flows. Values are ranges or arrays.
Examples MOD(6, 3) returns 0. MOD(7, 3) returns 1. MOD(8, 3) returns 2. MOD(-8, 3) returns 1. MOD(4.5, 2) returns 0.5. MOD(7, 0.75) returns 0.25. MODE The MODE function finds the most frequently occurring value in a set of numbers. MODE(number, [number, . . .]) Â number: A set of numbers, numeric expressions, or references to cells containing numbers or numeric expressions. Notes If more than one number occurs multiple times in the arguments, MODE returns the first qualifying number.
MROUND The MROUND function rounds a number to the nearest multiple of a specified factor. MROUND(number, factor) Â number: The number you want to round. It can be a numeric expression, or a reference to a cell containing a numeric expression. Â factor: The number whose multiples you want to round to. Notes Relative number size is based on magnitude (distance from 0). Both arguments must have the same sign. Examples MROUND(2, 3) returns 3. MROUND(4, 3) returns 3. MROUND(4.4999, 3) returns 3. MROUND(4.
NOW The NOW function gets the current date and time from the computer’s clock. Time zones aren’t taken into account. NOW() Â No arguments (but you must include the parentheses). Notes The displayed date and time are updated every time you open or modify your document. Whether you see the date, the time, or both depends on the cell formatting. See “Using the Date and Time Format” on page 62 for more information.
Examples NPER(0.1/12,-100,1000) returns 10.5 months, which you would round to 11, the time it takes to pay off a $1,000 loan at an annual interest rate of 10% if you make monthly payments of $100. NPV The NPV function calculates the net present value of an investment based on a series of future cash flows, assuming a fixed interest rate. NPV(rate, cash-flow, [cash-flow, . . .]) Â rate: The fixed interest rate.
OFFSET The OFFSET function retrieves a range of cells that is the specified number of rows and columns away from the specified base cell. OFFSET(base, row-offset, column-offset, [rows], [columns]) Â base: The address of the cell from which the offsets are measured. Â row-offset: The number of rows from the base cell to the target cell. 0 means the target cell is in the same row as the base cell. A negative number means the target is to the left of the base.
PERCENTILE The PERCENTILE function finds the value within a range of values that corresponds to a particular percentile. PERCENTILE(data, percentile) Â data: A range of cells containing the data. Â percentile: The percentile value you want to find. Examples Given the following table: PERCENTILE(A2:E2, 0.5) returns 80, the 50th percentile of the range. PERCENTILE(A4:E4, 0.5) returns 90, the 50th percentile of the range.
PMT The PMT function calculates fixed, periodic payments, given the interest rate, number of periods, present value, future value, and type of payment. PMT(rate, num-periods, present-value, [future-value], [when-due]) Â rate: The interest rate per period. Â num-periods: The number of periods. Â present-value: The present value of the investment. Â future-value: Optional; the future value of the investment or cash value remaining after final payment. If omitted, future-value is assumed to be 0.
Examples For a mean of 10 and an arrival rate of 8: POISSON(8, 10, FALSE) returns 0.112599. POWER The POWER function raises a number, the base, to a power, the exponent. Produces the same result as the ^ operator. POWER(base, exponent) Â base: The number that you want to raise to a power. Â exponent: The power to which you want to raise the base number. Notes You can also use the exponentiation operator ^ to raise a number to a power: POWER(2, 3) has the same result as 2^3. Examples POWER(2, 3) returns 8.
Notes Be sure that the interest rate is expressed as the rate per period. For example, if the interest rate is 10% per year and the payment period is monthly, the rate used in the function should be 0.1/12 (approximately 0.00833 per month). Examples To find the principal portion of the first payment on a $1000 loan at 12% annual interest with payments due at the end of each month: PPMT(0.12/12, 1, 12, 1000) returns -$78.85.
PRICEDISC The PRICEDISC function calculates the price of a discounted security per $100 par value. PRICEDISC(settle, maturity, discount, value, [day-count]) Â settle: The settlement date. Â maturity: The date when the security expires. Â discount: The discount rate. Â value: The redemption value on the maturity date. Expressed per $100 par value. Â day-count: Optional; specifies the number of days per month and days per year used in the calculations (sometimes called the day count basis).
Examples If cells A1:E1 contain 1/1/2000, 1/1/2010, 1/1/1999, 0.1, 0.1: PRICEMAT(A1, B1, C1, D1, E1) returns 95. PROB The PROB function calculates the probability of a range of values if you know the probabilities of the individual values. PROB(values, probabilities, lower, [upper]) Â values: A range of cells containing data values. Â probabilities: A range of cells containing the probability of each corresponding value in values. The sum of the probabilities must add up to 1.
PRODUCT The PRODUCT function calculates the product of the numbers in the arguments. PRODUCT(number, [number, . . .]) Â number: One or more numbers or numeric expressions, or references to cells containing numbers or numeric expressions. Notes Empty cells in a range are ignored and do not affect the result. Examples PRODUCT(2, 4) returns 8. PRODUCT(0.5, 5, 4, 5) returns 50. PROPER The PROPER function changes the first character in each word of text to uppercase.
 future-value: Optional; the future value of the investment or cash value remaining after final payment. If omitted, future value is assumed to be 0 and you must include payment.  when-due: Optional; specifies whether payments are made at the beginning or end of each period: 0 (or omitted) means payments are at the end of each period. 1 means payments are at the beginning of each period. Examples PV(10%,5,-100,5000) returns -$2725.53, unless the cell holding the return value has been formatted.
RAND The RAND function generates a random number greater than or equal to 0 and less than 1. RAND() Â No arguments (but you must include the parentheses). Notes Any time you change a value in the table, a new random number is generated. Examples RAND() returns, for example, 0.67, 0.12, 0.8, and 0.9 after 4 recalculations. RANDBETWEEN The RANDBETWEEN function generates a random integer in the range of the specified minimum and maximum. RANDBETWEEN(min, max) Â min: The smallest number.
Notes Equal numbers are ranked the same, but each counts as a rank position, so the next different number doesn’t get the next rank position. (Just like in sports competitions, if there’s a tie for first place, the next competitor gets third place, not second.) See the examples. Examples Given the following table: RANK(20, A2:E8) returns 7. RANK(20, A2:E2) returns 4 (50 ranks first). RANK(20, A2:E2, 1) returns 2 (10 ranks first). RANK(10, A4:E4) returns 5 (10 is the smallest in the range).
 estimate: Optional; an initial rough estimate of the interest rate. If omitted, 10% is used. Try adjusting the estimate if RATE fails to return a result. Examples RATE(10*12,,-5000,20000) returns 1.16% (when using a fixed number format with a precision of 4). The return value indicates that a monthly interest rate of 1.16% (or 13.9% annually) is required for $5,000 to grow to $20,000 in 10 years. The number of years is multiplied by 12 to find the number of monthly payments.
RIGHT The RIGHT function retrieves the specified number of characters from the right end of a string. RIGHT(text, [length]) Â text: A text expression. Â length: Optional; the number of characters you want to retrieve. If omitted, returns 1 character. Examples RIGHT("one two three", 2) returns "ee". RIGHT("abc") returns "c". ROMAN The ROMAN function converts a number to Roman numerals. ROMAN(number, [style]) Â number: The Arabic numeral that you want to convert. Must be nonnegative and less than 4000.
Examples ROMAN(12) returns XII. ROMAN(999) returns CMXCIX. ROMAN(999,1) returns LMVLIV. ROMAN(999,2) returns XMIX. ROMAN(999,3) returns VMIV. ROMAN(999,4) returns IM. ROUND The ROUND function rounds a number to the specified number of places. ROUND(number, digits) Â number: The number you want to round. It can be a numeric expression in the function or a reference to a cell containing a numeric expression. Â digits: The number of digits you want to round to, relative to the decimal point.
Notes Other functions related to rounding numbers: CEILING, INT, ROUND, ROUNDUP, TRUNC. Examples ROUNDDOWN(1.49,0) returns 1. ROUNDDOWN(1.50,0) returns 1. ROUNDDOWN(1.23456,3) returns 1.234. ROUNDDOWN(1111.222,-2) returns 1100. ROUNDDOWN(-2.2,0) returns -2. ROUNDDOWN(-2.8,0) returns -2. ROUNDUP The ROUNDUP function rounds a number away from zero to the specified number of places. ROUNDUP(number, digits) Â number: The number you want to round.
ROW The ROW function returns the row number of the specified cell. ROW([cell]) Â cell: Optional; a reference to a single cell. If not provided, ROW returns the row of the cell containing the function. Examples ROW(B7) returns 7, the number of row 7. ROW() returns the absolute row number of the cell containing the function. ROWS The ROWS function counts how many rows are in the specified range of cells. ROWS(cell-range) Â cell-range: A reference to a range of cells.
Examples SEARCH("ra", "abracadabra") returns 3; the first occurrence of the string "ra" starts at the third character in "abracadabra". SEARCH("ra", "abracadabra", 5) returns 10, the position of the first occurrence of string "ra" when you start looking at position 5. SECOND The SECOND function extracts the seconds from a date. SECOND(datetime) Â date-time: Any valid Numbers date or time value. Examples SECOND("4/6/88 11:59:22 AM") returns 22.
Examples SIN(1) returns 0.841470985, the sine of 1 radian (approximately 57.3 degrees). SIN(RADIANS(30)) returns 0.5, the sine of 30 degrees. SIN(PI()/2) returns 1, the sine of PI/2 radians (90 degrees). SINH The SINH function calculates the hyperbolic sine of the specified number. SINH(number) Â number: Any number. Examples SINH(0) returns 0. SINH(1) returns 1.175. SINH(5) returns 74.203. SINH(10) returns 11013.233.
SLOPE The SLOPE function calculates the slope of the best-fit line for the data set based on linear regression analysis. SLOPE(y-range, x-range) Â y-range: A range of cells containing the dependent variable y. Must be the same size as x-range. Â x-range: A range of cells containing the independent variable x. Must be the same size as y-range. Notes To find the y-intercept of the best-fit line, use the INTERCEPT function. Examples Given the following table: SLOPE(A2:F2, A1:F1) returns 2.
Examples Given the following table: SMALL(A2:E2, 1) returns 10, the smallest value in the array. SMALL(A2,E2, 2) returns 20, the third-smallest value in the array. SMALL(A2:E2, 5) returns 50, the longest value in the array. SMALL(A1:A5, 0) displays an error; rank must be greater than 0. SMALL(A1:A5, 6) displays an error; you can’t find the sixth out of five values. SQRT The SQRT function calculates the square root of a number. SQRT(number) Â number: A nonnegative numeric expression.
Examples STDEV(5, 5, 5, 5, 6) returns a standard deviation of 0.447 based on an average of 5.2. STDEV(1, 2, 3, 4, 5) returns a standard deviation of 1.58 based on an average of 3. STDEV(1, 1, 1, 1, 6) returns a standard deviation of 2.24 based on an average of 2. STDEV(2, 2, 4, 6, 6) returns a standard deviation of 2 based on an average of 4. Compare with STDEVP(2, 2, 4, 6, 6), which uses the population (true) variance to return a standard deviation of 1.79.
If the values you are analyzing are only a sample of a larger population, you might want to use the STDEV function instead. Cells that don’t contain numbers are ignored. If you want to include text or Boolean values in the computation, use the STDEVPA function. Examples STDEVP(5, 5, 5, 5, 6) returns a standard deviation of 0.4 based on an average of 5.2. STDEVP(1, 2, 3, 4, 5) returns a standard deviation of 1.41 based on an average of 3.
SUBSTITUTE The SUBSTITUTE function replaces a string of characters with new text. You can replace individual characters, whole words, or strings of characters within words. If a string occurs more than once in the text, you can specify which occurrences to change. SUBSTITUTE(text, old, new, [occurrence]) Â text: The text within which you want to replace characters. Â old: The string of characters you want to replace. Â new: The string of characters that will replace the old string.
SUMIF The SUMIF function calculates the sum of a set of numbers, but includes only numbers that satisfy a specified condition. The condition that determines whether each number is included can be performed on the number itself or on a corresponding value in a second range of values. SUMIF(test-range, "condition", [sum-range]) Â test-range: A reference to the range of cells containing values to test. Must be the same dimensions as sum-range.
Notes If any cell in any of the ranges contains a nonnumeric value, values in corresponding cells are not included in the calculation. Examples SUMPRODUCT(3,4) returns 12. SUMPRODUCT({1, 2}, {3, 4}) = 3 + 8 = 11. SUMSQ The SUMSQ function calculates the sum of the squares of a set of numbers. The numbers can be in individual cells, in ranges of cells, or included directly as arguments to the function. SUMSQ(number, [number, . . .
T The T function retrieves text from a cell. If the cell doesn’t contain a string, T returns an empty string. T(cell) Â cell: A reference to the cell from which you want to retrieve text. Notes This function is included so you can use spreadsheets created with older spreadsheet applications that might use it. Examples If cell A1 contains "text" and cell B1 is empty: T(A1) returns "text" and T(B1) returns nothing. TAN The TAN function calculates the tangent of an angle, the ratio of the sine to the cosine.
TANH The TANH function returns the hyperbolic tangent of the specified number. TANH(number) Â number: Any number. Examples TANH(0) returns 0. TANH(1) returns 0.762. TANH(5) returns 0.999909. TANH(10) returns 0.999999996. TIME The TIME function converts hours, minutes, and seconds into a time format. TIME(hours, minutes, seconds) Â hours: The number of hours (using a 24-hour clock). Â minutes: The number of minutes. Â seconds: The number of seconds.
TIMEVALUE The TIMEVALUE function converts a date, a time, or a text string to a decimal fraction of a 24-hour day. TIMEVALUE(date-time) Â date-time: A date, a time, or a text string in any of the Numbers date and time formats. Notes To convert a time that is formatted as a time value instead of a string, use the TIME function. Examples TIMEVALUE("4/6/88 12:00") returns 0.5 (noon represents one-half of the day). TIMEVALUE("12:00:59") returns 0.5007 (rounded to four decimal places of accuracy).
TRIM The TRIM function removes extra spaces from text. TRIM removes all spaces before the first character, all spaces after the last character, and all duplicate spaces between characters, leaving only single spaces between words. TRIM(text) Â text: The text from which you want to remove extra spaces. Examples TRIM(" spaces spaces spaces ") returns "spaces spaces spaces". TRUE The TRUE function returns the Boolean value TRUE.
Examples TRUNC(1.49,0) returns 1. TRUNC(1.50,0) returns 1. TRUNC(1.23456,3) returns 1.234. TRUNC(1111.222,-2) returns 1100. TRUNC(-2.2,0) returns -2. TRUNC(-2.8,0) returns -2. UPPER The UPPER function converts text to uppercase. UPPER(text) Â text: The text you want to convert to uppercase. Examples UPPER("a b c") returns "A B C". UPPER("First") returns "FIRST". VALUE The VALUE function converts a number formatted as text into a regular number.
Notes The VAR function divides the sum of the squares of the deviations of the data points by one less than the number of values to find the sample, or unbiased, variance (as opposed to the population, or true, variance). This is appropriate if the data points are only a sample of a larger data set. If the specified numbers make up the entire data set, you should use the VARP function to find the population (true) variance. Examples VAR(5, 5, 5, 5, 6) returns a variance of 0.2 based on an average of 5.2.
VARP The VARP function calculates the population (true) variance of a set of numbers. VARP(number, [number, . . .]) Â number: One or more numbers or numeric expressions, or references to cells containing numbers or numeric expressions. Notes The VARP function divides the sum of the squares of the deviations of the data points by the number of values to find the population, or true, variance (as opposed to the sample, or unbiased, variance). This is appropriate if the data points make up the entire data set.
VDB The VDB (variable declining balance) function calculates the depreciation of an asset over a chosen time interval, using either the double-declining method or another rate that you specify. VDB(cost, salvage, life, start, end, [factor], [no-switch]) Â cost: The initial cost of the asset. Â salvage: The salvage value of the asset. Â life: The number of periods over which the asset is depreciated. Â start: The first period for which you want to start the depreciation calculation.
VLOOKUP The VLOOKUP function retrieves a value from a range of columns by using a match with a left column value to choose the row and a column number to choose the column in that row. VLOOKUP(search-value, cell-range, column, [exact-match]) Â search-value: The value you want to look for in the left column of the range to choose a row. Â cell-range: The range of cells containing your data. The left column contains the data to be searched; the remaining rows contain values you can retrieve.
WEEKDAY The WEEKDAY function converts a date to a number that represents the day of the week using one of three numbering schemes. WEEKDAY(date-time, [number-scheme]) Â date-time: A date in any of the accepted date formats or a reference to a cell containing such a date. Â number-scheme: Optional; a single digit that specifies how days are numbered: 1 or omitted counts Sunday = day 1, Saturday = day 7. 2 counts Monday = 1 through Sunday = 7. 3 counts Monday = 0 through Sunday = 6.
3D charts defining scene settings 118 modifying the viewing angle 118 A ABS mathematical function 205 ACCRINT financial function 205 ACCRINTM financial function 206 ACOSH trigonometric function 207 ACOS trigonometric function 206 Address Book adding contact data to a new table 182 adding contact data to an existing table 182 fields supported 180 ADDRESS lookup function 207 Adjust Image window 174 alignment guides creating new 153 using 152 AND logical function 208 area and line charts adding shadows 117 fo
conditional formatting 67 CONFIDENCE statistical function 216 CORREL statistical function 217 COSH trigonometric function 218 COS trigonometric function 217 COUNTA statistical function 219 COUNTBLANK statistical function 220 COUNTIF statistical function 220 COUNT statistical function 218 COUPDAYBS financial function 221 COUPDAYS financial function 221 COUPDAYSNC financial function 222 COUPNUM financial function 223 COVAR statistical function 224 custom templates creating 188 creating initial content 191 def
HOUR date/time function 235 Hyperlink Inspector 143, 144 HYPERLINK lookup function 236 hyperlinks editing hyperlink text 144 to a preaddressed email message 144 to a webpage 143 types of links 143 I IF logical function 236 image formats supported 171 images adjusting brightness 175 adjusting contrast 175 adjusting exposure 175 adjusting saturation 175 adjusting sharpness 175 adjusting temperature 175 Auto Levels 175 automatically adjusting 175 histogram 175 masking (cropping) 172 placing inside an object 1
fraction format 63 number format 59 percentage format 61 scientific format 63 numeric functions 200 O objects adding a custom shape 164 adding a movie file 177 adding an image 171 adding a predrawn shape 164 adding a sound file 177 adding color and color gradients 160 adding reflections 158 adding shadows 156 adjusting opacity 158 aligning 151 changing the border style 155 changing the stacking order 151 copying 150 deleting 150 duplicating 150 flipping 154 grouping and ungrouping 159 locking and unlocking
ROWS lookup function 270 ruler showing and hiding 134 using to change a tab stop 135 using to delete a tab stop 135 using to set a new tab stop 135 using to set paragraph indents 136 S saving as a template 35 a spreadsheet 34 automatic backup version 35 copy of spreadsheet 36 Spotlight 36 undoing changes made since 35 with template images 34 scatter chart formatting symbols 117 specifying axis settings 117 searching for text 148 SEARCH text function 270 SECOND date/time function 271 shapes adding custom 16
tables adding 45 adding color banding to rows 73 adding columns 70 adding rows 69 copying among iWork applications 50 creating a footer row 72 creating a header row or column 71 defining reusable 49 deleting rows and columns 70 filtering (hiding) rows 78 formatting controls 65 formulas 83 hiding rows and columns 72 moving 48 naming 49 predefined 49 rearranging rows and columns 70 resizing 48 resizing rows and columns 73 selecting 51 selecting a row or column 52 using comments within 76 working with rows and
