Kako skriti in zaščititi formule, ki niso prikazane v vrstici s formulami v Excelu?
In Excel, it’s often necessary to hide formulas from being displayed in the formula bar, especially when sharing files or working with sensitive data. By hiding formulas, you can protect them from being viewed or modified, ensuring the integrity of your data. This article will guide you through the process of hiding formulas in Excel and provide some optimization tips to enhance the security and usability of your workbooks.
Hide and protect formulas not displayed in the formula bar
Why Hide Formulas in Excel?
Before diving into the methods, it’s important to understand why you might want to hide formulas:
- Zaščitite intelektualno lastnino: Prevent others from viewing or copying complex formulas.
- Prevent Accidental Changes: Stop users from editing or deleting formulas that are critical to your workbook.
- Izboljšajte berljivost: Hide formulas to make the workbook look cleaner and more professional.
- Varnost: Restrict access to sensitive calculations or logic.
Hide and protect formulas not displayed in the formula bar
This article will guide you through two methods to hide and protect formulas in Excel: one using Excel’s built-in features and the other using Kutools for Excel, a powerful add-in designed to simplify Excel operations.
➡️ Hide and protect formulas not displayed in the formula bar with Built-in features
Excel provides a built-in way to lock and hide formulas, preventing them from being displayed in the formula bar or modified by users. Here’s how you can do it:
1. korak: Odklenite vse celice na delovnem listu
- S pritiskom izberite vse celice na delovnem listu Ctrl + A.
- Nato pritisnite Ctr + 1 da odprete Oblikuj celice pogovorno okno.
- v Oblikuj celice v pogovornem oknu pod Zaščita jeziček, počistite polje Zaklenjen in nato kliknite OK . Oglejte si posnetek zaslona:
2. korak: izberite vse celice formule
Zdaj, ko so vse celice odklenjene. V tem koraku bi morali izbrati vse celice s formulo.
- Pod Domov jeziček, kliknite Poiščite in izberite > Pojdi na posebno. Oglejte si posnetek zaslona:
- v Pojdi na posebno pogovorno okno, preverite Formule izbirni gumb in izbrane bodo vse vrste formul. Oglejte si posnetek zaslona:
- Nato kliknite OK in vse celice s formulo so izbrane hkrati.
Step 3: Lock and hide the formula cells
After selecting the formula cells you want to hide. Then, you should lock and hide them.
- Right-click the selected cells and choose Oblikuj celice (ali pritisnite Ctrl + 1).
- v Zaščita zavihek, preverite Zaklepanje in skrita box. This will hide the formulas in the formula bar.
- klik OK , da zaprete pogovorno okno.
4. korak: zaščitite delovni list
To ensure the Hidden attribute works, you need to protect the worksheet. Without protection, the formulas will be visible in the formula bar when selected, even if you’ve marked them as hidden.
- Pojdi na klik pregled > Zaščitite list in the Excel ribbon.
- V pojavnem pogovornem oknu vnesite geslo in ga potrdite. In nato kliknite OK da zaprete pogovorna okna. Oglejte si posnetke zaslona:
Rezultat:
Now, the formulas in this sheet are hidden and protected, they will not display in the formula bar when those cells are selected. If you attempt to delete or edit these formulas, a warning message will appear. However, you can still edit other cells as needed.
➡️ Hide and protect formulas not displayed in the formula bar with Kutools for Excel
While Excel provides built-in methods to lock and hide formulas, these options can sometimes be limited or cumbersome to implement. Kutools simplifies the process of hiding and protecting formulas, offering a user-friendly and efficient solution for safeguarding your work. In this section, we’ll explore how to use Kutools for Excel to hide and protect formulas.
Po namestitvi Kutools za Excel, naredite naslednje:
- Pritisnite Ctrl + A , da izberete celoten list.
- klik Kutools Plus > Pogled oblikovanja odpreti novo Oblikovanje Kutools tab.
- v Oblikovanje Kutools jeziček, kliknite Odkleni celice lastnost.
- Then, please do the following operations:
(1.) Kliknite Označi formule from the ribbon;
(2.) Then, select the highlighted formulas;
(3.) Kliknite Lock cells from the ribbon;
(4.) Nato kliknite Skrij formule. - Končno kliknite Zaščitite list from the ribbon. And in the popped-out dialog box, type a password and confirm it.
Rezultat:
Now, all selected formula cells are hidden from the formula bar and cannot be altered unless the protection is removed.
Hiding and protecting formulas in Excel is essential for safeguarding sensitive data and preventing accidental modifications. While Excel's built-in features provide a way to achieve this, they require multiple steps and can be difficult to manage. Kutools for Excel offers a streamlined, user-friendly solution, allowing you to hide and protect formulas with just a few clicks. Choose the best way to you need. If you're interested in exploring more Excel tips and tricks, naše spletno mesto ponuja na tisoče vadnic, ki vam bodo pomagale obvladati Excel.
Sorodni članki:
Kako označiti / pogojno oblikovanje celic s formulami v Excelu?
Kako zakleniti in zaščititi formule v Excelu?
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!