Best Excel Formulas – We have always heard that How Excel is one of the most powerful software tools. Excel formulas are just like magic, making life easier from complex and time-consuming calculations of math, statistics, Business, etc. It has the ability to calculate numerical information using Excel formulas. In this article, I’m planning to emphasize especially on basic of ExcelfFormulas. So this article gives you answers to the following:
Best Basic Excel Formulas of MSExcel
- How to open Excel Spreadsheet
- What is an Excel formula?
- How to use Excel formulas in Excel?
- How to perform basic calculation in Excel
Top 10 Best Basic 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 them in your work. Before digging deep into Excel, let’s learn some basics like How to start Excel? So let’s quickly take the review of the Excel interface. Most of us may have knowledge of all this stuff. What do you need to do when you want to start an excel program? 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 a 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 a 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 mean by the Excel formula? It sounds like big jargon! But don’t worry! Excel formulas make life simple. If you want to calculate numbers in a 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 an Excel formula. Our main concern is to learn basic Excel formulas in Excel so let’s look at How to enter a formula in Excel? When you enter an Excel formula, 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 the 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 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 formula with the example; if you want to calculate the 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 the formula, Here you have do sum of 5 & 6, so the formula will be “= 5 + 6”
- Once you complete your formula, then press “Enter”. You will get your desired result, i.e. 11
Many times, instead of getting your desired Excel formulas result, you will encounter an error in Excel. Following are some Excel formulas errors faced by many Excel users.
Errors in excel formulas
How many times are you 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 valued, but Excel could not calculate a valid result from your formula.
Look at the 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 to add cells containing the non-numerical value. If you are trying to Add value Cell A2 and cell B2, then Excel gives you the #VALUE error because Cell A2 contains the 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. The following picture shows how to correct the #VALUE error. In the above example, we have seen why a #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 the following picture. If we want to calculate Excel formulas for the sum of a number of students, then what we do? We simply insert the SUM formula, then select range and press enter. But if you enter formula SUM and select range, then press enter. Excel gives an #NAME error. Because you enter the 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 the sum of all numbers|
|AVERAGE||=AVERAGE(number1, [number2],…)||Calculate average numbers in a range|
|MAX||=MAX( Number1, Number2, … )||Calculate or find the largest number in a specific range|
|MIN||=MIN( Number1, Number2, … )||Calculate or find the smallest number in a 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 Formulas Training:
If you are just simply searching on Google for free Excel courses, you may get a long list of Excel free training courses. However, you might get confused by referring to 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 the EduCBA website, and the videos include 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:-
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
- Excel Use formulas with Conditional Formatting (Examples)
- Top 25 Useful Advanced Excel Formulas and Functions
- 8 Awesome & Useful Features of 2016 Excel Workbook