You’ve seen how to create formulas using letter and number cell addresses, b2+b3 and so on with excel. And you can also create formulas using row and column labels. For example this summer we had a special promotion during June and July to get groups of people to come to our show. We want a formula on our worksheets that shows the group ticket sales for June and July. We select the cell where we want the formula to go, we want the formula to add this two cells. We could use their cell addresses but instead we’ll use the row and column labels.
We type in equal sign to start the formula then we type June group plus July group and then we press enter. The formula is created and the result is displayed notice that Excel changes the capitalization to match the labels. Next to the formula we type the label promotion and since we want to move to the cell below instead of pressing enter we press the down arrow key.
The formula we just created which Microsoft called a natural language formula is often easier to create and easier for others to understand. We’ll create another one in a minute but first we’ll show you a short cut for entering labels. We want to type a label that says full percentage. When we type the F the rest of the word full displays in the cell, if we start typing some text that matches an entry in that column excels auto complete feature fills in the rest. We can just press enter to complete it. Then to finish this label we press the F2 key to edit the cell contents, type what we want to add and then press enter. We use the right arrow key to move to our next cell.
Now we’ll create a formula that shows what percentage full price tickets were for each months sales. We’ll create a natural language formula and this time it will be even easier. The formula in the June column will be the June full amount, divided by the June month amount. Since they're all in the same column we don’t even need the column name, we type the equal sign and then full divided by month when we press enter the result is shown. It’s a little more than 74% shown as a decimal. We’ll format this as a percentage in the next topic, now we’ll copy this June formula to the other months and this time we’ll copy with the keyboard.
For quick keyboard editing we can cut with CTRL X, copy with CTRL C and paste with CTRL V. The formula is selected so we copy it with CTRL C then we select the next cell, we extend our selection by holding down the shift key moving as far as we need to and then releasing the shift key. And then we paste with CTRL V the formula is copied, notice that the mark key is still on showing that we could paste this formula somewhere else if we wanted to. Typing or editing data will turn off the mark key or we can just press the ESC key.
Using label names and formulas makes them easier to read and make cell addresses easier to remember. Another way to use names in a formula which is a specially useful when were working with the range of cells is to use range names. We’ll create arrange name for monthly total cells in the summer sales worksheet. We’ll name it summer, then we’ll use the range name in a couple of formulas. First we select the range of cells we want to name the monthly totals. To name the range we’ll use the name box on the formula bar. We click in the name box and type the name summer then we press enter our range is now named.
Now we'll use the name in a formula that will sum the monthly totals. We select the cell where we want the formula to appear and type in equal sign. We type the sum function name then we type a left parenthesis, the range name and the right parenthesis. The parenthesis display in bold for a moment showing that they are a match set. We press enter the sum of the cells in the named ranged is displayed. Now we’ll use the range name to figure the average of our summer monthly sales totals using another functions the average function.
First of all type a label for the average, we select the label cell and type average we press the right arrow key to enter the label and move to the results cell, we type in equal sign and the function name average, then a left parenthesis. If you use range names a lot its often how—a list of them to make sure you insert the range name you want. We open the insert menu and select name. Since were in the middle of creating a formula the only range name command that is available is paste. We select it and the paste name dialog box opens, we double click on the range name we want and its pasted into our formula. Now watch what happens if we make a common mistake, forgetting to type the right parenthesis. When we press enter excels formula auto correct feature fixes our mistake and completes the formula. The average is displayed.
If the mistake is obvious like that one, auto correct will just fix it otherwise auto correct will suggest a correction and let you decide what to do. Our worksheet now shows the data we want but it’s a little too crowded. Remove the promotion and average information down a few rows. We select those cells, we could use the cut and paste features but there is an easier way. We point to the selection border with the mouse pointer displaying as an arrow.
We click and hold with the mouse and then drag the selection to where we want to. A fussy outline shows where the selection will go and a screen tip shows the range address. When the selection is where we want it we release the mouse button our rows phasing is better now. This method of moving selection is called the drag and drop you can also copy with drag and drop.
[Demonstration]
Were going to copy with drag and drop you know there are some people I’d like to drag and drop. We want to copy the full percentage formula to the total column we select it point to the border and then hold the control key. The pointer displays with the plus sign to show that were copying, we drag to the new location release the mouse button and then release the control key the formula is copied. Using labels and range names can help you to create formulas and here is a tip, range names can even help you move around the worksheet. Clicking on the drop down arrow in the name box will display a list of range names, clicking on a name removing right to that range.
Transcription by:
Scribe4you Transcription Services