How to Hide Numbers When Copying Formulas in Excel
Sometimes it is necessary to create a list of formulas that you do not want the spreadsheet user to see. The formula itself or the results of the formulas may be hidden. This is necessary when a formula is copied down through many rows to analyze data in another column, but the results of these formulas are further analyzed by another formula which displays a conclusion. It can confuse a user to see the intermediate steps of Excel analysis spread across many cells when only the final calculation is relevant. If the formulas output numbers, these numbers can be hidden. If the formulas themselves contains numbers, the entire formulas can be hidden.
Other People Are Reading
Instructions
-
Hiding Numbers
-
1
Open the Excel spreadsheet you are manipulating. Create the formulas as normal, and copy them down through the program grid. For example, if you used a formula to find duplicates in a data list, it may read "=if(a2=a1,1,0)". This formula will show a "1" if the cell is a duplicate of the one above it, and a "0" if it is not. After the formula is copied down through all the data rows, it will show a string of numbers that are not attractive.
-
2
Select all the cells which contain the formula results. Click on the first cell and drag to the last, or alternately click the column letter at the top of the copied formula to quickly select the entire column.
-
-
3
Click the drop-down arrow next to the font color button on the Excel toolbar. Select the same color from this menu as the background color of the selected cells. In most cases, this color will be white. The numbers are now hidden from the copied formulas. You can create a final summation formula in another cell, such as "=sum(b1:b100)" which displays the total number of duplicates in the data list, without showing the intermediate formula results.
Hiding Formulas
-
4
Select the cells which contain the copied formulas.
-
5
Right-click any one of these cells and choose "Format Cells." A pop-up window will appear.
-
6
Choose the "Protection" tab. Check the "Hidden" option. Press the "OK" button.
-
7
Click the "Tools" menu. Choose the "Protection" sub-menu and select the "Protect Sheet" command.
-
8
Press the "OK" button on this pop-up window. All contents of the formula cells are all hidden. The user cannot see inside the numbers inside the formula by viewing them through the formula bar.
-
1
