How To Automatically Destroy Excel Table Data After Expiration

We all know that we can set various conditions for excel tables. Today, let’s take a look at a special operation, that is how to use conditional format settings to set our table data to be automatically destroyed after expiration.

In this example, after we set the computer date as the excel table data deadline, the personnel bonus information in the corresponding cells will be completely hidden, and the text will not be displayed in the edit bar when we use the mouse to click the corresponding cell. But after we modify the data cell date, the text can be displayed again.

1. How To Automatically Destroy Excel Table Data After Expiration Steps.

1.1 Get The End Date Unique Number.

1. We first set an end date ( such as May 7 ) in any cell of the table.
2. Then convert the cell date format to the normal format to display the numerical value of the corresponding date.
3. Because in Excel, a date is saved as a unique number, but you can set different cell display formats to display a date number in the date format string.
4. Right-click the cell, then click the Format Cells… menu item in the popup menu list to open the Format Cells dialog window.
5. Then click the Number tab in the popup dialog window.
6. Click the General item in the Category list.
7. Then you can see the number value of the date on the dialog right side.
8. For example, the date May 7 ‘s number value is 44688.

1.2 Set Conditional Formating Rule To Change Selected Cells Text Color With The Specified Formula.

1. After we get the number of the end date, we will now set the cell conditions.
2. Click and drag to select the data table area such as A3: E9 area.
3. Click the menu Home —> Conditional Formating —> New Rule… in the Styles group.
4. It will open the New Formatting Rule pop-up dialog window.
5. Select the Use a formula to determine which cells to format item from the Select a Rule Type list.
6. Then input the formula =today()>=44688 in the input text box Format values where this formula is true.
7. Click the Format… button to open the Format Cells dialog window.
8. Click the Font tab in the Format Cells dialog window.
9. Select the white color from the Color drop-down list.
10. Click the OK button to save the changes.
11. Now you can see the text in the Preview rectangle will be hidden.
12. After the above configuration, when the current computer date is greater than or equal to May 7, the selected cells’ text color will be changed to white.

1.3 How To Verify The Above Settings Take Effect.

1. Now, we change the date of the computer to May 7.
2. We will find the text in the area of the selected cells will not be displayed.
3. After restoring the date of the computer to the current date, the selected cells’ text will be displayed again.
4. However, we can still see the text in the Formula Bar input text box when we click the cells.
5. In order to solve the above problem, we need to set additional cell protection as below.
6. Select the A3: E9 cell range, then press Ctrl + 1 to open the Format Cells dialog window.
7. Click the Protection tab in the Format Cells dialog.
8. Check the Hidden checkbox and click the OK button to save the settings.
9. Click the Review —> Protect Sheet menu item to open the Protect Sheet dialog window.
10. Input a password in the text box Password to unprotect sheet.
11. Click the OK button to save and close the dialog.
12. Now when you click the hidden cell, the cell text will not be shown in the formula bar text box again.