How to add a ghost cell in Excel

How to add a ghost cell in Excel

by

A ghost cell is a cell that appears empty but contains some data. Creating Ghost cells in excel is possible, and users can use this feature to hide any data on the cells' cells. Confidential data on the ghost cell can be retrieved, be changed, and can also be formatted. Once the ghost cell is created and the information is added to that cell, the user can recheck the data by clicking or hovering the mouse on top of the ghost cell. However, this trick may not be familiar to all excel users. Similarly, some may find it challenging to create a ghost excel. This article got you covered.

Widely, there are three common and easy ways to create ghost on any cell on the excel document:

  1. I am adding data validation.
  2. Using conditional formatting and IF function
  3. Using Excel VBA

Each of the above methods is discussed below in detail.

Adding data validation.

This is one of the easiest methods to add a ghost cell to your excel document. It is associated with some steps. These steps include:

1. Open the Excel software, and then open the document you want to add a ghost cell(s).

2. Click on the cell that you want to modify to a ghost cell. By clicking, the cell is highlighted.

3. Once the cell is highlighted, click on the data button, one of the top bars found on the upper part of excel's page.

4. Click Data validation, then choose the data validation button.

5. Select the input message tab, and then enter the information that will appear on the ghost cell. Enter whatever you want the box to say.

6. Lastly, click the OK button. The message will only be displayed when you hover the mouse over it.

Using conditional formatting and IF function

This is another method that can be used to hide the content of the cell. The steps involved are:

1. Open the Excel software, and then open the document you want to add a ghost cell(s).

2. Highlight the cell(s) that will contain the hidden content.

3. Then, click on the Home tab found on the top bars of the screen.

4. Locate and click the "conditional Formatting" button. Then, create a new formula.

5. On the page displayed, add a formula on the blank space provided. An example that is in the form of AND function would be as follows:

=AND (B<> "", B<TODAY () +2)

6. Then click OK; the formula will be inserted on the cells as a ghost. You can only view if and only if the condition is met or when you hover a mouse over it.

Using Excel VBA

This is a method commonly to create ghost conditions on the excel data. Steps involved include:

1. Open the Excel software, and then open the document you want to add a ghost cell(s).

2. Click on the developer tab on the top part of the screen, then click the Visual Basic button.

code source: https://stackoverflow.com/questions/21447458/ghost-values-in-the-cells

This array-based code provides a very quick way of turning the cells to truly blank

Sub QuickReplace()
Dim rng1 As Range
Dim X
Dim lngRow As Long
Dim lngCol As Long

ActiveSheet.UsedRange

X = ActiveSheet.UsedRange.Value2
For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If Len(X(lngRow, lngCol)) = 0 Then X(lngRow, lngCol) = vbNullString
    Next
Next
ActiveSheet.UsedRange.Value2 = X

End Sub

3. On the page displayed, enter the data or information to hide within a cell. Finally, save the document.