Excel 2007 Formulas

In Excel 2007 formulas are instructions for Excel to carry-out an action, generally a calculation. Formulas can perform calculations, format cells and contents, and more. Formulas always start with =.

Functions are built-in, predefined formulas.

Formulas can be entered directly in the cell or in the Formula Bar.

Common Excel 2007 Formulas

Formula Description
SUM Adds all of the numbers in a range of cells.
AVERAGE Calculates the average for a range of cells.
TODAY Inserts today's date.

Inserting Formulas

To insert a formula in Excel 2007,

  1. Click the in the cell for the formula. In the example below, this is cell B6.
  2. Type, =SUM(.
  3. Click in cell B2. (Alternately, type B2:)
  4. Click in cell B5. (Alternately, type B5)
  5. Type ). The formula now looks like this:

    Excel 2007 Fomulas Sum

  6. Press Enter. The sum of the formula is displayed.

Arithmetic

Arithmetic operators are used to perform basic mathematical calculations.


The arithmetic operators are:

 

Description
* Multiplication
/ Division
+ Addition
- Subtraction
% Percent
^ Exponent

Addition is a mathematical operator. Let's suppose I want to add the contents of cells A9 and A10 in our worksheet.

To do this,

  1. Click in the cell for the formula. In the example below, this is cell A11.
  2. Type, =.
  3. Click in cell A9. (Alternately, type A9.)
  4. Type +.
  5. Click in cell A10. (Alternately, type A10.) The formula now looks like this:

    Excel 2007 Formulas Aritmetic

  6. Press Enter. The results of the formula display.
    Working with Formulas Total

Comparison

Comparison operators are used to perform comparisons between two values. For example, comparison operators can be used to see if the contents of one cell are greater than the contents of another. The comparison will return TRUE or FALSE as the result.

The comparison operators are:

Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

Let's suppose I wanted to see if the amount in cell A1 was greater than the amount in cell A2. I can use a comparison operator to do this.

To use a comparison operator,

  1. Click in the cell for the formula. In the example below, this is cell B2.
  2. Type =.
  3. Click in cell A1. (Alternately, type A1.)
  4. Type >.
  5. Click in cell A2. (Alternately, type A2.) The formula looks like this:

    Excel 2007 Comparision Formula
  6. Press Enter. The results of the formula display as TRUE because cell A1 is greater than cell A2.

    Excel 2007 Formula Comparison

Text Concatenation

The ampersand & is used to join to strings of text into a single string of text.

For example, let's suppose you have the word sea in one cell and shore in another.

Excel 2007 Concatenation

I can combine the text in both cells using concatenation to produce the word seashore.

  1. To concatenate cells A3 and B3 into seashore, type =A3&B3 into cell C3.

    Exel 2007 Text Concatenation

  2. Press Enter. Cells A3 and B3 are concatenated in cell C3.


    Concatenated cells Excel 2007

Reference

Reference operators are used to combine cell ranges in calculations.

The reference operators are:

: (colon)

: is used to include all of the cells between two cell addresses. It represents THROUGH or BETWEEN. For example, A1:A4 refers to cells A1 through A4, or cells A1, A2, A3 and A4.

Excel 2007 Forumulas Reference

, (comma)

, is used to combine multiple references into one reference. It represents AND. For example, A1:A4,B1:B4 refers to cells A1:A4 and B1:B4.A1,A2,A3,A4 and B1,B2,B3,4

workingwithformulas_10

(space)

(space) is used as the intersection operator. It represents BOTH or COMBINED. It allows you to evaluate cells that are common to two references or ranges.

NOTE: Named Ranges should be used when working with the Intersection Operator. For more information, see the Using Names to Work with Ranges section.

For example, let's suppose we have a worksheet displaying widget sales for our four regions over the last four quarters:

workingwithformulas_11

We want to find the total Quarter 3 widget sales for the South and East regions combined.

To find the total Quarter 3 widget sales for the South and East regions combined,

  1. Setup Named Ranges for each region and quarter (e.g., "North" cells B2:E2; "Quarter1", cells B2:B5).
  2. Type =SUM(South:East Quarter3).

    workingwithformulas_12


    Notice the ranges highlighted by Excel and the space between East and Quarter3.



  3. Press Enter. The results display.
    workingwithformulas_13

Order of Operations

Multiple operators are often used in a single formula. Because of this, Excel uses a ranked system to determine the order in which operators are used. Excel's order of operations is:

Reference Operators

  1. :
  2. ,
  3. (space)

Arithmetic Operators

  1. - (Negative; not subtraction)
  2. %
  3. ^
  4. * and /
  5. + and -

Concatenation Operator

  1. &

Comparison Operators

  1. =
  2. >
  3. <
  4. >=
  5. <=
  6. <>

Relative and Absolute Referencing

Relative Referencing

By default cell references are relative. This means that when a formula is copied and pasted into another cell (or range of cells) the cell references in the formula will update to reflect the formula's new location. For example, I want to find the sum of cells A1 and A2. I type this formula:

workingwithformulas_14

The result is 40.

If I copy and paste the formula into cell C2, Excel gives me the correct answer of 42 for cells A2 +
B2
.


workingwithformulas_15

Notice that the Formula Bar displays =SUM(A2+B2) rather than the copied =SUM(A1+B1). Excel has automatically updated the formula for me. How does this work? Rather than simply copying and pasting the cell references, Excel notices that in my original formula I was referring to cell A1 which is 2 cells to the left and cell A2 which is 1 cell to the left. Taking this into account, Excel updates my formula in cell C2 so that it is referring to cells that are 2 cells to the left and 1 cell to the left, or A2 and B2.

Absolute Referencing

Absolute cell references always refer to the same cell. Cell references are not updated in formulas when copied and pasted to new locations. If the position of a formula changes, the absolute cell reference does not change. To signify an absolute reference, place a $ before the column and row references. For example, $A$1 is an absolute reference to cell A1.

 

How to Link to This Page

It will appear on your page as: Excel 2007 Formulas

 


Search for Tutorials
 

Excel Tutorials: Excel 2007

Excel 2007 Tutorials
Excel 2007 Charts
Excel 2007 Graphics
Excel 2007 Worksheets
Excel 2007 Worksheet Views
Excel 2007 Formulas
Excel 2007 Named Ranges
Excel 2007 Cell Comments

Excel Video Tutorials
Lesson 1: Excel Basics
Lesson 2: Editing a Worksheet
Lesson 3: Making Bar and Circle Graphs
Lesson 4: Making a Gantt Chart
Lesson 5: Excel Keyboard Shortcuts

More Excel Tutorials at GuidesandTutorials.net

All Other Guides and Tutorials

Guides and Tutorials Blog
Listing of the latest tutorials published on the GuidesandTutorials website.
GuidesandTutorials: Audacity Tutorial - Record and Edit Audio
This Audacity tutorial will show you how to use the basic tools and procedures in the open source audio editing program, Audacity. Not just screen shots, but free narrated multimedia tutorials - You'll learn to use Audacity for recording and editing audio files that can be used for podcasting, mixing music and vocals, creating sound tracks, and other audio projects.
Audacity Tips & Tricks
Audacity tips and tricks submitted by users
Digital Photography Guide
This Digital Photography Guide will provide you with the basic information you need to take professional quality photogrphs with your digital camera.
GuidesandTutorials: Dreamweaver Tutorial
Dreamweaver Tutorial - this series of free Dreamweaver tutorials is just what you need to get up and running with Dreamweaver to create interesting and compelling web sites
GuidesandTutorials: Microsoft Excel 2003 Tutorial
This Excel 2003 Tutorial will introduce you to the basics of using the Excel 2003 spreadsheet program. You'll learn to create a spreadsheet, basic Excel workbook skills, work with cells, enter and manipulate data, apply formulas, format data, create and work with charts, and manage Excel workbooks.
Podcasting Tutorial
This Podcasting tutorial includes a step by step guide that will show you how to record, publish and promote your podcast. Using inexpensive equipment and free software, you'll be publishing podcasts in no time.
PowerPoint Tutorial - Microsoft PowerPoint 2003
This PowerPoint tutorial is just what you need to learn how to use PowerPoint 2003 to create effective and engaging presentations.
The PowerPoint 2007 Window
This PowerPoint 2007 tutorial will show you the features and how to get around the PowerPoint Window.
GuidesandTutorials: Free PowerPoint Templates
Free PowerPoint templates that you can download and use today. This is a growing library of free resources for PowerPoint users.
LCD Projector Guide and Tutorial
This LCD Projector Guide will assist you in the use and purchase of a digital LCD projector or DLP projector. It will discuss the differences between LCD and DLP projectors, size and portability considerations, lamp types and brightness, resolution, image quality and other considerations in the use and purchase of a digital projector.
Word Tutorial: Microsoft Word 2003
This Word tutorial is just what you need to learn how to use Word 2003 to create professional looking documents.
Word 2007 Workspace
This Microsoft Word 2007 Workspace tutorial will acquaint you with the new tools and features in the Word 2007 Window. It's changed significantly from previus versions and even experienced users will find this helpful.
Work at Home Jobs
This Work at Home Jobs tutorial will provide information on ways that you can put your skills and knowledge to use in creating part or full time income while working from home.
Guides and Tutorials: Free Guides and Tutoials Site Map
Free guides and tutorials on podcasting, digital video and audio, Audacity, RSS, multimedia development, educational technology, technology integration, K12 Technology, PowerPoint.
Suggest a New Technology Tutorial
Free Technology Tutorials on software and technology applications including PowerPoint tutorials, Podcasting tutorials, Dreamweaver tutorials, Audacity, Non-Linear PowerPoint, Digital Photography, RSS tutorial, and everything educational technology.
Adobe Acrobat 9 Tutorial
This Adobe Acrobat 9 Tutorial will show you how to create PDFs from MS Office applications, from Multipe files, from web pages and from several other types of documents.
GuidesandTutorials: Free Tutorials
Free Tutorials, software, hardware, and technology applications including PowerPoint, Podcasting, Dreamweaver, Audacity,
Excel 2007 Tutorial | GuidesandTutorials: Free Tutorials
This free Excel 2007 Tutorial will show you how to use all of the begginer and intermediate features in Excel 2007

This site proudly build with Site Build It!

Powered By SiteBuildIt

 

 

Excel 2007 Ebook: The Ultimate Guide - $12.95

Excel 2007 Ebook

Get the Excel 2007 Ebook: The Ultimate Guide and receive all of the Excel 2007 tutorials in one handy reference guide. Purchase online easily with your credit card or PayPal and have the Ebook emailed to your email address (pdf format)

Price: $12.95. California residents pay 7.25% Sales tax. Delivered via email.

Excel Video Tutorials
1. Excel Basics
2. Editing a Worksheet
3. Making a Gantt Chart
5. Excel Keyboard Shortcuts

Excel 2007 Tutorials
Excel 2007 Charts
Excel 2007 Graphics
Excel 2007 Worksheets
Excel 2007 Worksheet Views
Excel 2007 Formulas
Excel 2007 Named Ranges
Excel 2007 Cell Comments

More Excel Tutorials at
GuidesandTutorials.net

[?] Subscribe To This Site

XML RSS
follow us in feedly
Add to My Yahoo!
Add to My MSN
Subscribe with Bloglines