Microsoft Excel is one of the best tools to keep your data organised and use it for analysis. At first glance, Excel may look a bit tricky, but once you start using the same, you will find that things have become easier. Although there are multiple ways in which the Microsoft Excel can be employed, the primary purpose of Excel is managing data. Now there are so many features in Excel that you might not know. These features would not only simplify your work but ensure efficiency and save time. That is why one must have in-depth knowledge of the program as it is highly useful.
Whether you are using the Microsoft Excel 2013 or the current version, there are many direct functions which you can apply to get the desired results. You might be well versed with the ones that are in front of you, but there are many versions which can be utilized. So if you too want to have a good command over Excel and wish to learn some of the functions and the formulas, then you have come to the right place.
There is an extensive list of the Excel Formulas that you should be aware of, but here we have shortlisted the ones that can make a difference. So let us get started and learn more about these formulas and the quick tips and tricks for Excel.
Excel formulas for University students :
1. Inserting comment into any cell
While preparing the spreadsheet, you may feel the need to add some special comments to the value so that it is easy to understand when you open it later.
In order to use this feature, you just have to right click on the cell to which the comment needs to be added and then find INSERT COMMENT. Type whatever you want to and then click anywhere outside the box to save your comment. You are now done with your task. All the cells which have comments in it bear a red triangle, so you know which ones need to be read.
2. Adding time and date to cells
Many times you might feel the need of adding time and date to your different cells so that the tasks can be managed properly. Before you insert any function into it, the first step is selecting the cell. Once you have done this, you can now choose from the given options and insert date, time or both as per the requirements.
Insert current date = Control + ; (semi-colon)
Insert current time = Control + Shift + ; (semi-colon)
Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).
3. Use of IF
The next is finding the same values that occur several times in the sheet along with their cells. Now, this is slightly different and here begins your work. Rather than wasting time and entering values manually you can take advantage of the IF THEN statement.
IF(logical_test, value_if_true, value of false)
Using it you can assign a fresh value to all the cells and modify it without any problem.
As the name indicates, this is the feature through which you can get rid of all the rogue spaces that create problem in working. Now doing it manually can take up lots of time and that is why it is wise to use TRIM.
The formula: =TRIM(“Text”)
Your text is the place from where you wish to remove the spaces so enter it quotations and get your work done in just one single click.
This function can be used for a number of purposes, and that is why you need to know how to use it. SUMIF gives you the total cell value with criteria added to it. The implementation is as follows.
Formula: =SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Sum_range is the total of all the values
Criteria_range is the restriction that you will apply for the same.
It is the feature which enables you to search anything in the extreme left column and then return it as a value. It is pretty easy and very useful as you do not have to search the cells on your own but can be done with this simple formula.
Formula =VLOOKUP(lookup value, table being searched, index number, sorting identifier)
Another very useful function that you will come across is PROPER. It is meant to correct the formatting of the text and make it look the same. For example, you have a cell called S1 and holds the value “ShE Is bEautIful”. Now, this looks odd, and you want to correct it. Doing it one or two cells is easy, but when there are many cells to be formatted, PROPER is the function to be used.
Formula is PROPER(S1) and you will get the result She Is Beautiful.
8. MAX and MIN
As the name implies the function can be employed for calculating the maximum and the minimum vales as per your condition. According to the need you can apply the function and get the results. The implementation is given below.
Formula = MAX(S1: S10)
This means that all the maximum values in the S1 row and S10 will be retrieved as the result.
This is the function through which you can put together two different values of the cell in one. Now you will be thinking as how this can be done. Here is the syntax for you.
Formula = CONCATENATE(S1,” “F5)
This means that if S1 has the first name of a person and F5 the last name then it will bring together the first and the last name in one cell with “ “ meaning the space.
10. SUMIF, AVERAGEIF and COUNTIF
These are just similar to your SUM and AVERAGE functions but with a difference that a criteria is added to it. The implementation can be done in the following manner.
Formula = FUNCTION(range, criteria, function range)
When you apply this formula and set the range, you will get only the desired results that you want to thus save a lot of time.
So this is the list of the 10 Excel Formulas which you can definitely try. Implementing them is quite easy, but you should know the syntax. Once you do it on your own, you will understand how the various functions can be employed to reach the target. Hope the information really proves handy for you and helps you to get a good understanding of the Excel formulas in a simple way.
Author Bio: Morris Edwards is an Excel Trainer by profession and Writer by hobby. He is providing Microsoft Excel Training in Singapore. His advanced Excel skills will provide you with the most recent information. He has been writing for several years about the Microsoft Excel updates and having great knowledge in it.