Excel: Learning to Use Formulas

The first step towards being an Excel Power User is learning to use formulas. Pulling out a calculator to add up Excel columns is a sure fire way to let everyone in the office know your computer skills are lacking. Luckily for you, basic Excel formulas are pretty easy learn.

Lesson 1: Basic Arithmetic Formulas

Video Link: Excel Formulas Part 1

Excel supports all standard Arithmetic functions – addition, subtraction, multiplication, division, etc. **Excel comes standard with advanced engineering and statistical functions, but that is well beyond the scope of this article.

To start a formula in Excel: select the cell where the result belongs and type =. Using = as the first character tells Excel you want to create a formula.

In this example, we want to add the first row of column A and B and place the answer in C. Select cell C2, enter =A2+B2. Press enter and 7 will appear in the space. Try changing the values in cells A2 and B2. Notice the value in C2 changes in kind.

formulas1

Notice how as you type in a formula, it populates in the formula bar above as well. You can work in the formula bar instead if you chose (as your formulas get longer and more complex, you will find yourself doing more work in the formula bar).

You can try out some other arithmetic functions following the example below: **note: make sure to start your formulas with =

formula2

Lesson 2: Replicating Formulas

Video Link: Excel Formulas Part 2

If you had to input each formula individually every time you wanted to use one, this would not be any more efficient then simply doing the calculations on your old desktop calculator. Fortunately, Excel makes replicating your formulas across many cells as simple as clicking a mouse button.

In the bottom right corner of an active cell, there is a small green box. You can click on this box and drag it over the cells you would like the formula to replicate. Or, you can just double click corner and it will automatically replicate the remaining rows in your table.

formula3

Lesson 3: Functions

Video Link: Excel Part 3

Excel has hundreds of built in functions (far too many to cover in this article). These functions save you having to write complex formulas manually.

Below are examples of 3 commonly used functions (SUM, PRODUCT, AVERAGE)formula4

Let us break these functions down:

  1. SUM() — adds the values of all cells listed in the parenthesis.  **In the example above, the SUM function has C2:C5 in the parenthesis. The colon(:) means between. So C2:C5 actually means all the values C2 through and including C5 (C2,C3,C4,C5)
  2. PRODUCT() — multiplies the values of all cells listed in the parenthesis.
  3. AVERAGE() — takes the average of the values of all cells listed in the parenthesis.

If you are curious as to what other built in functions Excel offers, go to the Formulas tab in the ribbon cable. 

formula6

 

 

 

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s