Preserving formatting of merged cells when hiding columns

I have a spreadsheet that contains columns that my supervisor wants to
keep hidden when sending out to contacts. The problem is, there are
some merged cells further down the sheet that spread across those
columns, and hiding the columns messes up their formatting. Is there a
way to avoid this?
0
nxylas (3)
9/11/2008 5:30:53 PM
excel 39879 articles. 2 followers. Follow

8 Replies
361 Views

Similar Articles

[PageSpeed] 13

merged cells are the bane of Excel, figure out how to do without them.

Nick Xylas wrote:
> I have a spreadsheet that contains columns that my supervisor wants to
> keep hidden when sending out to contacts. The problem is, there are
> some merged cells further down the sheet that spread across those
> columns, and hiding the columns messes up their formatting. Is there a
> way to avoid this?

0
birelan (531)
9/11/2008 5:53:31 PM
You seem to wish to hide information rather than secure information. If so, 
a custom format may work for you:

A) Highlight cells containing values you want hidden
B) Format/Cells/Number and select Custom
C) Enter ;;; (three semicolons) into the "Type:" dialogue box

This will make values invisible but if someone clicks on a hidden cell any 
value or formula is visible in the formula bar.


HTH
-- 
Steve

"Nick Xylas" <nxylas@sc.rr.com> wrote in message 
news:cccd4c43-bd7b-46f3-93e6-4c1b83066871@r66g2000hsg.googlegroups.com...
> I have a spreadsheet that contains columns that my supervisor wants to
> keep hidden when sending out to contacts. The problem is, there are
> some merged cells further down the sheet that spread across those
> columns, and hiding the columns messes up their formatting. Is there a
> way to avoid this? 

0
AltaEgo
9/11/2008 10:49:13 PM
On Thu, 11 Sep 2008 10:30:53 -0700 (PDT), Nick Xylas <nxylas@sc.rr.com> wrote:

>I have a spreadsheet that contains columns that my supervisor wants to
>keep hidden when sending out to contacts. The problem is, there are
>some merged cells further down the sheet that spread across those
>columns, and hiding the columns messes up their formatting. Is there a
>way to avoid this?

What stops the client simply unhiding the columns?
-- 
Dave Mills
There are 10 type of people, those that understand binary and those that don't.
0
News11 (66)
9/12/2008 4:18:41 AM
On Sep 12, 12:18=A0am, Dave Mills <Ne...@nospam--djmills-dot-co.uk>
wrote:
> On Thu, 11 Sep 2008 10:30:53 -0700 (PDT), Nick Xylas <nxy...@sc.rr.com> w=
rote:
> >I have a spreadsheet that contains columns that my supervisor wants to
> >keep hidden when sending out to contacts. The problem is, there are
> >some merged cells further down the sheet that spread across those
> >columns, and hiding the columns messes up their formatting. Is there a
> >way to avoid this?
>
> What stops the client simply unhiding the columns?
> --
> Dave Mills
> There are 10 type of people, those that understand binary and those that =
don't.

They'd be password protected.
0
nxylas (3)
9/12/2008 12:33:23 PM
Excel's internal security is very weak.

Anyone with access to the Internet or these newsgroups can find a cracker
that would reveal your password in seconds.

If you don't someone to see it........don't include it.


Gord Dibben  MS Excel MVP

On Fri, 12 Sep 2008 05:33:23 -0700 (PDT), Nick Xylas <nxylas@sc.rr.com>
wrote:

>They'd be password protected.

0
Gord
9/12/2008 7:50:17 PM
On Sep 12, 3:50=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Excel's internal security is very weak.
>
> Anyone with access to the Internet or these newsgroups can find a cracker
> that would reveal your password in seconds.
>
> If you don't someone to see it........don't include it.
>

The people this is going out to aren't that tech savvy, and besides,
it wouldn't be a major disaster if they were to see the information,
it's just that we would prefer it if they didn't.
0
nxylas (3)
9/12/2008 8:29:45 PM
On Fri, 12 Sep 2008 13:29:45 -0700 (PDT), Nick Xylas <nxylas@sc.rr.com> wrote:

>On Sep 12, 3:50�pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>> Excel's internal security is very weak.
>>
>> Anyone with access to the Internet or these newsgroups can find a cracker
>> that would reveal your password in seconds.
>>
>> If you don't someone to see it........don't include it.
>>
>
>The people this is going out to aren't that tech savvy, and besides,
>it wouldn't be a major disaster if they were to see the information,
>it's just that we would prefer it if they didn't.

My preference would be to send a PDF but that's no good if they need Excel
functionality.
-- 
Dave Mills
There are 10 types of people, those that understand binary and those that don't.
0
News11 (66)
9/12/2008 11:05:44 PM
Nick Xylas <nxy...@sc.rr.com> wrote...
>On Sep 12, 3:50=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>>Excel's internal security is very weak.
>>
>>Anyone with access to the Internet or these newsgroups can find a cracker
>>that would reveal your password in seconds.
....
>The people this is going out to aren't that tech savvy, and besides,
>it wouldn't be a major disaster if they were to see the information,
>it's just that we would prefer it if they didn't.

There's no good way to do what you want to do using your current
layout.

If you'd prefer your recipients not see certain entries, don't include
them in what you're sending them. No need for hidden columns would
then mean no screwing up the formatting of merged cells.

Addressing the formatting problem directly, there's no way to fix
formatting in merged cells when you hide columns spanned by those
merged cells. Either don't use merged cells or don't hide columns.
Those are your choices.

0
hrlngrv1 (375)
9/13/2008 1:15:30 AM
Reply:

Similar Artilces:

Hiding formulas
I've finsihed my spreadsheet and I'm able to lock the cells I don't want people to mess with. How do I hide the formulas? Also, my workbook has more than one sheet. Can I hide formulas in one sheet, but not the other, etc.? The same would go for locking cells. Thanks On Nov 13, 4:27 pm, Drake <Dr...@Drake37.org> wrote: > I've finsihed my spreadsheet and I'm able to lock the cells I don't > want people to mess with. > > How do I hide the formulas? > > Also, my workbook has more than one sheet. Can I hide formulas in one > sheet, but not ...

How do I save a Word doc to the epub format?
Is there a download for this? Try Google: epub format... Roughly 1,000,000 hits :-) Regards |:>) Bob Jones [MVP] Office:Mac On 1/23/10 3:03 PM, in article 114A1769-E1F2-4EE1-A44E-38E2643F7699@microsoft.com, "Arnold" <Arnold@discussions.microsoft.com> wrote: > Is there a download for this? ...

Incrementing named cells
Merry Christmas everyone!!! Was wondering if any of you can help me with this. I have a financial model that currently has numerous named cells in the format of XXX_03 ( the 03 is to denote 2003 ) but I am making 2004 projection and therefore am wondering if there was an easy way to sort of copy and paste the names into a new column and automically renaming cells by an increment of 1 to XXX_04. If anyone can help, it will be greatly appreciated! Thank you Justin See one answer 1 minute later -- Don Guillett SalesAid Software donaldb@281.com "Justin" <jsim_hba2003@hot...

Using PERCENTILE with *specific* cells in a column
I am trying to use the PERCENTILE function with specific cells in a column. For example, lets say the column is: A 1 5 2 6 3 7 4 8 Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4. I can achieve this by defining a named range such that it would be equal to A1 and A4, but would rather not use named ranges, as the above is a very simplistic example of what I want to do -- using named ranges would require a substantial amount of effort to achieve my end goal. I also realize I could re-order the rows such that I swap A4 with A2, therefore could use perc...

Windows Explorer Columns
Hi, The 'Date Modified' column doesn't appear on any of my folders. I add it, and when i browse away from that folder and return to it, it's disappeared again. How do i get every folder to show that column by default? Thanks, Albert A known Vista annoyance, already solved in Windows7. There're several solutions invlving Registry edit which sets your Template for a specific filetype to what you want, not to MS default - if you Google or Bing for it it should showuo in search results. Sorry I don't have exact URL at hand. it i I know it's f...

Turning off all Automatic Formatting in Excel 2003
Hi Is there any way of turning off all automatic formatting in Excel 2003? For example Excel automatically changes strings such as 1-12 in a CSV file to 1-Dec, 10-10-2005 to 10/10/2005 and strings of numbers to an exponential representation. These changes are preserved when saving the file and thus corrupting it, preventing any other applications from reading it. I know you can import CSVs changing all the fields to text using the data import option, however reopening it and saving causes Excel to make the same changes again. Any suggestions would be really appreciated as this is caus...

Wrap Text in Merged Cell
Excel 2007 If I merge several (or even 2) cells in a row, I can not get the text to wrap when I hit enter. It works, of course, in a single cell. How can I get the text to wrap in merged cells? I have checked "Wrap text" in the Format Cells dialog box, Thanks, Bob Atkinson Long audible sigh here................. One more victim of "merged cells". Wrap Text works fine on merged cells, but Autofit does not work. You need VBA event code to do that. Here is code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cW...

CRM Integration requires NTFS formatted hard drive
Hi, When I'm trying to instal MSCRM Integration with Great Plains v. 1.2 results in this error -------------------------------------------------------------------------------------- Microsoft CRM Integration requires NTFS formatted hard drive. Please resolve the issue and re-start setup -------------------------------------------------------------------------------------- The integration server is running on Win 2000 Server SP4. The system partition and hard drive already using NTFS file system. Any suggestions? Thanx, David ...

Tab into specified cells
I'm looking to set up a worksheet so that when I open it up I can only tab into specific cells. Is there anyway of doing this? Thanks There are a few way. One is to unlock the cells you want to tab into (Format>Cells>Protection), and then lock the sheet (Tools>Protection>Protect Sheet) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "sho" <anonymous@discussions.microsoft.com> wrote in message news:644601c47543$7c069000$a401280a@phx.gbl... > I'm looking to set ...

Row/column grid lines
Can someone please tell me how to remove the grid lines from a single row or column Cheers Stuart. Hi Stuart, You need to use a fill color. Format>cells>pattern, pick the color closest to the background color. -- John johnf202 at hotmail dot com "Stuart" <anonymous@discussions.microsoft.com> wrote in message news:5CD88DAF-4CDD-4D04-8E9E-A50D9A319672@microsoft.com... > Can someone please tell me how to remove the grid lines from a single row or column. > > Cheers Stuart. Stuart, You can't remove the grid lines from a single row or column. Grid lin...

Find all cells with a number and mulitply
How can I find all cells in a sheet that are a number greater than zero and multiply each one by a number? IE: the number to multiply by is 1.2 A1 Tom A2 25 A3 0 G12 Bob G13 25 I want to programatically (without using a macro) find cells a2 and g13 only and multiply them by 1.2 Thanks in advance for your help. Jim Jim, As long as you don't have any negative numbers, it doesn't matter about being > 0 (0*n=0) or being text. Try this Put 1.2 in a spare cell and then copy it Select all the cells Goto menu Edit>Pastespecial Click the Multiply option OK out Now clear the c...

How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from totals in one column on my spreadsheet to show totals in another column on same spreadsheet. Can anyone help? Spent way to long trying to figure out correct formula. =A30*80% assuming totals in A30 -- HTH RP (remove nothere from the email address if mailing direct) "Full Effect Landscaping" <Full Effect Landscaping@discussions.microsoft.com> wrote in message news:7ACF893B-54DF-4EE4-A931-3BB5FA365821@microsoft.com... > I would like to know if someone knows the formula I use to subtract 2...

Cell comments
I have already created the code using VBA to pull the data from the M Access table. Is there a way to set it up so that when you pass ove the cell it will automatically run my routine -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 Hi not possible AFAIK as there's no event which got triggered by just moving over a comment field -- Regards Frank Kabel Frankfurt, Germany LAF wrote: > I have alr...

Merging cells #4
I am using an existing worksheet in Excel. Some cells have already been merged. I need to merge 2 more cells and i am unable to accomplish that. The "merge/center button" on the toolbar is not highlighted, so i am unable to merge the needed cells. Any suggestions? Any chance your worksheet is protected? brownk wrote: > > I am using an existing worksheet in Excel. Some cells have already been > merged. I need to merge 2 more cells and i am unable to accomplish that. > The "merge/center button" on the toolbar is not highlighted, so i am unable > to m...

Sometimes can see all text in a cell, sometimes not.
Hello On Sheet1, I can see all text in cells that have lots of text in them, but have cells to the right that are empty. On Sheet 2, I pasted the following formula into cell A1, and copied this into all of the cells on Sheet2: =IF(Sheet1!A1=0,"",Sheet1!A1) Now I can't see all of the text in a cell, even if the cell to the right of it is empty. Is there anything that can be done? I am copying these cells and pasting them into Word as a picture. Thanks! Its because if you type text into a cell it will spill over into empty cells so it can be read. If you then enter in a...

Excel 2002 annoyance
Greetings List I am using Excel XP amd Access XP I regularly run queries in Access and cut and paste the results into a new Excel spreadsheet. The Access data is usually formatted numeric, fixed, zero decimal places. Once pasted into Excel, it gains 2 decimal places and I get the little green triangle in each cell inviting me to convert to numeric of error check etc. Can anyone think of a way to retain the numeric formatting from the MS Access data? Regards George ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet mess...

Cell sizing
Hi everyone, I'm recently new to all of this so I hope this isn't a stupid question. Before using Excel, about 7 or 8 years ago, I used Corel Quattro pro. I was able to set the cell heights and widths in mm, cm , inches, points, etc., so that when I printed a section of a sheet it would be a specific dimension. I haven't been able to do this with Excel. Thanks for your help. Only MacXL 2004 allows you to set height and width directly in inches/cm/mm. Both platforms allow you to set row height in points. Column widths can be set in the number of widths of the zero character in...

Stop Chart Style (Format) Update
Hi TWIMC, I'm using Excel 2000 and I have a pivot table that aggregates data based on REGION, COUNTRY, ZONE etc etc. Once I have set up the chart they way that I would like it to look, if I then make a change to the selection using the pivot chart buttons, e.g. I select a country and the chart re-formats itself back to what appears to be the default style. I've set my chart style to the default but it still doesn't keep my chart style. What I want is that the chart style remains the same but the new values are just plotted onto the chart. I'm sure there is an option to turn ...

Format Problem #2
Hi how can i save an excel file to text file with out chainging the format. ie Excel file: adf 12 a 1 when i change this to text file the alighment changes like adf 12 a 1 so how can i solve this problem. i tryied many wayes and nothing work out properly. so if someone know this , plzz do help me. manikandan Try saving it as a "Formatted Text (Space delimited) (*.prn)" file. You may have to widen columns to make it look right. (And I like to change the font to Courier New--a nice non-proportional font. Then I can see how it'll line up.) manikandan wrote: > >...

Report Formatting & Writting
Hi Everyone, 1. I am just gettign to grips with writting .qrp reports in RMS and wondered if anyone knows if there is a WYSIWYG editor available to help speed up developement? 2. I want to be able to hightlight figures on reports in red if they are negative - is this possible? Tia 1) Nope, no WYSIWG editor. 2) No, you can't set the color, but you can use a standard VB Format String in the "ColFormat" field of the column definition. Here's teh MSDN Reference on number formats: http://msdn2.microsoft.com/en-us/library/4fb56f4y.aspx Glenn Adams Tiber Creek Consulti...

How to hide the dialog in a dialog based application..
Hi, I have a dialog based application which has MFC support.. (developed from class wizard...) Now, according to the my database situation, i want to show / hide the dialog.. so, what should i do ? What i do currently is, inside, oninit i start a thread and pass a dialog pointer.. now, after 500 ms of initialization, i will call ShowWindow(SW_HIDE) to hide the dialog.. and to work in invisible mode.. but this is not a good way.. so, any other way to hide the dialog before it gets shown.. ?? My exe should run in processes but it should not show the dialog only.. Hope you understand my...

Sort numeric data in more than 3 columns in excel
Hi All, I have to sort 12 columns of numeric data in descending order on a excel worksheet. I currently sort 3 at a time and then move forward. For example, I have sales data for months Jan to Dec. I want each of these columns to show zeros at the bottom when the macro is run. The rows would increase in future but the columns would be fixed. The worksheet would also remains unchanged each month. None of these columns are of least significance. Is there a way I could change a recorded macro to achieve this?? I know very little of VB. This would be of great help! Thanks! All of the columns ...

Average Cells with actuall numbers
Hey all, Hopefully this is a simple one. I'm SUMing a column: =SUM(L8 + L12 + L16) At times, some of these cells might be empty. Can I mod this equation to check for an empty cell? What I'm doing is averaging the numbers in this column by the number of cells that actually have numbers in them. (in this case it would be the sum of L8 + L12 + L16 divided by 3) If, let's say, L8 was empty, I need to divide the total by 2. Make any sense? Thanks for any help! -b Hi Bradley couple of things =SUM(L8+L12+L16) is quite an awkward way of writing either =L8+L12+L16 or =SUM(L8,...

autopopulate sl.no.based on a cell value
I have cell value in work sheet based on a function and it changes accordingly.Based on this cell value I want to populate a serial numbers in a column.suppose c1=15,Iwant to populate from d10:d100 serial number as 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 upto d24 only rest should appear as blank.Iwant this funtion as array that means d10:d100= formula.maximum cell value is around 90.Any idea to get this result. In D10 enter: =IF(ROWS($1:1)<=$C$1,ROWS($1:1),"") and copy down to D100 Hope this helps Rowan TUNGANA KURMA RAJU wrote: > I have cell value in work sheet based on a ...

how do i delete a * at the end of each word in a column
In a column I have all last names followed by * e.g. Jones* How do I delete the * Thanks in advance for your help Use Replace, and put ~* in the Find What box. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "celler6" <celler6@discussions.microsoft.com> wrote in message news:3C6160E7-7217-4D14-8671-0EA2513D501A@microsoft.com... > In a column I have all last names followed by * e.g. Jones* > How do I delete the * > Thanks in advance for your help =SUBSTITUTE(A1,"*",...