jump to navigation

Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet July 28, 2009

Posted by computertrainer in Excel Tips.
Tags: , ,
add a comment

If you have entered data in columns/rows at the right or end of your spreadsheet’s data area and later deleted that added data, Excel will remember that there was once something in that column or row … and remember … and remember … until you take action.

 In the example below, the data ends at D45 but when I pressed CTRL + END the mouse went to G75.  At one time there was data in column G and row 75.

 Excel ctrl + end to empty column row

I would like to fix it so that CTRL + END will take me to the actual end of the data, in this case D45.

 SOLUTION:

  1.  Select the empty columns that you need to remove.
    (Remember to check each column to be sure it is empty … start in E1 then press CTRL + the down arrow.  If you end up at the bottom of the spreadsheet – row 65536 in Excel 97 to 2003 or 1048576 in Excel 2007 – then the column is empty.  Then move over one column and press CTRL + the up arrowto continue checking – you should be in row 1.  Repeat as necessary.)  
    In this example you need to check then select columns E, F, and G.
  2. Right-mouse click on the selected columns and press Delete.
  3. Immediately SAVE. 
    NOTE:  This is the critical step.  Without pressing SAVE at this point, you might as well have not done the previous steps.
  4. Now select the empty rows 46 to 75.  Since you can see these rows over to column D (now the last column with data) you do not need to check each row to be sure it is empty. 
    If you want to check … just to be sure, start in A46 then press CTRL + the right arrow.  If you end up in the right most column (IV in Excel 97 to 2003 and XFD in Excel 2007), then the row is empty.  Go down one row and then test that row with CTRL + the left arrow.  You would continue until you checked down to row 75 in this example.
  5.  Right-mouse click on the selected rows and press Delete.
  6.  Again, SAVE immediately.

 Now you can test your work.

  1.  Press CTRL + HOME and you will be in the cell at the upper left of the active worksheet (A1 unless you have Frozen Panes).
  2.  Press CTRL + END and you would be in D45.

 Excel has finally forgotten that there was once data in row 75 and column G!

Print the ‘Field Titles’ Row at the Top of Each Page July 20, 2009

Posted by computertrainer in Excel Tips, Office 2007 Tips.
Tags: ,
add a comment

If you have an Excel sheet that prints on more than one page, it is helpful if the titles print at the top of each page, not just on page one.

Unfortunately you cannot set this feature up from Print Preview.

For Excel 97 – 2003:

  1. File, Page Set Up, then the Sheet Tab.
    Excel Rows to repeat at top
  2. At Rows to repeat at top, click the red marker at the end of the area then click on the row # (#’s) you want to repeat at the top, then press Enter.  You can then select Print Preview from this box to see how it works.

For Excel 2007:

  1. Click on the Page Layout Ribbon, then in the Page Setup group, select the Print Titles icon.
  2. The dialog box above in the 97-2003 instructions appears.  Follow the step 2 above.

NOTE:  You can also repeat columns on the left of a printed page.  This is helpful is the data will not print one page wide.

Change the Color of Messages in Your Inbox July 13, 2009

Posted by computertrainer in Outlook Tips.
Tags:
add a comment

When you check your e-mail, are you looking specifically for messages from a particular person?  You can easily find them at a glance by color-coding messages from that individual.

  1. Select an e-mail you want to change to a color when it arrives
  2. Click Tools on the menu bar
  3. Select Organize
    Outlook Organize
  4. Click Using Colors from the Ways to Organize Inbox area
  5. Color messages from (their e-mail address will appear here) in (select a color – you have 15 but white is useless as is Silver and Yellow, in my opinion)
  6. Click Apply Color
  7. Continue with other e-mails and colors, as desired
  8. When done, click the X on the far right corner of  Ways to Organize Inbox

Now you can quickly see when e-mail arrives from that individual!

Modify Word 2007’s Normal Style – Get Rid of the Spacing July 6, 2009

Posted by computertrainer in Office 2007 Tips, Word Tips.
Tags: , ,
add a comment

Word 2007’s default Normal style includes formatting for spacing after the paragraph and a line spacing larger than Single.

To adjust the Normal style to have no spacing after the paragraph and be single spaced do the following:

  1. On the Home tab, the Styles group, locate Normal
  2. Right-mouse click on Normal
  3. Click on Modify
  4. On the bottom left of the style box, click Format, then Paragraph
  5. On the Indents and Spacing tab, change the Spacing After: to 0 pt (zero)
  6. Change the Line spacing: to Single
  7. Click OK
  8. Click the radio circle in front of New documents based on this template that appears at the bottom of the Modify Style dialog box
  9. If you want to change the font type or size, do it now.  All future documents you created based on this default template will conform to the changes you make.
  10. Click OK