Microsoft Excel Tutorial
Spreadsheet
A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows. It is an environment that can make number manipulation easy and somewhat painless. The math that goes on behind the scenes on the paper ledger can be overwhelming. If you change the loan amount, you will have to start the math all over again (from scratch). But let's take a closer look at the computer version.
Looking at our previous example it seems pretty evenly matched. The nice thing about using a computer and spreadsheet is that you can experiment with numbers without having to RE-DO all the calculations. Lets change the interest rate and then the number of months. Let the COMPUTER do the calculations! Once we have the formulas setup, we can change the variables that are called from the formula and watch the changes. Do that on paper and you better get your calculator back out and get an Eraser and hope you punched all the right keys and in the right order. Spreadsheets are instantly updated if one of the entries is changed.
Basics of a Spreadsheet
So let's get started digging into what makes a spreadsheet work. Spreadsheets are made up of
In each cell there may be the following types of data
COLUMN
In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location.
ROW
In a spreadsheet the ROW is defined as the horizontal space that is going across the window. Numbers are used to designate each ROW'S location.
CELL
In a spreadsheet the CELL is defined as the space where a specified row and column intersect. Each CELL is assigned a name according to its COLUMN letter and ROW number.
Types of Data
In a spreadsheet there are three basic types of data that can be entered.
Labels in Excel
Labels are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about.
Constants in Excel
Constants are entries that have a specific fixed value. If someone asks you how old you are, you would answer with a specific answer. Sure, other people will have different answers, but it is a fixed value for each person. Sometimes constants are referring to dollars, sometimes referring to percentages, and other times referring to a number of items.
Formulas in Excel
Formulas are entries that have an equation that calculates the value to display. We DO NOT type in the numbers we are looking for; we type in the equation. This equation will be updated upon the change or entry of any data that is referenced in the equation. It is also important to type in the reference to the constants instead of the constants. Formulas are mathematical equations. There is a list of the functions available within Excel under the menu INSERT down to Function.
Basic Formulas in Excel
When we are entering formulas into a spreadsheet we want to make as many references as possible to existing data. If we can reference that information we don't have to type it in again. AND more importantly if that OTHER information changes, we do not have to change the equations.
Basic Math Functions
Spreadsheets have many Math functions built into them. Of the most basic operations are the standard multiply, divide, add and subtract. These operations follow the order of operations.
Methods of Selecting Cells
Selecting cells in an equation is a very important concept of a spreadsheet. We need to know how to reference the data in other parts of the spreadsheet. When entering your selection you may use the keyboard or the mouse.
We can select several cells together if we can specify a starting cell and a stopping cell. This will select ALL the cells within this specified BLOCK of cells.
If the cells that we want to work with are not together (non-contiguous cells) we can use the comma to separate the cells or by holding down the control-key (command key on a mac) and selecting cells or blocks of cells the comma will be inserted automatically to separate these chunks of data.
Sum Function
Probably the most popular function in any spreadsheet is the SUM function. The Sum function takes all of the values in each of the specified cells and totals their values. The syntax is:
In the first and second spots you can enter any of the following (constant, cell, range of cells).
Average Function
There are many functions built into many spreadsheets. One of the first ones that we are going to discuss is the Average function. The average function finds the average of the specified data. (Simplifies adding all of the indicated cells together and dividing by the total number of cells.) The syntax is as follows.
Text fields and blank entries are not included in the calculations of the Average Function.
Max Function
The next function we will discuss is Max (which stand for Maximum). This will return the largest (max) value in the selected range of cells.
Min Function
The next function we will discuss is Min (which stands for minimum). This will return the smallest (Min) value in the selected range of cells.
Count Function
The next function we will discuss is Count. This will return the number of entries (actually counts each cell that contains number data) in the selected range of cells.
CountA Function
The next function we will discuss is CountA. This will return the number of entries (actually counts each cell that contains number data OR text data) in the selected range of cells.
IF Function
The next function we will discuss is IF. The IF function will check the logical condition of a statement and return one value if true and a different value if false. The syntax is
PMT (loan stuff)
The PMT function returns the periodic (in this case monthly) payment for an annuity (in this case a loan). This is the PMT function that was used for the car purchase in the first example. There are a few things that we must know in order for this function to work. To calculate the loan we must know a combination of the following
SIN COS TAN etc.
Excel has most of the math and trig functions built into it. If you need to use the SIN, COS, TAN functions, they can be typed into any cell.
To calculate trig functions in degrees you must convert them - otherwise excel will calculate them in radians.
Function Wizard
In Excel there is a help tool for functions called the Function Wizard.
There are two ways to get the function wizard. If you look at the Standard Toolbar, the function wizard icon looks like the icon on the right.
The other way to get to the function wizard is to go to the Menu INSERT -- down to FUNCTION.
Either way you get there, at this point Excel will list all of the functions available. Upon choosing the function, Excel will prompt you for the information it needs to complete the function. Mini descriptions are available for each of the cells. It is often necessary for you to understand the functions in order to be able to figure out these descriptions.
Copying Formulas
Sometimes when we enter a formula, we need to repeat the same formula for many different cells. In the spreadsheet we can use the copy and paste command. The cell locations in the formula are pasted relative to the position we Copy them from.
Fill Down
Often we have several cells that need the same formula (in relationship) to the location it is to be typed into. There is a short cut that is called Fill Down. There are a number of ways to perform this operation. One of the ways is to
Absolute Positioning
Sometimes it is necessary to keep a certain position that is not relative to the new cell location. This is possible by inserting a $ before the Column letter or a $ before the Row number (or both). This is called Absolute Positioning.
Fill Right
We can also fill right. We must select the original cell (and the cells to the right) and select from the Edit menu -- Fill and Right.
Formatting Text
Spreadsheets can be pretty dry, so we need some tools to dress them up a little. We can use most of the tricks in our word processor to do the formatting of text. We can use : bold face, italics, underline, change the color, align (left, right, center), font size, font, etc.
Formatting Numbers
We often need to format the numbers to
display the appropriate number of decimals, dollar signs, percentage, red (for
negative dollars), etc. It is best to keep numbers describing similar items as
uniform as possible.
Column Width
In many spreadsheets you can also change the vertical height of a row by moving the lower edge of the row title (number).
Inserting A Column
Sometimes we (all) make mistakes or things change. If you have a spreadsheet designed and you forgot to include some important information, you can insert a column into an existing spreadsheet. What you must do is click on the column label (letter) and choose in Columns from the Insert menu. This will insert a column immediately left of the selected column.
Inserting A Row
Likewise, we can also insert rows. With the row label (number) selected you must choose the Row from the Insert menu.
Charts or Graphing
Numbers can usually be represented quicker and to a larger audience in a picture format. Excel has a chart program built into its main program. The Chart Wizard will step you through questions that will (basically) draw the chart from the data that you have selected. There are many types of charts. The two most widely used are the bar chart and the pie chart.
The BAR Chart is usually used to display
a change (growth or decline) over a time period. You can quickly compare the
numbers of two different bar charts to each other.
The PIE Chart is usually used to look at what makes up a whole Something.
If you had a pie chart of where you spent your money you could look at the
percentages of dollars spent on food (or any other category).