Excel: Themes, Locking Cells, Hiding Formulas

• Excel document themes unify a worksheet's look with coordinated theme colors, fonts, and effects.

Applying Workbook Themes

• Themes help maintain a uniform appearance for the worksheet.
• Access the "Page Layout" tab, then click "Themes" and select one.

Customizing Document Themes

• Go to the "Page Layout" Tab on the Ribbon.
• Click "Theme Colors", "Theme Fonts", or "Theme Effects" to select desired options.

Locking/Unlocking Cells

• Cells are locked when a worksheet is protected to prevent unauthorized edits.
• Select cells, click the "Format" button on the "Home" tab, choose "Format Cells", then go to the "Protection" tab, clear the "Locked" box, and click "OK" to unlock.
• Cell locking/unlocking is only enabled after protecting the sheet.
• Unlocked cells remain editable once the sheet is protected.
• Toggling the lock of cells can be done by selecting Lock Cell from the format button.

Hiding Formulas

• It is possible to prevent formulas from displaying in a protected worksheet.
• Choose the cells with formulas to hide.
• Click "Format" on the "Home" tab, select "Format Cells", and check the "Hidden" box before clicking "OK."

Worksheet Protection

• Protect a worksheet after preparation, it is accessible via the "Review" tab.
• Expand the "Protect" group if needed.
• Click "Protect Sheet".
• Select the elements to protect.
• A password isn't required, but if omitted, anyone can unprotect the sheet.

Password Protection

• To add password protection:
  • Click the "File" tab and choose "Save As".
  • Click "Browse".
  • Click "Tools," select "General Options," enter a password, and click "OK."
• Using a password ensures sensitive information remains secure.

IF Function

• The IF function assesses a condition and returns one value if true, another if false.
• To insert an IF function:
  • Click where to input the function and go to the "Formula" tab.
  • Click the "Logical" button and pick "IF".
• The Formula Arguments dialog box will appear.
• Arguments:
  • Logical Test: Any expression that evaluates to TRUE or FALSE.
  • Value if True: The value returned if the Logical_test is TRUE; if omitted, TRUE is returned.
  • Value if False: The value returned if the Logical_test is FALSE; if omitted, FALSE is returned.

Applying Key Excel Skills

Table Creation

• Create tables to efficiently manage data.
• Select sample data, go to the "Insert" tab, and click "Table”.
• Ensure the "Create Table" dialog box correctly identifies the data range and check "My table has headers" if applicable before clicking "OK".

Sorting

• Sort table data based on a column such in descending order.
• Click any cell, select "Sort" from the "Data" tab, choose the column from the dropdown, select "Descending" order, and click "OK".

Adding Rows or Columns

• Rows/columns added to include new information.
• Add this via a Right-click any cell, choose "Insert", and enter the data.

Removing Duplicates

• Remove any duplicate employee names.
• Selecting the column with names, go to "Data", click "Remove Duplicates", choose "Name" for duplicate checks, and click "OK."

Conditional Formatting

• To highlight employees with above-average salaries:
  • Select the salary column.
  • On the "Home" tab use conditional formatting > highlight cells rules.
  • Select "Above Average".

Themes

• Improve presentation by choosing a theme.
• Select a theme via the "Page Layout" Tab > "Themes" dropdown. Choose a desired theme.

Locking Cells

• Prevent edits to certain cells, like employee IDs.
• Do this by selecting relevant employee ID cells, right-clicking, selecting "Format Cells", and, in the "Protection" tab, checking "Locked".
• Protect the sheet via the "Review" tab.

Passwords

• Protecting sensitive employee data requires use of a password.
  • Go to "File" > "Info."
  • Click "Protect Workbook", select "Encrypt with Password", and confirm it.

Categorizing Using IF Function

• Steps using the If function to separate salary’s:
  • Create formula =IF(Salary>MEDIAN(Salary), "High Salary", "Standard Salary")
  • Adjust as required according to your dataset.