|
|
 Topic: Project Management Tips & TechniquesThe new items published under this topic are as follows.
Tips: Bob Umlas' Microsoft Excel Tip, or Trick of the Week |
|
Published on Monday, February 10, 2003 - 11:41 PM |
Beginner's Excel Tip: Using the Fill Handle
The Fill handle is a facility for making entry of data much easier. It is available only if the bottom right corner of any selected range looks a little thicker and the cursor changes shape to a heavy "+" sign when hovered over this corner. It is possible that you do not see this thicker corner, and this could be due to your Excel settings: From the menus, look at Tools/Options then access the "Edit" tab, and ensure the checkbox named "Allow cell drag and drop" is checked.
OK, how do you use this? Well, let's try it out. Enter Monday in cell A1. After you press enter, click on A1 and notice the thicker bottom right corner. Move the cursor to this and when it changes to a "+", press the mouse and drag it down a few rows (or over a few columns), then let go. You will see Tuesday, Wednesday, …! Pretty neat! OK, now enter MON in a cell and try again. This time you see TUE, WED, THU, etc. Try again with January, or Jan or JAN or JANUARY. You could start with August, too. It's also pretty smart for this partial list:
- 1st (...2nd, 3rd, ..._
- 1Q2002 (2Q2002,...)
- 2nd Qtr 02 (3rd Qtr 02...)
- Part 1 (Part 2, Part 3...)
- 1995, 1996 (1997, 1998...) ß this one requires you enter 1995 and 1996 in 2 consecutive cells, select them both, then use the fill handle. (Or you could select one number and use the fill handle with the ctrl key held down to make the values increment)
- 1995, 1997 (1999,2001...)
You can also repeat a pattern of cells. Enter Jan in A1, leave A2 blank, enter MONDAY in A3 and make it bold. Now select A1:A3 and use the fill handle down. You get Feb in A4, A5 is blank, A6 is TUESDAY (and it's bold), A7 is Mar, etc. If you select A1:A4 initially and use the fill handle (instead of A1:A3), then the pattern is Feb, blank, TUESDAY, blank, Mar,...
Another very nice feature of the fill handle is that it "knows" how tall touching ranges are. For example, if you have data in A1:A55, you can type something in B1 and if you double-click the fill handle in B1 it will be the same as if you dragged the fill handle down to B55!
Lastly (for this tip, not for the fill handle!), try this: In B1 enter 9:00 AM. In B2 enter 9:30 AM. Now select B1:B2 and fill down. You get times in half-hour increments!
Play with this feature of Excel - it will become one of your favorite tips!
Bob Umlas is an Excel expert, author of more than 300 articles about Excel, and a Microsoft Most Valuable Professional (MVP) since 1995.
|
  |
|
Tips: Bob Umlas' Microsoft Excel Tip, or Trick of the Week |
|
Published on Monday, February 03, 2003 - 11:30 PM |
Beginner’s Excel Tip: Entering information with the Ctrl key held down
Normally, you enter data in Excel by simply pressing the Enter key. But if you have more than one cell selected when you type a value or formula, you call fill all the cells simultaneously by holding the Ctrl key down before pressing enter (or clicking the check-mark in the formula bar).
Try this: Select A1:C3, type “Hello”, and hold the ctrl/key and press enter. Every cell contains the word “Hello.” This is much quicker than entering it then using copy/paste or fill right and then fill down. If you enter formulas, using this tip on several cells has the same effect of entering the formula once, then selecting several cells and filling down (or using the fill handle) – that is, the formula would update. As an example, if you select cells A1:A3 and ctrl/enter “=F4” (without the quotes), then cell A1 would contain =F4, cell A2 would have =F5, and A3 would have =F6.
But this can also extend to cells which aren’t in one row or column. If you select A1:A3, hold the ctrl key down and continue selecting F2, then G3, then H4:H5, then whatever you ctrl/enter will fill range A1:A3,F2,G3,H4:H5.
Let’s look at a very useful application of this tip. Suppose you have a structure something like the following:
<img src="http://www.iil.com/allpmnewsletter/images/bobstable.gif"align="center">
Now suppose you want to sort the information by department, so that you have Admin then Personnel then Sales. (Also, let’s assume there are hundreds of rows and many more departments!) Clearly, sorting this would make a big mess. The first 3 cells in the first column would be Admin, Personnel, then Sales, and the last 6 would be blank. You really want to sort by groups, but Excel won’t do that. Before this tip you might try to tediously fill in the blank cells in the department column.
Here’s the fast way: Select the entire department column, then use the menu command: Edit/Goto…, then click the “Special” button, then select Blanks, the click OK. Now, without selecting anything new, type an equal-sign, press the keyboard’s up-arrow, then ctrl/enter. Voilà! Now it’s ready for sorting! (After the sort, you can reselect the department column, use the Goto…Special command again, select Formulas, click OK, the press the “Delete” key to remove the formulas in the department column if you wish).
Bob Umlas is an Excel expert, author of more than 300 articles about Excel, and a Microsoft Most Valuable Professional (MVP) since 1995.
|
| |
|
Tips: Bob Umlas' Microsoft Excel Tip, or Trick of the Week |
|
Published on Tuesday, January 28, 2003 - 03:21 PM |
Beginner's Excel Tip: Using the Format Painter tool
One of the toolbar buttons in Excel, which happens to exist in other Office products, makes it very easy to copy the formatting of ranges and objects to other ranges and objects.
Let's look at copying range formats first. Suppose you have cell C3 formatted as bold, dollar sign, 2 decimal places, and underlined. You could copy the formatting of this cell by using the menu command Edit/Copy, then select the destination range, then use the menu command Edit/Paste Special, then click Formats, then click OK. But it's easier to click cell C3, then click the Format Painter tool (on the Standard toolbar, about the 11th tool from the left, and looks like a paint brush). Now click your destination range. That's it! If your destination range consists of many cells, you can drag the cursor to these cells and "paint" the formats to all of them. The resulting cells will all be bold, currency with 2 decimal places, and underlined.
If you want to copy the format to many ranges which aren't together, you don't need to keep repeating this process, you can initially double-click the Format Painter tool, then wherever you click you will paste the format of the cell which was selected when you used the tool. To stop "painting", either click the tool once again or press the Esc key.
The message in the statusbar says "Use the mouse to apply the copied format to another object." (A range is an object).
It the initial range of cells to copy the formats from contains mixed formats, such as A2 is bold and A3 is red italicized text, then if you select A2:A3 and click the Format Painter tool, clicking in F6 will format F6 and F7, where F6 will be bold and F7 will be red italicized text! If you first select A2:A3 and click the Format Painter tool and then "paint" across D2:F4, D2:F2 and D4:F4 will be bold, the D3:F3 will be red italics!
For objects, if you have a textbox with italic bold text with heavy blue borders, and you have another textbox which is plain text and no special borders, you can click the fancy textbox, click the Format Painter, then click the other textbox and you've copied the formatting!
Bob Umlas is an Excel expert, author of more than 300 articles about Excel, and a Microsoft Most Valuable Professional (MVP) since 1995.
|
| |
|
|
Tip of the day:
Establish an environment where reporting bad news in a timely manner is encouraged rather than an environment where fear prevents the flow of critical information.
2009-10 allPM.com Editorial Calendar
Register for allPM
August Poll Question
Get Involved With allPM.COM
Submit your...
PM Glossary
Latest Forum Posts
|
