Microsoft Excel helps you to enter, organize, sort, and format a volume of data in a spreadsheet— a handy and powerful tool for personal and professional use.
Good hold on Excel functions can boost the productivity and efficiency of any firm. There are hundreds of functions in Excel, but only an Excel formula expert can match the right formula for unique needs.
We have compiled 5 commonly used Excel functions that every world-class Excel practitioner must know.
1. Paste special feature
Cut, copy, and paste are the most common functions in Microsoft Excel. But most of us often carry over a format we don’t need, or we copy a formula instead of value.
This mistake is common but can be frustrating. That’s where ‘paste’ feature special comes in.
Ctrl+Alt+V is a shortcut to Paste Special menu, or you can use it from Edit > Paste Special on the Home Ribbon.
This feature apart from pasting a whole-cell will paste only a specific element such as value or formula. This feature offers a range of options to paste only specific elements of the copied cells.
For instance, you can paste the contents of a cell but not it’s formatting. Interesting, right?
2. Removing duplicates
This feature is super-useful in case you have a good amount of data to parse in Excel.
It can purge any duplicate value within a fraction of seconds. Rather than cluttering your whole spreadsheet, copy the data you want to dedupe to a separate sheet.
Then select the data and click on the “Remove Duplicates” feature in the Data section on the home ribbon. In case you want to highlight duplicates, use conditional formatting.
3. Conditional statements
If, AND, OR, and NOT are simple logical functions used to create conditional statements. Among them, the IF function is the most common.
The syntax for this formula is,
IF(logical_test, value_if_true, [value_if_false])
Here, a logical_test is an expression that can be expressed as TRUE or FALSE.
value_if_true is a value to return if logical_test is TRUE.
value_if_false is a value to return if logical_test is FALSE.
For instance, you’re checking the budget of your company, and you want to flag it as being “Over Limit” if the budget is at or above 85000, else the budget is “OK”.
The budget is located in cell C2. Now, the conditional statement can be written as :
=IF(C2>=85000, “Over Limit”, “OK”)
It is very simple to use.
4. Date statements
NOW and TODAY functions create data statements and return the “computer’s current time and date” respectively. Both functions are very easy to use and they don’t even require any parameters.
The syntax for these two formulas are,
The TODAY function specifies the current month, day, and year; while the NOW function specifies the current month, day, year, and time of day.
Pro tip: To make these two functions work properly, enter them in the Home cell, that is, A1, else, they won’t update automatically when the spreadsheet is refreshed.
5. Flash Fill feature
Flash Fill is a mind-blowing feature that automatically fills your data when it recognizes a pattern. In case, you have to input or change a lot of data quickly, it can be the best time saviour.
For instance, there is a list of employee codes in the first five cells of column A, e.g. ‘EMP-0000A’ to ‘EMP-0000E’ and you only want the letter after the ‘-’. By using flash fill, you can eliminate the EMP.
Build the pattern by typing ‘0000A’ into the first cell. If Flash Fill is turned on just type the next code in the cell below and Flash Fill will sense the pattern and will automatically fill down the remaining employee codes. Just hit the Enter key to accept.
Hopefully, these top 5 Excel functions will be helpful for you. Don’t stop here! There are hundreds of functions that every Excel formula expert should know. Keep boosting your knowledge to master Excel.
A firm hold on these important functions is valuable in any industry that deals with huge data.
In case you are looking for an Excel formula specialist for your business, feel free to call us at 07961593334.