Excel 2010: "freeze" multiple, non-adjacent columns?
Thread poster: Erik Freitag

Erik Freitag  Identity Verified
Germany
Local time: 03:47
Member (2006)
Dutch to German
+ ...
Nov 4, 2014

Dear colleagues,

In Excel 2010 on Win7, I am looking for a function to have multiple non-adjacent columns "fixed" or "frozen", so that only the columns in between will scroll horizontally.

Example: Columns A and Z fixed, columns B to Y will scroll horizontally.

Google research seems to indicate that this is not possible, but maybe proz peers are smarter?

Many thanks in advance for any hints!

Kind regards,
Erik


Direct link Reply with quote
 

John Fossey  Identity Verified
Canada
Local time: 21:47
Member (2008)
French to English
Can't be done Nov 4, 2014

AFAIK, it can't be done. The thing is that if column Z were fixed but B to Y not, where would Y go when you scroll it to the left? Leave a blank space between the fixed Z and the now-scrolled Y?

Direct link Reply with quote
 

Erik Freitag  Identity Verified
Germany
Local time: 03:47
Member (2006)
Dutch to German
+ ...
TOPIC STARTER
Thanks! Nov 4, 2014

Thanks, John!

John Fossey wrote:

AFAIK, it can't be done. The thing is that if column Z were fixed but B to Y not, where would Y go when you scroll it to the left? Leave a blank space between the fixed Z and the now-scrolled Y?


Yes, that'd be one possibility (the other one being that I can't scroll any further, just like I can't scroll further to the left when the cursor already is in column A.

Sad - that would have been so nice. Background: Column A is a given value. It consists of the sum of columns B-Y, the values of which I have to determine manually. Now I want column Z to be a checksum. I'd like to keep the same layout as a corresponding paper document, so moving the checksum to the beginning of the row is not an option.

Would it be possible to have the entire column coloured in red (or something similar) as soon as the checksum isn't zero? That way, I wouldn't always have to scroll to the end of the row in order to see if the numbers add up.

Thanks again,
Erik


Direct link Reply with quote
 
Terry Richards
France
Local time: 03:47
French to English
+ ...
Yes Nov 4, 2014

You can do that with conditional formatting.

Direct link Reply with quote
 
Charlie Bavington  Identity Verified
Local time: 02:47
French to English
Additional temporary column? Nov 4, 2014

Erik Freitag wrote:

Sad - that would have been so nice. Background: Column A is a given value. It consists of the sum of columns B-Y, the values of which I have to determine manually. Now I want column Z to be a checksum. I'd like to keep the same layout as a corresponding paper document, so moving the checksum to the beginning of the row is not an option.

Would it be possible to have the entire column coloured in red (or something similar) as soon as the checksum isn't zero? That way, I wouldn't always have to scroll to the end of the row in order to see if the numbers add up.



I've had similar requirements - a need to check equal values in 2 columns more than 1 screen-width apart.

I've used a temporary extra column, where I put a formula to show a "*" if A and Z are not equal
IF(AZ, "*", " ") or something like that. Or the opposite! IF(A=Z, " ", "*")
You can then see the "*"s. Indeed, if the spreadsheet is large and you think you might not spot all the "*", you can then filter to only display rows with a "*".

Edit to add that I was forgetting this forum displays unusual characters in unusual ways. Or not at all

Basically, the first formula is is A not equal to Z, then put a * character, otherwise nothing. Or you could reverse it and say if A equals Z, do nothing, else put a *.

[Edited at 2014-11-04 15:36 GMT]


Direct link Reply with quote
 

Oliver Walter  Identity Verified
United Kingdom
Local time: 02:47
Member (2005)
German to English
+ ...
Split window & perhaps also view a copy Nov 4, 2014

The following is not exactly what you are asking, but is partly equivalent. It works in Excel 2000, so there must be a similar facility in later Excel versions. Perhaps it's already well known but, if not, it will interest somebody, I suppose.

Immediately to the right of the scroll bar at the bottom of the window that displays the worksheet, is a small upright bar. When the mouse is hovering on it, the pointer becomes a vertical double line with left and right arrows. Click on this and drag it to the left. This splits the window into two parts, each with its own scroll bar; you can scroll either of them left and right without affecting the other one.

Unsurprisingly, you can do the same with the "split window" bar at the top of the vertical scroll bar, to obtain a part-window in which you can scroll through the rows while still viewing the fixed set of rows in the other part-window. In fact, you can do both, and have the window split into 4 parts. To end the split-viewing function, drag the bar separating the window parts back to its starting position.
The original question would be satisfied if two such splits could be present at the same time, but that appears to be not possible.
If you're not editing both columns A and Z of the spreadsheet while doing this, you could make a copy and then open both the copy and the original. Then you can drag the edges of the windows on these two sheets to see them at the same time; use one to view column A, and split the window of the other one so that column Z is fixed in one part and you can scroll in the other part of the window.

HTH somebody
Oliver


Direct link Reply with quote
 
Charlie Bavington  Identity Verified
Local time: 02:47
French to English
Ah-ha Nov 4, 2014

Oliver Walter wrote:

The original question would be satisfied if two such splits could be present at the same time, but that appears to be not possible.


Rings a bell - I think you can have a freeze AND a split at the same time. That would do it.
Freeze A, split at B, whizz over until you can see Z. Job's a good'un


Direct link Reply with quote
 

Erik Freitag  Identity Verified
Germany
Local time: 03:47
Member (2006)
Dutch to German
+ ...
TOPIC STARTER
Thanks, but... Nov 4, 2014

Thanks, Oliver and Walter, sounds like a good idea. Unfortunately, real life is more complicated than my example. I actually need columns A, B, and C as one fixed/"frozen" block on the left hand side, and columns AX and AY as one block on the right. The split window method works fine, but it seems I can only freeze column A on its own. Looks like two vertical splits should do the trick, but this seems to be impossible?

I'd be grateful for further ideas....


Direct link Reply with quote
 

Laura Harrison  Identity Verified
United Kingdom
Local time: 02:47
French to English
+ ...
If it's just for a check value, Nov 5, 2014

can you not hide a group of columns temporarily?

Plus you can freeze as many columns to the left of/rows above the cursor as you wish when you freeze panes.

HTH


Direct link Reply with quote
 

Dan Lucas  Identity Verified
United Kingdom
Local time: 02:47
Member (2014)
Japanese to English
Conditional Formatting might help Nov 5, 2014

Erik Freitag wrote:
Would it be possible to have the entire column coloured in red (or something similar) as soon as the checksum isn't zero? That way, I wouldn't always have to scroll to the end of the row in order to see if the numbers add up.

Sure. Including checksums and sanity checks is good practice when you have a large sheet. Take a look at the screenshot below, which shows a Excel 2007 sheet. You can use conditional formatting to check if the checksum is zero and if it isn't zero, to colour the whole row, which may be more useful than colouring the column, because not all columns will be visible at any one time.

First set up a little dummy test sheet. In this example, column A holds the given value while column H contains a checksum that subtracts the value in column A from the sum of the values in columns B to G. It should be zero.

Then select all the cells that you want to be subject to the formatting, in this case A to G. Don't include the header row. Now click on the Conditional Formatting button in the ribbon and create a New Rule



Click on "Use a formula to determine which cells to format" and enter the formula as show in the box below. If you're using column AX for your checksum, use something like "=AX20" instead of H, of course.



And you get this result. The value in A3 should be 57, not 56, so the checksum is not equal to zero. Accordingly the formatting condition is fulfilled and the row turns orange.



Dan


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Excel 2010: "freeze" multiple, non-adjacent columns?

Advanced search






Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
PDF Translation - the Easy Way
TransPDF converts your PDFs to XLIFF ready for professional translation.

TransPDF converts your PDFs to XLIFF ready for professional translation. It also puts your translations back into the PDF to make new PDFs. Quicker and more accurate than hand-editing PDF. Includes free use of Infix PDF Editor with your translated PDFs.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search