Welcome to the world of Excel 5.0 for Windows.
In this program, we will introduce the basic features of the Microsoft Excel 5.0.
This special edition program is based on Excel 5.0 for Windows, but most of the ideas can
be used with other versions of Excel, including help for Lotus 123 users.
Excel is a complex multitasking spreadsheet that is easily managed in the Windows environment.
You must have Windows 3.1 and a mouse to use Excel.
Excel provides a platform to design your own forms and statements that require mathematical
calculations in a format that is consistent to everyday operations.
During this session, we will set up an expense report for an employee that travels regularly.
The form will need to be formatted to look like an employee's company standard expense
form.
To begin Excel, Windows 3.1 must be running.
To start Excel, double-click the Excel icon to start the program.
Excel will appear at the top menu bar reading Microsoft Excel Book 1.
Book 1 indicates the file.
We will need to create our own file by using the Save As command.
With the mouse, click the File menu bar in the upper left-hand corner of the screen.
Select Save As under File Name to enter the new file name, Expense 1.xls.
Then click OK.
This will create and open the new file, Expense 1.xls.
We are ready to start formatting the expense report.
With the mouse, click on cell A1.
Note the location of the highlighted area.
The cell location is shown above the A1 cell.
Another way we can move around the spreadsheet is to type in a cell number in the cell location
box.
You can also use the scroll bars on the side and bottom of the Excel window to move the
screen up and down with ease.
When designing our spreadsheets, the first thing to do is to set the page parameters.
Select Page Setup from the File menu.
All of these settings should be OK, except for the orientation of this spreadsheet.
It needs to be set to Landscape.
Now press OK.
We will need to set a font for the text type of the spreadsheet.
Click the A1 cell, then select the font arrow beside the Font Style status bar.
Select Arial, then click on the Font Size arrow button box and select the size 12 font.
This is just the size of your company's name.
Select the cells A2 to L6 by clicking on cell A2 and dragging the mouse down to L6.
Select the Arial font and click on the size arrow key.
Select size 7.
Notice the size 7 does not appear, but we can type this in.
The font size is set to 7 in this area.
Click in the A1 box and we will put in the company's name.
Type your company.
Highlight it, then select the bold button.
Press Enter or click on A2 with the mouse to advance to the next cell below.
Type your address.
Now advance to the next line and type your city, your state, and zip code.
Now skip two lines.
Type your phone number.
Now use the quick save button to save your work.
Before we can add the detail expense items to complete the columns, we need to add more
personal information at the top of the form.
Select cell E3 and type employee.
Move to cell H3 and type employee signature.
Position the cursor to cell K3.
Type the word date.
Move the mouse to cell E4 and type department.
In cell H4, type manager signature.
Move to cell K3 again.
Type the word date.
Press the copy button to copy the selected text.
Now move to the cell below, K4.
With the mouse, press the paste button to place the copy text into the cell.
These two rows need to be underlined.
To do this, highlight cells E3 to K4.
Select the border style arrow to display the borders pull down menu.
Choose the bold border box.
The two selected rows are now underlined.
Now we can do the expense data columns for the separate expense items.
Looking at the example on paper, you don't see any grid lines, but in this spreadsheet,
the grid lines appear on your screen.
Grid lines are a guide for Excel users and can be removed at the time the document is
printed.
In this example, the data cells are surrounded by borders, not grid lines.
Highlight cells A7 to L8 to change the font size of this area.
Click the font size arrow and select 11.
While this area is highlighted, click the text center icon button to center all the
data of these cells.
Then click on column B, which highlights the complete column.
Click on the format bar in the upper menu area.
Select column and then select width.
Enter a width of 11.
Select column C by repeating the process.
Highlight columns D through H and repeat the column width command and set these columns
for a width of 7.
Highlight columns I through K and repeat the column width command and set these columns
to a width of 10.
Then set the column width of L to 15.
We need to click on cell A8.
Then type date, then click on B7 and type location.
And then press enter.
Notice the activated cell is now B8.
Type from, then click on C7 and type location.
Then type to.
As you can see, the columns are starting to shape into an expense report.
Click on D7 and type air.
Then type fair.
Moving along to E7, type car.
And then type rent.
Click on F8 and then type taxi.
Continue these column inputs as per the example provided, park, toll, gas, hotel, phone, meals
and total.
Highlight cells A7 through L8.
Then click on the format section of the upper menu bar.
Select the cells, click on border, and then click on outline.
Then click on the second thickest line for the left and right borders.
Click on patterns.
And then click the arrow button next to the pattern box in the lower left.
Select a pattern that matches the example and select it.
Click OK.
Now highlight the cells A9 through L25.
Click on format and select cells.
Then by clicking on each of the left, right, top, and bottom boxes, select the thin continuous
line from the style box.
Press OK when finished.
Highlight cells A23 to C25.
We need to add information in a couple of these cells.
Type paid by employee and then press Enter.
Then type paid by company and finally type total.
We align the text in cells A23 through A25 by highlighting them.
Then click the left align icon.
Click on format, click cells, and select the pattern folder.
Click the pattern button and choose the pattern per example.
Click the border folder to make sure the left and right borders are clear.
Click on the outline and set that for the medium thick line.
Click OK.
To see the spreadsheet the way it is right now, let's click on file in the menu bar.
Select print preview.
This shows the actual page that your printer will print.
If grid lines do show up on your screen, turn them off by clicking on the setup button at
the top of the page preview screen and selecting the sheet folder.
Then in the print box, select grid lines.
Select the box to toggle on or off the grid lines.
Notice how the grid lines do not show up on the screen.
The only lines shown are the borders we put in earlier.
Click on close at the top of the menu bar and this will bring back the sheet in the
editing screen.
Now is a good time to save your work, so select save.
Write cells A27 through B28.
Choose the format menu, cells and the border folder.
Select a thin line for the left and right borders and then press OK.
Now select cells I27 through J28.
Again, go through the procedures to reach the borders folder.
Select the left and right borders with a thin line and press OK.
Highlight cells A27 through J28.
Give these cells a thick outline border and press OK.
You now have created two narrow cells on each side of a large cell.
Select date and place in cells A28 and B28.
Highlight both cells and then center justify the text.
Click on cell C27 and make it left justified.
Now type details of entertainment and miscellaneous.
And then press Enter.
In cell C28, type attendees purpose and make it left justified.
Highlight cell H28 and with right justification type description.
Highlight cell I27 and type ENT.
In cell I28, type AMT and also center it.
Now highlight cells I27 and 28.
Copy the text of these cells to J27.
Go to cell J27 and replace the text EMT with MISC.
Highlight cells A27 through J28.
Select the format menu and choose cells and the pattern folder.
Choose the light pattern and press OK.
Now save your file.
Now we can take a quick look by doing a print preview.
Select the file menu then click print preview.
To get a closer look at your document, position the mouse on the document.
The mouse arrow changes to a magnifying glass.
Close this screen to return to the normal print preview.
Highlight cells A29 to B32 and format the left, right, top, and bottom borders with
a thin line style.
Press OK when finished.
Select font size 11 and press OK.
Highlight cells C29 through J32 and format cells with top and bottom borders.
Select OK.
Set the font size to 7 and press Enter.
Click in cell K26.
Enter justify the cell and select font size 8.
Type in the word totals and make the text bold.
Highlight cells K26 to L33.
Click on format and select cells.
Set the formatting of the borders by selecting the left, right, top, and bottom to the thin
line style and make the outline border thick.
Center justify the cells.
Highlight K27 through L33 and select font size 7.
We are ready to format the final cells of the expense report.
Click on cell K27 and type ENT.
Then press Enter and type miscellaneous, total expenses, advance, prepaid, due company, and
due employee.
Select cell K26 and change the pattern in the format menu.
Select a darker pattern and click OK.
Format cells K27 to K33 and click on the format menu.
Select cells followed by the patterns folder.
Click on the pattern button and select background pattern.
Now click on L26, choose format, and choose the color black.
Then highlight cells L27 through L33 and set the font size to 10.
We have now completed the outline format of the expense report.
Now we can add the formulas to the cells that add up the totals automatically in the proper
totals columns.
Highlight cells A9 through A22.
Click on format and then select cells.
Then select the number folder.
Click on date and select the standard date style on top.
Make sure the correct font size is set for 11 while all of these cells are still highlighted.
These cells are set up for date format.
To check this setup, click on A13 and type 317.
Highlight cell A9 through A32.
Click on format then select cells.
Select the number folder.
Click on date and select the standard date style on top.
Make sure that the correct font size is set for 11 while all these cells are still highlighted.
When using the date format, you only need to enter the month and the day and Excel will
add on the year.
You can enter the date one of two ways.
One is to type in the number of the month and the day and the other is to write out
the name of the month and then add the day.
Remember, if you don't write out the month, you must separate the numbers with slashes.
Highlight cells D9 through L22 and click on format.
Then select the currency in category.
Next select the top selection in the format codes.
Click OK.
This sets up the type of number that is inserted in the cell.
To check this format, click on cell H15 and type 25.
Notice the number format will be seen in dollars and cents.
Click on the undo icon.
Highlight cells D23 through L25 and format these cells in the same configuration.
Then format the cells L27 through L33 the same way by highlighting them and selecting
proper numerical formatting.
We need to format the cells in the entertainment section of the expense report the same way
by highlighting the cells I29 through J33 and format these cells to the currency.
Now we can move ahead and put the formulas in the proper cells to add the totals of the
entries.
Click on cell L9 and click on the auto sum button.
This cell will be affected by the equation we set up.
Notice the equation block shows equal sum open and closed parentheses.
What we do now is highlight cells D9 through K9.
This cell will automatically set the formula for this cell to equals sum parentheses D9
colon K9 parentheses showing us that the sum of D9 and K9 will be inserted into L9.
Click the green check mark when the formula is OK or check the red X if the formula is
not.
Notice the zero dollars in cell L9.
To copy this click on the cell L9 and go to edit in the upper menu bar and choose copy.
Then highlight cells L10 through L24 go to edit and choose paste this time.
Notice the zero dollars in all the cells selected.
These cells are set up to sum up all of the rows in this area.
You can check this by entering values to the cells and checking the total to the right
in column L.
The employees company has a policy to pay for all airfares in advance of travel.
This prepay travel will be totaled in the pay by company column.
Click on cell D24 and click the auto sum button.
Then highlight cells D9 through D22 and click the green check mark when completed.
This will auto sum all the rows in the selected column to this cell.
Click on cell D25 and select the auto sum button.
Highlight cell D24 to auto sum this line.
Click on the green check mark when completed.
Select cell E23, auto sum cells E9 through E22 and press the green check mark.
Select cell E23 and highlight cell F23 through K23 and then paste.
Highlight cell E25 and then auto sum E23 and hit the green check mark.
Highlight cell E25 and highlight cells F25 through K25 and paste.
Highlight cell L23 and auto sum cells E23 through K23.
Select the green check mark.
Highlight cell L24 then auto sum D24.
Select the green check mark.
Highlight cell L25 and then auto sum D25 through K25.
Select the green check mark.
We have just set these cells to sum up the lines 23 and 24 to a total, but now we need
to black out a few cells to make this spreadsheet appear proper.
Click on cell D23 and go up to format.
Select the pattern folder and select the black pattern, then press OK.
Then copy that format to the cells E24 through K24.
Notice the blackout of this area.
To check this, do a print preview and take a look.
Let's format the date cells in the entertainment and miscellaneous section of the expense report.
Click on cell A22 and go to edit.
Select copy and highlight cells A29 through A32.
Go to edit again and select paste.
Now the place column should have a general setup and the text should be centered.
If they are not, highlight the cells B29 through B32 and set these settings.
By clicking on the center button, checking the format section, and changing the numerical
value to general.
Also, set these cells for a font size of 11.
Do the same to cells C29 through H32.
But click on the left justify button and check the formatting.
Remember, the font size for these cells should be set at 11.
Then highlight cells I29 through J33 and select format.
Select the numerical format to currency.
Click on cell I33, then click on the auto sum button.
Highlight cells I29 through I32 and click the green check mark.
Copy this cell over to J33 by again clicking on the cell I33 and using the copy and paste
commands in the edit window.
To finish setting up the formulas, we'll do the totals column.
Click on cell L27 and click on the auto sum button, then click on cell I33.
Then hit the green check mark.
This will put the entertainment amount total in cell L27.
Click on cell L28 and then the auto sum button and then cell J33.
This will put the miscellaneous amount total in cell L28.
Now click on cell L29 and the auto sum button to highlight cells L25 through L28.
Click the green check mark.
This will sum up the totals of the miscellaneous entertainment and paid by sections of the
expense report for the total expense.
To put the paid by company total in cell L31 for prepaid, select L31, auto sum D24 and
hit the green check mark.
Now we come to a somewhat complex formula input for the cells do company and do employee.
If you have questions or you want to learn more about formula inputs, see your Excel
operator's manual.
Click on cell L32 and select the auto sum button.
You need to delete the present formula and then type equals IF open parentheses L29 is
less than L30 comma plus L30 minus L29 minus L31 comma open quotes 0.00 close quotes close
parentheses and then click on the green check mark when finished.
This in simple terms tells Excel if the amount in cell L29 is less than the amount in cell
L30 to subtract L29 from L30 and subtract L31 or to enter 0.
Click on cell L33, select the auto sum button.
You need to delete the present formula and type equals IF open parentheses L30 is less
than L29 comma plus L29 minus L30 minus L31 comma open quotes 0.00 close quotes close parentheses.
This tells Excel if the amount in cell L30 is less than the amount in cell L29 to subtract
L30 from L29 then subtract L31 or to enter 0.
These formulas complete the expense report by finding the difference of the total expense
versus advance minus prepaid to equal due company or due employee.
Save your file again.
Use print preview to see if everything looks fine or print out the form by selecting print.
Press OK when finished.
To start a new expense report you must do a save as command while having this master
form open.
You will use this master form to create all new expense reports in the future.
Select save as and name the new file by typing in expense2.xls.
This has been an introduction to Excel 5.0 for Windows.
You should now be able to design your own functional spreadsheets for your home or business
needs.
We hope this video has made it easier for you to understand and utilize spreadsheet
designing with Excel.