CPS100 • Introduction to Computers


Lakeland College • Japan Campus

Excel Basics

Spreadsheets

A spreadsheet, or worksheet, is just like a piece of paper you use to write down numbers and make calculations in business. It has columns and rows, and you add, subtract, or do other mathematical equations along those columns and rows.

Before electronic spreadsheets were invented, people had to do the calculations by hand. On any spreadsheet, many different calculations are made, and many are linked together. That means that if one calculation is wrong, it may affect many other calculations. Sometimes the spreadsheets took up many books, and if a single calculation was wrong, it could take hours or days to correct all the errors that resulted from the initial error.

With electronic spreadsheets, the computer does the math automatically. If you find an error, then you can correct it--and the computer instantly re-calculates the entire spreadsheet. This is called automatic recalculation, and it is the most valuable feature of electronic spreadsheets.

The first electronic spreadsheet to become popular was VisiCalc. This program helped personal computers become popular. Many people bought a computer just for this program.

Since then, just about every business that can afford a computer uses spreadsheet software.

 

Terms

First, you should get to know the cell. A cell is a single box. It may contain a label (text), a value (number, date, time) or formula (calculation).

Cells are arranged in columns and rows. A column is a vertical strip of cells (up and down), and a row is horizontal (right and left). Columns are marked by letters of the alphabet and rows are marked by numbers. A cell is named according to the column letter and row number; for example, a cell in column "B" and row "5" is called "B5." This is called the cell's address.

This is a column
This is a row
The highlighted box is a cell
with the address "B3"

Previous versions of Excel could have up to 256 (28) columns and 65,536 (216) rows in one worksheet, for a total of 16,777,216 (224) cells. Since the 2007 version, Excel can have 1,048,576 (220) rows and 16,384 columns (214) for a total of more than 17 billion cells per worksheet.

To move from cell to cell, you can use:

  • the arrow keys
  • the tab key (to go to the right)
  • shift+tab (to go to the left)
  • the enter key (to go down)
  • shift+enter (to go up)

You can control the width or columns and the height of rows by clicking and dragging the lines between the "headers" (the letters and numbers at the beginning of each column and row).

If you want to change many columns or rows so that they are all the exact same size, then click-and-drag-select the headers of several columns or rows, then change the size of just one of them:

 

Worksheets

There can be more than one worksheet in a spreadsheet file; when you open an MS Excel spreadsheet, there are three sheets prepared automatically. The sheets are shown at the bottom left by small tabs; click on a tab to go to that sheet.

You can add more worksheets. Just clik on the little button to the right of the last tab, and a new worksheet will be added.

For more options, just right-click on any of the tabs at the bottom, to get this menu.

You can also duplicate an existing worksheet by holding down the control and alt keys, then clicking and dragging the worksheet's tab to a new spot.

 

Address & Range

A cell is identified by its cell address, which is a combination of the column and row names. For example, if a cell is in COLUMN "B" and also is in ROW "3", then the address of the cell is "B3".

It is also possible to select more than one cell at a time, by clicking-and-dragging. Let's say you start at B3, and finish at B10, so all the cells in between are selected. This is called a range. A range address is written by typing the address of the first cell and the address of the last cell, separated by a colon. For example, B3:B10 .

A range can be more than one row or column. For example, you can have B3:C10, or B3:H5. Look at the range below. How would you type it?

Can you guess? To find the answer, select the space on this line between the stars: **  B2:F6  **

 

Data

A cell can have many different kinds of things inside of it. This is usually called data. You can put plain text, for example: people's names, names of objects, categories, etc.--or perhaps you can just enter plain text, as you would in a word processor. Any text is OK. This kind of data is called a label.

A cell can also hold numerical data. This would include numbers, currency (money amounts), percentages, fractions, or other kinds of numbers.

A cell can also hold times and dates, or other special values. Each of these is called category of data. A quick list of data types:

  • General (could be anything; all Excel cells begin this way)
  • Number
  • Text
  • Currency (usually counted in the currency the computer is set to; in the case of the U.S., it is dollars)
  • Date (there are long and short forms)
  • Time
  • Percentage
  • Fraction (e.g., 1/2 or 2/3)
  • Scientific Notation (e.g., 531 = 5.31E+02)

Excel sees what you type into a cell. If you type a number, it will recognize it as a number. If you type a date, Excel will see that it is a date.

Note that when you type text or numbers into cells, they do not change the category of the cell. You could write text, then achange that to a number, then change it back to text.

However, most other types of data entered will cause Excel to change the cell to a new category. For example, if you type "11/25" into a cell, Excel sees that as a date (November 25 of the current year), and so changes that cell to a date cell. If you try to enter a number value, Excel will translate that into a date. (It will use Jan. 1, 1900 as a starting point and count upwards, so that "3" in a date cell will read as January 3, 1900. 41,238 is November 25, 2012.)

If you accidentally change a cell to a date and want to change it back to a number, you must select the cell, and then, in the Home Tab, in the "Number" group, click on the menu and change the cell back to "General" (or whatever category you desire).


Terms to Know

spreadsheetalso worksheet; a page on which you enter data in columns and rows
automatic recalculationthe ability of an application to instantly change all of the connected calculations when a new value is entered
cellone "box" in a spreadsheet
rowa horizontal range of cells, to the left and the right; rows are named with numbers
columna vertical range of cells, up and down; columns are named with letters
rangea rectangular group of cells, possibly spanning over multiple rows and/or columns
addressthe name of a cell or range based upon the column and row names
cell addressthe name of a cell based on the column letter and the row number; e.g., C12
range addressthe name of a range based on the cell addresses for the top-left and bottom-right cells in the range, separated by a colon; e.g., B5:E14

Unit Main Page Next Chapter