The tutorial provides a list of Excel basic formulas and functions with examples and links to related in-depth tutorials. Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when microsoft office excel 2007 formulas list free download comes to calculating numbers or solving math and engineering problems.
It enables you to total or average a column of numbers in the blink of an eye. Apart from that, you can compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees.
All this is done by entering formulas in cells. This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel. Microsoft office excel 2007 formulas list free download providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page.
So, what do we call an Excel formula and Excel function? Function is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters. You can find all available Excel functions in the Function Library on the Formulas tab:. Of course, it's next to impossible to memorize all of them, and you actually don't need to.
The Function Wizard will help you find the function best suited for a particular task, while the Excel Formula Intellisense will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell:. Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.
What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional. The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:.
In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:. If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria A2:A6in the 2nd argument - the criteria itself D2and in the last argument - the cells to sum B2:B6 :.
In your Excel worksheets, the formulas may look something similar to this:. Its syntax is similar to SUM's:. Sums values in cells B2 through B6, and then divides the result by 5.
And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average! So, instead of dividing sum by count, you can simply put this formula in a cell:. For our sample data set, the formulas will be as simple as:.
If you are curious to know how many cells in a given range contain numeric values numbers or datesdon't waste your time counting them by hand. In both formulas, you use the so-called "whole microsoft office excel 2007 formulas list free download reference" B:B that refers to all the cells within column B.
Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:.
For example, the following IF statement checks if the order is completed i. As the result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":. If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in microsoft office excel 2007 formulas list free download referenced cells You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets microsoft office excel 2007 formulas list free download until something goes wrong!
There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:. For example, to trim extra spaces in column A, enter the following formula in cell A1, and then copy it down the column:. It will eliminate all extra spaces in cells but a single space character between words:. Whenever you want to know the microsoft office excel 2007 formulas list free download of characters in a certain cell, LEN is the function to use:.
Wish to find out how many characters are in cell A2? Just type the below formula into another cell:. Please keep in mind that the Excel LEN function counts absolutely all characters including spaces :. Want to get the total count of characters in a range or cells or count only specific characters? Please check out the following resources. These are the two most popular logical functions to check multiple criteria.
The difference is how they microsoft office excel 2007 formulas list free download this:. For example, to check the test results in columns B and C and return "Pass" if both are greater than 60, "Fail" otherwise, use the following IF formula with an embedded AND statement:. If it's sufficient to have just one test score greater than 60 either test 1 or test 2embed the OR statement:.
For example, to combine the values from cells A2 and B2, just enter the following formula in a different cell:. To separate the combined values with a space, type the space character " " in the arguments list:. To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:. The beauty of these functions is that they don't require any arguments at all, you type the formulas exactly as written above.
Now that you are familiar with the basic Excel formulas, these tips will give you some guidance on how to use them most effectively and avoid common formula errors. Any text included in your Excel formulas should be enclosed in "quotation marks".
However, you should never do that to numbers, unless you want Excel to treat them as text values. For example, to check the value in cell B2 and return 1 for "Passed", 0 otherwise, you put the following formula, say, in C Copy the formula down to other cells and you will have a column of 1's and 0's that can be calculated without a hitch. At first sight, the output is normal - the same column of 1's and 0's.
Upon a closer look, however, you will notice that the resulting values are left-aligned in cells by default, meaning those are numeric strings, not numbers!
Please remember this simple rule: numbers supplied to your Excel formulas should be entered without any formatting like decimal separator or dollar sign. When crating a complex Excel formula with one or more nested functions, you will have to use more than one set of parentheses to define the order of calculations.
In such formulas, be sure to pair the parentheses properly so that there is a closing parenthesis for every opening parenthesis. To make the job easier for you, Excel shades parenthesis pairs in different colors when you enter or edit a formula. Once you have typed a formula into a cell, there is no need to re-type it over and over again. Simply copy the formula to adjacent cells by dragging the fill handle a small square at the lower right-hand corner of the cell.
To copy the formula to the whole column, position the mouse pointer to the fill handle and double-click the plus sign. For the detailed step-by-step instructions, please see How microsoft office excel 2007 formulas list free download copy formulas in Excel. When you remove a formula by pressing the Delete key, a calculated value is also deleted. However, you can delete only the formula and keep the resulting value in the cell.
Here's how:. For the detailed steps with screenshots, please see How to replace formulas with their values in Excel. If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the Calculation Options somehow switched to Manual.
This is how you make and manage basic formulas in Excel. I how you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week. Excel formula basics 10 basic Excel functions you should definitely know Best practices for writing Excel formulas The basics of Excel formulas Before providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page. Formula is an expression that calculates values in a cell or in a range of cells.
The Function Wizard will help you find the function best suited for a particular task, while the Excel Formula Microsoft office excel 2007 formulas list free download will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell: Clicking the function's name will turn it into a blue hyperlink, which will open microsoft office excel 2007 formulas list free download Help topic for that function.
You don't necessarily have to type a function name in all caps, Microsoft Excel will automatically capitalize it once microsoft office excel 2007 formulas list free download finish typing the formula and press the Enter key to complete it. The fastest way to sum a column or row of numbers is to select a cell next to the numbers you want to sum the cell immediately below the last value in the column or to the right of the last number in the rowand click the AutoSum button on the Home tab, in the Formats group.
Excel will insert a SUM formula for you automatically. After copying the formula, make sure that all cell references are correct. Cell references may change depending on whether they are absolute do not change or relative change.
