Excel is a strict aunt. On the one hand, it is an indispensable tool to create reports, lists and analyses. On the other hand, you can only extract the desired information from the spreadsheet if you master the typical Excel language. Such Excel formulas connect all kinds of relationships to cells to return targeted information. Here are 15 features that can save you time.
Manual or the formula wizard?
We assume that by now you have mastered the basic formulas to apply the main operations. Without falling into hocus pocus for specialists, we show how useful formulas work. You can enter them manually, but you can also use the fxbutton in the formula bar: the formula wizard. He will take you by the hand to build up the formula step by step.
01 Current time
Are you someone who regularly forgets to correctly date his work? The formula TODAY automatically fills in the day, month and year while the function NOW even adds the time to the minute. Then you type =TODAY() or =NOW(). These functions are also useful in a worksheet where you want to calculate a value based on the current day and time. With a right click and the choice for Cell properties you can then adjust the display of date and time. To update this time information in the active worksheet, press Shift+F9; use F9 to update the entire workbook.
02 Counting filled cells
If you have a group of cells with both text and numbers and you want to know how many numbers are in a selection, use the function NUMBER. The structure of the formula then looks like this: =COUNT(search area). The area where Excel should search appears between the parentheses. This can be cells below or next to each other, but it can also be a rectangular selection of cells. If there are words in the selection, they are used with the function NUMBER not counted. If you just want to count all the cells that contain something, you use the function =COUNTER (without dot).
03 How often?
To count specific data in a targeted manner, use the function COUNTIF. Suppose you have drawn up a schedule in which four people appear, then you can use =COUNTIF(search area; "Herman") see how often the name Herman occurs. You specify the search range between the brackets and put the search criterion in quotes.
04 Selective addition
The function SUM to count cells is widely used. A smarter variant is SUMIF(). In the parentheses, first specify the area where Excel should search. The search range must be a series of contiguous cells. After the semicolon you determine what should be added. That can be numbers or a reference. If it's an equation, put it in double quotes. For example =SUMIF(B20:B40;”>50”) sums all cells in this range that are greater than 50.
05 Addition under condition
You can extend the addition condition by using information in another column. An example makes it clear. Suppose you have numbers that refer to three cities: Amsterdam, Rotterdam and Eindhoven. Then you can only add the numbers of Amsterdam with =SUMIF(range;”Amsterdam”,additionrange). So in this case the formula becomes =SUMIF(C48:C54;”Amsterdam”;B48:B54). In plain language: when the word Amsterdam is in the range C48 to C54, Excel must add the corresponding value from the adjacent cell in the range B48 to B54.
06 Merge
With the function PUT THE TEXT TOGETHER merge the data from different cells. For example cells with first names and last names with something like =CONCATENATE(E34;" ";F34). The double quotes with the space ensure that there is a space between first name and last name. In the same way it is possible to merge text with currency. For example, to add the currency euro, you would have to type it as a function like =CONCATENATE(A1;" ";B1;" "EURO(C1)). This reads as “merge cells A1, B1 and C1 with spaces between them and place the euro sign in front of the third element of the concatenation”.
07 Finish
Excel has several options to complete. The default rounding looks like =ROUNDING(number; number of decimals). The formula =ROUNDING(12.5624;1) so returns 12,6. You are asking to round to one number after the decimal point. Also with function ROUND.TO.TO.UP and TO ROUND DOWN Excel will round to the number of decimal places you specify. =ROUND UP (12.5624;2) so returns 12,57 and =ROUND DOWN (12.5624;2) results in 12,56. The function INTEGER is actually also a rounding function, but with that, Excel rounds to the nearest whole number.
08 Uppercase – lowercase
To make sure that everything in a column appears in capital letters, use the function CAPITAL LETTERS. The formula LOWERCASE does the opposite. And if you want every word to start with a capital letter followed by lowercase letters, you use the function Initial LETTERS. The formula =SMALL LETTERS(B4) shows the contents of cell B4, but in lowercase.
09 Conditional
When a calculation is dependent on certain conditions, you use the IF-function. The principle of this function is: =IF(condition; calculation if condition is met; other cases). To formulate the condition, use the signs: = equal to, not equal to, > more than, < less than, >= more than or equal to, <= less than or equal to. Suppose that in an organization everyone receives a bonus who has sold for 25,000 euros or more. If you receive a bonus, the word “Hurrah” will automatically appear next to his name, if not, the word “Unfortunately” will appear. The formula you need for this is =IF(B2>=2500;”Hurrah”;”Unfortunately”).
10 Biggest - Smallest
To quickly find the highest and lowest value, there is the function MAX and MIN. with =MAX(B2:B37) ask for the highest value of these cells, and with =MIN(B2:B37) you get the lowest value in the series. The features BIGGEST and SMALLEST are more subtle: you can also retrieve, for example, the third largest or second smallest. The largest can be found with =LARGE(B2:B37; 1); the number 1 denotes the greatest of all. with =LARGEST(B2:B37;2) you get the second largest and so on. That way you can easily put together a top 3 or top 10.
11 Vertical search
Suppose you have two worksheets with different information about the same people. Of VLOOKUP retrieve your information from worksheet 2 in worksheet 1. To make that easier, we have given each person a unique registration number on both tabs. Also give a name to the range on tab 2 from which you want to get information. In this example, in worksheet 2, we select columns A and B and type in the name box at the top left Address List. In cell E2 of worksheet 1 we place the function VLOOKUP. The structure is now =VLOOKUP(A2;AddressList;2;FALSE). A2 refers to the cell with the enrollment number in the second worksheet, Address List indicates the search range, 2 is the number of the column in worksheet 2 where the requested data is located. The last argument is a logical value where you FALSE if you want the found value to match exactly.
12 Clear spaces
With the function trim erase unnecessary spaces in text. This function leaves some spaces between words untouched, but will remove spaces before or after the word. =TRIM(cell range) useful with text imported from another program. In some versions of Excel, this function is called CLEAR SPACES.
13 Exchange
Transferring the contents of columns to rows or vice versa can be done with the function TRANSPOSE. First select the cells where the information should be entered. Make sure you select as many cells as the original series. Here we typed the years in row 8 and the quarters in the A column. Then type the function =TRANSPOSE and open the parentheses. Next, drag over the cells you want to swap (here from cell B2 to E5). Close the brackets and now press the key combination Ctrl+Shift+Enter. This creates an array formula enclosed in curly brackets.
14 Monthly Repayment
If you borrow for a purchase, how much do you have to pay off each month? Let's assume that you have 25,000 euros (B1) borrows at 6% interest (B2) for 5 years (B3). We show the formula in the wizard, but you can also just type. Bee interest place you B2/12, because the interest refers to a year and you want to know how much you pay monthly. Bee Number of terms multiply you B3 of 12, because you have to convert years into months. The subject hw stands for Current value, which is 25,000 euros. This gives the formula =BET(B2/12;B3*12;B1) or =BET(6%/12;5*12;25000).
15 Fake Numbers
When experimenting with formulas, it's helpful to have fake data. The function RAND BETWEEN generates random data that lies between a specified lowest and highest value. The function =RANDBETWEEN(50;150) produces numbers between 49 and 151.
