MAKING A SPREADSHEET DO YOUR MATH HOMEWORK
By the End of this Tutorial, You’ll be Able to:
See the similarities between your checkbook and a spreadsheet
Type text, numbers and dates in a spreadsheet cell
Add formulas to a spreadsheet to perform calculations on the values you entered.
Graph the values in a spreadsheet even if you don’t know how to graphTo place an order for the Complete Project Material, pay N5,000 to
GTBank (Guaranty Trust Bank)
Account Name – Chudi-Oji Chukwuka
Account No – 0044157183
Then text the name of the Project topic, email address and your names to 08060565721.
There’s no mystery to spreadsheets. A checkbook is a spreadsheet. A calendar is a spreadsheet. Your 1040 tax form is a spreadsheet. Any sheet that has boxes you can fill in is a type of spreadsheet.
So, what’s so special about computerized spreadsheets? For one thing, they do the math for you. For example, a computerized grade book spreadsheet can add each student’s grades, determine the average for each student, and even assign the correct letter grade for each average. And that’s not all. The spreadsheet can also display the averages as a graph, shown how each student is doing in relation to the other students. In this tutorial, you’ll learn what it takes to create your own spreadsheets, and some of the things you can do with them.
A Computerized Ledger Sheet
A spreadsheet is a sheet with a spread. What’s a spread? It’s a grid: a series of columns and rows that intersect to form thousands of small boxes called cells. Most spreadsheet applications display a collection of spreadsheets in a workbook. You can flip the pages in the book by clicking on tabs.
Why Did the Column Cross the Row?
Look across the top of any computer spreadsheet, and you’ll see the alphabet (A, B, C, and so on). Each letter stands at the top of a column. Down the left side of the spreadsheet, you’ll see numbers representing rows. The place where a column and row intersect forms a box called a cell. This is the basic unit of any spreadsheet. You will type text, values, and formulas in the cells to make your spreadsheet.
Knowing Where a Cell Lives
If a spreadsheet loses a cell, it’s in deep Voodoo. To keep track of cells, the spreadsheet uses cell addresses. Each cell has an address made up of a column letter and row number. For example, the cell that’s formed by the intersection of column A and row 1 has the address Al (see the previous picture).
Naming Cells and Ranges
Some spreadsheet programs let you name individual cells or groups of cells (ranges). You can then use the names, instead of the cell addresses, to refer to the cells.
Look in the upper-left corner of a new spreadsheet screen, and you’ll see a cell that has a thick box around it. The thick box is the cell selector, it tells you which cell you’re on. To jump from one cell to the next, you can use a mouse to click on the desired cell, or you :in use the arrow keys or the Tab key (press Tab to move to the next cell to the right or Shift+Tab to move left). When the cell selector is on a cell, the contents of that cell are _5uaffy displayed in the formula bar or input line at the top of the screen. (Technically, the entire bar is referred to as the formula bar. The part in which you type is called the input line)
Building a Spreadsheet from the Ground Up
I bet you’re just dying to know how you go about making a spreadsheet. The easiest way is to get a friend to set it up for you to insert all the formulas and other complicated stuff. Then, all you have to do is type in your data and watch the spreadsheet do its thing If you’re a do-it-yourselfer, however, you’ll need to take the following steps (don’t worry. I’ll go into more detail later in this tutorial):
Step 1: Design the spreadsheet.
Step 2: Label the columns and rows.
Step 3: Enter your data: values and dates.
Step 4: Enter the formulas and functions that the spreadsheet will use to perform calculations.
Step 5: Perform a test run to make sure the spreadsheet works. Step 6: Format the cells (to display dollar signs, for instance). Step 7: Print the spreadsheet.
There’s no law that says you have to perform the steps in this order. Some users like to enter their formulas before entering their data, so the formulas calculate results as they work. Regardless of how you proceed, you will probably have to go back to previous steps to fine-tune your spreadsheet.
Step 1: Designing the Spreadsheet
If you have a form that you want the spreadsheet to look like, lay the form down by your keyboard and use it as a model. For example, if you’re going to use the spreadsheet to balance your checkbook, use your most recent bank statement or your checkbook register to set up the columns and rows.
If you don’t have a form, draw your spreadsheet on a piece of paper or a napkin to determine the columns and rows you need. (It doesn’t have to be perfect, just something to get you started.)
Step 2: Labeling Your Columns and Rows
When you have some idea of the basic structure of your spreadsheet, you’re ready to enter labels. Labels are common-sense names for the columns and rows.
To enter a label, click in the cell where you want it to appear, type the label, and press Enter. If your label starts with a number (for example, 1994 Sales), you may have to type something in front of it to tell the spreadsheet to treat it as text rather than as a value. In most programs you have to type an apostrophe (‘) or a double quotation mark (“). Usually, whatever you type appears only in the input line until you press Enter. Then the label is inserted into the current cell.
If an entry is too wide for a cell, it will overlap cells to the right of it…unless the cell to the right has its own entry. In such a case, the entry on the left will appear chopped off (hidden). If you click on the cell, you can view the entire entry on the input line. If you want to see the entire entry in the cell, you can widen the column, usually by dragging the right side of the column header, as shown here.
Step 2 1/2: Editing Your Entries
When you make mistakes or change your mind about what you entered, the best way to make corrections is usually to replace the entry. Tab to the cell that contains the entry, type the replacement, and press Enter. That’s all there is to it.
To edit an entry, click in the cell you want to change, and then click inside the entry in the input line or press a special key, for example F2. This puts you in Edit mode, and allows you to edit the entry on the Input line. You can then use the arrow keys to move the cursor or insertion point and type your change. Press Enter when you’re done. Some spreadsheets offer something called in-cell editing. Instead of editing the entry on the Input line, you edit it directly inside the cell. To edit an entry, you simply double-click on it, and then enter your changes.
Step 3: Entering Values and Dates
Once you have labeled your rows and columns, you’re ready to enter your raw data: the values and/or dates that make up your spreadsheet. As you type your entries, keep the following in mind:
Values are numbers. Whenever you type a number, the spreadsheet “knows” it is a value. You don’t have to do anything special.
Don’t enter dollar signs or percents. You can have the program add these symbols for you when you format the cells. Type only the number.
Type dates in the proper format for your program. In most programs, you must type the date in the format mm/dd/yy (02/25/96) or dd-mm-yy (02-FEB-96).
Dates are handled as numbers. Although the program displays dates in a format that people understand, it treats a date as a numerical value (for example, the number of days since January 1, 1900). You can then have the program use the date in a formula to calculate when a payment or delivery is due.
********** Long entries. If a value you type is too wide for a cell, the program may display a series of asterisks instead of the value. Don’t worry — your entry is still there. You can click on the cell to see the entry in the input line, and if you widen the column, the program will display the entire value.
To enter values or labels quickly, many programs let you copy entries into one or more cells or fill selected cells with a series of entries. For example, in Excel, you can type January in one cell, and then use the Fill command to have Excel insert the remaining 11 months in 11 cells to the right. Fill also allows you to duplicate entries. For example, you can type 250 in one cell, and then use the Fill command to enter 250 into the next 10 cells down.
Step 4: Calculating with Formulas and Functions
At this point, you should have rows and columns of values. You need some way to total the values, determine an average, or perform other mathematical operations. That’s where formulas and functions come in. They do all the busy work for you…once you set them up.
What Are Formulas?
Spreadsheets use formulas to perform calculations on the data you enter. With formula you can perform addition, subtraction, multiplication, or division using the values contained in various cells.
Formulas typically consist of one or more cell addresses and/or values and a mathematical operator, such as + (addition), – (subtraction), * (multiplication), or / (division). For example, if you wanted to determine the average of the three values contained in cells Al, Bl, and Cl, you would use the following formula:
Entering Formulas in Your Spreadsheet
To enter a formula, move to the cell in which you want the formula to appear, type the formula, and press Enter. Some spreadsheets assume that you want to type a formula if you start your entry with a column letter. Other spreadsheets require you to start the formula with a mathematical operator, such as an equal sign (=) or plus sign (+).
Most spreadsheets let you enter formulas in either of two ways. You can type the formula directly in the cell in which you want the result inserted, or you can use the mouse to point and click on the cells whose values you want inserted in the formula. To use the second method, called pointing, you would use the keyboard and mouse together. For example, to determine the total of the values in B4, B5, and B6, you would type =, click cell B4, type +, click cell B5, type +, and click cell B6.
If your spreadsheet application has a toolbar, It probably has a Sum button that looks like £. To quickly determine a total, click inside the cell to which you want the total inserted, click the Stun button (£), and then drag over the eels that contain the values you want to add, When you release the mouse button and press Enter, the spreadsheet performs the re¬quired calculations and Inserts the result.
Using Ready-Made Functions for Fancy Calculations
Creating simple formulas (such as one for adding a column of numbers) is a piece of cake, but creating a formula for the one-period depreciation of an asset using the straight-line method is a chore. To help you in such cases, many programs offer predefined formulas called functions.
Functions are complex ready-made formulas that perform a series of operations on a specified range of values. For example, to determine the sum of a series of numbers in cells Al through HI, you can enter the function @SUM(A1..H1), instead of entering +A1+B1+C1+ and so on. Every function consists of three elements:
The @ or = sign indicates that what follows is a function.
The function name (for example, SUM) indicates the operation to be performed.
The argument (for example A1:H1) gives the cell addresses of the values the function will act on. For example, =SUM(A1:H1) determines the total of the values in cells A1 through HI.
Although functions are fairly complicated and intimidating, many spreadsheets have tools to help. For example, Microsoft Excel offers a tool called the Function Wizard, which leads you through the process of inserting functions. It displays a series of dialog boxes asking you to select the function you want to use, and pick the values for the argument. The following figure shows Function Wizard in action.
Step 5: Performing a Test Run
When your spreadsheet is complete and you’re fairly sure it will work, perform a test run to verify that the spreadsheet works. Most spreadsheets automatically calculate formulas as you enter them, but some programs require that you enter a Calculate command. After you enter the Calculate command (if your program uses it), look for the following problems:
Crazy results If your formulas or functions produce results that you know can’t be correct, make sure you entered the formulas correctly and that the cell addresses are referring to the right cells.
Narrow columns If a column is too narrow, you may end up with a label that is chopped short or a value that appears as a series of asterisks. Change the column width to accommodate the longest label or value.
Wrong order of operations Make sure each formula performs its calculations in the right order. You can change the order of calculations by using parentheses. For example, if you entered =C3+C4+C5/3 to determine the average of C3+C4+C5, the program will divide the value in C5 by 3 and then add it to C3+C4. =(C3+C4+C5)/3 would give you the correct result.
My Dear Aunt Sally
Use this mnemonic to remember the order in which a spreadsheet performs mathematical operations: My (Multiplication) Dear (Division) Aunt (Addition) Sally (Subtraction). To change the order of operations, use parentheses. Any operation inside parentheses is performed first.
Forward references If you use formulas that rely on other formulas for their calculations, make sure that no formula uses the formula in a later cell. In other words, a formula cannot use a value that has not yet been calculated.
Circular references A circular reference occurs when a formula uses its own results as part of a calculation. The spreadsheet goes around in circles trying to find the answer, but never succeeds.
If something doesn’t work, go back and correct it; then perform another test run until the spreadsheet works.
Step 6: Making the Cells Look Pretty
Once you have the basic layout of your spreadsheet under control, you can format the cells, to give the spreadsheet the desired “look.” The first thing you might want to do is change the column width and row height to give your entries some breathing room. You may also want to format the values tell the program to display values as dollar amounts or to use commas to mark the thousand’s place.
In addition, you can change the type style and type size for your column or row headings, change the text color, and align the text in the cells. For example, you may want to center the headings or align the values in a column so that the decimal points line up. To improve the look of the cells themselves, and to distinguish one set of data from another, you can add borders around the cells and add shading and color to the cells.
Many newer programs have an Autoformat feature that allows you to select the look you want your spreadsheet to have. The program then applies the lines, shading, and fonts to give your spreadsheet a makeover, as shown in the following figure.
Formatting cells means to improve the look of the cells or cell entries without changing their content. Formatting usually includes changing the type style and size of type, adding borders and shading to the cells, and telling the program how to display values (for example, as currency or in scientific notation).
Step 7: Getting It in Print
When you finally have all your numbers entered and the spreadsheet has performed the calculations, you may want to print the spreadsheet to send to someone else or to file with your records.
The problems you are most likely to encounter when printing a spreadsheet occur be¬cause a spreadsheet is too wide for the paper on which you want to print. If you print a spreadsheet that’s too wide, you end up with several pages that you have to tape together. Most spreadsheet programs offer various ways to solve this problem:
Automatic font reductions. You can tell some programs to fit the spreadsheet on the page no matter how small it has to make the type.
Landscape printing. You can print your spreadsheet sideways on a page to fit more columns across the page. However, you must have a printer that can print in landscape mode (most printers can handle landscape printing).
Partial printing. You can select the section of the spreadsheet you want to print and then print only that section.
Hiding columns. You can enter a command to hide some of the columns in the spreadsheet. When you print the spreadsheet, the hidden columns are omitted.
Instant Graphs (Just Add Data)
People, especially management types, like to look at graphs. They don’t want to have to compare a bunch of numbers; they want the bottom line. They want to see immediately how the numbers stack up. Most spreadsheet programs offer a graphing feature to trans¬form the values you entered into any type of graph (aka chart) you want: bar, line, pie, area, or high-low (to analyze stock trends). The steps for creating a graph are simple:
1. Drag with the mouse over the labels and values that you want to include in the graph. (Labels are used for the axes.)
2. Enter the Graph or Chart command. (This command varies from program to program.)
3. Select the type of graph you want to create.
4. Select the OK option. The program transforms your data into a graph and inserts it into the spreadsheet, as shown here.
Five Cool (and Not-So-Cool) Things You Can Do with a Spreadsheet
I already mentioned a couple practical uses for spreadsheets: averaging grades and balancing your checkbook (although a personal finance program, such as Quicken, works much better). I sat around awhile and thought up some other practical things you can use spreadsheets for:
Schedules Use a spreadsheet to keep track of your various projects. You can use a separate row for each project and a separate column for each stage in the project.
Invoices Create an invoice that lists the parts delivered, the number of parts, and the price per part. The invoice can calculate the total due for each part, the subtotal of all parts, the amount of sales tax due, and the grand total (total plus tax).
Loan amortization If you are purchasing a house or car or taking out a loan to start your business, you can use a spreadsheet to determine how much interest and principal you will be paying on various loans.
Home or business inventory Use a spreadsheet to keep track of each item you own and how much it is worth. Such a record is invaluable in the event of a fire or theft. (Assuming the record doesn’t get burned or stolen.)
Tic-Tac-Toe. Those little boxes are just begging for some Xs and Os. You can crank up your font size and have a ball playing tic-tac-toe on-screen.
Dropping a Few Spreadsheet Names
The big three names in spreadsheets are Quattro Pro, Lotus 1-2-3, and Excel. Quattro Pro is the easiest of the lot, but offers fewer features than either Excel or Lotus 1-2-3. The choice between Lotus 1-2-3 and Excel is a toss-up. If you use Lotus 1-2-3 at work, you might want to stick with it at home as well. If you’re looking for a popular spreadsheet that provides plenty of tools for simplifying tasks, Excel is your best bet.
The Least You Need To Know
This tutorial has given you a glimpse of the spreadsheet’s power. When you get the opportunity to work with some of the spreadsheet applications that are on the market, you’ll be amazed at how much work they can save you and how fast they perform their chores. Until you get that chance, make sure you understand the basics:
A spreadsheet is a grid consisting of rows and columns that intersect to form cells.
Each cell has a unique address that’s made up of a letter (representing the column) and a number (representing the row).
A cell can contain any of the following entries: a row or column heading, a formula, a function with an argument, or a value.
Formulas perform calculations on the values in the cells. Each formula consists of one or more cell addresses and a math operator.
A function is a ready-made complex formula that performs calculations on a range of values.
Before you begin using your spreadsheet, perform a test run and work out any bugs.
You can format the cells in a spreadsheet to control the text size and style, and to add lines or shading to cells.
GTBank (Guaranty Trust Bank)
Account Name – Chudi-Oji Chukwuka
Account No – 0044157183
Then text the name of the Project topic, email address and your names to 08060565721.