Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I inherited an Excel workbook from a colleague which left the company. Somehow, this colleague managed to hide or delete all columns to the right of a sheet. For instance, currently this worksheet ends at column BG -- it's not that column BH is empty, it is not shown at all. Whether it has been hidden or deleted, I have no idea. Said colleague can't be reached to ask how this was done, and even if reached, probably wouldn't remember anyway.

Periodically I must add two columns to the right of this sheet, copying these columns from the last two, so that the cells referenced by the formulas are also shifted. For instance, suppose I currently have some in BF6 a formula like "=AnotherSheet!C6", and in BG6 a formula like "=AnotherSheet!D6", where AnotherSheet is some other sheet in my workbook. What I need to do is create columns BH and BI and populate them with a copy of the contents of BF and BG, respectively. Returning to the example, BH6 should contain the formula "=AnotherSheet!E6" and BI6 should contain the formula "=AnotherSheet!F6".

Now, if BH and BI weren't hidden, or deleted, or whatever, I could just select BF and BG, then copy and paste it into BH and BI. Formulas would be shifted exactly in the way I need them. Since I can't do that, I have to insert new columns. However, columns are always inserted to the left of the current selection (I haven't found a way of inserting them to the right). Hence I end up with "=AnotherSheet!A6" in BF6, "=AnotherSheet!B6" in BG6, "=AnotherSheet!C6" in BH6 and "=AnotherSheet!D6"; i.e. the original contents of BF6 and BG6 are carried over without change to BH6 and BI6, and since new cells are inserted to the left, formula contents are shifted left as well.

Can anyone suggest a way to either:

  • recreate or unhide all columns to the right that were deleted or hidden?
  • insert columns to the right of the current selection?
  • some other way to quickly create 2 columns to the right that are a copy (with formulas correspondingly shifted) of the current 2 columns?

If it's not asking too much, no VBA solutions please. And in case it makes a difference, this is on MS Excel 2011 for Mac.

share|improve this question

3 Answers 3

Here is a solution I came up with, but I am certain there must be a better solution.

Starting with a sheet ending at row BG, such that BF6 contains the formula "=AnotherSheet!C6" and BG6 contains the formula "=AnotherSheet!D6":

  1. Select columns BF and BG and copy them (Ctrl+C)
  2. Right click on the selection and click on "Insert Copied Cells"; now the sheet ends in column BI
  3. Select columns BH and BI
  4. Right click on the selection and click on "Insert"; this shift BH and BI to the right, creating BJ and BK, while BH and BI become blank columns
  5. Select columns BF and BG and copy them (Ctrl+C)
  6. Select columns BH and BI and paste (Ctrl+V) the columns copied on the previous item
  7. Select columns BH and BI and copy them (Ctrl+C)
  8. Select columns BJ and BK and paste (Ctrl+V) the columns copied on the previous item; now BJ6 contains the formula "=AnotherSheet!E6" and BK6 contains the formula "=AnotherSheet!F6"
  9. Select columns BF and BG, right click on the selection and click on "Remove"; now the sheet ends in columns BI

The result is as requested in the question: BF6 contains "=AnotherSheet!C6", BG6 contains "=AnotherSheet!D6", BH6 contains "=AnotherSheet!E6" and BI6 contains "=AnotherSheet!F6".

I'm still open for a better suggestion.

share|improve this answer

Select both the column before and after the hidden ones by holding down the left button then right click and select unhide, alternatively select resize and set the size to 50.

Alternatively, you can select all -A and either un-hide or format->set col width.

share|improve this answer
    
The problem is that the sheet ends at column BG. There simply don't exist any other rows to the right of BG. It's not that they exist but are empty. They don't exist at all. Try creating a new workbook and scrolling to the right until you reach column XFD; to the right it's just a gray void. That's exactly what happens here, but in column BG rather than XFD. The difference is that I can select BG and insert a new column and it works, whereas doing that with XFD doesn't work since there's probably a size limit. –  swineone Jul 6 at 17:43
    
Alternatively, you can select all <ctrl>-A and either un-hide or format->set col width. –  Steve Barnes Jul 6 at 18:54
    
I tried selecting the whole sheet and then going to Format -> Column -> Unhide, but it had no effect. Perhaps these rightmost columns were actually deleted and not merely hidden. –  swineone Jul 6 at 18:55
    
If they were deleted then you could not reference them, to find out you could export the sheet as .csv and load it into a good text editor then count the commas on a single line. –  Steve Barnes Jul 6 at 18:59

Click in the column BG column header.  Then, while holding down the mouse button, drag the mouse to the right.  A tooltip saying “16326C” will appear; release the button.

       

You have now selected 16,326 columns – BG through XFD.  You should now be able to Unhide them.

(Tested on Excel 2007 on Windows 7.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.