IDIS 110 - Excel Auto Fill and Dates Exercise

Excel Auto Fill and Dates Exercise
(Jeff Nyhoff's IDIS 110 sections)

Auto Fill

Open Microsoft Excel.
Excel begins with a blank workbook file. Save this new workbook as:
Excel Exercise 4 - John Calvin (replace "John Calvin " with your own name )
Right-click on the Sheet1 tab at the bottom of the worksheet

and select Rename from the menu that appears:

enter "AutoFill" as the name of this worksheet.
Enter 10 in cell A2:
See that little black square in the lower right corner of the border that indicates the active cell?
This is called the "Auto Fill handle."

Click and drag this handle to cell F2. This copies cell A2 and pastes it to the other cells:
Enter "Calvin" in cell A5:

Click and drag the Auto Fill handle to cell A7. This copies cell A5 and pastes it to the other cells:
Enter 10 in cell A10
and 20 in cell B10 :
Select both cells A10 and B10 :

Click and drag the Auto Fill handle to cell E10.

Notice what happened: Excel noticed that you were counting by 10, so it kept this progression going...
This works for numbers with decimal fractions as well.

Enter 1.2 in cell A12 ,
enter 1.4 in cell B12 ,
and then select both cells:
Click and drag the Auto Fill handle to cell E12. The result is that Excel continues the progression of counting by increments of .2 :

Auto Fill also works for times :
- Enter 8:00 in cell A14
- Click and drag the Auto Fill handle to cell E14. The result is that Excel counts in one-hour increments:
- Now, enter 8:15 in cell A15
  and select both cells A14 and A15 :
- Click and drag the Auto Fill handle to cell A18. The result is that Excel counts in 15-minute increments:
Auto Fill also works for dates :
- Enter 9/11/01 in cell A20 :
- Click and drag the Auto Fill handle to cell E20. The result is that Excel counts in one-day increments:
- Now, enter 9/18/01 in cell A21
  and select both cells A20 and A21 :
- Click and drag the Auto Fill handle to cell A25. The result is that Excel counts in one-week increments:
  
  What are all of the "#" signs doing in cell A25 ?
  This is how Excel indicates that the column a too narrow to display the entry in this cell.
  Solution:
  - Double-click on the divider between the heading of column A and column B :
  - This causes Excel to expand column A to be wide enough to display the widest entry in the column:
Auto Fill also works days of the week and month names :

Enter "Monday" in cell A28 :
Click and drag the Auto Fill handle to cell G28. The result is that Excel enters the other days of the week:
Widen column C so that "Wednesday" is completely visible.
Enter "January" in cell A30 :
Click and drag the Auto Fill handle to cell A34. The result is that Excel enters the names of the following months automatically:

Your "AutoFill" worksheet should now look like:
Save this Excel file.

Date Arithmetic

Right-click on the Sheet2 tab

and choose Rename.
Rename this worksheet to

Click on this tab to make it the active worksheet.
In cell A2, enter the words "Today's Date: "

Notice that the the full text string "Today's Date:" is displayed, even though column A is not wide enough to display it.
- This is because cell B2 is currently an empty cell.
- As we shall see, however, as soon as something is entered into cell B2, the last part of "Today's Date:" will be cut off...
In cell B2, enter the following: 9/11/01
Notice that now, because cell B2 is no longer empty, we can no longer see
all of the phrase "Today's Date:" in cell A2 :
- So, go ahead and widen column A so that this phrase is completely visible.
  - Remember: you can do this by clicking and dragging
    on the border between columns A and B:
  - Or, you can just double-click on this border,
    and Excel will make the column just wide enough
    to display the widest entry in the column.
- Afterward, these cells should now look something like this:

Right-click on cell B2 and choose Format Cells from the menu that appears:

The following dialog then appears:

In the Format Cells dialog, clicking on the tabs that appear
at the top of the dialog

gives you access to the many options for formatting cells in Excel:
The Number tab:

brings up all of the ways that numbers can be formatted in Excel :
- In the Category panel, all of the different general types of
  number formats are listed:
- When you select one of these categories, the specific formatting
  options within this category are displayed in the Type panel:
As we can see, the current format selected for cell B2
is a Date format

and, more specifically, it is date that will be displayed in the
default Date format such that the month number, day, and full year
are shown, separated by slashes.

This is indicated by the format selected in the Type panel

and the Sample box shows what the data currently
in this cell will look like in this particular Date format:
Thus, if we click on the next Date format instead

we see a preview of how the date we entered in cell B2
will appear in this format:
Click the OK button

and the format of cell B2 is changed:
Notice, however, that the Formula Bar still shows
the text in the default Date format:

Right-click on cell B2 again, and choose Format Cells from the menu that appears:

In the dialog that appears, select the Number category

and change the number of Decimal places to zero :

Then, press the OK button

Our cells now look like this:

Why does Excel allow you to format a date as a number ?
As it turns out, it is because Excel actually stores dates as integers.

Specifically, this integer represents the number of days
since January 1, 1900 .
Now, in cell B2, enter the following formula:=TODAY()
- The TODAY() function displays the current date.
- Unlike most Excel functions, it doesn't have any arguments...
- Thus, our cells now look something like the following
  (The integer you see may be slightly different.)
- The integer that appears in cell B2 represents today's date.
  
  Again, the way that Excel stores a date is by storing an integer
  representing the number of days between that date and
  January 1, 1900. Thus, the integer you see represents the
  number of days that have passed between January 1, 1900
  and today...
But let's change the format of our date back to a Date format:

Right-click on cell B2 and choose Format Cells from the menu that appears:

Select Date format in the Category panel:

and then the first Date format in the Type box:

Our cells now look something like this:

Note: Your date is likely to be a different one.

(Because your "today" — i.e., the day when you are doing this exercise
— is at a later date than my "today" : the day when I am creating
these screen snapshots... ) :

Now, however, column B is unnecessarily wide for the changed
Date format, so adjust the width of column B, either by
clicking and dragging or double-clicking on the border
between columns B and C : |

Afterward, the cells now look something like this:

Why does Excel store dates as integers?

Because it enables us to be able to do arithmetic with dates...

Let's try some Date arithmetic :

In cell A4 , enter "My Birth Date: "
- Widen column A so that this phrase is completely visible.
In cell B4 , enter your birth date:
In cell A6 , enter "Number of Days I've been alive:"
In cell B7 , enter this formula: =B2-B4

By default, a date will appear in cell B7 :

Again: the date shown in your worksheet will be different than this one...
Why does it show a date, here? Because Excel assumes that,
since you are doing an arithmetic operation with dates, you want your
result to be in Date format as well. In this case, that is not true...
Right-click on cell B7 , and select Format Cells.
Click on the Number tab and under "Category:" , select General :

Tip: Select "General" whenever you don't want the number in this cell
to have any "special" formatting. "General" is Excel's default,
"generic" number format.

Click the OK button:
The number of days you have been alive is now displayed in
Excel's "General" number format:
The "Dates" worksheet should now look something like this:

Save this Excel file again, and submit it via KnightVision.