How to Create a Spreadsheet in Microsoft Excel 2007
Posted in WordPress on May 18, 2014
Please Note: In order to follow along with this tutorial please have your Microsoft Excel 2007 software open.
What are spreadsheets?
When writing an article, you organize the information by breaking it up into paragraphs and sentences. You group similar ideas into sections and arrange them in a logical sequence. This structuring of information makes it easy to locate and understand the text. But what will you do if you are asked to organize information that is represented with numbers? For example, how will you structure your monthly budget or analyze survey results?
Numerical data when entered into tables (rows and columns), becomes easier to locate and understand for quick viewing.
For example, remember how baseball or cricket scores are displayed on your TV screen during a match? There are rows and columns. The rows display the names of the players and the columns display information such as the number of balls each batsman played the number of runs he made, and the run rate.
Benefits of Working with Computerized Spreadsheets (or Worksheets)
Meet John Nicholas who teaches at St. Xavier’s and is the class teacher for the seventh grade. Every year, he spends hours manually entering the marks for different subjects for each student into the result sheet, finding the highest and the lowest totals for the class, and then calculating the class average. But this year, things promise to be different. John plans to use computerized spreadsheet for his task.
A spreadsheet is essentially a grid containing several rows and columns, in which text or numerical data is entered.
The word spreadsheet comes from the account keeping ledgers that use a double spread or both the sheets of an open register to enter financial data.
Spreadsheets are typically used to create budgets, analyze survey results perform various types of financial analysis, create financial reports and charts, organize lists, sales forecasting, and tracking expenses. These are also used for tabulating data for result sheets for your exams, attendance registers in offices, and keeping scores during games etc.
Can you think of other instances where spreadsheets can be used?
Traditionally, people used huge sheets of paper as spreadsheets which are not only cumbersome and complicated but time consuming. Any information that changes must be erased and rewritten. With computerised spreadsheets, however, you can create formulas that automatically update when you change your data.
Computerised spreadsheets are created using a special computer software program, such as MS-Excel and Lotus 1-2-3.
Microsoft Excel is a spreadsheet program that lets you create and work with computerised spreadsheets. It is a powerful tool to organize, manipulate and present data, fast and simple to use, even when you are calculating complicated formulae. The program has Word-like features to help you format text and add borders to cells. Excel also allows you to create attractive-looking graphs and charts to supplement the figures in your spreadsheets.
What is a Workbook?
An Excel file is also called a workbook. It is the file in which you work and store your data.
To create a new blank workbook in Excel 2007,
Click Quick Access toolbar > New.
To save a workbook in Excel 2007,
1. Click Quick Access toolbar > Save button. 2. Type Class9_results and save the file to the desired location.
Now, let’s explore our spreadsheet to understand more about its features.
Notice that in the lower left corner of your spreadsheet, next to a bunch of arrow buttons, are tabs called Sheet1, Sheet2 and Sheet3. These tabs represent the worksheets where you will enter your data.
The rows and columns grid that you see on the screen is the worksheet represented by Sheet1.
1. Click on the Sheet2 tab. 2. A blank spreadsheet opens. 3. Click on Sheet 3. 4. Another blank spreadsheet opens.
You can compare these worksheets to the pages in a notebook. Like a notebook has many pages, a workbook can contain many worksheets. A worksheet is also called a spreadsheet.
This feature enables you to create separate spreadsheets for all related topics in one common Excel file. For example, the Class9_results.xls file can hold separate worksheets for the First Terminal Examination Results, Second Terminal Examination Results etc.
Note: By default, an excel file or workbook has three worksheets. However, as you work along, you can add as many worksheets as you want to the workbook. You can also delete existing worksheets form a workbook.
Let’s rename the first worksheet as First Terminal Examination Results.
To rename a worksheet,
1. Double click on the tab. 2. The name Sheet1 is selected. 3. Enter the new name First Term Exam Results to overwrite the previous name Sheet1.
Here are some more ways you can manipulate a worksheet.
To Copy a worksheet in Excel 2007
1. Right click on the worksheets. 2. A dialog box opens. 3. In the Before Sheet section, click on the sheet before which you want to put the new sheet. 4. Click ok.
To Insert a worksheet
In Excel 2007 click the insert sheet button which is located next to the worksheets (at bottom beside sheet1, sheet2 etc.)
To Move a Worksheet
Click on the sheet tab and drag it to the desired location.
To Delete a Worksheet
To delete a sheet in Excel 2007, use the right click menu > and delete button.
To Open and Close a Workbook in Excel 2007
1. Click Office button > open.
1. Click Quick access toolbar > Open. 2. Select the workbook to be opened and click open. 3. The workbook opens.
Now look at your spreadsheet. Do you notice how it is neatly divided into a grid of horizontal rows and vertical columns?
FACT: A spreadsheet contains 256 columns and 65,536 rows.
The point of intersection of a column and a row is called a cell. It is the basic unit of a worksheet. This is where you enter, calculate, manipulate and analyse data such as numbers and text. Each cell can contain text, values, or a mathematical formula.
A cell has a unique address, or “cell reference”, which is composed of the coordinates of the intersection of a column and a row.
Each column is identified by an alphabet on the top of the worksheet – A, B, C. Each row is identified by a number on the left side of the grid – 1, 2, and 3. These letters and numbers of the columns and rows are called labels.
You can easily locate a cell by its label. For example, a cell located at the intersection of the fourth column i.e. Column D, and third row i.e. Row 3 will have the address D3.
Activity: In your spreadsheet, locate the cells B2, E9, L13.
Active cell is the cell currently selected for either data entry or editing. You can identify it by the heavy black border, with a small square in the lower corner that surrounds it. John begins his work by selecting cell A1. To make cell A1 your active cell,
1. Click inside the cell A1. 2. A black border will appear around it, distinguishing it from other cells.
Entering Information (Text and numbers) into cells
Having learnt about the Worksheets and Workbooks John wants to now use Text to display non-numerical information like names of students and numbers to display the marks obtained in the second terminal examination.
To enter text / numbers in a cell, Select a cell and start typing.
• To move to the cell below the active cell, press Enter. • To move to the cell adjacent to the active cell, press Tab.
Activity: Type the following information in your spreadsheet.
• Ensure that the first cell A1 is selected.
• Now type: Second Terminal Examination Results
• As you go along you will notice that a blinking cursor appears in front of every character that you type. Also, notice that your text appears in the formula bar, too.
• After you have finished typing, press Enter.
• The current cell becomes deselected, and the outlined border now moves to the cell below it, A2.
Another thing that you notice is that the last portion of the text in cell A1 appears to flow over the column A into the adjacent cells in columns B, C and D. In fact, if you look closely, the common borders between the cells seem to have disappeared. Don’t worry. Your text is safely in cell A1.
• Now type the following in the corresponding cells:
Cell A1 – “Second Terminal Examination Results ”
Cell A2 – “Roll No”
Cell B2 – “Name”
Cell C2 – “English (100)”
• The text / numbers appear in the formula bar, too.
• Although the last portion of the text in a cell A1 appears to flow over column A into the adjacent cells in columns B, C and D, don’t worry. The borders between the cells have not disappeared. Your text is safe in cell A1.
If you find that text in cell C2 is overflowing into cell D2,
1. Move the mouse pointer over to the C column label.
2. Carefully place the mouse over the common border between columns C and D. The pointer changes to a two headed arrow.
3. Press the left mouse button and slowly drag to the right.
4. Column C gradually widens.
5. Release the mouse button.
6. Column C becomes wide enough to enclose the entire text in it, and all columns in front of it shift to the right.
7. Type the text shown against each of the remaining cells. Do not forget to press Tab after typing each text item.
8. After you have entered text in the various cells, adjust their column width to show all the typed content.
Consider these issues when creating a worksheet:
• Which items are data, numbers that you will type
• What do you want to calculate from the data
• Which number are constants and used in a variety of calculations
• What arrangement in the columns and rows will make it easiest for you and your intended audience to work with the spreadsheet?
The suggested order for entering data in a spreadsheet is:
• Type the text information to create a structure.
• Type the data.
• Create formulas, using cell references to do calculations on the data.
• Format the text, numbers to make it easier to read.
By John Tiniakos