
Excel 2010 TutorialJust recently I received a request from a great friend to put together a mini Excel project for an assignment she has. While we were discussing this, I thought that maybe others may want to learn from my experience with this tool. Excell can be simply thought of as an "overblown" scientific calculator". You can use it to perform calculations and create fancy charts. The bottom line is to know that there are actually only a few basic formulas that most people use. Excel FormulasOne of the first important concepts to grasp is to understand how Excel uses "formulas" to perform its' magic. I will start with a few basic ones to get the ball rolling here. Excel Formulas: Quick Lookup on this pageBefore diving into this lesson, note that I have created a specific index search to allow you to look up a formula more quickly on this page. Just click on a link below to navigate to that section. Please note: They are listed by relevance based on their brevity of use in particular markets. Click on SUM to see that formula. Click on AVERAGE to see that formula. Click on COUNT to see that formula. Click on MAX to see that formula. Click on MIN to see that formula. Click on MOD to see that formula. Click on INT to see that formula. Click on INDEX to see that formula. Click on VLOOKUP to see that formula. Click on IFERROR to see that formula. Click on CONCATENATE Click on IF to see that formula. to see that formula. Click on IF(COUNTIF to see that formula. Click on COUNTIF  Less Than to see that formula. Click on COUNTIF  Greater Than to see that formula. Click on SUMPRODUCT to see that formula. Click on HYPERLINK to see that formula. Click on NOW to see that formula. SUMThis formula allows one to take a list of Excel columns and add them up to get a total. Look at the picture below to learn more. I will break this down into a series of steps so I don't lose anyone. Step 1: Match up the data in the rowsLooking at the formula bar you will see =SUM(F6:F11). Notice next the letters at the top range from AG for the columns (for this demo). Also you can see here that the rows range from 115. That is the secret ingredient behind Excel. Once you understand how to match these up you will know how to zero in on that specific formula. Step 2: Look at the formula columnSo in row F6 you should be able to see the number 480.00. Look to the top and the left (using your fingers to meet up at the cell if necessary) and you will see how it works. Step 3: Get all of the data in the rowsNow the next part may be a little more complex to understand how a series of calculations were derived from this formula. So the next part of the formula shows F10. Please note that the colon in between indicates that a sequence of instructions will follow after it. Step 4: Add up all of the rowsIn row F10 you will see the number 80.00.The way to read this is give me the sum of all rows from F6 through F10 and put the result in cell F14. So to clarify the formula =SUM(F6:F10) = $930.00 (the total of a monthly budget.The answer explained in detailHere is how this may look on a calculator 480.00 + 255.00 + 15.00 + 100.00 + 80.00 = 930.00. Try creating your own totals this time as an excercise.
AVERAGEThis formula allows one to get an average of a range of numbers. The mathematical equation is (Num1 plus Num2) Divided by the Total Range of Numbers = Answer. Excel will interpret this as (Num1 + Num2) / Total = Answer.
This example is very similar to the first one except it uses the AVERAGE formula instead. The rows shown this time range from 120 and the columns go from AB. Note that we are not paying attention to the letters CE since no data exists there. The formula will take all rows from 120 and place the result in cell B22 (remember to look at the top and the left to see where this lines up) for those who see things visually. So this result is interpreted as =AVERAGE(get the sum of rows B1 through B20) and divide them by 20 (which is the total number of data here). Excel sees this as =AVERAGE(B1 through B20) / 20 = 143. MAXThis formula will check a range of values and return the highest number found there in rows B3B14. In this example the result shows $350.00 since that was the highest price paid for a ticket to the concert. Look at this example.
MINThis formula will check a range of values and return the lowest number found there in rows B3B14. In this example the result shows $200. since that was the least price paid for a ticket to the concert. Look at this example.
MODThis formula will return the remainder of two cell values. The mathematical equation is Number1 divided by Number2 equals the modulus (or remainder) of. In this example the result shows $24.45 since that is the remainder of the cell B5 divided by cell B14. Look at the example below.
COUNTThis one should be easy. All this formula does is count up the number of items in a list. In this example shown is a range of numbers from 112. Since there are only 12 items that is the answer. Please note it does not add up the numbers by counts each one individually.
INTThis formula will take a decimal value and convert it to a regular (whole) number. In the example the cell being returned is B14 which has the data of $225.55. Since this is known as taking the "integer" of a value the cents part is portioned off so that only $225.00 remains. The example is seen below. Please take note that the column for "Colin" was added to fulfill this example.
INDEXThis formula will evaluate cell data based on it's row and column value contained within. Step 1: The first field will contain the range of cell data to verify.In this example the cells being evaluated are in the range from A1 through B13. Step 2: Get the data contained in a specific rowThe second field contains the row number of the data being indexed. The value shown here is 8. Step 3: Get the data contained in a specific columnThe third field contains the column number of the data being indexed. The value shown here is 2. The result returned is 210 since the data that appears in row 8 and column 2 is $210.00. The cell produces an unformatted result. The easiest way to understand this (as explained earlier) is to look at the top part of the screen with the letters AB alone. These are the columns. Then look at the the data in cells A1 through B13 going down. These are the rows. Here is a screenshot of the final result.
VLOOKUPThis formula will look for a specific match in a list and return that result. VLOOKUP is commonly used to search through a vast amount of data and find results rather quickly. Step 1: Define a name for the range of namesFor this example a list of Customers have all paid to see a concert. There however are several customers who received a 20% discount. Before VLOOKUP is used it is necessary to define a name for the list of items that are being searched. You will need to highlight the list of Customers starting with Brian and ending with Marsha. To do this, after entering the list of names (as seen) right click the mouse and choose Define Name In the name field enter the name discount. This name is now assigned to the Customer name list.
Step 2: Create the name of Customers who didn't get a discount.In the next part enter the list of names shown in the column called "Cust' with discount". This column indicates the customers that received a 20 percent discount to get into a concert.
Step 3: Start the VLOOKUP formula.Now that the data is entered you are going to use VLOOKUP to search through the Customer list (defined as "discount") to see which Customers received their concert discounts. First look at this area in the Excel document. The "fx" (also known as "function") will be used to enter a formula for the VLOOKUP. This is also the same icon used when entering any formula into a cell, such as SUM, AVERAGE, COUNT as seen earlier.
Click on the fx icon and you will see this popup which will be used to gather the data to evaluate the Customer list to be compared to the Customer discount list. Once you see the name VLOOKUP appear in the area for Select a function, press the OK button. The GO button is used to search for items not in the list.
Step 4: Enter data for the VLOOKUPThe next thing we are doing is entering a value in the Lookup_value field. The field will be used to search a range of cells. Enter the D4 as seen here. We are choosing Paul to start with since he is on the discount list.
In the next field called Table_array we are going to enter the name discount. Recall that this was created earlier (above) to define a variable name for the list of Customers. In the next field called Col_index_num enter a 1. This will search in the first row where the names are listed in column A. In the next field called Range_lookup enter False. False means that we must have an exact match. If you enter True then it will locate the closest match. Press the OK button and you will see the name Paul appear in column E3. In the fx field you will see =VLOOKUP(D4,discount,1,FALSE).
Here is a screenshot of that result. The name "Paul" appears since we only selected one name on the list. I may have forgotten to mention this, but when you entered a "1" in Col_index_num it found the name of Paul.
Return list of all customers with a discountCurrently we only searched for the first customer "Paul". Now we are going to search every name on the list. Click on cell E4 where it says "Paul". Next look closely at the bottom right hand corner of cell E4. Move your mouse over it until you see a black plus icon. Then press and hold the left mouse button while dragging the mouse down to row E13. You should see this result. The #N/A is a temporary placeholder for "nonapplicable". However we are going to change this to another result next.
IFERRORNow we are going to replace the #N/A with a more logical result. So if a customer didn't get a discount then the word "Full price" will appear instead. That is the purpose of the IFERROR function. To get this started click on the fx icon again. This time you will be using the formula IFERROR with VLOOKUP to compare a customer that paid full admission vs. the ones who got their discount. Step 4: Enter data for the IFERRORIn the field called Value type in the earlier formula name VLOOKUP(D4,discount,1,FALSE). In the Value_if_error field enter the name "Full price".
Now click on cell E4 again and repeat the section for Return list of all customers with a discount to drag and get a copy of all the discounted customers again. When done you should see this.
CONCATENATEThe formula CONCATENATE will take a list of rows and merge them into one name within a single cell. You can enter the formula into the fx field or click on the fx icon. It is up to you which approach you prefer. For now I will use the formula field. Enter the formula =CONCATENATE(A4, "", A13) as shown.
Step 1: The first field is for the first nameCell A4 contains the name "Brian" so it is represented by the first field that shows =CONCATENATE(A4 . Step 2: The middle field will contain double quotesThis field can be used to insert a space in the middle or any other text. For example try using =CONCATENATE(A4, " ", A13) instead or maybe even =CONCATENATE(A4, " and ", A13). Here is that result.
Step 3: The third field is for the second nameCell A13 contains the name Marsha so it is represented by the second field that shows =CONCATENATE(A4, "", A13). IFThis formula will compare a condition based on entered data in the fields. If a conditon doesn't match that part of the formula will fail. Step 1: Define the first condition name fieldThe first field D4 will be defined to that cell which has the name "Paul" within. Step 2: Search a cell to compare againstThe next field contains the name "Paul" which has been assigned to the D4 cell. This is a way of asking "Does the data that shows in cell D4 have the name "Paul" there? Step 3: Assign a result for the first conditionThe data contained in this field shows "Discounted rate" which is a way of assigning the condition to a statement. This is a way of asking "Does that data that shows in cell D4 have the name "Paul" there? If so then the returned result will be called "Discounted rate". The next area continues to look at a second IF statement. Step 4: Define the second condition name fieldThe second field A10 will be defined to that cell which has the name "Mark" within. Step 5: Search a cell to compare againstThe next field contains the name "Jay" which has been assigned to the A10 condition. This is a way of asking "Does the data that shows in cell A10 have the name "Jay" there? Step 6: Assign a result for the second conditionThe data contained in this field shows "Full price" which is a way of assigning the condition to a statement. This is a way of asking "Does that data that shows in cell A10 have the name "Jay" there? If so then the returned result will be called "Full price". However this condition failed since the data contained in cell A10 has the name "Mark" instead.
IF(COUNTIF  Nested conditionsThis formula is for the advanced user. In this example we will examine the Customer data to see if a name was found. The IF(COUNTIF is used to check if a match exists based on the parameters proceeding it. Take a look at this example.
Step 1: Search a range of cellsThe second field with the cells A4 through A13 will search in those cells. Step 2: Check for a matchThe third field contains the name Mark. This is used to locate that name in the list. Step 3: Show a name if a match is foundThe fourth field contains the word "Yes". This will display the word "Yes" if "Mark" was found in the search. Step 4: Show a name if a match is not foundThe fifth field contains the word "No". This will display the word "No" if the name "Mark" was not found in the search. COUNTIF  Less ThanThis formula is used to check a range of numbers to determine if they are less than a result. Step 1: The first field checks a range of cellsThis formula will look at all of the customers concert admissions starting with Brian and ending with Marsha. Step 2: The "< means less thanThe second field will do a less than comparison. Step 3: The third field is the number to compare toThis field will check if the customers in cells A4A13 have values less than 201. Final result for COUNTIFThe example below shows the outcome. Since there were 3 values less than 201 then this was the result found by Excel. These are the discounted customers.
COUNTIF  Greater ThanThis formula is used to check a range of numbers to determine if they are greater than a result. Step 1: The first field checks a range of cellsThis formula will look at all of the customers concert admissions starting with Brian and ending with Marsha. Step 2: The "> means greater thanThe second field will do a greater than comparison. Step 3: The third field is the number to compare toThis field will check if the customers in cells A4A13 have values greater than 200. Final result for COUNTIFThe example below shows the outcome. Since there were 7 values greater than 200 then this was the result found by Excel. These are the customers that paid full price to get into the concert. SUMPRODUCT  Greater Than AND Less Than (Checks a Range)This formula is used to check a range of numbers to determine if they are greater or less than a result. Step 1: The first field checks a range of cellsThis formula will look at all of the customers concert admissions starting with Brian and ending with Marsha. Step 2: Check if range is Greater Than or Equal ToThe second field will do a greater than or equal to comparision. So this says is the customer admission greater than or equal to 300? Step 3: The * signifies an "AND"The third field is used to compare one range of values against another condition. Step 4: The fourth field checks the second range of valuesThe fourth field is actually checking the customer list again so we can comparison the greater and less than ranges. Step 5: Check if range is Less Than or Equal ToThe fifth field is checking the customer list again to see if the comparison on these sets of numbers was less than or equal to. So this says is the customer admission less than or equal to 350? The final result shows 4 since the customers Brian, Alexandria, Janice, and Mark were all in betweeen >=250 AND <=350.
HYPERLINKThis formula will create a link to any URL (website address) listed in the parameters. When it is clicked a browser will launch and send the user to that website. So in this example, a user will be navigated to my website.
NOWThis formula returns the system date and time. There is nothing more to this function, except that it contains no fields. Examine the data in cell E21.
Questions? Ask Here
Copyright 2010  All Rights Reserved 