MS Excel is a tool used in every workplace and is the best way to manage and work with data. It is a widely popular tool used to generate reports and business insights, and the fact that it supports several in-built applications makes it all the more effective.
Knowing how to use Excel well means you’ll stand out at work, which means hello promotions and appraisals! Even though you may fear the spreadsheet, all it takes is some effort to understand features that’ll make navigating it all the easier.
One such feature that makes Excel super-resourceful are the formulas. These formulas and functions allow you to perform calculations and data analysis faster and more efficiently.
The Excel program by Kool Kanya PowerPass takes you through using basic and advanced formulas. And to give you a taste, let's look into the top 10 Excel formulas that one must know while working on Excel.
Table of contents
- What are Excel formulas
- The most-used Excel formulas and functions
So, what are Excel formulas?
A formula is an expression that operates on values that are present in a range of cells. These formulas always have a result, even when it is an error. They let you perform calculations such as addition, subtraction, multiplication, and division. Moreover, you can also find out averages and calculate percentages in excel for a range of cells, and manipulate date and time values, and there’s still a lot more for you to do.
You must have heard of another term that’s often used when talking about Excel, which is “function.” While the words "formulas" and "functions” are closely related and often used interchangeably, they are quite different. While a formula begins with an equal sign, functions are used to perform complex calculations that cannot be done manually. Functions in excel have names that reflect their intended use.
The most-used Excel formulas and functions
The SUM() function gives the total of the selected range of cell values. It performs the mathematical operation which is addition.
The formula used is =SUM(number1, [number2], …)
The AVERAGE() function calculates the average of the selected range of cell values.
You can calculate the average and store it in the desired location.
The formula is =AVERAGE(number1, [number2], …)
The function COUNT() counts the total number of cells in a range that contains a number. It does not include blank cells and the ones that hold data in any other format apart from numeric.
The formula is =COUNT(value1, [value2], …)
The IF function is one of the most popular functions in Excel as it allows you to make logical comparisons between a value and what you expect. The IF statement can have two results, where the first result will be true if your comparison is True, and the second would be false if the comparison is False.
The formula is =IF(logical_test, [value_if_true], [value_if_false])
This function removes all spaces from text except for single spaces between words. You can use TRIM on the text that you could have imported from another source, and that might have irregular spacing.
The formula is TRIM(text)
6. MAX & MIN
If you have a lot of numbers on your excel sheet, the =MAX function will help you find the largest number in the data set, and the =MIN function will do just the opposite, it will help you find the smallest value.
The formulas are =MIN(number1, [number2], …), =MAX(number1, [number2], …)
When you merge two or more cells into a single cell, it often ends up with one larger cell being displayed across multiple rows and/or columns in your worksheet. When you use CONCATENATE in the cells in Excel, you combine only the contents of those cells.
In other words, this function joins two or more values together and is used to combine a few pieces of text that reside in different cells.
The formula used here is CONCATENATE(text1, [text2], …)
The upper function is used to convert text into uppercase.
The formula is =UPPER(text)
The lower function is used to convert text into lowercase.
The formula is =LOWER(text)
The proper function is used to capitalise the first letter of the text, and it’s mostly used for proper nouns.
The formula is - =PROPER(text)
These are the most-used Excel formulas and would make your life way easier once you master them.
While Excel makes your job incredibly easy, why not learn some neat tricks to impress your audience with PowerPoint presentations as well? Learn more about designing good decks with KoolKanya's PowerPoint programme.