Excel Sheets - Yash-777/ITextReports GitHub Wiki

Data validation Drop Down ListExcel Demy, Excel Jet

📝 Steps to Create a Drop-Down List for a Cell or Range 📘 Range for Statuses
  1. Select the Cells:
    • Highlight the cells where you want the drop-down list to appear.​
  2. Open Data Validation:
    • Navigate to the Data tab on the ribbon.
    • Click on Data Validation in the Data Tools group.​
  3. Configure the Drop-Down List:
    • In the Data Validation dialog box: Under the Settings tab,
      • select List from the Allow dropdown.
      • In the Source field,
        enter your list items separated by commas
        (e.g., Option 1, Option 2, Option 3)
        (e.g., To Do, In Progress, Done, etc.)
        or
        select a range of cells that contain the list items.
  4. Finalize:
    • Click OK to apply the drop-down list to the selected cells.
 image

Format Painter

The Format Painter copies formatting from one place and applies it to another.

Steps Image
1. Select the cell.
2. Double-Click the Format Painter Icon. Note: This will keep the paint brush next to your cursor:
3. Click every cell you want to copy the format to.
4. When finished, click the Format Painter icon again or hit ESC to remove the paint brush from your cursor.

Freeze panes to lock rows and columns

To keep a section of a worksheet visible while scrolling in Excel, go to the View tab. Use Freeze Panes to lock specific rows or columns in place, or use Split to create separate scrollable sections within the same worksheet.

Steps Image
* Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.
* Select View > Freeze Panes > Freeze Panes.		 image

Filter for unique values or remove duplicate values

Generate A List Of Sheet Names From A Workbook

Create our named function Formula
Go to the Formulas tab. Press the Define Name button. In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") =INDEX(SheetNames,A2)
⚠️ **GitHub.com Fallback** ⚠️