1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Column Borders Missing

Discussion in 'Business Applications' started by Delphi123, Oct 16, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear friends:

    Please see screenshot. For some reason, the column borders are missing. The entries are copies from another Excel list.

    Would appreciate your help.

    Thank you.

    Benjamin
     

    Attached Files:

  2. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    what's the problem?

    have you tried extending the column/row borders and it doesn't work?
    OR
    you need to know how to do it?

    if the second:
    - highlight the cells in the row above the last correct column
    (ie click in column A and drag across all columns to the end or click on the button to the left of the column (the row number) to highlight the entire row)
    - click on the paintbrush icon (the format painter) in the menu at top
    - then click on each row to copy/paste the format (ie column borders) from the correct one to the incorrect one(s)
    (you can also click and drag down the rows to get all the rows you want

    another way is to highlight the borders you want to repeat, click copy, then paste special, formats - does the same thing, just a different method

    caution: make a copy first as this will copy/paste ALL formats used - numbers, text etc - which should be all right but might mess up any formulas or cell formating that is different row to row
     
  3. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear jbcalg and friends:

    Thanks so much for your kind help. I've tried to follow your instructions but I am having problems. There may be another reason for this. Please see error message in screenshot at URL:

    http://www.websher.net/Excel1.jpg

    And here is the original screenshot at full resolution at:

    http://www.websher.net/Excel2.jpg

    May I ask if this is just a cosmetic issue? What could have caused it? What's the best way to correct it?

    Thank you so much.

    Benjamin
     
  4. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    again, always work from a copy, so you don't mess up the original :)

    looks like you have data from another worksheet or file (according to the error message) - so by pressing OK with the error message, you run the risk of corrupting the linked data, how much of a risk i don't know, depends on the external source

    a couple options:
    1. go ahead and click OK, and refresh, then use one of the copy & format paint options in my first post to format paint the worksheet the way you want to - caution that some data may go goofy on you (that's a technical term btw;))

    2. if it's a static worksheet (ie the data doesn't change in the linked sheet) then copy/paste, values for the external data so it's all in the worksheet you're working with - caution, it looks like the data is filtered in some ways in the external sheet before it's brought over, you may/will lose that filtering

    you may want to store the external data (ie copy it) over to a worksheet in the workbook instead of in a separate file, the linking and troubleshooting is easier that way usually

    hard to tell which way to go, not knowing how the external source/sheet is set up or being used.

    i'd go with option 1 and then do some eyeball audits to make sure the data comes across ok

    again, make sure you have a copy of the original before you muck around too much.

    and overall - yes, it's just a cosmetic issue - the borders make it easier to read the individual records (ie rows), may not be too important if you're reading on screen, but useful for others if it's printed. looks like a large file (1700+ records) so it'd be a lot of printed pages
     
  5. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear jbcalg:

    Once again, my deep thanks for your kind help. First, let me explain that the error message makes no sense to me. The original Excel file was created on my wife's computer using Excel 97. Other than the missing column border issue, there is NO error message on this document when opened on her computer. On the other hand, when I transfer this document to my computer (by copy to and pasting from a diskette) and open the document by clicking on it in the diskette, it opens with this error message. My wife tells me that she created a new Excel document called Terrace Lake with about 40 entries and then copied and pasted them into the ALL mailing list. I cannot imagine why would this error message appear on my computer. It does NOT appear on my wife's computer when open the same document.

    Any suggestions what could be causing this?

    Thanks so much.

    Benjamin
     
  6. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear jbcalg:

    I finally succeeded in following your instructions concerning the paintbrush formatting, but in doing so I discovered that I had not only copied the formatting but ALL the data, which means that I found myself with 40 identical entries. What did I do wrong? How do I copy the formatting without copying the data?

    Thanks again.

    Benjamin

    P. S. How do I indicate that I want to be notified when I someone responds to my forum message?
     
  7. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear jbcalg:

    Note: My wife uses Excel 97. I use Excel 2002.

    Benjamin
     
  8. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    The error message appears to suggest that on your wifes computer, some of the data is actually held in another spreadsheed, and referred to from that one.

    She would not get the error as the data is still on her computer, just in another file, but being displayed in that one.

    In your case, you only have the displayed one and not the source of some entries.

    Select the entire spreadsheet, then Copy - Paste Special - Values to remove all links and formulas, and simply retain the data as shown.

    Always make a backup copy before playing with the data.

    To fix the columns, simply highlight the areas and Format - Cells - Border. Select the border format you want.

    Regarding the format painter, you chould not have duplicated the entries unless you used a Copy command. The format painter only takes the attributes, and does not require any other function to make it work. Highlight a cell that you want to base the format on. Click on the format painter button. Now, when you highlight a cell, or an area, the format is transferred the moment the mouse button is released, then the function is cancelled.
     
  9. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear Kiwiguy:

    Thanks so much for the excellent advice. Fixing the column border was a no-brainer. Thanks so much.

    Yes, I made a mistake and used the Copy function along with the Paintbrush. That's why it copied the data as well as the attributes. Will avoid that in the future.

    Finally, concerning the error message:

    Your explanation makes sense. But I was unable to follow your instructions exactly as you explained them. I highlighted everything by clicking on the top of the first column. Then, I selected, Edit, Paste Special (or Edit, Copy, Paste Specil). But I did not see "Values" anywhere. Could you please spell out precisely where "Values" is in Excel 2002?

    Thank you so much.

    Benjamin
     
  10. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    [edit: d@mn you guys are quick, here's what i was going to say, some still applies i hope, thanks for jumping in kiwi!]

    email notification is one way to be notified, click on the box directly below, under options

    On the other hand, when I transfer this document to my computer (by copy to and pasting from a diskette) and open the document by clicking on it in the diskette, it opens with this error message
    - try saving the file to your hard drive, not using the diskette/floppy at all
    - i haven't seen that specific error message before, but looks like excel 'thinks' there's some connections to another worksheet or area of worksheet
    -you could try copying the entire workbook you want (top left row/cell button selects the entire sheet), then paste special, values into a new worksheet or on top of the existing sheet

    ...not only copied the formatting but ALL the data
    sounds like you just did a copy/paste, not a copy, paste special?
    if using copy/paste special for formats
    - select the area, rows, columns, etc
    - copy
    - paste special (i have an icon cuz i use it so much, from the main menu it's under Edit)
    - formats
    [note: the default is ALL, need to change how much of the cell format - values, formulas, format, etc - you're actually pasting when using paste special this way]

    [​IMG]

    if using the format painter tool (paintbrush looking icon)
    - highlight/select the formatting area, ie entire row in this case
    - click on the paintbrush icon
    - click and drag the cursor across the cells to you want to paint

    [​IMG]

    (probably easier to do the whole row - click on the row button, click on the paintbrush icon, click on the row you want to have 'painted' with the same format - with practice you can click on a sinbgle row of formatted cells, then click the icon, then click and drag down multiple rows, which applies the singlw row format to the multiple rows you highlighted)

    some of these things are harder to explain in words than they are to do! :)
     
  11. Delphi123

    Delphi123 Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    28
    Dear jgcalg:

    A million thanks for your expert advice. Finally, thanks to your second post with the screenshot, I figured out how to use Select All, Copy, Paste Special - Values. I did just that, saved the Excel document, reopened, error message GONE!

    Sorry I didn't answer sooner. Didn't get notification. I was trying to find my user profile where I could select Email Notification as default. I see that you actually have to enter this each time you post a message or reply. So, I just did that for this message.

    All my thanks to you and to everyone at TechSupportGuy!

    Benjamin
     
  12. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    glad it worked out

    come by TSG anytime ;)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/172272