Excel 2010 Formulas and Functions
calculations on their data. Excel also contains built-in formulas called functions that make it easy to
perform common calculations on data. Here you will find step by step tutorials, tips and shortcuts on
how to use formulas and the common and less common functions available in Excel.
Formula Basics
Formulas in Microsoft Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters constitute a formula. If you don't enter the equal sign, Excel will treat your entry as text and the calculation will fail.
To show how formulas work, we'll begin with a simple exercise by selecting blank cell A1. Then type =5+5, and press Enter. Excel performs the calculation and produces a result of 10 in cell A1.
Notice the formula bar shows the formula you just typed. What appears in the cell is the result; what appears in the formula bar is the underlying value, which is a formula in this case.
Order of Operation
When performing calculations in a formula, Excel follows certain rules of precedence: Excel calculates expressions within parentheses first. Excel calculates multiplication and division before addition and subtraction. Excel calculates consecutive operators with the same level of precedence from left to right.For example, the formula = 10+10*2 gives a result of 30 as Excel multiplies 10 by 2 and then adds 10. However, the formula =(10+10)*2 produces a result of 40. This is because Excel calculates the expression (10+10) within the parentheses first. It then multiplies by 2.
If you are unsure of the order in which Excel calculates, use parentheses - even if the parentheses aren't necessary. Parentheses also make your formulas easier to read.
Conditional Formatting
Conditional Formats respond to the contents of cells. They are
almost always applied to group of cells, often rows or columns of totals, if not entire tables. Click Home
Tab, locate the Style group and click on the downward arrow of Conditional Formatting.
When you click on Highlight Cell Rules this method will keep the cells “inactive” until the values (numeric or text) contain the specific rule you select. You have the option to select: Greater Than, Less Than Between, Equal To, Text that Contains, A Date Occurring, and Duplicate Values. Once you have selected a rule a dialog box will appear where you may specify the appropriate criteria.
Note: Within the dialog box the dropdown arrow will display numerous options of how the information will appear.
In addition to Highlighting Cell Rules there are four other options:
1. Top Bottom: Selected formatting applied to all cells in a range that are greater than or less than a given threshold. Click Top 10 Items, Top 10%, Botton 10 Items, Bottom 10%, Above Average, or Below Average to display a dialog box where you can specify the appropriate criteria.
2. Data Bars: Gradient fills of color within cells whose lengths indicate the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different colors, based on the current theme. Excel will automatically allow you to preview the proposed changes before actually clicking on the changes.
3. Color Scales: Two-color or three-color formats whose color indicates the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different color combinations, based on the current theme.
4. Icon Sets: Sets of three, four, or five tiny graphic images placed inside cells whose shape or color indicates the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different types of icons.