Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.
The Formulas tab in Excel 2010 simplifies working with numbers. It allows us to insert functions, define the name, create the name range and more. In this tutorial we will review each of the sections: Function Library, Defined Names, Formula Auditing and Calculation.
There are several hundred functions stored on the Formulas tab that will allow you to perform a number of mathematical and statistical calculations. Excel uses the values of the cells to compute the end result using formulas and functions. This tutorial will give you some basic knowledge about the underlying formulas and gain a better understanding of these basic concepts.
In Excel, we have over 400 functions and they are available in the Function Library section of the Formulas tab. There is no need to learn every formula because we can locate formulas by their categories: Financial, Logical, Text, Date & Time and more (as shown in the image above).
Let’s take a look at some of the function capabilities by using our example below: Golf Scores for the 2019 season.
We would like to know what the total scores achieved for each golf player for the 2019 season. We will start with the AutoSum command in the Function Library section of the Formulas tab. We have designated column K (Total) for the total scores. Click on K3 and select the AutoSum button. Your formula will automatically be populated in K3 as shown below: =SUM(B3:J3). What this function is saying is that we would like to add all the numbers in cells B3 through J3 and place the results in K3. Excel also puts a dotted line around the cells that are associated with the formula.
Verify the formula is correct and includes the intended cells, and then select Enter on your keyboard. The total (455) now appears in cell K3.
We can quickly compute the totals for the other golf players by copying the formula in K3 to the rest of column K. Select cell K3, and move the mouse to the right bottom quarter of the cell until the icon changes to a fill handle. Holding the left mouse button, drag the mouse down to cell K11 and then let go of the mouse.
We will now see a sum for all the golf player’s scores in Column K just like for Kim Byers in cell K3 (as shown in the image below).
Next, we will calculate the Average score for each Game. This information will go in Row 13. Start by clicking on cell B13. In the Formulas tab, select the Insert Function button which will open the Insert Function dialog box as shown below.
You can type Average in the Search for a function box or click on AVERAGE from the Select a function box. Select OK after making your selection.
Now the Function Argument dialog box appears, as shown in the image below. Microsoft Excel 2010 is smart enough to select cells B3 through B11, the player’s scores for game 1. After reviewing the cell selection in the Number1 box, select OK.
The average has been computed and the end result is placed in cell B13. Copy the average formula from B13 through K13 just like we did before; click on B13 and drag the fill handle through K13. We now have the average score for each game, as shown in the image below.
We have collected some meaningful information on our golf player’s scores for the 2019 season. Now, let’s find how the percentage of each player’s scores compare to the total average.
Select cell L3 and type =K3*100/K13, and then select Enter. Our answer is 90.6776% as shown in the image below. This tells us that Kim Byer’s score is about 10% below the average. Since you want the lowest score in golf, this would be favorable. We completed the formula for the rest of the players and the results are in Column L. We can also determine that Christine Marley is the highest above the average at 106.82%.
Let’s take a look at some of the other commands in the Function Library section.
Right next to the AutoSum button we have a list of Recently Used functions. This might be useful if you using a few functions consistently. When we selected the Recently Used drop-down menu, we could see the list of functions shown in the image to the left.
The Financial drop-down menu has many functions, with some of the more common ones being loan, interest, payment and rate (as shown in the image to the right). Scroll down through all of the functions in Financial to see which ones might be useful to you.
Logical functions include operators like true, false, if, and not (as shown in the image to the left).
You can use Text functions to perform operations like converting text case, replacing text, cleaning up the text, repeating text and more. Scroll through all the functions and hover over each one for a brief description.
The Lookup & Reference command includes functions such as address, hyperlink and match (as shown in the image to the right). Hover over each function for a brief description.
You will find some of the Math & Trig functions shown in the image to the left. Again, hover over each function for a brief description.
The last command listed is More Functions, as shown in the image to the right. When we click on this button, we see options for statistical, engineering, cube, information and compatibility functions. These are functions that you may not use often, but scroll through each list to see what is available.
This concludes the Function Library tutorial.
Click next to continue to the Excel 2010 Defined Names tutorial.