Join Dennis Taylor for an in-depth discussion in this video Becoming more productive with these 10 tiny tips you'll use often, part of Excel Tips Weekly.
- In Column A of this worksheet are 10 tips. They're short. They're designed to make your use of Excel more proficient. I'm going to cover them more or less in order. Press Control Enter to keep the active cell in place. I'm about to write a formula in cell C2. It's going to calculate a new salary. As soon as I finish that formula, I want to copy it down the column. If you press Enter when typing a formula it automatically moves the active cell downward. I don't want that to happen. So here's the formula I'm about to write. =B2xD1 I need that to be an absolute reference.
I want the entire column to contain the reference to D1. I'll press the function key F4 to make that an absolute address, and I'll complete the formula by putting in + and then click cell B2. Rather than pressing Enter, which would move the active cell downward and complete the formula, I want the formula completed, but the active cell to stay in place. I'll press Control Enter, and then I'll simply double click to copy this down the column. If I want to format it quickly, I'll just use the Comma button here. That's on the Home tab in the Number group. Then readjust the column width by double clicking the column boundary between C and D.
Don't capitalize function names as you type them. I want to know the median of the new salaries here, =median. I'm not typing these in capital letters, don't need to. When I complete the entry the letters will automatically be capitalized. I'll refer to Column C in the example here, and no reason to type a right parenthesis. When you're using functions by themselves, in other words you're not nesting them, no reason to type that. Just press Enter or Control Enter. Either way, Excel will automatically put in that right parenthesis for you.
If we revisit this by simply clicking it, looking in the formula bar, or double clicking, we can see what's happened. No reason to capitalize those, or to type the right parentheses, unless you are nesting or using multiple functions together. Don't use the Collapse button in dialogue boxes. Some of you might not even be aware of what that feature's all about. At different times when you're using dialogue boxes, you're invited to collapse the dialogue box. But nearly all the time you don't need that. Now I probably have not used every single dialogue box in Excel.
There could be an exception to this, but I certainly haven't found it. Here's one case where it could be used. In Column K, I'm about to set up what's called a pick list, by way of data validation. I don't want anybody typing anything in Column K. I want them to pick from a list. Currently the list is right here. Eventually I'll move that off to the side or put it on a different worksheet or hide the column. So in Column K I'm about to set up a data validation rule. Data tab, data validation, and the kind of validation criteria I'm using here under Allow will be List.
Now, here's the collapse button right here. In older versions of Excel, you needed to use this at times, in other words to get the dialogue box out of the way, to collapse it. Now even if I have the data sitting here, or like that, as long as I can see what I'm about to highlight, I don't need to use this at all. In other words, I'm not going to be using this button. Nothing wrong with using it. Here's what happens when you do it. But suppose I ignore that. The source of the list here, and we see this blinking cursor right here, I'll simply highlight the data in the background.
As I highlight this, the dialogue box automatically collapses, and I'll simply click Okay. Now we have this pick list set up. If you've never seen this before, it's a great feature. I'll click here and use Contract. You can use Alt Down Arrow and other techniques. Eventually, of course, you probably wouldn't want the list to be visible, but it's certainly okay for now. Tip number five. No need to select a column or a row. Let's imagine that I'm about to change the format in Column B. Now I can click Column B and then right click and go to Format Cells.
Or why not just right click Column B. Of course, in some cases I might want to be deleting the data, at other times formatting the cells. Maybe I'll go to Format here. It could be a number format, or any of the other possibilities here. Maybe I'll change this to currency, or some other feature out here. Maybe I'm concerned about other features and so on. I'll just pick the one I want, and maybe I do want decimals now. Something like that. Double click if I want those there. But I didn't have to click Column B first and then right click. I simply did a right click that activates the selection and automatically causes the Shortcut menu to appear.
If you need to format cells, select them, and then right click those cells to activate the mini Tool Bar. Now right now the Home tab is not active. Maybe I've forgotten that. I'm going to change the format of these cells right here, so I highlight them, and then right click. The mini Tool Bar appears either above or below the Shortcut menu. You could actually think of it as part of the Shortcut menu. Maybe I wanted to make these bold, or italic, or add colors, or do other things with them. These are features that you'll find on the Home tab, but I don't have to activate the Home tab right now.
Again, when you highlight data and right click you do get that mini Tool Bar. That's really handy at times. Tip number seven, press Shift F10 to activate the Shortcut menu. Well, don't you just right click to activate the Shortcut menu? Well, you surely can, but there are those times when maybe the mouse isn't working, or maybe your hands are on the keyboard. You want to get to things a little bit faster. Maybe these cells here, for example, are still highlighted. Now, if you do want to highlight these, by the way, from the keyboard, you could go right here using arrow keys.
Hold down the Shift key and start using arrows. This is not too efficient with large amounts of data, of course. Now I want to get to the Shortcut menu. The mouse isn't active, or batteries are out, or whatever. I'll simply press Shift F10, and then use the arrow keys moving up and down the list. Maybe I want to do some sorting. Probably wouldn't want to do that here, but Delete, or go to some other options. Maybe I want to copy the data. We have access to the Shortcut menu by way of Shift F10. Format an entire column, not just the data.
If I click Column C, I'm looking in the status bar at the bottom trying to figure out what the average salary is. Now ultimately I can read that number down there, but it's a little bit tricky to read. Same thing with the total. What are we paying out in salaries here? In other words, we're not seeing them being formatted. Each of these cells is formatted, but the heading isn't. Now why would you say, or why would we want to say, let's format this as a number? Because it allows us to read information in the Status Bar more easily. Whatever format I have here, maybe I've changed my mind, I don't want to see the commas here.
So I'll go to the Home tab. Remember I've selected the entire column. I'll simply use comma here. I like that option better. Then off to the right decrease the decimal. Does it really make any difference that Cell C1 has a format like C2 and C3? Does it hurt? Well, obviously it doesn't hurt based on appearance. Now we have the advantage, as I've clicked Column C, what do we see in the Status Bar at the bottom? That's $81,236 is the average. I can see the total, and I can see some of these other statistical measures down here that are activated any time you highlight two or more cells.
So just by selecting the entire column when we apply the format makes this easier to read. Tip number 10, No need to select cells for a chart if those cells are surrounded by empty cells and/or worksheet boundaries. If I want to create a chart from this data here, it's certainly not wrong to highlight the data, and sometimes, of course, that could be a substantially larger list. All I need to do is click inside. The reason is, these cells are surrounded by empty cells. So it's not going to pick up any extraneous data that might be on the worksheet.
So I'll click right here. If I want a chart on a separate sheet I'll press F11. If I want a chart right here, it's Alt F1. And there it is. These tips, all short, are going to be helpful in the long run. You'll use a lot of them every day. It will make your work with Excel more proficient.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.