Excel 2010 Tutorial: Using Formulas

exceltutorial2010In most business environments, you are required to use the Microsoft Office suite to perform daily tasks. One of the most important programs you should have at least a basic understanding of is Excel 2010. This powerful spreadsheet program is used to keep track of many business components including inventory levels, basic accounting, and can even generate graphs based on the information within the spreadsheet.

Although Excel does a number of things, you should at least understand how to create simple formulas that allow you to add, subtract, multiply, and divide values within a spreadsheet (also known as a workbook or worksheet). You can learn more about formulas in Mastering Excel.

Simple Formulas

A formula in Excel is an equation that performs calculations on the numbers contained within various cells of a workbook. At the most basic level, Excel is able to execute formulas for addition, subtraction, multiplication and division. It is also capable of much more complex mathematical operations, but this can be confusing when you are just learning how to use Excel.

Excel uses standard operators to create formulas. These include the plus sign (+), minus sign (-), asterisk (*), forward slash (/), and the caret (^). When you are creating formulas in Excel, it’s important to remember that every formula must begin with the equal sign (=). That’s because the numbers contained within the cells affected by the formula have to be equal to the formula and its value.

In its most basic form, a formula uses exact numbers to calculate the result. For instance, a spreadsheet used to calculate the cost of painting a house might be represented like this:

A

B

1

Estimated Painting Cost per Square Foot

2

Total Cost

$75.00

3

Square Feet

250

4

Total/Sq.Ft

=75/250

In this example, the object it is to calculate the total based on the numbers contained in B2 and B3. By putting the formula “=75/250” in cell B4, Excel will automatically calculate the total (in this case $0.30). Although this makes calculations faster than doing it by hand, what happens if the cost of painting per square foot increases or the total number of square feet changes?

Since you created a formula with set numbers, you will have to go into this particular cell and change all the mathematical calculations.

A much more effective method is to use what is known as Cell References. Using cell references allows you to update the data in your worksheet without having to rewrite every value within every formula. For example:

A

B

1

Estimated Painting Cost per Square Foot

2

Total Cost

$75.00

3

Square Feet

250

4

Total/Sq.Ft

=B2+B3

Using this formula instead of the actual numbers means that if the value in either B2 or B3 (or both) is changed, Excel automatically recalculates the total found in B4. Using cell references makes Excel much more versatile as it is very common for data to change as inventory levels increase/decrease, finances go up and down, etc.

Excel 2010 Made Easy explains the concept of Cell References in more detail.

Double Check your Excel Formulas

The one drawback to using formulas in Excel is that a small mistake can lead to big problems if you are working on something business essential. Excel will usually not tell you if your formula is wrong. It could make calculations based on the inaccurate formula and give you the wrong answer as a result. It’s important for you to learn how to double check your Excel formulas.

Fortunately, there are a few things you can do to check your formulas and they are relatively easy to perform.

The first thing you want to do is check your references. Since you will most likely be using cell references in your formulas, you should make sure that the cells you have referenced are the correct ones. When you double-click on a formula, Excel will highlight all of the reference cells in that particular formula. This is an easy way to make sure that you put the right cell numbers into your formula.

You can also switch to the Formula View. This is especially useful when you have a lot of formulas in your spreadsheet. You can access the formula view by pressing Ctrl+`. You can switch back to the normal view by pressing the same key combination again. In formula view, Excel shows you every formula along with cell references you used in that particular spreadsheet and is a quick way to assess the integrity of formulas; especially in large, complex worksheets. Microsoft Excel 2010 Intermediate shows you how to effectively work with complex spreadsheets.

Be careful not to mix up the order of your formulas either. For instance, if you want to subtract B2 from B3, the formula should be =B3-B2, not =B2-B3. Although this may seem like common sense, you would be surprised to find out that many people accidentally mix these cell references up resulting in inaccurate data.

Who said you would never use math outside of high school? Remembering the order of operations (Please Excuse My Dear Aunt Sally or PEMDAS) is also very helpful when you are creating more complex formulas in Excel. The most important part of the order of operations in Excel formulas is the use of parentheses. Parentheses force Excel to make the calculations contained in the parentheses first. If Excel does not automatically calculate your formula correctly, consider adding parentheses around the calculation you need to have happen first.

Excel Essentials covers basic and advanced formula creation.

Finally, you can break up your formulas to make them simpler. For instance, instead of making five calculations in one formula, simply create five separate formulas that reference each other to arrive at the correct answer. Although this may seem like slightly more work up front, it will be much easier to troubleshoot if you have issues with the formulas you’ve created in the future.

Learning to use Excel 2010 is an important skill in most business environments today. If you are familiar with using formulas effectively, it is almost guaranteed that you will have set yourself above many of your colleagues who are unfamiliar with these basic concepts; making you a much more valuable part of your company’s business infrastructure.