Recently tried to defrag my computer using the Window's defragger, but large chunks of the hard drive could not be defrag. So, I went looking for a professional defrag program. I tried 4 different ones, and was most impressed with Defaggler. It was simple to use, and totally defrag the hard drive in a very short time. I personally recommend this product.
http://www.defraggler.com/
--Rob
Because today is Christmas Eve, I thought I would share a few little Word and Excel stocking stuffers with you!
For instance, want a quick way to fill an entire column with a formula? Enter your formula in the first cell of the column. Select that cell. Not press Ctrl+Shift+the down arrow key. This will select the column all the way to the end. Press Ctrl+D and your formula will make its merry way down the column.
Need to insert a row or column and just don’t feel like moving away from the keyboard? Press Ctrl+Shift+= and the Insert dialog box will appear.
Want to add a page break without going through the rigmarole of going all the way up to the Insert menu? Ctrl+Enter will do the trick for you.
You can quickly scroll through pages of a Word document by pressing Ctrl+Page Up or Ctrl+Page Down. This will take you directly to the top of the next or preceding page.
Promptly remove tabs from a document someone sent you – or one you created but need to reformat – by selecting the entire document (Ctrl+A is a quick way). Double click the middle of your horizontal ruler and the Tabs dialog box will open. Click Clear All and you are all clear!
Double click elsewhere on the ruler (you do not have to have the text selected) and you have yourself the Page Setup dialog.
Finally, double click the small square button just above the scroll bar on the right and you have a split menu!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
Hey there and happy Monday! For those of you in the midst of celebrating Hanukkah and Christmas, I hope the weekend was not too hectic for you and that you are enjoying all the wonder of this season. Today’s tip comes from reader Gayle Larson, who is always generous with her oh-so-rocking insights. Gayle writes:
Want quick options for copying that picture in Excel?
Applies to versions through 2003…
The SHFT key (known as one of the booster keys) can change the behavior of some commands on the Menu Bar
Here’s a trick for a quick Copy Picture command in Excel:
Click on the graphic. Hold SHFT key and click on Edit on the Menu Bar. The Copy command now says Copy Picture…
Release SHFT key and click the command to display a mini dialog box that lets you copy it as a picture or bitmap and as shown on screen or as it prints...
Make desired selections. Click OK. Click on destination cell. Use desired method to Paste. (If you need more options than this, use the Edit | Paste Special instead.)
More tricks with the SHFT key:
Holding SHFT key when clicking on File in Menu Bar displays Close All command instead of just Close. This allows for closing multiple files but leaves the Excel program open. (In MS Word, both Save All and Close All commands display on the File Menu).
Holding SHFT while clicking certain icons on the toolbar(s) will reverse a command, i.e., SHFT Open icon actually saves; SHFT Save actually opens; SHFT Print = Print Preview; SHFT Print Preview prints your spreadsheet. This can be handy if you are missing icons or just want to use a minimum of icons to accomplish normal tasks.
One that works in all versions of Excel: Make a formula Absolute in the Address Bar. You may already know that pressing the F4 function key repeats your last action but the behavior changes if you are in the Address Bar in a cell with a formula. Click in a cell reference part of the formula (in the Address Bar) and press F4…Presto—instant Absolute $$! Press F4 three more times and cycle through making just the row$ or column$ Absolute, then back to Relative cell addressing.
Thanks, Gayle, for the impressive tips. And thanks to all of you for reading!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
Last week we talked about Excel’s Row function. As always, you all shared some tips that were just too good not to share! So here goes. Phil Williams writes:
To make the automatic row number a bit more robust, title the cell above the area that contains the numbered rows
Name this cell, say "START_LIST"
Now use formula =ROW()-ROW(START_LIST)
Now if cells are deleted above the start of the list it will automatically detect the change without having to alter a cell that contains the manipulation variable.
Very sharp, Phil! Mark Yount’s idea is similarly wise:
In reference to your Excel ROW() function newsletter, another way to ensure that the rows remain numbered beginning at one, use this formula in each cell, where cell A8 is the #1 row:
=ROW() - ROW($A$8) + 1
You must be sure to make the cell reference absolute using the $. I'm not sure why, but if you add or delete rows above the first row, this absolute reference is adjusted correctly to keep the reference to the first cell so the first row is always numbered 1.
It saves having to enter beginning row number into a different cell elsewhere on the worksheet.
Thanks Mark. Rod Laughridge made a great point, too. Rod writes:
One caveat: if you start at a row other than Row 1 and subsequently delete rows above the numbered ones, your numbering will be skewed. However, you can remedy this by entering the digit of the row number into a different cell elsewhere on the worksheet and use the cell number as a reference in your formula. For instance, if you are starting in Row 27, enter the number 26 into cell K130, your formula would be =ROW()-K130.
In this example, you have to make K130 absolute, otherwise when you copy it down, it won't refer to this cell.
Nice job, Rod. Finally, I just had to share Joanne Winfield’s brief yet terrific tip:
You can also number columns using the =column() function. Very cool!
Very cool, indeed! Thanks Joanne, and thanks to ALL of you who took the time to email me with your numbering tips. I’m sorry I don’t have room for them all here, but I sure do appreciate you!
See you online!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
In the past, we have talked about lots of different shortcuts to use in Excel. Today, though, I thought it would be fun to go over what the Function keys do. You know, those “F” keys that run across the top of your keyboard? I think they just do not get enough attention. So let’s start at the top, shall we?
F1 by itself opens the Help task pane. In Excel 2007, Ctrl+F1 shows or hides the Ribbon. In 2003, it shows or hides the current task pane. Alt+F1 creates a quick chart using data that is selected, and Alt+Shift+F1 inserts a brand new worksheet.
F2 moves the cursor to the end of the selected cell, and Shift+F2 adds a comment. F3 opens the Paste Name box, and Shift+F3 is a quick way to insert a function.
F4 will repeat your last action, while Ctrl+F4 closes your window. F5 opens the Go To dialog, and Ctrl+F5 restores the window size.
In Excel 2003, F6 switches to the task pane or second window, if you have a split pane. In 2007, it toggles between the sheet, the Ribbon, the task pane and the zoom controls. If you have a split pane, that will be in there between the Ribbon and the task pane.
F7 brings up Spell Check. F8 turns on (or off) extended mode, allowing you to use your arrow keys to select cells. F9 calculates all open workbooks, and Shift+F9 calculates the active worksheet.
F10 turns key tips on or off, which allows you to move around the toolbar or Ribbon with your arrow keys. Ctrl+F10 maximizes or restores the window. F11 (one of my personal faves) creates an instant chart. Finally, F12 brings up the Save As dialog.
And a partridge in a pear tree!!!
See you online!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
Merge from Excel into Word Table
Reader Shani Nunkumar asks:
I want to merge the data from an [Excel] spreadsheet in separate rows in a word table – how can this be done?
This is a cool feature in Word, Shani, so I am glad that you asked. It is not too much different than performing a standard mail merge. Really, there are only a few steps you will do differently than you would in a typical mail merge. And none of them are difficult steps. The first thing you will want to do is create a table on your new, blank document. If you are not sure how to do so, go to the Table menu in Word 2003. Point to Insert and click on Table. In Word 2007, click on the Insert tab, click the Table button and choose Insert Table. You will only want to make one row to start off, but insert as many columns as you will need in order to merge your data from Excel.
When you get to the part of the mail merge process that asks you to select a document type, choose Directory. Make sure to select Use the Current Document. When your task pane shows Arrange Your Directory, insert the merge fields into the appropriate cells of the table. When you get to the Complete the Merge section, click To New Document on the task pane.
Voila! You will now have a Word table that you can adjust, format or even copy and paste into another existing document.
See you online!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
A reader named Melissa asks:
I have a question. I want to number rows. I highlight 1 and 2 and drag so I can get to 100 or so. [However] if a row gets deleted, the numbers are off. How can I prevent [this]?
Good question, Melissa. I am sure many of us have experienced this problem. You enter the numbers 1 and 2 into two cells of a column and drag it down, down, down to fill up all the numbers you need. You enter other data and formulas. Later you decide you no longer need the info in Row 42 so you select it, right click and delete. Only problem is, now your list jumps from 41 to 43. You could delete start from scratch, or you could use the Row function.
Here is how to fill a column with succeeding numbers using the Row function. If the row you are starting with is indeed Row 1, the process is super easy. Simply select cell the first cell and enter the formula =ROW(). Then all you need to do is drag the fill handle down as many rows as you like, and they will all be filled with the appropriate number. Once that’s done, try deleting one of rows. You will see that, no matter how many you delete, the numbers will still be in order.
If you are beginning your column on a row other than Row 1, you will need to do just a little more work. You will need to subtract one from the row number you are starting in, so that it comes back with the number “1” in the first row. Therefore, if you were beginning in Row 27, you would enter the formula =ROW()-26 into the cell in Row 27. Pull the fill handle down to copy your formula and you are good to go.
One caveat: if you start at a row other than Row 1 and subsequently delete rows above the numbered ones, your numbering will be skewed. However, you can remedy this by entering the digit of the row number into a different cell elsewhere on the worksheet and use the cell number as a reference in your formula. For instance, if you are starting in Row 27, enter the number 26 into cell K130, your formula would be =ROW()-K130.