Excel Basics

This is a basic tutorial of Excel. START HERE Specific examples may refer to Excel (but most items discussed should work in other spreadsheets).

 

IT is recommended that you have EXCEL running at the same time. You can try what you are reading. On an PC you can toggle back and forth between the Tutorial and Excel using the ALT-TAB. If you are using this on a Mac, switch back and forth with the finder. Using either machine, if you can tile the windows just a little you can switch back and forth by clicking on the window of the program you want to become active.

 

 

• Welcome
• What is a spreadsheet
• Why on a computer?
• Basics of a spreadsheet
  • columns
  • rows
  • cells
• Types of Data
  • Labels
  • Constants
  • Formulas
    • Basics
    • change in formulas
    • Basic Math
    • Selecting cells
• Specific Formulas (or FUNCTIONS)
  • SUM
  • AVERAGE
  • MAX
  • MIN
  • COUNT
  • COUNTA
  • IF
  • PMT
  • SIN COS TAN etc.
  • Function Wizard
• copying formulas
  • fill down
  • absolute positioning
  • fill right
• Formatting
  • formatting text
  • formatting numbers
  • column width
• inserting a column
• inserting a row
• charts or graphing
• get ready

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.

2813

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)

 

*ALL formulas MUST begin with an equal sign (=).

     

Labels are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about.

In our first example: the labels were

• 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 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.

In our first example: the constants were

• $12,000
• 9.6%
• 60

 

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 of number by formatting (we will talk about this later).
Again, we use constants to enter FIXED number data.

   

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.

In our first example, the solution was $252.61
This was NOT typed into the keyboard. The formula that was typed into the spreadsheet was:
=PMT(C4/12,C5,-C3)

C4 (annual interest rate) was divided by 12 because there are 12 months in a year. Dividing by 12 will give us the interest rate for the payment period - in this case a payment period of one month.

It is also important to type in the reference to the constants instead of the constants. Had I entered =PMT(.096,60,-12000) my formula would only work for that particular set of data. I could change the months above and the payment would not change. Remember to enter the cell where the data is stored and NOT the data itself.

Formulas are mathematical equations. There is a list of the functions available within Excel under the menu INSERT down to Function.

Formulas OR Functions MUST BEGIN with an equal sign (=).

Again, we use formulas to CALCULATE a value to be displayed.

 

hen 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.

If you work for 23 hours and make $5.36 an hour, how much do you make? We can set up this situation using

• three labels
• two constants
• one equation

Let's look at this equation in B4:

• = B1 * B2
• = 23 * 5.36

Both of these equations will produce the same answers, but one is much more useful than the other.
DO YOU KNOW which is BEST and WHY?

It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

   

In our last example, things were pretty straightforward. We had number of hours worked multiplied by wage per hour and we got our total pay. Once you have a working spreadsheet you can save your work and use it at a later time. If we referenced the actual cells (instead of typing the data into the equation) we could update the entire spreadsheet by just typing in the NEW Hours worked. And -- you're done!

 

Let's look at the new spreadsheet:

• hours have been changed to 34
• wage is the same
• total pay would now be = 34 * 5.36
• but would still be = B1 * B2

If we had typed in ( = 23 * 5.36 ) the first time and just changed the hours worked, our equation in B4 would still be ( = 23 * 5.36 )

INSTEAD we typed in references to the data that we wanted to use in the equation.
We typed in ( = B1 * B2 ). These are the locations of the data that we want to use in our equation.

It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

   

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 (just like algebra). Let's look at some examples.

For these following examples lets consider the following data:

A1 (column A, row 1) = 5 A2 (column A, row 2) = 7 A3 (column A, row 3) = 8 B1 (column B, row 1) = 3 B2 (column B, row 2) = 4 B3 (column B, row 3) = 6

A B 1 5 3 2 7 4 3 8 6

       

Selecting cells 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.

For this following examples lets consider the following data:

A1 (column A, row 1) = 5 A2 (column A, row 2) = 7 A3 (column A, row 3) = 8 B1 (column B, row 1) = 3 B2 (column B, row 2) = 4 B3 (column B, row 3) = 6

A B 1 5 3 2 7 4 3 8 6

This is just a discussion of selection methods. If we wanted to add the cells in the (To Select) you would type in

=sum(Type In)
or
=sum(Click On)

                 

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:

• =SUM(first value, second value, etc)

In the first and second spots you can enter any of the following (constant, cell, range of cells).

• Blank cells will return a value of zero to be added to the total.
• Text cells can not be added to a number and will produce an error.

       

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.

• =Average (first value, second value, etc.)

Text fields and blank entries are not included in the calculations of the Average 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.

• Blank entries are not included in the calculations of the Max Function.
• Text entries are not included in the calculations of the Max 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.

• Blank entries are not included in the calculations of the Min Function.
• Text entries are not included in the calculations of the Min 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.

• Blank entries are not counted.
• Text entries are NOT counted.

 

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.

• Blank entries are not Counted.
• Text entries ARE Counted.

Write comment

Your Contact Details:

Name:

Email: do not notifynotify

Website:

Comment:

Title:

UBBCode:         -color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge

Message:

Security

Please input the anti-spam code that you can read in the image.