Excel Ethiopia

  Do you want to calculate how much your current savings might be worth in 10 years or in 30 years? It’s an easy calculation that doesn’t require any specific function in Excel. Some simple multiplication, addition and exponentiation is all you need. Still, the answer you will most likely get if you search for “compound interest in Excel” on Google is the FV function. The FV function is difficult to use, and it actually calculates compound interest based on a monthly rate rather than a yearly rate, which gives a slightly different result. If you have $1,000, the yearly return is 8% and you let it compound for 10 years, you would calculate it like this: Of course, in Excel we don’t want to hardcode anything in the formula, so we’ll put the numbers in separate cells. This is how you can set it up: Now you can change any of the variables in C2, C3 and C4, and the Future value will update instantly. If you want to use the FV function instead, this is how it’s done. As mentioned above, ...

  Instead of typing a SUM formula, an easier way to go is to use the shortcut Alt + Equal sign (=) But did you know that you can use this shortcut for a whole table? If you have a table like the one below, select all the values  plus one extra row and one extra column : And use the shortcut: Alt + Equal sign (=) Instantly, Excel inserts all the formulas and calculates the sums:

  Creating a drop-down menu in Excel may look difficult, but it’s actually super easy! It won’t take you more than 3 minutes the first time you do it, and when you get the hang of it, it only takes a few seconds! Just follow these 3 steps to create a drop-down list in Excel: 1. Create a list of the items that you want to appear in the drop-down menu. 2. Name the list. Select the range and press  Ctrl+Shift+F3  to open the name manager. If the list has a headline (it should!), choose Top row. 3. Create the drop-down menu. Now you can create a drop-down menu anywhere you want in the workbook. Select a cell and press  Alt, then D, then L  to open the Data Validation window. Choose List in the first field. In the source field, simply press  F3  to retrieve your list names and choose the one you want for this drop-down. Done! Note: If you want to create many drop-down menus in one workbook, it might be a good idea to create all the lists in one tab.

  How to use a wildcard In the example below I have used the COUNTIFS function to count the number of members who fulfill the two criteria in the report, and the SUMIFS function to calculate the total amount paid if both criteria are met. =COUNTIFS(B2:B11,G2,D2:D11,G3) =SUMIFS(C2:C11,B2:B11,G2,D2:D11,G3) But what if you only want to see how many gold members there are, no matter if they have paid or not? Or you want to see the total payments, regardless of membership level? If you leave G2 or G3 empty, the formulas will return zero. The only way to do it without changing the whole setup is to use a wildcard. The wildcard character in Excel is asterisk (*), and all you have to do is to type an asterisk in the criterion field(s). For example, if you replace “Gold” in G2 with an asterisk (*), the formulas will not consider the Membership criteria, and the results will be the totals of all members who have paid the fee. Make your report more user-friendly If someone else is going to us...

If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it. The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that. A better way to do it is to use this formula: =SUM(Jan:Dec!B2) (and copy down and across) With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference. You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone ...

  Sometimes you receive an Excel report or an export from another system with a lot of empty cells in a category column: If you want to analyze the data, either with a Pivot table or with formulas such as SUMIFS and VLOOKUP, you need to populate all the empty cells in the category (dimension) columns, in this example column A. For example, if you want the Total Sales for Northeast, the formula =SUMIFS(E:E,A:A,”Northeast”) will only work if you have the right region in every cell in column A . This is how you do it: First, select the range you want to amend. If the dataset is small, you can select it manually with the mouse or with Shift + Arrow, but if you have a large dataset, it’s easier to use the Name Box: Just type the range in the Name Box in the format FirstCell:LastCell, or in this example A2:A33. The whole range will be selected. Open the  Go To Special  dialog box. You find it on the Home tab, under Find & Select, or you can use the shortcut Ctrl + G and cli...

  In the newer versions of Excel (2019 and 365) you can finally extract unique values with one simple function:   UNIQUE . In previous versions, you either had to do it manually with the advanced filter, with a pivot table, or with a super-complicated formula that was impossible to remember Now it’s easy: Just use the  UNIQUE  function! UNIQUE  is one of the new Spill functions in Excel, which means that you type the formula in a cell, e.g. D2, and it populates as many cells below as it needs. So keep in mind that you need to have a sufficient number of empty cells to make room for the results. Here’s how you do it: In this example, I have a list of transactions. There are 20 rows of data, but we only have six unique sales reps, so I want to create a small table with one row for each rep. This formula is all I need: =UNIQUE(A2:A26) It will find the unique values and spill down automatically. We don’t even have to copy down! To add sales figures per sales rep, ju...