Let Your Data Do the Talking:
Using Excel to Analyze and Display Data
Developed by Jane Cook
EASTCONN Staff Development/Literacy & Educational Technology Specialist
Phone: (860) 455-1510
E-mail:
Revised 8/11/11
Table of Contents
Getting Started with Microsoft Excel 1
Launching Excel and Creating a New Spreadsheet (AKA Workbook) 1
Entering Data in Worksheet Cells 1
Enter numbers, text, a date, or a time 1
Enter the same data into several cells at once 2
Entering A Formula To Calculate A Value 2
Enter a formula 3
Tips on Formulas 3
About using functions to calculate values 3
Example of a Formula with a Function 3
How to enter a formula that contains a function 4
Special Formulas For Statistical Analysis of Data 4
Mean (Average) 4
Median 5
Mode 5
Standard Deviation 6
Copying and Filling Data 7
Copy data within a row or column 7
Fill in a series of numbers, dates, or other items 7
Printing Data in Microsoft Excel 8
Define What Part of the Worksheet to Print - Setting the Print Area 8
Print a selected area of a worksheet 8
Add cells to an existing print area 8
Center worksheet data on the printer page 8
Clear a print area 9
Printing Microsoft Excel Spreadsheets 9
Print the selection, the active worksheet(s), or a workbook 9
Print several worksheets at once 9
Cancel a selection of multiple worksheets 10
Print several workbooks at once 10
Canceling Printing of Microsoft Excel Spreadsheets 10
Using Excel’s Help Features 11
About getting help while you work 11
Ask a Question box 11
The Office Assistant 11
Help topics 11
Office Assistant tips 11
The different Office Assistants 11
Giving feedback and getting help on the Web 12
Using Help 12
The appearance of the Help window 12
Screen Tips 12
Help on the World Wide Web 13
Simple Data Analysis with Microsoft Excel 14
Using Conditional Formatting for Data Analysis 14
Apply conditional formats to cells 14
Change, add, or remove conditional formats 15
Find cells that have conditional formats 15
Sorting and Filtering 15
Sorting a List 15
Sort rows in ascending order (A to Z, or 0 to 9) or descending order (Z to A, or 9to 0) 15
Sort rows by 2 or 3 columns 16
Sort rows by 4 columns 16
About Filtering 16
AutoFilter 17
Advanced Filter 17
Examples of Complex Criteria for Filtering 17
Multiple Conditions in a Single Column 17
One Condition in Two or More Columns 18
One Condition in One Column or Another 18
One of Two Sets of Conditions for Two Columns 18
More than Two Sets of Conditions for One Column 19
Conditions Created as a Result of a Formula 19
Taking Advantage of Some of Excel’s Special Features 20
Specific Features of Excel 20
The COUNTIF Function 20
Example of Using the COUNTIF Function 20
The COUNTA Function 21
Example of COUNTA: Counting the Number of Students by Using Their Names 21
Hiding Data 22
Hide a row or column 22
Display hidden rows or columns 22
Inserting Comments in an Excel Spreadsheet 22
About comments 22
Viewing comments 22
User names in comments 22
Adding a comment to a cell 23
Deleting a comment from a cell 23
Deleting Selected comments 23
Deleting all comments on a worksheet 23
Protecting an Excel Spreadsheet with a Password 23
Change or remove a spreadsheet (workbook) password 24
Help with Creating Microsoft Excel Charts 25
Examples of Common Chart Types 25
Column 25
Bar 25
Line 25
Pie 25
Doughnut 25
Cone, Cylinder, and Pyramid Data Markers 25
Creating a chart 25
Create a chart 26
Create a chart from nonadjacent selections 26
Create a default chart in one step 26
How to add a button to a toolbar 26
Troubleshooting Charts 27
Text is missing along the horizontal axis of the chart. 27
Why does the font size in my chart keep changing? 27
My chart doesn't measure or display data the way I want. 27
I made or moved a chart, but I can't find it. 28
How do I size and set up a chart for printing? 28
I can't scale a chart to print the size I want or print multiple charts on a single page. 28
Help with Creating Microsoft Excel Spreadsheets
Source: Adapted from the Microsoft Excel Help Index
Microsoft Excel is the spreadsheet that comes in the Microsoft Office suite of professional productivity tools. A spreadsheet is a special type of computer software that is the equivalent of an electronic ledger book, which allows the easy manipulation of numbers. A spreadsheet contains a series of rows and columns. The intersection of a row and a column is called a cell. You can use Microsoft Excel to create a spreadsheet file that stores numbers and text. Below are some commands that you may find useful when creating a spreadsheet.
Getting Started with Microsoft Excel
Launching Excel and Creating a New Spreadsheet (AKA Workbook)
To create a spreadsheet (AKA workbook), you must first launch MS Excel. To do that, follow these steps:
- Click on Start.
- Click on Programs.
- Click on Excel
If Excel doesn’t open with a blank spreadsheet, simply follow the steps below to create a blank spreadsheet:
- On the File menu, click New
- Then click on Blank Workbook on the New Workbook task pane.
A spreadsheet will open with three worksheets. Your cursor will be in cell A1 (the cell that is the intersection of Column A and Row 1).
Entering Data in Worksheet Cells
You can enter numbers, text, dates or a time into cells in an Excel worksheet.
What do you want to do?
Enter numbers, text, a date, or a time
Enter the same data into several cells at once
Quickly fill in repeated entries in a column
Enter numbers, text, a date, or a time
1 Click the cell where you want to enter data.
2 Type the data and press ENTER or TAB.
NOTE: Use a slash or a hyphen to separate the parts of a date; for example, type 3/14/04 or Mar-04.
To enter a time based on the 12-hour clock, type a space and then a or p after the time; for example, 9:00 p. Otherwise, Microsoft Excel enters the time as AM.
Return to top
Tips:
· To fill in rows of data in a list, enter data in a cell in the first column, and then press TAB to move to the next cell. At the end of the row, press ENTER to move to the beginning of the next row. If the cell at the beginning of the next row doesn't become active, click Options on the Tools menu, and then click the Edit tab. Under Settings, select the Move selection after Enter check box, and then click Down in the Direction box.
· To enter today's date, press CTRL+; (semicolon).
· To enter the current time, press CTRL+SHIFT+: (colon).
Enter the same data into several cells at once
1 Select the cells where you want to enter data. The cells can be adjacent or nonadjacent.
2 Type the data and press CTRL+ENTER.
Quickly fill in repeated entries in a column
If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. Microsoft Excel completes only those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed.
· To accept the proposed entry, press ENTER. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries.
· To replace the automatically entered characters, continue typing.
· To delete the automatically entered characters, press BACKSPACE.
NOTE: You can also select from a list of entries already in the column. To display the list, press ALT+DOWN ARROW to display the list, or right-click the cell, and then click Pick from List on the shortcut menu.
Entering A Formula To Calculate A Value
You can create a wide variety of formulas in Microsoft Excel, from formulas that perform a simple arithmetic operation to formulas that analyze a complex model of formulas.
A formula can contain "functions". Functions are predefined formulas that perform simple or complex calculations.
What do you want to do?
Enter a formula
About using functions to calculate values
Example of a formula with a function
How to enter a formula that contains a function
Return to top
Enter a formula
1 Click the cell in which you want to enter the formula.
2 Type = (an equal sign).
Tip: If you click the Edit Formula icon in the formula bar (the = sign next to the cell value information) or the Paste Function icon (the fx symbol on the Standard Toolbar), Microsoft Excel inserts an equal sign for you.
3 Enter the formula.
4 Press the ENTER key.
Tips on Formulas
· You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.
· You can also enter a formula into a range of cells by copying a formula from another cell.
About using functions to calculate values
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, called the syntax. For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.
Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.
Example of a Formula with a Function
Function name - the SUM function will add up all of the arguments
Arguments (these can be a number, a range of numbers or a cell value)
=SUM(A5, B5:B10, 40, 89) NOTE: A comma separates each argument.
Parentheses enclose arguments
Return to top
How to enter a formula that contains a function
1 Click the cell in which you want to enter the formula.
2 To start the formula with the function, click the Edit Formula icon in the formula bar (this is the = sign next to the cell value information).
3 Click the down arrow next to the Functions box (this is the box to the left of the = sign).
4 Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions.
5 Enter the arguments.
6 When you complete the formula, press the ENTER key.
Special Formulas For Statistical Analysis of Data
The Mean, Median, and Mode are all statistical formulas that are used to help make sense of data. All three measures (MEAN, MEDIAN, and MODE) are statistical location measures that are known as measures of central tendency. In a set of scores, the Mode is the most frequently occurring score; the Median is the middle score; and the Mean is the average score. The Mean is the most commonly used measure of central tendency; the Median is the second most common; the Mode is the least common. They are used to represent an entire distribution of scores. Each can be "mapped" on a normal distribution curve. No single measure of central tendency provides a complete picture of the data. Suppose data is clustered in three areas, half around a single low value, and half around two large values. Both AVERAGE and MEDIAN may return a value in the relatively empty middle, and MODE may return the dominant low value. These measures are used as the "best bet" when a single score needs to be used to represent an entire distribution of scores.
Mean (Average)
The Average or arithmetic Mean is the sum of a range of numbers divided by the total amount of numbers in the range. The Excel formula for Average returns the Average (arithmetic Mean) of the range of cells. To calculate the Average or Mean of a series of numbers, follow these steps:
- Place the cursor in the cell where you want the Average displayed.
- Click on the equal sign (=) in the white area at the top of the worksheet.
- Click on the Down Arrow to the left of the equal sign (=).
- Select Average by clicking on it.
NOTE: If Average does not appear in the list, click on More Functions to call up the Paste Function dialog box. Then click on Statistical in the Function Category (left) window and click on Average in the Function Name (right) window.