Excel Formulas – We have always heard that How Excel is one of the most powerful software tool. Excel formulas is just like magic which makes life easier from complex and time consuming calculations of math, statistics, Business etc. It has ability to calculate numerical information using excel formulas. In this article I’m planning to emphasize especially on basic of Excel Formulas. So this article gives you answers of following:
Basics of Microsoft Excel
- How to open Excel Spreadsheet
- What is Excel formula?
- How to use excel formulas in excel?
- How to perform basic calculation in excel
Top 10 Excel formulas useful for any professionals.
Free excel Training Program that make you Excel Master
Once you do know how to use basic formulas in excel, then you can easily find ways to use it in your work. Before digging deep into the Excel lets learn some basics like How to start Excel? So let’s quickly take the review of Excel interface. Most of us may have knowledge of all these stuff. What you need to do when you want to start excel programme? Generally we click on the start button then >> Programme >>MS office >> select MS Excel. Or just simply type “excel” in Run and press Enter then blank Excel spreadsheet will open.
When you open a blank Excel spreadsheet, it is known as a “workbook” The top of the portion is called “ribbon”; it’s again divided into the “tabs” (Highlighted in red)
Now most importantly and our concern is with excel formulas and functions . Alternatively referred to as a formula box, it shows the contents of the current cell and allows you to create and view formulas.
Now look at the following picture, this portion is called worksheet. It consists of “columns” (denoted by letters e.g. A, B) and “rows” (indicated by numbers e.g. 1 ,2). Each location is a “cell” and a group of cells is known as a “range”.
That’s it, pretty simple, and just a short overview of some of the terminology that you’ll encounter in excel.
What is Excel Formula?
Now what do you meant by excel formula? Sounds like big jargon! But don’t worry! Excel formulas makes life simple. If you want to calculate numbers in very dynamic passion then you can use excel formulas. Excel Formulas will calculate numbers or values for you so that you don’t have to spend much time in calculating large numbers manually and risk making mistakes.
“A formula is an instruction given by the user to carry out some activity within spreadsheet, generally its calculation.”
This is the formal definition of excel formula. Our main concern is learn basic excel formulas in excel so let’s look at How to enter a formula in Excel? When you enter a formula in excel then you have to clear about your ideas or what you want to do in excel. Let’s say you are in one cell and you want to enter formula then firstly you have to start by typing the = (equals) sign, then the remaining of your formula. Here excel provides another option for you. You can also start a formula with either a plus (+) sign or minus (-) sign. Excel will assume that you’re typing a formula and after pressing enter; you will get your desired excel formulas result. In case you don’t type the equals sign first, then Excel will assume you are typing either a number or a text.
Following are some steps that you need to follow for entering a formula into the excel
- Go to the cell in which you want to enter a formula.
- Either type equal sign (=) or (+) or (-) sign to tell Excel that you’re entering a formula.
- Type remaining formula in the cell.
- Lastly Press Enter to confirm the formula.
Take a simple excel formulas with example, if you want to calculate sum of two numbers i.e. 5 & 6 in cell A2 then how do you calculate. Let’s follow the above steps:
- Go to the “A2” cell
- Type “=”sign in cell A2
- Then enter formula, Here you have do sum of 5 & 6 so formula will be “= 5 + 6”
- Once you complete your formula then Press “Enter”. You will get your desired result i.e. 11
Now many times instead of getting your desired excel formulas result, you will encounter with an error in excel. Following are some excel formulas errors faced by many excel users.
Errors in excel formulas
How many times you are encountered with excel errors? It’s a frustrating experience when excel gives an error. Mostly there are two kinds of excel formulas error occurs in excel first is #VALUE And #NAME. This will happen if the formula you’ve typed is invalid.
This means that you have entered a formula that was value, but Excel could not calculate a valid result from your formula.
Look at following picture carefully;there are two columns consist of Department and number of students.Now #VALUE error occurs when you are trying to calculate excel formulas for addition of cells which contain non numerical value.If you trying to Add value Cell A2 and cell B2 then excel gives you #VALUE error. Because Cell A2 contains non numerical value.
Reasons for #VALUE Error
- Adding cells that contain a non-numerical value.
- Error with formula
- Non-numerical value
This excel formulas error is commonly encountered when the formula isn’t formatted correctly or contains other errors as seen in the example below
How to Correct #VALUE Error
Instead of using arithmetic operators, use a function, such as SUM, PRODUCT, or QUOTIENT to perform an arithmetic operation.Ensure that adding cell does not contain a non-numerical value.Following picture shows how to correct #VALUE error.In above example we have seen that why #VALUE error occurs.Now We are trying to Add cells including numerical values only.in short we can say that avoid addition,subtraction, division or multiplication of non-numerical values with numerical values.
#NAME error occurs when Excel doesn’t recognize text in a formula. Look at following picture. If we want to calculate excel formulas for sum of number of students then what we do? we simply insert SUM formula then select range and press enter. But if we you enter formula SUM and select range then press enter.Excel gives an #NAME error. because you enter wrong formula.
How to Correct #NAME Error
- Ensure formula is correctly entered
- Make sure quotation marks are added and they are balanced from left and right.
- Replacing a colon (:) in a range reference. For e.g. SUM(A1A10) should be COUNT(A1:A10)
Some Basic Arithmetic Excel Formulas
Arithmetic formulas are by far the most common type of formula. It generally includes mathematical operators like Addition, Subtraction , Multiplication, Division etc to perform calculations. Look at the following table.
The Arithmetic Operators
Top 10 Basic excel Formulas That You Should Know
Don’t waste any more hours in Microsoft Excel doing things manually. Excel formulas decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.
|Name of Formulas||Formula||Description|
|SUM||=SUM ( Number1, Number2, … )||Add all numbers within range resulted into sum of all numbers|
|AVERAGE||=AVERAGE(number1, [number2],…)||Calculate average numbers in a range|
|MAX||=MAX( Number1, Number2, … )||Calculate or find largest number in specific range|
|MIN||=MIN( Number1, Number2, … )||Calculate or find smallest number in specific range|
|TODAY||=TODAY()||Returns the current date|
|UPPER||=UPPER(text)||Converts text to uppercase.|
|LOWER||=LOWER(text)||Converts text into lowercase.|
|Countif||=COUNTIF(range, criteria)||This function counts the number of cells within a range that meet a single criterion that you specify.|
|counta||= COUNTA(value1, [value2], …)||This function counts the number of cells that are not empty in a range.|
|ABS||= ABS(number)||ABS function returns a value of the same type that is passed to it specifying the absolute value of a number.|
Recommended Free Excel Formulas Training:
If you just simply searching on Google for free excel courses you may get long list of excel free training courses. However you might get confused by referring so many excel resources. I would like to suggest you go for EduCBA’s free online training on Excel. Online courses are much better than some of the expensive classroom training courses because they provide time flexibility and cost effectiveness. The excel formulas learning materials are freely available on EduCBA website and the videos includes in the course are short and very easy to understand, planned especially for beginners. Also for your convenience, the spreadsheets explained in the videos are available for download. This way you can immediately practice what you have just seen and learned.
Free Demo Excel Formulas Video:
Here are some articles that will help you to get more detail about the Excel Formulas Useful For Any Professionals so just go through the link.
- Benefits of Microsoft Excel Tips and Tricks (Spreadsheet)
- Popular Things About Excel VBA Macro
- Innovative Ways To Format Tables- Using Excel Table Format
- Best Excel Shortcuts – Selection, data, and formula
- Excel Use formulas with Conditional Formatting (Examples)
- Top 25 Useful Advanced Excel Formulas and Functions
- 8 Awesome & Useful Features of 2016 Excel Workbook