Excel Tips & Fundamentals Video

Excel Tips and Fundamentals

In this first exercise, we'll take a look at some common calculations.

Average & Max

You can use to come up with the total average and high score for a list of students who taken five tests. So I'm going to start with the total. Let's start with the first student. Alex, we want to be able to come up with is total a simple way that you could come up with his total to simply to go to the home tab.

Head over to the editing group and click auto sum. Then you'll press, enter and we have his total. Now I'd like to come up with the average. I could also use the autosum functions that are also available on the formulas tab. I'll click the dropdown because I want to go beyond some and actually choose average. I'll click average.

Now I need to be careful here not to go on automatic pilot and simply press enter. I want to notice that the cells being selected are the tests, but also the total. So I don't want to include the total. So I'll just reselect and only select the tests.

Now, I could press enter, but I'll press tab because that's gonna take me to the next cell. Now I want to get the high score. Here's another method that's available to you. You can simply select the cells you want to calculate.

Then you'll go to the auto sum function that you're looking for. In this case, I want to go to Max that will tell me what the maximum score is. And it will add that value in the next empty cell. The next empty cell to the right is high score. So with one click of Max, I get my total right there. Actually, my high score.

Copying Results

So now that I have all three test scores for Alex, I want to be able to come up with the same results for all the other students. So I'm going to select all three and go to the autofill handle, which is the black plus sign that shows up on the bottom right-hand corner when you hover your mouse over that square.

I'll click and drag down and there are my calculations. Another method that's available to you simply to double click on the black plus sign. And that will copy those values down. And yet another method that you can use using keyboard, the keyboard shortcuts are to simply hold onto the shift key pressed the down arrow key and you'll select multiple blank cells as well as the original formulas.

The keyboard shortcut to copy the formula down is control D as in the word down. And then let's take a look at another way you can copy formulas. But this time form a horizontal orientation. Now I'll need to get the total for test one. There is a keyboard shortcut for auto sum. That's very easy to figure out.

All you have to do is hover your mouse over the auto sum function and Excel will pop up a little tooltip that will let you know that the keyboard shortcut for auto sum is alt equal. So in the current cell that I'm in if I hold onto último press equal, it would be the same as if I had clicked on autosum.

So now I'll press enter and there's my total, but I want to copy that value across. Horizontally and come up with the total for test two, three, four and five. Now, if the keyboard shortcut to copy down is control D, well then you can probably guess the keyboard shortcut to copy a formula to the right is going to be control H. Ah, now don't worry about all the methods that I just mentioned for copying formulas.

If you go over to column and you'll notice that the letter M is missing between L and N and if you click the plus sign. We have some tips here that were hidden until we reviewed this particular topic. All right. Let's now take a look at formula, auditing formula.

Cell Auditing

Auditing allows you to audit your formulas and fix errors. So here I have a formula that gives me the percentage of the total, but I'm only seeing the percentage of the total for the first value for the rest of the values. I get a division by zero error. So I'm going to use the F two key to take a look underneath the formula to see what's going on.

So F2 on your keyboard and if you're on a laptop you may need to use the function key. So I'll press f f2. And it displays the values that make up the result. What I notice is I am selecting 500 and I'm selecting the value underneath the total.

I'll press escape to release that selection and go up. Let me take a look. The original values. That's the big difference. I am selecting the total in the first formula, but when I move down a row to select the value for tablet, I'm also moving down a row for the total. So I need to make sure that the total does not move. I need to lock its position.

Keyboard shortcut for that is f for by press. F foreign press. Enter. The result is the same. But when I use autofill. I get different results for the rest of the values and they're actually the correct results. Now there's a little green triangle next to the total.

This is Excel way of letting you know there is an inconsistency in the pattern. In the pattern that gives me the result. So let me click and take a look at the yellow diamond, error says formula omits adjacent cells.

I could press F2, but let's use another method called trace precedents on the formulas tab. If you head over to the formula auditing group and click trace precedents, you'll see visually the values that make up the result. And here what I notice is that I'm not including the price of the keyboard so I can go into the formula or go over to the edge.

And move this down one more row. I'll go into the formula by pressing F2 and simply drag the edge. And you'll need to see a double arrow and you have to be in just the right position and I can drag that down. Now when I press enter, that will update my total. It also updates my percentages.

Let's move over to 700. I may be interested to see what formulas are, quote, dependent on seven hundred. So.

Trace Dependents

On the Formula tab in the same area, you found trace precedents. If you look directly underneath it, you'll find trace dependents. What this does is it shows you the formulas that are dependent on seven-hundred for their result. So this is another way to audit the formulas in your spreadsheet. If I want to remove the arrows right underneath, trace dependence is remove arrows.

In the next exercise, you want to figure out why are we not getting the first letter of each name in this list so I could press F. I could also use trace precedence or trace dependence. What I'm going to do in this case is to use a function or an option called show formulas. If I click on show formulas. It shows all the formulas in the spreadsheet here. I can look underneath and see what is happening and what I notice is that F 34 is locked.

So the values are not changing and I'm not able to select Mary, Keith Carroll, and Steve. So we have the opposite problem here. We need to remove the lock on that cell. So out backspace on the dollar sign and remove them for the first value and press enter. Now I can use autofill and the function will be able to automatically increment the cell references. So I'm picking up each of the names.

Now I'm not seeing the result here because I'm still in show formulas. So I want to remove show formulas. I'll click. On it, and there I can see the result. And then for this last exercise, if I press F2, the issue here is that the columns are not the same size. So there are certain formulas that require the arguments in your function to be exactly the same size.

So I'll go over to K 34 and A through 36 and extend it to include the value for avocadoes. And when I press enter, I get the final result, which is the total price of ten apples at 50 cents. Fifteen argies of the dollar.

Twenty bananas at twenty-five cents and ten avocados at a dollar fifteen. So in this section, we just reviewed the ways that you could work with the auto sum functions and also formula auditing to troubleshoot and fix errors in your formulas by using key auditing tools.

Learn More in Hands-on Training

Want to master Excel in just 3 days? Check out our Excel Bootcamp where you'll go from beginner to expert-level Excel in 3 days with hands-on training. If you are not in NYC, check out our Excel class live online. If you want to get Excel training for the whole team, check out our Excel Corporate training.