We use Excel to create some financial statements, budget sheets, and other files that contain important data and can only be reviewed by specific people. To prevent others from opening the files casually and viewing the data, and to avoid data leakage, we need to set an open password for Excel files. If we have many Excel spreadsheets that need to have open passwords set, we can use the following 4 methods for batch encryption: HeSoft Doc Batch Tool , Excel add-ins, compression software encryption, and VBA macro scripts.
When Should You Batch-Set Open Passwords for Excel Files?
1. When the finance department generates a large number of Excel reports containing core company financial information that must not be seen by others. In such cases, you need to set open passwords uniformly, allowing access only to yourself or management, while others have no access rights.
2. For company Excel project files, such as project budget sheets, cost accounting sheets, and customer requirement lists, these files also constitute business secrets. After a project is completed, batch-setting passwords on these files before archiving can effectively prevent others from viewing them.
3. When we send Excel spreadsheets to leaders or clients, information can easily leak during transmission, allowing unrelated personnel to see the file contents. For data confidentiality, we need to batch-set open passwords for Excel spreadsheets and only give the password to designated individuals. This way, others cannot view the files without the password.
Preview of the Effect After Setting an Open Password on Excel
Method 1: Using HeSoft Doc Batch Tool for One-Click Batch Encryption
Recommendation Rating: ★★★★★
Pros:
- The operation is very simple. We just need to import all Excel files and set the password type; it will automatically encrypt all files without any difficulty.
- True batch processing allows us to import hundreds of Excel files at once and encrypt them all with one click, saving time and effort.
- Supports batch setting of various password types for Excel, including open passwords, read-only passwords, and edit-restriction passwords.
- Supports multiple languages such as Chinese, English, French, German, Spanish, Japanese, and Korean.
Cons:
- Requires installation on a computer and only supports Windows systems.
Steps:
1. Open [ HeSoft Doc Batch Tool ], select [Excel Tools] - [Add Password Protection to Excel].
2. Click Add Files or Import Files from Folder to add all the Excel spreadsheets you need to encrypt. It supports various file formats including xlsx, xls, xlsb, xlsm, xlt, xltx, xltm, csv, and numbers.
3. Select [File Open Password] and enter the password you want to set below. It is recommended to use a combination of letters and numbers and set the password length to medium-long for better encryption, preventing the password from being easily cracked.
4. After processing is complete, check the save location. The files inside will all be locked Excel files, requiring a password to open.
Method 2: Using the Excel Plugin Kutools to Batch Encrypt Files
Recommendation Rating: ★★★★☆
Pros:
- The Kutools plugin is directly embedded in Excel, so no additional software needs to be opened, making operation convenient.
- It's straightforward: select the plugin's encryption function, add all files, fill in the password, and it will automatically encrypt.
- Feature-rich for long-term use, it helps improve work efficiency and is suitable for frequent Excel users.
Cons:
- It's a paid plugin, and some features may be limited by the Excel version.
Steps:
1. Install the Kutools plugin, click [KUTOOLS PLUS], and select [Encrypt Workbook].
2. Click [Add], add all Excel files for which you need to set an open password, fill in the password below, and finally click [OK]. All Excel files are then encrypted.
Method 3: Packing Excel Files into an Encrypted Archive
Recommendation Rating: ★★★☆☆
Pros:
- No additional tools are needed; using the computer's built-in compression function, you can directly pack files into an archive and set a password, which is very convenient.
- The steps are simple: just select the files, save them as an archive, and set a password. Anyone with basic computer skills can easily handle it.
Cons:
- This does not directly encrypt the Excel files; once the archive is decompressed, the Excel files can be opened directly.
Steps:
1. Right-click the Excel file(s) that need to be encrypted, select [Add to archive], fill in the password in the encryption field and confirm it, then click [OK].
2. If you need to batch-encrypt multiple Excel files, simply select all the files, right-click, choose [Add to archive], and set the password.
Method 4: Using VBA Macro Script to Batch-Set Open Passwords for Excel
Recommendation Rating: ★★★☆☆
Pros:
- Excel has a built-in VBA macro script feature, so no other tools need to be installed; operations can be performed directly within Excel.
- True batch processing: the script can automatically identify all Excel files in a folder and batch-set passwords.
- Flexible password setting: each Excel file can be assigned a different open password.
Cons:
- The operation is relatively difficult with a high barrier to entry, making it unsuitable for beginners.
Steps:
First, place all Excel files to be encrypted into the same folder. Then open Excel, press [Alt+F11] to open the VBA editor, click [Insert] - [Module], paste the code, and finally press F5 to run the VBA script.
