Sometimes we are faced with Excel tables often have many spaces, so that the statistics and check the data is very troublesome, thus reducing the efficiency of work. In addition, for the privacy of Excel tables in the data, we also need to be hidden. Next, I will join their own work experience, to introduce how to solve the above problem.
A key to hide blank cells
Many units of logistics management, need to be responsible for the unit’s office supplies procurement and distribution, that is, the unit in advance to draw up a catalog (Figure 1), employees fill in whatever they need (this example assumes a total amount of up to 30 yuan). Since the office supplies declared by each employee are different, it is very easy to make mistakes in issuing them. In fact, the problem can be solved by hiding all blank cells for office supplies declared by a particular employee.
1, add the macro code (editor’s note: the subheading number serial number removed when typesetting, the same below)
Run Excel (this article to the 2016 version as an example) to open the workbook, and then press the Alt + F11 shortcut key, open the Visual Basic editor and enter the following code.
Dim rng As Range
For Each rng In [D8:DI8]
If Len(rng.Value) = 0 Then rng.EntireColumn.Hidden = True
EntireColumn.Hidden = True
Copy the above code again, press enter and paste it, change “yincang” to “xianshi” and “True” in the macro code. Modify to “False” (Figure 2).
2, add a button and specify the macro
In the “Development Tools” tab, click “Insert → Button”, in the interface shown in Figure 1 to insert two command buttons, and then modify their text to “Hide blank cells” and “restore the initial state”. And then right-click the “Hide Blank Cells” button, select “Specify Macro”, specify the macro “yincang” (Figure 3), for the button ” restore the initial state” to specify the “xianshi” macro on it.
If Excel 2016 does not show the “Development Tools” tab, you can click “File → Options → Customize Ribbon”, open the “Excel Options” dialog box, check the “Development Tools” check box to bring up the “Development Tools” tab can be.
3, hide the blank cells
first select column B cells, click the “Data” tab in the “Filter” button; then click the drop-down arrow in column B cells, select the first data record (that is, the eighth row of cells), and then click the button shown in Figure 1 “Hide Blank Cells”, the completion of the first data record to hide the space (Figure 4).
Because the redundant blank cells are hidden, so the reconciliation and issuance of office supplies is much easier, and less prone to error. Of course, for the other data records shown in Figure 1, you can first click the “Restore Initial State” button to display all the data, and then click the drop-down arrow in column B cells, filter out the second data record (that is, the ninth row of cells). Then open the Visual Basic Editor, all the numbers shown in Figure 2 8 replaced by 9 (a total of four), and finally click the “Hide Blank Cells” button, you can complete the second data record blank cells to hide.
Figure of the string “D8: DI8”, that is, the 8th line “D8: DI8” cell in the blank cell to perform the hidden operation. In practice, if different, you can flexibly modify.
For the privacy of the data to add a “lock”
Sometimes some private data (such as the phone number in the above example), do not want others to see, then you can hide the relevant data, and add a password to achieve.
1, set the password and add text
interface as shown in Figure 5, first switch to the “Sheet2” worksheet, and enter a password in cell A1 (such as “12345678”), and then switch to the “Sheet1 “worksheet, select the second row of cells and right-click it, select “Insert”, insert a blank cell. Then in the second line of A2, C2, E2 and G2 cells, respectively, enter the text “password:” and set the font color, fill color and other parameters (Figure 6).
2, create and edit the rules
select the need to hide the cell (such as “B2: B43”), and then in Excel 2016, “Start” tab, click “Conditional Format ¡ú New Rule”, in the pop-up dialog box, select “Use formula to determine the cells to be formatted” (Figure 7), enter the formula “=B$2<>Sheet2!A$1”, and then click the “Format “button to open the “Set Cell Format” dialog box “Numbers” tab, select the tab “Custom” option, enter three English state of the quotation marks and click the “OK” button can be.
After completing the above operation, you will find that all the data in column B cells are hidden. A$1″, “=F$2<>Sheet2!A$1” and “=H$2<>Sheet2!A$1 “, hide column D, F and H of all the data on it.
3, cancel the cell lock and protect the worksheet
In the interface shown in Figure 6, while selecting B2, D2, F2 and H2 cells and right-click it, select “Set Cell Format”, in the dialog box with the same name opened, switch to the “Protection” tab, uncheck the “Lock” check box. Then click the “Protect Sheet” button in the “Review” tab to add protection for the “Sheet1” sheet, and finally right-click the “Sheet2” sheet and select the “Lock” checkbox. Sheet2″ worksheet, select “Hide”, it will be hidden on it.
After the above steps, “Sheet1” worksheet only B2, D2, F2 and H2 cells in the editable state, if a cell (such as cell B2), enter the password set in advance, you can view all the data in column B cells (Figure 8). And delete the relevant password and enter, the data in the displayed cells will be automatically hidden again.