How to hide a formula in one sheet ?

Chef DET

Hi everyone ,

Is there a way to hide a formula in one sheet .

I want to share the sheet to some partners but I don't want them to be able to see the formulas . I've locked the coulumns containing the formulas but it's not enough .

Is there a solution ?

Thanks in advance .

Jose da Silva

You can create an additional column next to the one with the formula and type =[column with the formula], then hide the column with the actual formula.

It will not make it totally invisible, since they can simply "unhide all columns" but it will not be in plain sight.

Paul Newcome

@Jose da Silva

What is the purpose of creating the additional column?

All you should have to do is right click on the column header(s) and select "Hide Column".

Jose da Silva

@Paul Newcome @Chef DET The idea is to hide only the formula or both the formula and the result?

If it is both, then @Paul Newcome is correct...just hide column. But if all you want is to hide the "means to your end", then i would suggest a support column.

Paul Newcome

@Jose da Silva I am still not understanding what the purpose of the support column is. If all you are doing is creating a direct cell reference, then all you have done is created two columns that need hidden. The end result is in the formula cell...

ALAIN-STEPHANE

Hi @Jose da Silva & @Paul Newcome ,

The idea is to hide the formula so that any person I share the sheet to, cannot see and copy it .

Regards .

ALAIN-STEPHANE

PS : @Chef DET and @ALAIN-STEPHANE are the same persons.

Paul Newcome

@ALAIN-STEPHANE Is the formula text shown?

Chef DET

@Paul Newcome When I hide the column , of course any person ( editor , viewer ) cannot see the formula since they can't see the column . But as @Jose da Silva suggested , I created an additionnal column just to retreive data from the column with the actual formula .

The purpose is just to hide the formula , not the data .

Andrée Starå

Hi @ALAIN-STEPHANE ,

If i’m not mistaken they can still see the formulas if they know where to look (in the edit row).

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I follow now. My apologies. It's been a long week. But as Andree said... You can either look in the "edit Row" section or as Jose said, you can "unhide" the column(s).

Now that I am understanding exactly what we are talking about (finally)...

Reports show data and not formulas. Published reports can be viewed without having access to the underlying sheets.

Cell linking/cross sheet formulas can pull the data to another sheet, run your calculations, then return a value without the actual calculations being seen.

ALAIN-STEPHANE

You are right @Andrée Starå & @Paul Newcome .

It's better to use reports or cross-sheet formulas for this purpose.

Thank you .

Andrée Starå

@ALAIN-STEPHANE

Happy to help!

Dominique Rose Winther

I just had a call with Smartsheet's @Michael Loughrey and he figured out a solution to my issue, which seems like its the same as yours here. I had combed over all the forums for a solution and hadn't found one, so I want to come post it for future users.

I have editors that will use a sheet, but I am referencing data through INDEX/MATCH that I don't want them to have access to (salary data, which is stored on a separate sheet). If they can see the formula, then my concern is they could take part of that formula and put it in a free cell to figure out the portion of the formula that I need to keep confidential.

My concern was that I don't ever use HIDDEN COLUMNS because you can export to Excel, but he figured this out:

Have the secret formula in a column that you hide and the visible column just references that cell's answer. Its still "safe" because if you export to Excel, it doesn't export the formula - only the value. So actually its entirely hidden - from everyone except for other Admin.

Thanks Michael!

Andrée Starå

Hi @Dominique WINTHER

FYI. Be aware that they still can see the formula if they open the edit view and hoover over the fx symbol.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Dominique Rose Winther

Great flag! I should be ok as I have the actual formula in the hidden column and where I show the value, it will just refer to that cell.