The first and most important thing to building a formula for automatic calculations in Excel is to know a cell reference when you hear it, understand what it means and how it applies to your objective; to build a calculating cell. The next is to know your operators. By the time you finish this tutorial you will be able to do the following:
- Recognize and identify a cell reference
- Explain its purpose
- Recognize and identify the basic arithmetic operators as used by Excel
- Build a basic formula for a calculating cell
Below is a picture of a very simple worksheet built specifically for this lesson. Notice that a single cell has a darkened border around it. You will also notice that above that cell is a yellow block with the letter F in it. This is your column indicator. This one indicates that you are in the F column of the worksheet. If you follow the row to the left you will notice a similar yellow block that has a 3 in it. This is your row indicator. This one indicates that you are in the 3rd row of the worksheet. The combination of the two indicators is always expressed as ColumnRow; in this case F3. This is what is known as a cell reference. If there is any question as to what cell you are in you can always view the Name Box, which is located right above where the column indicators and the row indicators meet at the top-left of the worksheet. Notice that this one has F3, as it is the active cell that I am in. If you want to save time navigating to a particular cell simply type its reference in the name box and hit the enter key to go straight to that cell. If you have Excel, take a moment to try it out.
Now that you know what a cell reference is, review the table below for your basic Excel operators.
Operator (key stroke)
Operations/Action
+ Addition
- Subtraction
* Multiplication
/ Division
= shows the outcome a formula or comparison
Excel uses cell references in conjunction with operators to calculate your figures based on the information you have placed in the cells referenced within the formula of the calculating cells. Let’s try it out with a practice.
In this practice we are going to begin in cell F3 and build formulas to calculate the markup of the items listed on the sheet, the percentage of the markup, the items sold, and the gain/loss. I have attached this worksheet to this blog if you would like to download and use it for this practice. It is saved to be compatible with versions of MS Office or MS Excel dating all the way back to 97. If you use a newer version simply save it to the version you use; or keep it in compatibility mode if you wish. Follow along and perform each step as it is stated. If you do not wish to perform the practice as you review the instruction feel free to share the post to your favorite networking site (twitter, facebook, RSS feed, etc) for future reference. You also have the option to subscribe to this blog (absolutely no charge) and receive automatic updates with each new tutorial posted. Click Simple Calculations – Template for Download to get the practice worksheet or you can get it from the grab box at the bottom of this blog; the file is called ‘Simple Calculations – Template for Download.’
Regardless as to when you decide to do the practice, I only ask this of you – “Make sure you read each task instruction to its entirety before performing.” Many instructions come with NOTES of importance that will minimize your errors while performing them. This ensures your full understanding prior to your attempt to perform and you’ll get more out of it. Let’s begin.
Once you have opened your worksheet go to F3 by clicking on the cell or typing it in the name box and hitting the enter key on your keyboard. Follow these steps (Each step will have an image below to reassure you that you are on the right track.):
1. Strike your = key (NOTE: Do not hit the enter button or any other key – move on to next step. If you have already hit another key and your cell does not look like the one in the screen below hold down your Ctrl key, strike the Z key, release the Ctrl key. This will undo you last move. I give you this process because it works in all versions of Excel for as far back as I can remember. Other ways of making this same function occur differ from one version to another. If you know them already, great. Once you have undone your mistake, click on F3 and try again – strike your = key)
2. With your mouse point and click cell D3 (NOTICE: the cell reference of the cell you just clicked on appears in cell F3. See below. Again, do only as instructed. If you make a mistake at any point in the adventure, simply perform that Undo procedure explained in step one and try again. Your active cells should always appear as mine does in the image presented as you perform each instruction) Now with these reassurances in place, let us pick the pace up a bit.
3. Strike your – sign
4. With your mouse click cell E3 (NOTICE: the formula that tells this cell how to calculate the data in your sheet for this item reads =D3-E3. This tells Excel to subtract the value of cell E3 from the value of cell D3. The value is whatever you make it. Say you purchased your comic books for resale at $1 per comic book. You are selling them for a $2 price. This formula will provide the difference, which is the mark up (or mark down if on clearance).)
5. Strike the tab key (NOTICE: the formula in F3 became a value of $1; which is the difference, the markup/down. Recall the dealer’s retail was $1 and the buyers price was $2 which revealed a markup of $1. If the item was on clearance and priced at .75 cents to clean up inventory the outcome would have been a -.25 or (.25) mark down. If you are following along, take a moment to click on D3 and change the price from 2.00 to .75 and watch as the value of cell F3 changes from 1.00 to (.25) or -.25. Make sure you change it back to 2.00 so that what you see on your worksheet as you go through this tutorial will match what you see in this tutorial. You can always go back and play with the numbers when we are done.)
Now let’s determine the markup percentage. Percentages are determined by dividing the smaller by the larger. So we will divide the retail by the price. With your active cell being G3 complete the following steps:
7. Strike your = key and with your mouse point and click cell E3
8. Strike your / key
9. With your mouse point to and click cell D3 and strike the tab key (Notice: the markup was calculated at 50% of its current price.)
It’s the end of the week. You are taking inventory. The beginning balance is what you had at the start of the week. As you take inventory you note what you have remaining in the worksheet. And now you want the sheet to calculate how many items were sold during the course of the week. With your active cell being H3:
10. Strike the = key and with your mouse point to and click cell B3
11. Strike your – key and with your mouse point to and click cell C3. Strike your tab key. (NOTICE: that apparently you sold 5 comic books all week long)
Considering the mark up and the number of items that were sold you now want to determine how much profit your store made. You will do this by multiplying the number sold by the markup value. With your active cell being I3 perform the following:
12. Strike the = key and with your mouse point to and click cell H3
13. Strike your * key
14. With your mouse point to and click cell F3
15. Strike the tab key (NOTICE: that selling 5 comic books at the markup value of $1 generated a gain of $5.)
Now that you have entered the formulas for this one item, is it necessary to repeat these all the way down the entire list? Most stores lists get pretty extensive if the stores have any size to them at all. Luckily the wonders of Excel have a very quick solution to this task. To fill these formulas in all the way down the entire list perform the following tasks:
16. With your mouse point to and click without releasing the mouse button cell F3; with the mouse button held down, drag through cell I3, and then release the button. This will highlight the select range of cells from F3 thru I3 as shown below. (NOTE: recall the undo procedures in step one if you make a mistake while performing this task.)
Notice that at the lower-right corner of your selection (the range of highlighted cells) there is a small square (see image at right). If you were to place your mouse over that square just so it will become what is known as a cross-hair. It will look like a bold plus sign +. Now leave your selection highlighted and perform the following steps: 17. Without clicking on any cells, position your mouse over that square mentioned above until you see the cross-hair +. Once you see the cross-hair you will press and hold the left mouse button and drag it down until you reach the bottom of your list. (NOTE: As you are doing this, you will know that you are on the right path if it appears that a growing border is appearing as you move downward, see below.)
18. Once you have reached the bottom of your list you may release the button. Upon releasing the button values will appear in all the cells and the range of selection/highlight will have grown as far down as you dragged and released. (NOTE: If you find that you released too soon, don’t fret and don’t undo, simply point to the square at the lower-right corner of the new selection and pick up where you left off.)
Now that you know how much you made off each item, what about overall. There is a very quick 2-click solution to this. At the bottom of the table you will notice a row labeled as Total Gain/Loss. This means that this row is reserved to reveal the total amount you gained or loss in sales this week. This also means that you will want the value of the total to fall in the column it represents, which is the gain/loss. So with that in mind perform the following:
19. With your mouse point to and click cell I20.
20. Then locate the AutoSum button on your toolbar or ribbon; it will look similar to
or in older versions of Excel it may only show the symbol 
. Once you have located it, click on it once. Do not click or strike anything else until you perform step 20. (NOTICE: that the range of cells immediately above your active cell has a scrolling marque border and that your active cell has a formula in it =SUM(I3:I19). This means that this cell will sum (add) all the values found within the cell range of I3 thru I19. As a rule Excel will automatically pick up the range of adjacent cells above first; if none exist above it will pick up the range of adjacent cell to the left. Any other options you prefer can be easily chosen simply by highlighting the desired range while the formula is active as it is below. You might want to play around with this feature after we are done. Only one more step.) 
21. Strike the enter key; your total gain/loss value will appear. It appears that your store profited $266.75 this week. Save.
Below is an image that shows what the formulas in your worksheet look like to Excel. Notice that during the fill-down process Excel automatically adjusted to formulas accordingly by changing the row indication in the cell references for you as you filled down from row to row. This will also occur when you are filling in from column to column; it will update the column indicator in the cell references and you progress from one to the next.
As you toy with Excel you may find creative ways to manage your data. During the process there will come a time when you don’t want the indicator to change for a particular reference. In this case when building your first set, the set you intend to use for filling down, you can place a $ sign before the indicator you don’t want to change in the applicable cell reference. This will prevent Excel from updating that indicator. For example: take for instance this formula =$C$3*D5 as I fill down cell reference C3 will always be C3. D5 however, will change from row to row (D6, D7, D8, and so on) or will change from column to column (E5, F5, G5, and so on) depending on the direction I am filling in.
Now, answer me these riddles:
Can you recognize and identify a cell reference? What is a cell reference’s purpose? Can you identify Excel’s basic operators of arithmetic? If asked, could you build a basic formula for a calculating cell? Try it.
Keep in mind that you are welcomed to post inquiries, special requests, comments, and recommendations. I try to post at least once a week and you are welcomed to subscribe for email alerts so that you don’t miss out on any tutorials.
I love mathematics and have trouble understanding why aptitudes rise and fall in developed nations over a few years. It leads me to question the survey methods, of course. :)
ReplyDeleteMultiplication worksheets