Excel 2010 Tutorial

Just 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 Formulas

One 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 page

Before 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.

SUM

This 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 rows

Looking at the formula bar you will see =SUM(F6:F11). Notice next the letters at the top range from A-G for the columns (for this demo). Also you can see here that the rows range from 1-15. 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 column

So 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 rows

Now 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 rows

In 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 detail

Here 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.

Excel

AVERAGE

This 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.

Excel

This example is very similar to the first one except it uses the AVERAGE formula instead. The rows shown this time range from 1-20 and the columns go from A-B. Note that we are not paying attention to the letters C-E since no data exists there. The formula will take all rows from 1-20 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.

MAX

This formula will check a range of values and return the highest number found there in rows B3-B14. 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.

Excel

MIN

This formula will check a range of values and return the lowest number found there in rows B3-B14. In this example the result shows $200. since that was the least price paid for a ticket to the concert. Look at this example.

Excel

MOD

This 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.

Excel

COUNT

This 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 1-12. Since there are only 12 items that is the answer. Please note it does not add up the numbers by counts each one individually.

Excel

INT

This 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.

Excel

INDEX

This 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 row

The 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 column

The 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 A-B 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.

Excel

VLOOKUP

This 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 names

For 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.

Excel

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.

Excel

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.

Excel

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.

Excel

Step 4: Enter data for the VLOOKUP

The 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.

Excel

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).

Excel

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.

Excel

Return list of all customers with a discount

Currently 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 "non-applicable". However we are going to change this to another result next.

Excel

IFERROR

Now 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 IFERROR

In 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".

Excel

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.

Excel

CONCATENATE

The 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.

Excel

Step 1: The first field is for the first name

Cell 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 quotes

This 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.

Excel

Step 3: The third field is for the second name

Cell A13 contains the name Marsha so it is represented by the second field that shows =CONCATENATE(A4, "", A13).

IF

This 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 field

The first field D4 will be defined to that cell which has the name "Paul" within.

Step 2: Search a cell to compare against

The 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 condition

The 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 field

The second field A10 will be defined to that cell which has the name "Mark" within.

Step 5: Search a cell to compare against

The 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 condition

The 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.

Excel

IF(COUNTIF - Nested conditions

This 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.

Excel

Step 1: Search a range of cells

The second field with the cells A4 through A13 will search in those cells.

Step 2: Check for a match

The 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 found

The 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 found

The fifth field contains the word "No". This will display the word "No" if the name "Mark" was not found in the search.

COUNTIF - Less Than

This 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 cells

This formula will look at all of the customers concert admissions starting with Brian and ending with Marsha.

Step 2: The "< means less than

The second field will do a less than comparison.

Step 3: The third field is the number to compare to

This field will check if the customers in cells A4-A13 have values less than 201.

Final result for COUNTIF

The 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.

Excel

COUNTIF - Greater Than

This 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 cells

This formula will look at all of the customers concert admissions starting with Brian and ending with Marsha.

Step 2: The "> means greater than

The second field will do a greater than comparison.

Step 3: The third field is the number to compare to

This field will check if the customers in cells A4-A13 have values greater than 200.

Final result for COUNTIF

The 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 cells

This 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 To

The 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 values

The 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 To

The 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.

Excel

This 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.

Excel

NOW

This 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.

Excel

Questions? Ask Here

 

Subscribe to the Programmermind newsletter
Copyright 2010 - All Rights Reserved
Privacy Notice: Programmer Mind will not compromise your privacy