How to custom format in excel

Source: https://www.ablebits.com/office-addins-blog/custom-excel-number-format/#decimal-places

Converting a number to MILLIONS in currency without dividing the number by 1000000.

$* #,##0.00,,"M";;"";

Excel validating whether required columns (marked with *) are filled or not

 Here is the formula

=LET(dataOriginal,A1:C100,

data,FILTER(dataOriginal,BYROW(dataOriginal,LAMBDA(row,SUM(--(row<>""))>0))),

firstRow,MAP(TAKE(data,1),LAMBDA(x,ISNUMBER(SEARCH("~*",x)))),

BYROW(MAP(DROP(data,1),LAMBDA(x,NOT(ISBLANK(x)))),LAMBDA(array, IF(SUMPRODUCT(--(array)*--(firstRow))=SUMPRODUCT(--(firstRow)),"OK","REQUIRED COL VALUE MISSING")))

)

#HowTo Autofit columns in excel

 Problem: Adjusting the width for every column using ALTH+H+O+I is painful 

Solution: Use AutoFit

#How To Steps:

1. Right-click the sheet and select View Code

1.  

2. Add the following Code under the Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Cells.EntireColumn.AutoFit

End Sub

Excel: Adding Table as a Data validation using INDIRECT function

1. Create a table and name it. Say TableDiv and the items should be of the column name Division
2. In the data validation source, select the INDIRECT function and add the following validation =indirect("TableDiv[Division]")