Hiding formulas

[madocar]

Hi chandoo,

please is there any way how to hide formulas and keep all function in my excel?
Since I hide formulas on all sheet I can not use all function like merge and center and many others.

I just want to hide my formulas.

Thanks for any response

 

[Luke M]

Select this hidden property, then protect the sheet


If user clicks on the cell, they won't be able to see the formula.

Or, you can of course hide the row/column, if there's nothing else in the row/column that needs to be seen.

 

[Khalid NGO]

Hi,
In addition to above...
When you protecting your sheet, enable the Format Cell check box.
So you can format your cells but this will not allow to merge.

Regards,

 

[madocar]

Still not able to center and merge cells after hidden cell.

 

[madocar]

Select this hidden property, then protect the sheet
View attachment 17240

If user clicks on the cell, they won't be able to see the formula.

Or, you can of course hide the row/column, if there's nothing else in the row/column that needs to be seen.

Luke M I know how to hide formulas but when I do that I'm not able to use merge and center function in excel. It happens to be blocked.
That is the problem

 

[bobhc]

DO NOT USE MERGE

You have found out how limited merge is and how it can and will screw up your work sheets.


Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.

 

[madocar]

DO NOT USE MERGE

You have found out how limited merge is and how it can and will screw up your work sheets.


Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.

Thanks bobhc,
but let's forget the function merge and center. I absolutely agree with you. The bottom line is there is a lot more functions blocked if I hide my formulas. Therefore the question is Is there any better way how to hide my formulas?

Thanks a lot

 

[Luke M]

I think the issue is that the sheet is protected, not that the formula is hidden. Unprotect the workbook, do whatever change you need, and then reprotect the workbook.

Or, step back, and tell us what the overall goal is?

 

[madocar]

I think the issue is that the sheet is protected, not that the formula is hidden. Unprotect the workbook, do whatever change you need, and then reprotect the workbook.

Or, step back, and tell us what the overall goal is?

My issue is simple. If I hide formulas then I can not use my excel with all functionality (merge and center, pivot tables .. etc).
I want to send my excel file with formulas hidden, but I want it to be all functional

 

[Luke M]

You could put all the formulas on a different worksheet, and hide that sheet. Protect the workbook structure then so that can't unhide sheet. This would leave all the regular sheets usable. If you need information from the Calc sheet, use a simple:
=A1
type formula.

Are you wanting to hide the formulas because it's somehow proprietary and don't want user to see, or are you protecting the formula from being changed?