MS EXCEL
Excel is the spreadsheet program created by Microsoft. Although you can use any spreadsheet program for analyzing data, the instructions given here are specific for Excel and you must use Excel for the three Excel quizzes. NOTE: Microsoft also makes a less powerful spreadsheet program as part of Microsoft Works or some similar title. Some of the features that we will use in these exercises are not found in MS Works, so you will not be able to complete all the exercises using MS Works.
Excel is, in its most basic form, a very fancy calculator. The information given in this quick tutorial is meant to give a working knowledge of how to use Excel. There are usually several different ways to perform the same function in Excel, this tutorial will usually just give one way. If you need more information on how to use Excel, there are many web sites dedicated to using Excel, a simple 'google' search will find many of them. In addition, accessing the HELP menu from within the program can also be useful!
The first figure shows the typical Excel sheet with the important parts of the programs interface labeled. Please become familiar with these.
The Interface:
Editing and Saving
You are essentially finished with data manipulation for this exercise. However, the spreadsheet is not clearly labeled. In order to make the sheet easier to understand, you will now insert some labels.
Highlight cell A1. Use the right mouse button (the one you normally do not use!). This will bring up a menu specific for dealing with cells. An example is shown below.
All these menu items deal with cells. Note, any changes you make will only occur on the highlighted cells. In this case, the changes will only occur to cell A1. If you highlight several cells and then right click, the changes will occur to all the highlighted one. Also, you will find that the right click is a very convenient trick in Excel. You can right click over almost anything and get a menu specific for that item. It always pays to try it. Play with the various menu items to see what they do.
In any case, let us select the INSERT item. This will bring up a new dialog box, check "entire row" and press OK. This will insert a new, empty row and shift all other rows down 1 row. Insert a column to the left of column A using the same technique. You should now have an empty row 1 and an empty column A.
Type "Dogs" into cell B1, "Cats" into cell C1 and "Aardvarks" into cell E1. In A8 and A9, type in "Total". Finally, in F1, type "Total Animals". By using labels, you can identify what the values in the spreadsheet represent.
You are now done with the exercise and must now save the file. From the FILE menu item, select save and complete the dialog box. Name the file firstinitiallastnamequiz1 (using you first initial and last name - my file would be named emeyertholenquiz1.xls - the xls extension will be added automatically). Save the file in some place where you will be able to access it so that you can email the file to your instructor if required. Good luck.
Enter a series of numbers, dates, or other items
Do any of the following:
Quickly enter repeated column items into a cellIn the column, type the first few characters for the entry.
If the characters that you type match an existing entry in that column, Excel presents a menu with a list of entries already used in the column.
Press the DOWN ARROW key to select the matching entry, and then press RETURN .
NOTES:
Excel automatically 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.
Entries that are repeated within a row are not included in the list of matching entries.
If you don't want the entries that you type to be matched automatically to other entries, you can turn this option off. On the Excel menu, click Preferences. Under Formulas and Lists, click AutoComplete 
, and then clear the Enable AutoComplete for cell values check box.
Quickly enter repeated column items into many cells at onceSelect the cells that contain the data that you want to repeat in adjacent cells.
Drag the fill handle 
over the cells that you want to fill.
NOTE: When you select a range of cells that you want to repeat in adjacent cells, you can drag the fill handle down a single column or across a single row, but not down multiple columns and across multiple rows.
Click the Auto Fill Options smart button 
, and then do one of the following:
To
Do this
Copy the entire contents of the cell, including the formatting
Click Copy Cells.
Copy only the cell formatting
Click Fill Formatting Only.
Copy the contents of the cell, but not the formatting
Click Fill Without Formatting.
Do any of the following:
Quickly enter repeated column items into a cellIn the column, type the first few characters for the entry.
If the characters that you type match an existing entry in that column, Excel presents a menu with a list of entries already used in the column.
Press the DOWN ARROW key to select the matching entry, and then press RETURN .
NOTES:
Excel automatically 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.
Entries that are repeated within a row are not included in the list of matching entries.
If you don't want the entries that you type to be matched automatically to other entries, you can turn this option off. On the Excel menu, click Preferences. Under Formulas and Lists, click AutoComplete , and then clear the Enable AutoComplete for cell values check box.
Quickly enter repeated column items into many cells at onceSelect the cells that contain the data that you want to repeat in adjacent cells.
Drag the fill handle over the cells that you want to fill.
NOTE: When you select a range of cells that you want to repeat in adjacent cells, you can drag the fill handle down a single column or across a single row, but not down multiple columns and across multiple rows.
Click the Auto Fill Options smart button , and then do one of the following:
To
|
Do this
|
Copy the entire contents of the cell, including the formatting
|
Click Copy Cells.
|
Copy only the cell formatting
|
Click Fill Formatting Only.
|
Copy the contents of the cell, but not the formatting
|
Click Fill Without Formatting.
|
How to Auto Fill Using the Fill Handle
The easiest example of a linear series of numbers is 1,2,3,4,5. In a linear series, the next number in the series is always obtained by adding a constant, or step value to the previous number. Said another way, each subsequent number is incremented by the same value.
A linear series can consist of decimals (1.5, 2.5, 3.5, 4.5), decreasing values (100, 98, 96, 94), or negative numbers (-1,-2,-3,-4). But in each case we ADD (or subtract) the constant or step value.
Let's see how we can autofill a column using the Fill Handle.
- Open the Excel worksheet. In the first cell enter 1, and enter 2 in the cell immediately below.
- Highlight the two cells, and hover the cursor over the bottom right corner until you see the Fill Handle (+) - see figure 1.
- Press the LEFT mouse button and drag down the column as far as you want Excel to auto fill - see figure 2.
- Release the mouse button and Excel fills the column with the linear series as shown in figure 3.
Alternate Option
If you want a linear series with a step value of 1, you can enter the first number only, click and drag the Fill Handle with the RIGHT mouse button, and click Fill Series on the small menu that displays. The default step value for Fill Series is one.
If you have trouble using the Fill Handle, you may accomplish the same task using the Fill button on the Editing section of the ribbon's Home tab (Excel 2007 and higher). To use the Fill button on the ribbon:
- Enter the two numbers in the worksheet.
- Highlight these numbers and the rest of the column you want auto filled.
- Click the Fill Button, and click Series.
- In the Series window, make sure "Linear Series" is selected with a step value of 1, and click OK.
No comments:
Post a Comment