Materials Credited to DMACC
Agriculture Technology Class
Spreadsheet Unit – Lesson #3
Autofill
Need to enter a series of numbers or dates? Excel can help! The next time you need to fill a column or row with values that increase incrementally, you might want to enlist the assistance of a little-known Excel feature known as Autofill.
Months of the Year -- Assignment Lesson to follow along with
Let's learn how to use this feature. First, you'll need to start Excel. When a new worksheet appears, click in cell A1 and type the word 'January' (without the quotes). Be sure and spell the word exactly as I have.
Make sure the cell pointer is still sitting in cell A1. In the lower right corner of the cell pointer, you should see a small black square -- the copy handle from lesson 2. Place your mouse over the copy handle and your mouse will turn into a small black cross.
When the mouse turns into a black cross (and not a second before), hold the left mouse button down and drag your mouse down to the bottom of row 12. When you reach row 12, release the mouse button. You'll never have to write out the months of the year by hand again!
Will this trick work if we drag the copy handle horizontally? Let's try!
Click once on cell A1 to return the cell pointer to that cell. Position your mouse over the pointer's copy handle so that it changes into a black cross. Hold down the left mouse button and drag your mouse a few cells over to the right. Release the mouse button to see the cells fill with data.
Let's try another example. Click in cell C3 and type the word 'Jan' (without the quotes). Just type the three letters I've indicated. Don't type anything else. Make sure your cell pointer is still sitting in cell C3. Position your mouse over the copy handle. When your mouse turns into a black cross, hold down the left mouse button and drag your mouse straight down to row 14. You should see the cells fill with abbreviations for the month of the year. This would have worked even if we had dragged the mouse across the screen instead of down the screen.
Days of the Week
Now, go to cell D3 and type 'Sunday' (without the quotes). Grab the copy handle with your mouse and drag it from cell D3 down to cell D9. The cells should fill with the days of the week! You don't have to start with Sunday, either. You could have started your week with 'Monday' or 'Saturday' or any other day of the week. Just make sure you spell it correctly!
Go to cell E3 and type 'Sun' (without the quotes). Drag the copy handle from cell E3 down to cell E9. The cells should fill with common three-letter abbreviations for the days of the week.
Quarters
Go to cell F3 and type '1st Qtr' exactly as I have typed it (but without the quotes). Qtr is a commonly accepted abbreviation for 'quarter.' Drag the copy handle from F3 to F10. Notice how Excel fills subsequent cells with '2nd Qtr,' '3rd Qtr,' '4th Qtr,' and, because there can only be four quarters in a year, starts all over again with '1st Qtr.'
Words and Numbers
Now, go to cell G3 and type '1st Street.' Drag the copy handle from G3 to G10. Because we're no longer talking about quarters, notice that Excel does not start counting over again when we reach '5th Street.' This will happen if you combine a word and number in a cell and drag the copy handle. Try typing and dragging 'Store 1' or 'Customer 1' or '1st day' to see a similar effect.
Dates
Move over to cell H3 and type '3/20/00' (without the quotes). Grab the copy handle with the mouse and drag it down to H17. Notice how the cells fill with dates from today through April 3. Go to cell I3 and type '4/99' (without the quotes). Drag the copy handle down to cell I15 to see the cells fill with dates from April of 1999 to April of 2000.
Numbers
Now, let's try using Autofill to write out a series of numbers.
Autofill is going to work a little differently here. First, let me show you what not to do.
Go to cell J3 and type the number '1' (without the quotes).
Place the pointer in cell J3 and drag the copy handle from cell J3 down to cell J12. Notice that the cells merely fill with copies of the number '1.'
This is nice, but in this case I would have preferred to see Autofill type a series of numbers from 1 through 12.
However, it didn't work. Why? Well, there is a special three-step procedure you must always use if you wish to use Autofill to type out a series of numbers.
First, you will need to type the first two numbers in the series in two separate, but adjacent, cells. The numbers in this series must increment upwards by a fixed amount. You could use 1 and 2, or 2 and 4, or 1.1 and 1.2, or 1.0 and 1.5, or 1 and 3, or any other pair of numbers as the first two numbers in your series.
Secondly, you will need to select both of these cells.
And finally, you will need to drag the copy handle associated with this pair of cells.
Let's give it a shot. Go to cell K3 and type the number '1' (without the quotes).
Next, move down to cell K4 and type the next number in the series. Let's use the number '2' (without the quotes).
Now, return to cell K3. To select this cell and the next one, hold down the SHIFT key on your keyboard and tap the down arrow on your keyboard just once. Your cell pointer should grow to surround both cells. Cell K3 will stay white, but cell K4 will be highlighted. Release the SHIFT key when both cells are selected. Now, position your mouse over the copy handle in the lower right corner of these two cells. Make sure your mouse looks like a black cross and drag the copy handle down to cell K12. Notice how the cells fill with values from 1 through 10.
Let's try this again! Go to cell L3 and type the number 5. Then, move down to cell L4 and type the number 10.
Return to cell L3, hold down the SHIFT key and tap the down arrow to select both cell L3 and cell L4. Grab the copy handle with your mouse and drag it down to cell L12. Release your mouse, and watch the cells fill with the numbers 5 through 50, incrementing upwards 5 at a time.
Note: Although we have been applying Autofill in a vertical direction in all of these examples, we could just as easily have dragged the copy handle to the right to accomplish the same type of effect.
Custom Lists
Let's say you repeatedly type the same series of words over and over again in your Excel worksheets. This series of words could contain the names of your employees, the locations of your stores, the types of expenses your company commonly incurs, or any other bits and pieces of information.
Suppose further that you'd like to add this custom list of words to Autofill so that you can type the first word on this list, drag the copy handle, and have Excel supply the rest of the words for you.
Sounds nice, doesn't it?
Well, Excel can do!
Let's try creating our custom list with the names of the planets in our solar system. Go to cell M3 and type Neptune.
Move to cell M4 and type Pluto
Type Mercury in cell M5.
Type Venus in cell M6.
Type Jupiter in cell M7.
Type Saturn in cell M8.
Type Mars in cell M9.
Type Uranus in cell M10.
Type Earth in cell M11.
Now, to turn this custom list into an Autofill series, return to cell M3.
Hold down the SHIFT key on your keyboard and use your down arrow to move down to cell M11 to select all of these cells.
Next, click the 'Tools' menu at the top of your screen and choose 'Options.'
The 'Options' dialog box should appear. Click the 'Custom Lists' tab at the top of this box.
Now, click the 'Import' button in the lower right corner of this dialog box. Your list of planets should appear inside the dialog box.
Click the 'OK' button to close the dialog box.
That's it! Your custom list is now an Autofill series. You will be able to type the name of one planet in any cell in any worksheet, drag the copy handle, and watch the rest of the planets come tumbling out!
Let's try: move to cell N3 and type 'Earth' (without the quotes).
Make sure the cell pointer is sitting in cell N3 and drag the copy handle down to cell N11. Ta-da!
In the last lesson, you learned that you can select one or more numbers and click the 'Currency Style' button on your formatting toolbar to make the numbers look like currency. But did you know that Excel provides you with many other styles you can use to format the numbers in your worksheet? Below, please find descriptions of some of Excel's most commonly used number formatting styles:
Percent Style
Right next to the 'Currency Style' button, you should see a 'Percent Style' button. This button can be used to turn a decimal value into a percent. Let's try it out: go to cell O3, type 0.45 and press the ENTER key. Now, return to cell O3 and click the 'Percent Style' button. Excel should immediately convert the number to 45%.
Once a cell has been formatted, it remains formatted. Return to cell O3 and try typing the value .32 in that cell. Press the ENTER key, and Excel will display 32%.
Now, return to cell O3, type .1273 and press ENTER. Notice how Excel rounds the number up to 13%.
Let's say you want more accuracy. Return to cell O3 and then turn your attention to the formatting toolbar. Two buttons to the right of the 'Percent Style' button, you should find an 'Increase Decimal' button. Click this button twice to see 13% change to 12.73%.
Removing a Format
Now, let's say you change your mind and want the cell to display 0.1273 instead of 12.73%. To get the cell to do this, you'll have to clear all formatting from the cell. The process is simple: return to cell O3 and click the 'Edit' menu at the top of your screen. Next, choose 'Clear' and then choose 'Formats.' That's it!
Any time you want to remove any type of formatting from a cell, all you have to do is click 'Edit/Clear/Formats.'
Comma Style
Almost all numbers other than currency or percents should be formatted using the 'Comma Style' button. This button gets its name because it will insert commas into your number to separate the thousands place from the hundreds place (or, if you work with really large numbers, the millions place from the hundred thousands place).
For example, go to cell O4 and type the number 1234567. Press the ENTER key and then return to cell O4. Now, click the 'Comma Style' button. It's right next to the 'Percent Style' button.
If your number is replaced by a series of ##### symbols, widen the column as described in lesson 2.
Once the column is wide enough to display your number, you will notice that Excel has inserted a comma between the '1' and the '2' and another comma between the '4' and the '5.'
Please notice that Excel has also inserted a decimal point and two digits after the decimal. If you don't want the decimal, return to cell O4 and click the 'Decrease Decimal' button twice (it's two buttons to the right of the 'Comma Style' button).
Other Styles
In the vast majority of cases, the 'Currency Style,' 'Comma Style,' and 'Percent Style' buttons will solve all of your formatting needs, especially when coupled with the increase and decrease decimal buttons.
On the off chance that the formatting options described above do not meet your needs, you will find an even larger selection of alternative formats by highlighting the cell(s) to be formatted, clicking on the 'Format' menu, then on 'Cells, and then on the 'Number' tab.
You will see several predefined format categories in a list box on the left. Click on a format category, and you will see other options to the right of the category.
Use your mouse to experiment with options. Look closely at this dialog box--you should see a sample showing what your number will look like should the selected format be applied. When you find an acceptable format, click on OK. The format you chose will be superimposed over each of the numbers in the selected cells.
Moving Data
If you ever need to move data from one part of your worksheet to another, the process is fairly simple.
1. Use your mouse to select the cells you wish to move. Make sure your mouse looks like a large white cross, hold down the left mouse button, and drag your mouse across the cells.
2. Click on the 'Edit' menu and then choose 'Cut.'
3. Click once on a SINGLE cell elsewhere in your worksheet.
4. Click on the 'Edit' menu and choose 'Paste.'
All of the cells you selected in step 1 will be transported to the new location.
Alternative method:
Here's another, slightly faster way to move data from one part of your worksheet to another. However, it takes quite a bit of skill. If you have trouble using a mouse, you might want to avoid using this technique.
1. Select the cells you wish to move.
2. Position your mouse anywhere around the extreme outer edge of the selection (avoid the copy handle). When you get to the very edge of your selection, your mouse will change into a small white arrow.
3. After (not before) the mouse changes into an arrow, hold down the left mouse button and drag your mouse to a new location. Release the mouse button at the new location, and your data will jump across!
Save the resulting file as assignment
Ag Technology – Taylor Spreadsheet Unit –Lesson #3 Page #5