If you are able to clear the first step then working with functions is a cake-walk, and the first step is to know which function you need. Now let us talk about how to find the function you need while working with Microsoft Excel.
While we are working with a database or a spreadsheet and we want to perform an action on some cells or fields, we require functions.
- First of all we need to know what action we need to perform.
- We find out what kind of action it is - the action/function that needs to be performed can be one among the following:
Date and Time functions
Date and Time functions are used when we want particularly work with fields that need to be formatted in date formats or for using date and times in calculations like calculating the the retirement period left of employees from the current date. Some examples of date and time functions are the DATE(),TIME(),TODAY() functions.
Text functions are used when data needs to be re-formatted and re-organized in a desired format. For example, when information is received and inputted from customers, employees or suppliers etc.
These are used to retrieve information or data from a database. Examples are DCOUNT, DAVERAGE etc.
As the name suggests, using these functions we calculate values relating to financial instruments like loans and investments. For Example, PMT(),FV(),NPER() etc.
Again as the name suggests, using these functions, we can get information regarding our spreadsheet, a cell or range of cells. For Example, ISNUMBER(),ISBLANK(),INFO() etc.
Logical functions are used when we want to manipulate our data based on logic i.e conditions and comparisons. Example, IF(),OR(),AND() etc.
Lookup and reference functions
By using Lookup and reference functions, we query and find out specific data in cells. For Example, VLOOKUP(), HLOOKUP() etc.
Not very commonly used, it is for engineering and scientific purposes. For Example, for converting values - CONVERT() pr for comparison - DELTA() etc.
Math and trigonometry functions
Used for mathematical operations such as SUM(),PRODUCT() or trignometric operations such as finding out the Sine,Cosine values. Examples; RADIANS(),DEGREES() etc.
Statistical functions are used to analyse the data based on the calculation of various parameters and statistics. For Example, AVERAGEIF(),VAR(),STDEV().
Compatibility functions are generally used to test the compatibility of a value with its popluation values, for instance, in a probability distribution. Examples: CHITEST(), COVAR(), FDIST() etc.
Basically cubes are multi-dimensional data sets stored by Excel from unprocessed databases. By using Cube functions of Excel, these cubes can be accessed and queried for specific information. For Example, CUBEMEMBER().
- Mostly there are only a few functions like database functions, logical functions, statistical functions and Lookup and Reference functions that are generally used because they apply to 90% of situations.
- However, if there is still a difficulty in finding out your function, a quick tip will be to tap Ctrl+F and then typing out the first few letters of the function or action that you wish to perform and then pressing Next.
- The Function Wizard is a great help to find the kind and then finally the function that you really want. We will talk about the Function wizard in detail in the end.
Working with Autocomplete
While working with normal text
It can be utterly frustrating to enter the same data entry every time and again whenever we move to a new column. Well, with the Autocomplete function of Excel we can give ourselves a treat by merely prompting Excel to replicate our entry.
For example, if we enter "Excel is fun" in cell A4 and move to cell A5 and type E, Excel automatically prompts "Excel is fun". If we want to accept it we press Enter, otherwise we just ignore the suggestion and continue writing.
While working with functions and formulas
Not just text, the autocomplete feature applies to functions and formulas as well.
As soon as the user enters '=', Excel immediately prompts a drop-down menu of suggestions for functions and formulas. Any function can be selected from this drop-down menu by clicking on it.
If the user does not want to select a function from the menu suggested he can continue to write while ignoring the drop-list.
The following screenshot will make it more clear:
If we feel that we do not need Autocomplete, we can turn it off as well by following the steps given below:
- File > Options
- Go to Advanced tab under Options.
- Under Editing Options, deselect "Enable AutoComplete for Cell Values".
- OK and you're done.
The Function Wizard is a great help to get to the function you need, especially when you are new to Excel or do not remember names of functions.
Simply put, go to the fx button located on the Excel toolbar next to formula bar and the Function Wizard will appear.
Another option is to go to Formulas and then click on Insert Function to open the Function Wizard.
In the Function Wizard;
- Enter a description for the kind of action in the search bar. For example, "Loan Payment".
- Select a function from the numerous suggestions given by the Function Wizard, for instance, in this case, "PMT".
- Insert an appropriate function by completing the function parameters and click OK.
That's it! That's all you need to know to start off working with functions.