Microsoft Excel 2007 - Formulas Tab

Microsoft Excel 2007 - Formulas Tab The Formulas Tab in Microsoft Excel 2007 greatly simplifies the task of number crunching. The Excel Formulas Tab has the following groups: Function Library Defined Names Formula Auditing Calculation Before we get started with the Excel Formulas Tab, let's talk about basic math, functions and formulas. In order to show you basic Math operations, I have copied a list from Microsoft office help.  This table shows you the basic math operator symbols, their meaning along with examples.  These will come in handy as we work with data in our lesson on Excel Formulas today. Arithmetic operator Meaning Example + (plus sign) Addition 3+3 – (minus sign) Subtraction Negation 3–1 –1 * (asterisk) Multiplication 3*3 / (forward slash) Division 3/3 % (percent sign) Percent 20% ^ (caret) Exponentiation 3^2 In Microsoft Excel 2007, mathematical computations are typically done by built-in functions.  Excel has a library of several hundred functions on Formulas Tab that will let you perform a number of mathematical and statistical calculations.  For example you can use the sum function to add numbers, average function to compute averages on a list of numbers, the PMT function to figure out payment on a loan, so on and so forth. In order to use these functions, we need to have some basic knowledge about the underlying formulas. Microsoft Excel 2007 does a great job of filling in the blanks, however it is vital that you have some understanding of these basic concepts.  Excel uses the values from cells (cell referencing) to compute the end result using formulas and functions.   Function Library: Let's go ahead and see the function capabilities of Excel 2007 Formulas Tab in action next. We are going to be using a Grading workbook for our lesson today. We have the students listed on the left side while the assignment and test scores are noted across the columns. I have included a screen shot of the students grades data here with the Excel Formulas Tab highlighted in red.

We will start with the AutoSum command on the Functional Library group under the Formulas Tab in Microsoft Excel.  We would like to know the total scores achieved by every student across all the assignments and tests.  We have designated column K (Total) for the total scores.  Go ahead and click on the cell K6 which would highlight Jessica Kevin's total points.  Click on AutoSum command on the Ribbon under excel formulas tab.

You will notice that Microsoft Excel placed a dotted rectangle around the cells from B5 through J5.  In addition it placed this function =Sum(B5:J5) in cell K5.  Notice that all functions in Excel start with an equal sign.  What this function is saying is that we would like to add all the numbers from B5 through J5 and place the result in cell K5. After I pressed the Enter key, Excel 2007 computed the result and placed the total score of 707 in the correct location as visible below.

This is great, but how can we copy this formula to the other students data?  Let's see that in our next practice on Excel Formulas Tab.  We are going to use the copy formula ability in Microsoft Excel 2007 to repeat this task. Select cell K5, 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, you cannot drag your mouse all it down to cell K13 and then let go of the mouse.

It will go ahead and sum up the scores for all the students just like it did for Jessica Kevin. Very nice!  If you prefer using the keyboard you could've done the same thing by using copy (Ctrl+C) and paste (Ctrl+V) commands.

Next we would like to compute the Average for all the class assignments and the test scores. We'll be adding this information in row 15. Go ahead and click on cell B15. This time we are going to click on the Excel Formulas Tab and select Insert Function command which will launch Insert function dialog box as shown below.

Go ahead and type Average in the search text box and then click OK.  You will get the Function Arguments dialog box next.  Microsoft Excel 2007 is smart enough to pick cells B5 through B14 (adjacent cells), the scores for Chapter 1 assignment.  You can see some additional information on this dialog box.  It even has a links to the Excel Help section if you need more explanation.  Finally hit OK on this one.

Observe that it went ahead and computed the average (37.55) and placed the end result in cell B15, Sweet! You can see how easy it is to work with functions and formulas in Microsoft Excel 2007. We are going to copy the average formula to all the other class assignments and tests. Instead of dragging the fill handles, go ahead and select the B15, do right click on the mouse and choose Copy.  This will copy the formula to the clipboard and place dotted borders around the cell.  Next hold the left mouse button and select cell C16 through K16.  Finally choose paste from the right click menu.  I have included these action and its effect in the following two figures on Microsoft Excel Formulas Tab.

Our Excel Grade Worksheet is looking pretty good so far and giving us some meaningful results about our students grades.  We need to do one more thing which is to calculate the percentage of every student in the last column L.  However let's take a look at some of these other commands on the Function Library on Excel Formulas Tab first. Right next to the AutoSum command, we have a list of Recently Used functions.  This comes in handy when you are using a few functions over and over again.  In our case when I selected the dropdown, I was able to see the following functions.

The next few commands include functions that relate to a specific category.  The Financial drop down has a host of functions like loan, interest, security etc.  Logical functions include operators like true, and, or and if.  Text functions are useful to perform operations like converting text case, replacing text, concatenation and string manipulation. The next command is definitely beneficial as it has to do with Date & Time functions in Excel Formulas Tab. Maybe we can try an example from this one.  Let's say that we would like to add today's date in our Excel Sheet. How can be we do this task? Go ahead and select cell A2, click on the Date & Time command and select Today from the list.  Bingo! The next two screen captures display this functionality.

The next function command under Excel Formulas Tab is Lookup & Reference.  This one has a few useful options; hyperlink is the one that I tend to use sometimes.  Let me show you how this one works next.  Let's say that we need to store our Grading Excel sheet somewhere on the network, possibly for your co-workers to look at the data.  We can use the hyperlink command to achieve this.  Select the cell F1 as the location where we would like to insert the hyperlink.  Choose Hyperlink from the Lookup & Reference drop down as shown below.

Next you will get the Function Arguments dialog box shown in the first screen shot.  For the link location, enter a value similar to this one: C:\Users\kmughal\Documents\Grades_Access_final2.xlsx For the friendly name, enter Access Grades and then click OK.  In the second screen shot, you will observe that now we have a hyperlink (cell F1) to this file so when I email this to my colleague, he/she can go to the original file by clicking on this link. In this manner, if the underlying data has been updated, they can simply go to the most recent version!

The next function command is Math & Trig which includes quite a few mathematical function and formulas in Excel 2007. The last listed option is More Functions .  When I selected this dropdown, the choices that I see are listed below.

Defined Names: We are finally done with the Functions Library which as we have seen is quite elaborate in Microsoft Excel 2007. We can move onto of the next group of commands which is Defined Namesunder Excel Formulas Tab. Utilizing a named cell or range of cells can make your Excel Workbook more personalized.  Let's see how we can do this in the next practice.  Please note that cell K3 has the maximum possible points (1000) for our Access 2007 class.  We would like to use a Name for this value so we can use it to compute the students percentages in the next section. Select cell K5, then click on Define Name under the Defined Names group.  This will invoke the New Name dialog box as shown below.  For name enter Total_Points, add any additional information and then click OK.  Now we are able to use this name cell in our Class Percentage formula.

We need to find the overall percentage received by every student. This is necessary so we can find out the final grade.  Go ahead and click on cell L5.  Type in this formula =K5*100/to.  You will notice that after you type to, you now get an option for Total_Points.  Go ahead and select this to complete the percent formula which will be=K5*100/TotalPoints. There's an excel formulas tab screen shot of this step and resulting percentage are shown as follows.

As a final step in this group, we need to copy this percentage to all of the other student scores as well. This time let us try using the keyboard. First you need to select cell L5, press control + C, click on cell L6 and holding down the Shift key and go all the way down to L15 by using the down arrow key.  Finally do Control + V to paste the formula in the new cells.

Formula Auditing: The next two groups Formula Auditing and Calculation are really more for advanced topics so we will go over only a few options. Sometimes when your worksheet gets really crowded under Excel Formulas Tab, it helps to have some sort of navigation for all your formulas and functions.  Our grade book is fairly simple so this is not the best example for this.  But let's say we wanted to know where are total scores and averages coming from? We could select cell B15 and click on Trace Precedents.  This will highlight all the cells that are being used to compute the Average which is the value in cell B15. This is what it looks like on my computer display.

In a similar fashion we can also find out dependents in my Excel Workbook.  For example I am curious to find out if any cells are using the value from cell E5.  I click on that cell and then select Trace Dependents on Excel Formulas Tab.  This will highlight cells K5 and E15 as highlighted below. What this means is that the cells K5 and E15 (Total and Average) depend on E5 for its computation.

If you want to remove all the precedents and dependents from your Excel Workbook, you can simply use Remove Arrow under the Formula Auditing group. This will clear all the arrows from your worksheet.

Last but not least, a useful command in this group is the Show Formulas command on Microsoft Excel Formulas Tab.  Sometimes it makes sense to locate all the formulas in your Microsoft Excel 2007 worksheet, maybe you need to print it out for your reference. You can do this by using Show Formulas command under the Formula Auditing group. When I did this on my Excel sheet, this is what I saw. Notice you have one central place where you can review all the functions and formulas. Lets go ahead and save the spreadsheet now.

This concludes the tutorial on Microsoft 2007 Excel Formulas Tab. 

Posted in: How to, MS Excel, Tips and Tricks, Tutorials