Given the huge amount of MS Excel functions, coming up with a top 5 list is not easy but here it goes:
Autofilter: They restrict cells to lines containing a specific value. Using them is very easy: Just pick the title of a list of data and use Data-Filter-Auto Filter from the command list. Now in the one on top of the list make a new selection. Select one of the values you want to filter for. This will display only the correct lines you are interested in. It’s that simple.
The SUMIF Function helps with the regular filter limitations. Autofilter shortcomings can be circumvented with the SUMIF function easily. It works exactly like the SUM function, but adds the ability to only filter for values within a defined area. The function is as follows: SUMIF (range; search criterion; sum_range).
The COUNTIF function: Countif is not unlike SUMIF, but COUNTIF answers questions like this: “How many mistakes have we made in our trial?” The formula basically counts each instance of a mistake from a list of data. The function reads something like this = COUNTIF (range; search criteria). To answer our initial problem about the number of mistakes, the function with the following criteria should work: COUNTIF (D8: G19, G6) G6 is the criteria to filter for in the area of D8:G19. It will count all of the instances of the value defined in cell G6.
The IF Function is one of the most important ones to know. It is extremely versatile in its usage. Use it to check results and then make a decision how to proceed from there. An example: When billing, the rule is that on purchases over 100 euros, a discount of 2% is being offered automatically. This means that IF the value is less than 100 euros, 0% discount will be given. IF the value is more, 3% will be given.
The NETWORKDAYS operator: There are about 20 different date functions in Excel. This depends on the installed add-ins so it varies from user to user. For labor intensive companies, NETWORKDAYS should be of particular interest. It calculates how many working days are available between two dates. Here, the weekends will be deducted automatically and on request also takes into account the holidays or long weekends. Here’s the function: NETWORKDAYS (start_date, end date; holidays).
The NETWORKDAYS Function (special usage). Before the function can be fully effective, we must exclude non-working days (excluding weekends) . You write somewhere on the same or another worksheet all the days-off. Give the list a title like “Days off”. Now select the menu item Insert / Name / Create and select the option “Top row” so that the term “Days Off” will be applied automatically.