Microsoft Excel is a member of the spreadsheet family of software. Spreadsheet software is used to store information in columns and rows which can then be organized and/or processed. Spreadsheets are designed to work well with numbers but often include text. Sometimes text in a spreadsheet is called a label, because it is labeling columns and rows of numbers. Numbers are called values sometimes, and can include numbers for counts or measurements, dates, times, and calculations from numbers. Spreadsheets can help organize information, like alphabetizing a list of names or other text or reordering records according to a numeric field. However, spreadsheets are more often used for calculating, such as totaling a column of numbers or generating a more sophisticated formula to calculate some statistical measure on a list of numbers.
Spreadsheets and databases are in competition and have similar features. Yet the way they work in the background is different. When you work in a spreadsheet, you view the data you are entering as a section. In a database, you only see the data you are entering - you have to request a report or different display to see more of the information. Other differences are:
(1) databases are more often used for applications with long textual entries,
(2) very large applications (thousands of entries) are more often handled in databases; and
(3) spreadsheets are easier to learn to use and get calculations from than a database program.
List of applications that can be done in spreadsheets are for example: budgeting displays, checkbook registers, enrollment records, inventories, coded surveys, field and laboratory research data, and financial and accounting applications.
The capacities of Excel are as follows: you can have 256 columns of information. You can have up to 16,384 rows. That comes out to over 4,194,000,000 cells of information and that's only on the first sheet! You can have 16 sheets of information in one workbook, and the number of sheets can be increased, if needed. Excel refers to each file as a workbook, because there can be multiple sheets (pages) in one file.
Tools and Features of Excel's
Application Window
The application window area contains five rows or lines of command menus which can be used to act upon the information found in the document window. These command menus display command words and graphic icons. These menus include the title bar (at the very top) followed by the menu bar, the Standard toolbar, the Formatting toolbar, the Formula bar, and the Status bar. The function of each of these is as follows:
Title Bar
Displays the name of the application "Microsoft Excel." The control menu box is on the far left and the minimize/maximize or restore buttons on the far right. These are standard Windows features, and you can consult the Windows manual for additional information.
Menu Bar
Displays the main level commands. These commands can be executed with a click of the left mouse button; then additional command options are presented in pull-down menus. The commands FILE, EDIT, VIEW, WINDOW and HELP are common menu options found in other Windows applications.
Toolbars
There are two toolbars which normally display--the Standard toolbar and the Formatting toolbar. The toolbars have graphic icon buttons which can be mouse clicked to perform an action or bring up another set of menus. The actions performed by the toolbar buttons are also found in the menu bar/pull-down menus. Buttons are popular with Excel users because they give a quick way to perform an action but it is not necessary to use them. Because the buttons duplicate menu items, there are often several ways to "perform an action."
Examples of tool bars:
New Workbook (results in a blank document)
Open (workbook/file)
Save (workbook/file)
Print
AutoSum (to add up numbers in a row or column)
Undo (un-does the last editing action)
Formula/Address/ Bar
This is the last line before the document window. It displays information entered or about to be entered in a cell and gives the address location of that cell. Cell editing can also be done on this formula bar. The active cell position is indicated on the far left end of the Formula bar ... more about this later.
Status Bar
Displays information about the current state of the program. This feature is located at the very bottom of the document window. The user is provided information about what Excel is doing. Examples are Ready when Excel will accept new entries or Edit if you are editing a cell.
Document/Workbook Window
This is the window where the spreadsheet information will appear. If this window is not maximized, the top of this window also contains a document title bar which will display the filename. Below the title bar is a grid matrix area where information will be displayed or entered. There is a vertical scroll bar on the right margin, a horizontal scroll bar at the bottom right, and sheet tab controls at the bottom left. Each of these items is listed and described below.
Title Bar
This will only appear if the document window is not maximized. It displays the workbook (file) name. The name on a blank application would be Book1, Book2, etc., depending on how many windows are open. You can click on this title bar and drag it to move the window. The control menu box is at the left end and the minimize and maximize buttons are on the right end.
Workbook Page
Contains the grid matrix of cells where the spreadsheet information will be entered or found. The column headings are referenced by alphabetic characters and the rows are referenced by numbers found on the left. The area where the column letters and row numbers are found is called the frame.
Scroll Bars
The scroll bars are used to view different parts of the spreadsheet. You use the vertical scroll bar at the right margin to move vertically or the horizontal scroll bar at the bottom right to move horizontally.
Sheet Tabs and Scrolling Buttons
Tabs appear at the bottom of the document window with names of Sheet1, Sheet2, Sheet3, etc. You can go to another sheet by clicking on a different tab. Also, the scrolling buttons appear to the left of the tabs; they allow to scroll more quickly through the sheets.
Basic Terms and Definitions
These are additional terms you need to know:
Cell
This is the basic unit of the spreadsheet. It is a location that can contain information and is most often defined by its column and row address. For example C6 represents a cell in the third column (column C) and sixth row. The naming convention for a cell reference is the alphabetic column letter position followed by the row number. C6 is correct but 6C is incorrect. You may use either lower or upper case letters when referencing a column.
Active Cell or Selected Cell
The cell which has the dark border around it is the active cell. This is the cell that can be acted upon and indicates where the insertion point is located. You can select a new active cell by using the keyboard's arrow keys or clicking on a new cell with the mouse. You can also use keys like [PageDown] and [PageUp] and [Home] to change the active cell location.
A group of adjacent cells forming a rectangle is called a block. It is defined by the addresses of the two cells that are in the opposite corners of the rectangle block area, from the top left cell in the block to the bottom right cell in the block. A block of cells can be marked by using the mouse or by holding down the shift key and using the arrow keys. Once a block is defined, you can do many things with that block such as move it, copy it, delete it, or alter the display of its contents with formatting options.
Workbook
This is Excel's name for a file. The workbook can have multiple sheets with different information on each sheet. This permits you to keep related data in one file rather than break it up into several different files.
Types of Data
In a spreadsheet there are three basic types of data that can be entered.
- labels - (text with no numerical value)
- constants - (just a number -- constant value)
- formulas* - (a mathematical equation used to calculate)
data typesexamplesdescriptions
LABELName or Wage or Daysanything that is just text
CONSTANT5 or 3.75 or -7.4any number
FORMULA=5+3 or = 8*5+3math equation
*ALL formulas MUST begin with an equal sign (=).
Labels /text/ are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about.
In this example the labels are:
- computer ledger
- car loan
- interest
- # of payments
- Monthly Pmt.
Again, we use labels to help identify what we are talking about. The labels are NOT for the computer but rather for US so we can clarify what we are doing.
Constants in Excel
As you can see from these examples there may be different types of numbers. Sometimes constants are referring to dollars, sometimes referring to percentages, and other times referring to a number of items (in this case 60 months).These are typed into the computer with just the numbers and are changed to display their type by formatting them.
Constant Values
Data that you type directly into a cell is called a constant value by Excel. These can include text, whole numbers, decimal numbers, dates, times, currency, percentages, and scientific notation.
In this same example the constants are:
Formulas
A formula is a sequence of values, cell references, names, functions, or operators that produces a new value from existing values. A formula will display on the screen as a number but the formula bar will show what is really stored in that cell. As numbers affecting the formula change, the formula value will change.
