Excel 2010: "freeze" multiple, non-adjacent columns? Thread poster: Erik Freitag
|
|---|
Erik Freitag 
Germany
Local time: 03:06
Member (2006)
Dutch to German
+ ...
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
| | | | John Fossey
Canada
Local time: 21:06
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?
| | | | Erik Freitag
Germany
Local time: 03:06
Member (2006)
Dutch to German
+ ...
TOPIC STARTER
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
| | | |
You can do that with conditional formatting.
| | |
|
|
|
| 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]
| | | | Oliver Walter
United Kingdom
Local time: 02:06
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 ... See more 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 ▲ Collapse
| | | |
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
| | | | Erik Freitag
Germany
Local time: 03:06
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....
| | |
|
|
|
Laura Harrison
United Kingdom
Local time: 02:06
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
| | | | Dan Lucas
United Kingdom
Local time: 02:06
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
| | | | 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? | Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
| | Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
