Using Spreadsheets in Public Health
Excel (PCs) and Numbers (Mac & iPad)

Introduction

Spreadsheets are very useful tools in public health because they are widely available, and can be used for collecting data, statistical analysis, constructing graphs and tables which can be exported into other applications or converted into image files. The basic function of Excel spreadsheets (for PCs) and Numbers (for Mac and iPad) is similar; they all consist of a matrix of rows (1, 2, 3, 4, etc.) and columns (A, B, C, ... Z, AA, AB, AB, etc.) which can be used to store text or numeric data and to perform a wide variety of mathematical and other functions that greatly facilitate analysis, organization, and reporting.

However, there are differences between Excel and Numbers in the user interface and the menus, and there are also differences among different versions of Excel. Nevertheless, the basic layout and functions within all of these are quite similar, and it is also noteworthy that Excel spreadsheets will open and run in Numbers, and Numbers spreadsheets can be "Saved as" Excel spreadsheets.

This tutorial will provide videos that illustrate the user interface for various versions of Excel and for Numbers '09. These will introduce users to whichever version of Excel or Numbers they have. The examples that we have developed to illustrate the application of spreadsheets in public health practice have been created in Excel, but the example spreadsheets can be downloaded and opened in whichever spreadsheet application you are using, and the examples are more or less generic and should be easily adapted to the spreadsheet you are using.

Excel 2010

Basics

The figure below shows and empty spreadsheet in Excel 2010. The top rows show the main menu elements (File, Home, Insert, etc.), and beneath that is a toolbar which provides quick access to a number of commonly used tools. The bottom of the figure shows the matrix of columns and rows that comprise the actual spreadsheet. This window shows just a tiny fraction of the available spreadsheet. The columns are labeled with letters, and if we scrolled to the right, we would see that once one gets to column "Z", the columns continue with "AA", "AB", etc. to provide an enormous number of columns all the way to "XFD". Similarly, there are many available rows numbered 1 through 1,048,756. This large area makes it possible to story large amounts of data in tabular form.

Orientation and Navigation

This video provides a short orientation to the Excel 2010 spreadsheet how to move around within it.. Topics include:

• Opening Excel
• Excel "Sheets"
• Renaming a Sheet
• Menus & Toolbars
• Customizable Toolbar
• Close, Minimize, Maximize the Window.
• The Excel Help Menu

The menus in previous versions will be different from these, but navigation and other design aspects will be the same.

Entering Data

 This video illustrates:

• How to enter text and numeric data and dates
• How to make text "wrap" within a cell
• How to fix the number of decimal places for numeric data
• How to freeze the titles (headers) of the columns and rows
• Column Headers
• Change Column width
• Wrapping Text in a Cell
• Centering Text
• Entering Dates
• Entering Dichotomous Variables
• Moving to Another Sheet
• Fixing the Number of Decimal Places

To view the video in full screen mode, click on the icon at the very lower right, next to "You Tube".

Exercise I - Basics

• Open whichever spreadsheet application you have on your laptop, and enter the data in the table below into a new sheet.

• Format the column titles so that the text for each title is centered and boldfaced.
• Single Cell: First, select cell A1 and click "B" on the toolbar or, from the Home menu, click "B" in the font selections.
• Format entire row: Next, click on the grey "1" for row #1. This should make the entire row become "selected", and it should appear dark. With row 1 (column titles) selected, click on the dark capital letter B on the toolbar. This should make all the column titles bold-faced. Then click on the centering icon.

• Change the Name of the worksheet to "BMI Data"

The worksheets at the bottom of your screen have titles. The default names are "Sheet 1", "Sheet 2," etc. You can change the names by double-clicking on the worksheet title to select it, and then typing in a new name.

• Freeze Titles (Window, Freeze Panes)

1. Use the scroll bar at the right side of the window to scroll down. Note that the column titles disappear.
2. In Excel 2010 you can freeze the column titles at the top, so they are always visible. First place your cursor in cell A2 to indicate that you want the cells in the first row to freeze. Then click on "View", then select "Freeze Panes". Note that when you do this the line between rows 1 & 2 becomes a little bit darker.
3. Now scroll down and up again, noting that you can see the column titles at all times.

• Adjust the column width so that all of the column titles are accommodated. To do this, click on a vertical line between two column letters, e.g., between column "C" and column "D". Click and hold and move the mouse right or left to expand or reduce column width.

• Compute Joe's weight in kilograms (kg) in column E, but first add a comment to cell E1.

• Put your cursor on cell E1, and click on "Review", "Add Comment". When the comment box opens, type in "1 lb. = 0.453592 kg".
• Then place your cursor in cell E2. Enter "=C2*0.453592 and hit the Enter key. You should get a weight of about 123.377 kgs. in cell E2.
• Compute Joe's height in meters .
• Now add a comment to cell F1 as follows: "1 inch = 2.54 cm; 100 cm =1 meter".
• Then place your cursor in cell F2 to compute the height in meters based on the subject's height in inches. Since 1 inch =2.54 centimeters, and 100 centimeters = 1 meter, Joe's height in meters can be computed by entering the following into cell F2:

=D2*2.54/100

This should give a result of 1.8288 meters.

• Fix the number of decimal places for cells F2, G2, and H2 at 2 decimal places, i.e., hundredths. Click on F2 and hold the left mouse button down, and move to the right to select these three cells. Then release the mouse and click on "Home" from the top menu, then toward the right side of the toolbar where it says 'cells', select "Format", then "Format Cells" (bottom of the pop down), then "Number", and then select 2 decimal places. All three cells will be formatted at 2 decimal places, even though there is nothing in cell H2 yet.

• Compute Joe's BMI. BMI is computed as weight in kg. divided by height in meters squared. Put your cursor in cell H2 and enter the following:

=E2/(F2^2)

You should get a BMI of 36.89. Note also that the cell is already formatted to two decimal places.

Note also that the parentheses around (F2^2) are critical, since they indicate that the value of F2 (meters) should first be squared (i.e., raised to the second power), and the result should then be divided into E2.

• Now we will replicate the formulas you created for Joe so that they can be applied to all of the other subjects. First, select cells E2, F2, and G2. Note that the lower right corner of cell G2 is dark. Carefully position your cursor over this corner so that the cursor turns into a black "+". Then click and hold the left mouse button and drag your cursor down to the bottom of cell G9 so that the block of missing calculations is outline by a broken line. Then release the cursor. All of the appropriate calculations should have been completed for all subjects, and all cells should be formatted at 2 decimal places.

• Note: If you make a mistake, you can go back a step by hitting the curving back arrow on the toolbar. In fact, you can back up multiple steps. If you don't see the curved undo arrow on your toolbar, you may have to add it. There is also a redo icon that can be added.
  
• Sort the data set by age. Select the entire block of data by placing your cursor in cell A1 and holding the left mouse button while you scroll down to cell G9. Then release the mouse button. The data set should still be highlighted. From the upper menu bar, select "Data" and then select "Sort" which appears toward the middle of the toolbar below the top menu. You can now indicate which variable you want to sort by, and you should select "age", which is in column B. Note that you can sort in ascending or descending order, and you can designate whether or not your data has a "header" row, i.e., column titles. If it does and you check the box to indicate this, then the sort function will use these column titles.
• Use the built in formulas for COUNT, SUM, and AVERAGE. Excel and Numbers (the Mac/iPad spreadsheet) have many built in functions that facilitate computations in many fields (e.g., financial, math, statistical, logical, database management, etc.). To illustrate, you will use our simple data set to compute the number of observations and the sum and average of the numeric variables.

In cells A11 through A15 enter the following labels: Count, Sum, Average, Std. Deviation, Median.

In cell B11 enter the function "=COUNT(B2:B9)".

In cell B12 enter the function "=SUM(B2:B9)"

In cell B13 enter "=AVERAGE(B2:B9)".

In cell B14 enter "=STDEV.S(B2:B9)".

In cell B15 enter "=MEDIAN(B2:B9)".

Now replicate these functions to the right in order to apply them to columns C through G.

Now select rows 12-15 and format them to one decimal place.

Exercise 2 - Graphing

In the handouts on the right side of the page there is a link to a spreadsheet called "MAVEN data set." This contains de-identified data from MAVEN (the Massachusetts Virtual Epidemiologic Network), which is used for surveillance of infectious diseases.

The lower portion of the first worksheet lists partial data for over 1,500 cases of infectious disease reported to MAVEN. (For simplicity, the data set you see contains some of the more common infectious diseases). Note that I have already sorted the data by type of infection.

In the uppermost portion of the spreadsheet I have created a summary table that uses the "COUNT" function to count the number of cases of each type of infection. 

• The last entry in the summary table for Shigella was left incomplete on purpose. Scroll down to the bottom of the data set to see which rows are occupied by Shigella cases; then scroll back up to the summary table and use the COUNT function to compute the number of Shigella cases. Note that you will have to "count" the occurrences using the date column, since the function only works for numeric entries.
• Now select cells B1 through C12 and click on the graphing icon (the color pie chart) on your customizable toolbar. If hasn't been added yet, you can add it now or click on "Insert" on the top level menu to see the Chart options. Select the option for a pie chart. This should result in immediate construction of a pie chart for the summary data.
• Now repeat the process, but select a column chart instead of a pie chart.
• Delete the legend on the column chart. Clicking on the legend on the right side of the chart, and delete it.
• Now resize the pie chart and the column chart by moving your mouse over the edges and clicking to grab an edge or a corner and moving your mouse to resize the chart.

Your charts should look something like the ones below:

Creating an Epidemic Curve

In the handouts on the right side of this page, the Excel file called "Epi_Tools.XLS" has two worksheets, each demonstrating a simple way to create an epidemic curve when you have dates of onset for a series of cases. The first method, simply sorting the cases by date of onset and then counting the number of cases occurring at, say, two day intervals, is illustrated in the worksheet called "Epi Curve 1". The second method, using pivot tables, is illustrated in the worksheet called "Epi Curve 2".

Exercise 3 - Solving an Outbreak of Hepatitis A (Case-Control Study)

In the handouts on the right side, open the Excel file call "Hepatitis Outbreak". Save this file to your local computer or thumb drive.

This file contains actual data from investigation of a hepatitis outbreak that occurred in Marshfield, MA in 2004. There were 20 cases reported within a short period of time. The Massachusetts Department of Public Health was asked to investigate the outbreak. Descriptive epidemiology, including hypothesis-generating interviews, suggested that the source was most likely a food handler at one of several possible food establishments.

The investigators conducted a case-control study. They created a questionnaire and collected data from 19 cases and 38 controls. The controls were matched to the cases by age group and by neighborhood of residence. The data is in the first worksheet in the Excel file above. Note that the data indicating whether the subjects were cases or control are inconsistent, with a variety of responses indicating yes or no. The same is true for the data indicating which food establishments they were exposed to.

• Your first task is to clean up the data set. When you have dichotomous data (e.g., yes or no), the most unambiguous way to enter the data is to use the numbers 0 (for no) and 1 (for yes). Select the block of data from B3 to M59, which includes all of the cases and all of the controls. Use the Replace function on the far right hand side of the Home menu to replace all of the "y"s with 1, then all of the "Yes" with 1, etc., until all of the dichotomous fields have been converted into 0 or 1 as appropriate.
• Next, sort the data by "Case" status in column B.
• The controls, i.e., case=0, should be on top if you sorted in ascending order. Now find where the controls end and the cases begin, and insert 3 or 4 blank rows between the cases and controls. From the Home menu, select rows 41-44 and click on "Insert" in the cells section of the toolbar options. This should insert 4 blank rows.
• Now use two of the rows beneath the controls to create rows for =COUNT and =SUM in cells E41 and E42 respectively.
• Then replicate the formulas in E41 and E41 to the right to replicate the formulas into the columns for the other restaurants.
• Repeat the two previous steps beneath the cases, and save your work.

 You now have the information you need to compute odds ratios and 95% confidence intervals for each of the food establishments. 

• You can open and save "Epi-Tools.XLS" from the Handouts on the right side of the Spreadsheet module. This is an Excel file that contains multiple worksheets that have been pre-programmed to perform selected analyses. Note that there is a worksheet for Case-Control" studies. Click on the tab to go to this worksheet, and use the blue-green 2x2 table to enter the number of exposed and unexposed cases and controls, based on the results of your =COUNT and =SUM tallies in the hepatitis spreadsheet. For example, the counts for Brian's Place indicate that 2 of the 19 cases ate there, and 4 of the 38 controls ate there. So, in the cells for the cases you would enter 2 and 17 (19 total), and for the control column you would enter 4 and 34 (total 38). The odds ratio and confidence interval get calculated automatically once you click on a cell outside the 2x2 table.

Exercise 4 - Analysis of a Cohort Study

Is there an association between High Density Lipoprotein Cholesterol (HDLC) levels and myocardial infarction?

In Handouts on the right-hand side of the spreadsheet module there is an Excel file with actual data from the Framingham Heart Study. In this data set there a variable called labeled HDLC in column R. This is the subject's value for high density lipoprotein cholesterol at the beginning of the study (baseline). HDLC is sometimes called the 'good' cholesterol, because higher levels of HDLC seem to be associated with a lower risk of atherosclerotic heart disease. [For more on lipoproteins and heart disease see the discussion of this topic in the PH709 module on Atherosclerosis]

In this analysis you will examine the association between low HDLC (i.e. values < 40) and risk of myocardial infarction. In the spreadsheet with the Framingham data set there is a variable called MI_FCHD in column W which indicates whether the subject was hospitalized for a myocardial infarction or died of a myocardial infarction during this phase of the study. This is the outcome of interest for this particular analysis. TIMEMIFC in column AD indicates the disease free observation time (in days) for having a fatal or non-fatal myocardial infarction (heart attack). This time represents the number of days elapsed from baseline until one of three things happened: 1) they had a myocardial infarction, 2) they were lost to follow-up, or 3) the subject reached the last date for this phase of the study without an event. Note that many subjects have 8,766 days of TIMEMIFC; these are all subjects who had the maximum number of observation days because they were observed from baseline until the end of this phase of the study (8.766) and did not have a myocardial infarction during that entire period.

Example

This is a 21 minute video which walks you through a similar analysis comparing the risk of myocardial infarction between subjects with high LDL (> 130) and low LDL (<130). This will provide detailed instructions on performing an analysis in a cohort study.

Detailed Instructions:

Open the Framingham data set in the Handouts section of the online module and save it to your computer using "File", "Save as". Then analyze the association between low HDLC (HDLC <40) and myocardial infarction (MI_FCHD). Compute BOTH the cumulative incidence and the incidence rates in the exposed and unexposed groups. Use these results to compute the Risk Ratio (using cumulative incidence) and the Rate Ratio (using the incidence rates).

Note: if there are individuals who do not have a recorded value for HDLC, these subjects should be excluded from the analysis.]

You will need to compute:

1. The number of 'events' (MI_FCHD) in exposed and unexposed groups.
2. The number of subjects in exposed (HDLC<40) and unexposed (HDLC>40) groups.
3. The person-time (in years) for the appropriate groups.
4. The incidence rate in person-years for each group.
5. The cumulative incidence for each group.

With this information complete your analysis by using the Cohort Study worksheet in Epi_Tools.xls to compute the

1. Risk ratio (using cumulative incidences), 95% confidence interval for the risk ratio, and the p-value for this comparison.
2. Rate ratio (using incidence rates), 95% confidence interval for the rate ratio, and the p-value for this comparison.

Finally, using the Excel data file that you used to sort and tabulate the data, insert a text box that summarizes the results of your analysis, and interpret your findings in a few sentences. Label the summary worksheet tab as "Summary."

Excel 2010 - Smart Method Video Course

The Smart Method - Free Online Video Course with 302 video tutorials

• 1-1 Start Excel & check your program version 9:03

• 1-2 Maximize, minimize, re-size, move & close the Excel window 3:39
• 1-3 Application and Workbook windows 3:56
• 1-4 Open and navigate a workbook 8:00
• 1-5 Save a workbook 2:54
• 1-6 Understand common file formats 5:43
• 1-7 Pin a workbook & understand file organization 6:34
• 1-8 View, move, add, rename, delete & navigate worksheet tabs 5:41
• 1-9 Use Versions feature to recover an unsaved draft file 8:44
• 1-10 Use Versions feature to recover a workbook 4:34
• 1-11 Use the Ribbon 11:27
• 1-12 Understand Ribbon components 9:40
• 1-13 Customize the Quick Access Toolbar & preview the printout 9:38
• 1-14 Use the Mini Toolbar, Key Tips & keyboard shortcuts 6:29
• 1-15 Understand Views 3:13
• 1-16 Use full screen view 2:34
• 1-17 Use the help system 8:42
• 2-1 Enter text & numbers into a worksheet 9:27
• 2-2 Create a new workbook; view two workbooks at the same time 6:11
• 2-3 Use AutoSum to quickly calculate totals 4:31
• 2-4 Select a range of cells & understand Smart Tags 6:00
• 2-5 Enter data into a range and copy data across a range 5:31
• 2-6 Select adjacent and non-adjacent rows and columns 3:07
• 2-7 Select non-contiguous cell ranges and view summary information 5:44
• 2-8 AutoSelect a range of cells 5:28
• 2-9 Re-size rows and columns 6:13
• 2-10 Use AutoSum to sum a non-contiguous range 3:37
• 2-11 Use AutoSum to quickly calculate averages 5:58
• 2-12 Create your own formulas 7:03
• 2-13 Create functions using Formula AutoComplete 6:40
• 2-14 Use AutoFill for text and numeric series 5:38
• 2-15 Use AutoFill to adjust formulas 6:04
• 2-16 AutoFill Options 6:11
• 2-17 Speed up your Auto Fills and create a custom fill series 5:05
• 2-18 The zoom control 3:20
• 2-19 Print out a worksheet 8:44
• 3 12: Understand absolute and relative cell references 8:53
• 4-18: Create a formula driven conditional format 8:12
• 4-19: Insert a Sparkline into a range of cells 6:13
• 5 18 Create a combination chart containing different chart types 4:37
• 6 7 Understand worksheet groups 8:23
• 7 8 Add custom headers and footers 7:55
• Exp1-8 Convert a range into a table with a total row 6:30
• Exp1-13 Sort a range or table by custom list 3:41
• Exp2-7 Validate numerical data 6:45
• Exp3-5 Use the IF logic function 8:51
• Exp3-21 Extract text using the FIND and LEN functions 9:53
• Exp3-22 Use a VLOOKUP function for an exact lookup 7:14
• Exp4-1 Automatically create single-cell range names 4:48
• Exp4-16 Use Speak Cells to eliminate data entry errors 8:02
• Exp5-1 Create a one dimensional pivot table report from a table 10:52
• Exp5-7 Filter a pivot table visually using slicers 7:05
• Exp6-1 Create a single-input data table 9:06
• Exp6-11 Restrict the cells users are allowed to change 8:38
• Exp7-3 Hyperlink to worksheets and ranges 6:17
• Exp7-10 Three different ways to share a document 3:22

More Tutorials for Excel 2010

Excel 2010 Tutorial For Beginners #1 - Overview (Microsoft Excel)

(From YouTube.com; http://www.youtube.com/watch?v=eI_7oc-E3h0

Excel 2010: Simple Formulas

(From YouTube;

Excel 2010: Functions (AVERAGE, MAX, MIN, COUNT)

From YouTube;

Excel 2010: Functions (AutoSum, Sum, Max, Min, Average, Large, Small, ABS, Power)

(From YouTube;

Excel 2010: The IF function: what it is, and how to use it

(From Microsoft.com)

The IF function checks to see if a condition you specify is true, or false. If true, one thing happens; if false, something else happens. For example, if you use the IF function to see if amounts spent are under or over budget, the result for True could be "Within budget," while the result for False could be "Over budget."

Download  | Watch online

Excel 2010: The AND, OR, and NOT functions

(From YouTube; http://www.youtube.com/watch?v=SNbxFSMRw6g

Excel 2010: Sparklines

From YouTubel

How to create a basic chart in Excel 2010

(From Microsoft.com)

Charts make data visual. With a chart you can transform spreadsheet data to show comparisons, patterns, and trends.

Download  | Watch online

Video Modules for Excel 2003

Basics of Excel Worksheets

• Getting Started
• Workbooks & Worksheets
• Scroll Bars
• Columns, Rows, & Cells

Formatting an Excel Worksheet

(Note Before watching the video, download the Beach Inspections spreadsheet and save it to your own computer. Right-click on this Link to Example Spreadsheet, and select 'Save target as'.)        

• Enter Information  
• Insert Text Box          
• Adjust Cell Size          
• Adjust Column Width          
• Wrap Text 05:18          
• Merge Cells & Alignment          
• Format Font         
• Outline Borders          
• Colored Backgrounds       
• Insert Comments       
• Save Workbook

Simple Math Formulas

(Note: Before watching the video, download the IgG Dose Calculation spreadsheet and save it to your own computer. Right-click on this Link to example spreadsheet, and select 'Save target as'.            

• Mathematical Operations
• Using Formulas

Additional Formatting Tips            

• Format Numbers
• Freeze Panes
• Insert & Delete Rows and Columns
• Delete & Clear Cells

Using the "IF" Function & Sorting Data

(Note: Before watching the video, download the PneumoVaccines spreadsheet and save it to your own computer. Right-click on this Link to example spreadsheet, and select 'Save target as'.            

• Create "IF" Functions
• Absolute References
• Example:  Food Establishment Inspections
• More "IF" Functions
• Sort Data
• Find Specific Data

Creating Form Letters in Excel

(Note: Before watching the video, download the GreaseTraps spreadsheet and save it to your own computer. Right-click on this Link to example spreadsheet, and select 'Save target as'.            

• Print Preview
• Insert Pictures
• The "Lookup" Function
• Print Letters

Password Protecting Excel Files

(Note: Before watching the video, download the PneumoVaccines spreadsheet and save it to your own computer. Right-click on this Link to example spreadsheet, and select 'Save target as'.            

• Protect Whole Worksheets
• Protect Specific Cells in a Worksheet
• Protect Workbooks/Files
• Maximum Protection 08:44

Using "Find", "Replace", "Count", "Sum", and "Average" 

(Note: Before watching the video, download the Hepatitis Outbreak spreadsheet and save it to your own computer. Right-click on this Link to example spreadsheet, and select 'Save target as'.            

• Data Cleanup
• Find & Replace
• Summary Statistics
• Sum, Count, & Average

Microsoft Tutorials for Excel 2007

Introductory Tutorials from Microsoft.com

Description of the layout How to accomplish everyday tasks in Excel 2007, and a bit about the XML file formats. Get to know Excel 2007: Create your first workbook
How to create a workbook, enter and edit text and numbers, and add rows or columns Get to know Excel 2007: Enter formulas
How to enter simple formulas into worksheets, and how to make formulas update their results automatically Learn how to figure out dates using formulas in Excel 2007
How to find the number of days between dates, or the date after a number of workdays, or the date after a number of days, months, or years Charts I: How to create a chart in Excel 2007
How to create a chart using the new Excel 2007 commands and make changes to a chart after you create it Pivottables in EXCEL 2007
How pivottable reports organize, summarize, and analyze data. how to create a pivottable

The layout of the 2007 spreadsheet, everyday tasks, and an explanation of the XML file formats Get to know Excel 2007: Create your first workbook
How to create a workbook, enter and edit text and numbers, and add rows or columns Get to know Excel 2007: Enter formulas
How to enter simple formulas into worksheets, and how to make formulas update their results automatically Learn how to figure out dates using formulas in Excel 2007
How to find the number of days between dates, or the date after a number of workdays, or the date after a number of days, months, or years

How to create a chart using the new Excel 2007 commands and make changes to a chart after you create it Pivot Tables I:
How pivot tables organize, summarize and analyze data PivotTable II: Filter PivotTable report data in Excel 2007
How to filter to hide and display selected data in PivotTable reports PivotTable III: Calculate data in PivotTable reports in Excel 2007
Power tools: how to summarize data by using summary functions other than SUM, such as COUNT and MAX. How to show data as a percentage of the total by using a custom calculation. How to create your own formulas in PivotTable reports.

"Numbers" on Apple Mac

"Numbers" is the spreadsheet application for Apple devices. It functions much like Excel, but there are some differences. Note also that Excel spreadsheets will open and run in Numbers, and Numbers spreadsheets can be "Saved as" Excel spreadsheets.

Here is a list of hyperlinks to tutorials on specific aspects of Numbers

Numbers Help

Get started in Numbers

Numbers at a glance

Create a new spreadsheet

Open an existing spreadsheet

Replace template placeholders

Organize a spreadsheet with sheets

Customize your app

Undo or redo changes

Use Handoff with Numbers

Add and edit tables

Add or delete tables

Work with rows and columns

Add content to table cells

Format table cells

Create a custom cell format

Add controls to cells

Merge or unmerge cells

Add a comment to a cell

Add conditional highlighting to cells

Change the look of a table

Change the look of table text

Save a table as a new style

Move, resize, and lock a table

Format tables for bidirectional text

Sort data in a table

Filter data

Enter formulas and functions

Calculations

Types of arguments and values

Ways to use the string operator and wildcards

Functions that accept conditions and wildcards as arguments

Add and edit charts

Add or delete a chart

Scatter charts and bubble charts

Interactive charts

Move, resize, and rotate a chart

Modify chart data

Adjust a chart's markings and labels

Change a chart's type

Change the look of a chart

Save and organize chart styles

Add images, shapes, and media

Objects overview

Add and edit images

Add and edit shapes

Add video and audio

Change the look of an object

Create object styles

Resize, rotate, and flip an object

Align and position objects

Layer, group, and lock objects

Add, edit, and format text

Add text

Change the look of text

Use paragraph styles

Align text

Format lists

Adjust character spacing and formatting

Format punctuation

Add color or a border to a text box

Check spelling

Find and replace text

Add comments and highlight text

Use bidirectional text

Format Chinese, Japanese, or Korean text

Manage spreadsheets

Save and rename a spreadsheet

Locate a spreadsheet

Save a spreadsheet in another format

Save a spreadsheet in package or single-file format

Lock a spreadsheet

Password-protect a spreadsheet

Add custom templates

Move a spreadsheet

Delete a spreadsheet

Share and print

Use iCloud with Numbers

Share and edit a spreadsheet with others

Send a copy of a spreadsheet

Print a spreadsheet

Use iTunes to transfer files

Share files over a remote server

 Formulas and Functions Help

Formulas

Functions

Date and time functions

Duration functions

Engineering functions

Financial functions

Logical and information functions

Numeric functions

Reference functions

Statistical functions

Text functions

Trigonometric functions

There are also many You Tube video tutorials for Numbers.

Biostatistics Applications

This page provides links to Excel files which contain multiple worksheets that enable you to perform a wide variety of statistical functions and tests. To use these, you must open them and then save them on your local computer.

Summarizing Data

Generates summary statistics on a continuous variable

Confidence Interval Estimates

Worksheets compute confidence intervals for means, proportions, the mean difference in matched or paired samples, and the difference in means and difference in proportions in two independent samples and accept either raw data or summary statistics.

Hypothesis Testing Procedures

Worksheets conduct tests of hypothesis for means, proportions, the mean difference in matched or paired samples, and the difference in means and difference in proportions in two independent samples and accept either raw data or summary statistics.

ANOVA

Worksheets conduct analysis of variance tests of equality of k independent means and accept either raw data or summary statistics.

Chi-Square Tests

Worksheets conduct the chi-square goodness of fit and chi-square test of independence based on observed frequencies.  

Sample Size Determination

Worksheets estimate the sample size(s) required to ensure adequate precision in confidence intervals or high power in tests of hypothesis for means, proportions, the mean difference in matched or paired samples, and the difference in means and difference in proportions in two independent samples.