Excel Spreadsheets
Assignment 1
Excel Overview
Excel is a powerful spreadsheet program. It is a spreadsheet, i.e., if you have a lot of information to store, you can place it in Excel and organize it. Excel is best used as a storehouse for numbers, and that is why the accounting term, "Spreadsheet" is applied to it.
Excel is not a database program in the true sense that Microsoft Access is a database. What is the difference? Excel is a powerful "Number Cruncher", while Access is a powerful "Information Cruncher". Excel is more useful for balancing your checkbook and keeping track of budgets. Access would be more useful for compiling and organizing a telephone directory.
Using Excel
When you manage your budget using paper, pencil and a calculator, you know how it is to re-enter information, change your mind on the basic setup of your spreadsheet, and need to add and delete columns of information. Excel makes it easy to accomplish all of these tasks. We'll make a little budget as our exercise. Remember, what we are creating is called a spreadsheet. We are going to spread our information out across the sheet by arranging it in columns and rows.
Column - A column of information is a vertical line of information. Like visually looking down a list of names or numbers in a telephone directory.
Row - A row of information is a horizontal line of information, like looking across a telephone directory for someone's last name, first name, address and telephone number.
Our spreadsheet will contain both text and numbers.
Raw Data - is the information and values that we type into our spreadsheet. This is what we type into our columns and rows.
Derived Data - are values that we calculate from the raw data that we enter.
Notice in our Excel spreadsheet that the vertical columns are designated across the top of the spreadsheet as Column A, Column B, Column C, etc. The horizontal rows of information are numbered along the left side of the screen as 1,2,3 etc. The intersection of a column and row creates a cell:
-The upper left hand corner of your screen is cell A1
-If you go over 3 columns and down 4 rows, you intersect at cell C4.
Notice that when a cell is selected (and ready to accept your information input) it has a dark outline. You select a cell by clicking on it. While we are talking about selecting cells, notice the cursors on your spreadsheet.
- The cross shape is your basic selector cursor. When you click on a cell, that is made the active cell, i.e., where you enter your information.
Setting Up a Spreadsheet - Four Basic Steps
You can look at creating a spreadsheet in Excel as consisting of four basic steps:
-
Enter text to set up your spreadsheet structure
-
Enter the numbers, the values for your information
-
Enter Formulas to calculate the results for your spreadsheet
-
Format the spreadsheet to make it easy to read
What kind of information goes into a cell?
-You enter information that makes sense to you as you set up your spreadsheet. For example, you can enter text which serves as a label for your columns and rows. You may want to label columns by the month headings, Jan, Feb, Mar etc.
-You may also enter numbers. These may be your monthly expenses for specific items such as rent, telephone and cable expenses .
-Finally, you can enter formulas, that is mathematical expressions to total your bills, and then subtract that bill total from your monthly income. We can then calculate our spending money. We will create this simple budget for our exercise. It will look something like the following graphic.
Notice the cell contents, e.g., cell B7 contains the text, "Rent", and cell E9 contains the number 85 that is formatted to look like currency.
You can print this page for reference. It is the budget outline that we will create for our exercise.
Creating an Outline for our Budget Spreadsheet
The cell designations for entering data can be chosen arbitrarily, but let's all do the same so that when we refer to problems, we are all referring to the same cell. We are going to duplicate the spreadsheet shown above.
-
In cell B5 type "Monthly" (do not type the quotes)
-
In cell B6 type "Bills"
-
In cell C2 type "Personal Budget for First Quarter"
-
In cell C3 type "Net Monthly Income"
-
In cell C6 type "Jan"
-
In cell D6 type "Feb"
-
In cell E6 type "Mar"
-
In cell F5 type "Quarter"
-
In cell F6 type "Totals".
Now we have created the outline for our budget. Look it over to make sure that what we have done makes sense to you.
Entering Data
-
In cell C7, and for each month enter 500. Note that the entry is just the number 500. We would like to have it show as currency. How do we do that? We must format the cell. To do that,
-
Click on the FORMAT menu at the top of your screen
-
Select Cells from the drop down menu
-
Finally select currency from the number window
-
Click the OK Button
Note: The cell shows $500.00 because we formatted it to do so. The real contents of the cell show in the formula bar at the top of your screen. The formula bar is the long white box next to the = sign. This box shows the true contents of the cell that is selected. The true contents are the numbers 5 and 0 and 0.
How do I copy the format of the cell without having to go through the format menu every time?
On your toolbar there is a button called a Format Painter.
The Format Paint button allows you to copy the way a cell looks, e.g., like currency without copying the contents.
-
Select cell C7 by clicking on it
-
Click on the Format Paint Button
-
Drag your cursor (hold down the left mouse button while moving across the cells) across cells D7 and E7.
Note that the cells change from just showing 500 to the currency format.
Now we will enter the rest of our monthly expenses:
-
Enter 125 in cell C8, 150 in cell D8 and 60 in cell E8.
How do you format the cells to read currency?
-
Enter 120 in cell C9, 100 in cell D9 and 85 in cell E9.
Format the cells to read currency.
(You may print this assignment)