In this video, we're just very quickly going to run through four efficiency tips that you might not be aware of in Excel. So let's dive in. Did you know that you can copy values directly from the status bar? If we select values in our worksheet, I'm just going to select this profit column.
Notice that in the status bar, we can see various different calculations. SUM, MIN, MAX, AVERAGE, COUNT, COUNTA, all of that good stuff. But did you know that we can copy these numbers directly from the status bar? For example, if I just want to output the sum of these cells into a cell, I can simply click my mouse.
On sum in the status bar, click in the cell, press Ctrl V, and I have my number.
If you have a range of cells that contain formulas, you can see here I have some calculations in column F, where we're multiplying the value in column E by 20%. Now if I want to do something else with this data, it might be that I want to keep the value and throw away the formula underneath. So a way that we can do that is to press Ctrl Shift Down Arrow to select the entire column, Ctrl C to copy it, and then we can use the new shortcut key to paste the values only.
And that shortcut key is Ctrl Shift V. We're going to paste them directly over the top, Escape to come out, and now when we click on the cell, if you look in the formula bar, The formula is gone, but the value remains. If I want to do a calculation in cell H4 and add up everything in the profit column, I can type in equals SUM and then I need to select that profit column.
Now, most of us are aware of the keyboard shortcut CTRL, SHIFT, down arrow. But what happens when we get down to the bottom? If you're using your mouse to scroll back up, then you're doing it the long way round. All we need to do is press Ctrl, backspace, it's going to jump us back up to where our formula is, and we can then continue on, close the bracket, hit enter, and we have our result.
You may not be aware of named ranges in Excel. Named ranges allow us to select a range of cells and give it a meaningful name. For example, I could name this entire range of cells Agent ID to make it easy to identify. And named ranges are beneficial because we can use them for navigation and we can also use them in formulas.
Now if I wanted to name all of these different ranges that we have in this little data set, I could simply select everything in here, go to formulas, And choose Create from Selection. I could then tell Excel that I want to use the labels in the top row as the names for each of the ranges. When I click on OK, and then go to the Name box, I should be able to see all of those different ranges in here.
So if I select Area, it's going to show me that range. If I select Profit, it's going to show me that range. And if I'm clicked somewhere over here and want to quickly jump back to this data, I can use those named ranges as well to quickly jump there. We can also use the name of the range in a formula instead of using the cell references.
Now, none of this that I've told you so far is the tip that I wanted to share. Because what you'll find is that if you use a lot of named ranges on your worksheet, sometimes you can't always remember what the name of the range is. Particularly if this is a worksheet that's been given to you by somebody else.
For example, if I wanted to add up the revenue, I could type in equals SUM. Maybe I want to use the named range. Well, what we can do here is press the F3 key on our keyboard. That's going to pop open this little dialog box, and then we can choose the name of the range. So I want to calculate everything in the revenue column.
I can simply double click to select it. Press enter and my calculation is done. So those are four very quick tips to help make you more efficient when you're working in Excel.
