My favorite trick of all time would have to be the F4 Function key. Most people I know forget that there are function keys at the top of the keyboard. But they come in very handy at times, especially the wonderful, versatile F4. F4 provides the repeat functionality. It works in Word, Excel and PowerPoint to repeat the last function you did. For example, if I type a word into Word, select it and click the B button in the formatting toolbar to make it bold, to repeat this action, I simply select another word, and hit F4. So instead of continually having to click the B button, you can just continue to select, and hit F4. Of course keep in mind, F4 will repeat the last action you did, whatever that action was.

In Word, I recently used F4 to paste the same word multiple times in a table. I simply copied and pasted it once, and then clicked in the next cell, and hit F4, and so on. I use F4 in Excel all the time to copy formatting changes. Excel can be tricky when you start formatting cell borders. It’s sometimes difficult to tell which cell has the border, for example, in the formatted cell in the figure, it appears that the middle cell has a surrounding border. However, what you could be looking at is the top middle cell with a bottom border, the bottom middle cell with a top border, a middle left cell with a right border, and a middle right cell with a left border. Why do I care?!!? you ask. Well, it makes a world of difference when you try to copy the format, either through format painter, copy and paste, or the handy-dandy F4 key. So just keep in mind that looks can be deceiving, and if you try to copy the format with the F4 key and it doesn’t work the way that you wanted, it’s actually not a bug. I’ve had this problem in Excel more times than I care to recall. I simply had to fix the formatting and move on. The thing to keep in mind, too, about the F4 option is that it will only copy the last action you did. For example, if I highlight a cell in Excel, click on the border button on the formatting toolbar, then click on the fill button on the formatting toolbar, then click the B button to bold it, F4 will only repeat the last action, the bold format. The trick to get around this limitation is to simply format it the long way one time, and use F4 for all subsequent formats. The long way of course, is to go to Format on the menu bar, and click Cells… As you can see, there is a plethora of choices on how you can format your cells. I showed you the Alignment tab because there are a lot of neat choices here many people don’t know about. The second drop-down, for example, is vertical alignment. This allows you to center the text vertically as well as horizontally. Another neat option is the Wrap Text checkbox. This allows you to make the width of a cell smaller, and make the height larger, but still be able to view all text. 

And of course you can’t forget about text orientation – it looks pretty cool when you tilt your headers at a 45 degree angle for your charts. So that’s just the Alignment tab, you can feel free to make changes on every other tab – I’ve been spending the most time in the Borders tab and Patterns tabs to give my cells custom borders and patterns you can’t get from the toolbar buttons. I make all my changes in the Format Cells dialog box, and then when I click OK, I can proceed to use the F4 key to duplicate absolutely all of the formatting changes I just made, because the Format…Cells was my last action. Believe me; making seven changes to the Format…Cells dialog every time I need to format a cell is a lot harder and more time-consuming than simply clicking the F4 key. That’s why I usually type all my data in first, and save all the formatting until the end. F4 works the same way in PowerPoint. You can open a dialog box like Font and make multiple changes in there, then simply click F4 to copy all changes in one keystroke.

Tip: Wondering where the custom color option is in Excel? Yeah, I was wondering about that too. I’m talking about the fact that when you do a fill in Word or PowerPoint via the Drawing toolbar…Fill Color button and click on the drop-down, you get the More Fill Colors option. When you select that, you have the option to click on the Custom tab to create your own color. Yes, this is something I use all the time and have stayed up nights wondering why it’s not available in Excel. OK, not really, but in my occasional ponderings, I became frustrated enough to figure out a workaround. To get a few of your favorite custom colors to be available as fill color choices, go to Tools…Options on the menu bar in Excel, then click on the Color tab. This displays all of your current color selections you see when you click on the Patterns tab of the Format…Cells dialog box (you will also see the top-most colors in the Fill Color drop-down on the Formatting toolbar – all the colors listed above the line in the Standard section). Click on one of the color boxes that you hate and know you will never use, and then click the Modify button on the right. This brings up the Colors dialog box, much like the one you see in Word or PowerPoint. Click on the Custom tab, and pick your own color by moving the crosshair and adjusting the hue arrow on the right-hand side. Click OK to exit the Options dialog box, and check the fill color choices by click on the drop-down arrow. Congratulations! You’ve replaced an ugly color with a custom color you can now use in any fill operation in Excel.

My next favorite trick would have to be the fill handle in Excel. I know I’ve covered this before, so I won’t go into heavy detail about it; I’ll just give the basics. The fill handle is the little black square in the lower right-hand corner of a selected cell or range of cells. When you point at it, your normal white cross cursor turns into a small black cross. When you see that, you can click and drag it to copy and paste very quickly. It copies and pastes formulas relatively and also detects patterns and fills those. For example, if I type Monday in the first cell, and then click and drag the fill handle down, I will see that it detects that I want to use days of the week, and proceeds to fill them in downward. You can see what it looks like as you’re dragging down — it shows you on the right what day you are on as you drag down. It also works for months and quarters very nicely. In addition, you can do number patterns if you show it the pattern first. For example, if I want to start at 100, and show hundreds only until 1000, then I need to show it that I want to skip by hundreds. To do this, I enter 100 into the first cell and 200 into the second cell. Then I select both cells and use the fill handle of both selected cells to drag down until I see 1000 on the right. I can’t begin to calculate how many hours of typing this feature has saved me.

Tip: You can populate all the cells of a range with a single value very quickly in Excel. You simply select the range first, type the value you want to repeat in the range, and click Ctrl+Enter immediately after you enter the value. This will copy and paste the value in every cell in the entire range. Quite a timesaver for entering repeating data!

Well, now you know my most cherished time-saving tips and tricks for Office. These are the ones I use practically every day in my Office adventures. I hope they can save you as much time as they have for me!

I hope that my Office column has been useful to you this past year. I appreciate those of you that wrote in with questions and comments. It’s been a pleasure writing this column; thank you very much.