Excel

1. To get sum for all the values from column B1 to B6 where value in column C1 to C6 is "S1"  all  from Sheet1.

=SUMIF(Sheet1!C1:C6,"S2",Sheet1!B1:B6)

See "S2" in double quotes because without that, it will represent the value in cell S2.

2. In a formula, a single string can only 255 char long. So if you need to have longer string, you will have represent it by concatenation of multiple smaller strings like  for example   "abcdef" => "abc" & "def"

3.  If you are editing a long formula and getting errors, one thing you might want to look for is new lines in it esp. if you copied the text and pasted it from some place.

4. LEFT(txt , num_chars) => Returns  left num_chars characters from   txt.

5. How to escape double quote in string  as part of formula ? by double quote itself .   If you want to have "abc" as text where double quotes are included in it ...you have to put it  like """abc"""

6. If you want to fill days/dates of month in a column. It is easy, first select the type of column as date and select the format you want  via " Format cells...".   Fill the start date in the column. Then use the auto fill feature by dragging the lower right corner of the cell  over to the columns below where you want the successive dates to appear.  There is setting to define autofill configuration under "Home"  -> Fill ( indicated downward arrow )   tab.

7.  FIND AND MID functions cal allow to extract substring by looking up particular start and end characters in a string, but to have robust implementation , you have to deal with errors. When you use FIND and the string you are trying to look is not present , you get #VALUE  error, which subsequently messes up dependent expressions. To deal with it , you can use combination of IF and ISERRO.    IF ( ISERROR(FIND("(", text)) , ExpressionIFError, ExpressionNoError )

8. Updating column with same value

• Within same column to go to the last row below( means you are somewhere before the last  row with values in that column ) with same value  use  Control+Down Arrow
• If the number of rows are high, even the above step will be hard to carry out. In that scenario , first set the topmost records ( not including header ) to value you want to update to, then do Ctrl+Shift+Down Arrow , it will take you to last record, now do Ctrl+D

9. To go up ( means you are below the last record having some value in that column) to very first row having values for that column use Ctrl+Shit+Up. Very useful in copying a formula to rest of the column. See the link Copying formula to rest of the column 

10.  Multiple pivot tables can be included in a single sheet.

11.  To count number of times a particular value appears in a column , use COUNTIF( cell range, value to be matched )
12. In a formulae , if you want to reference a cell row statically and don't want to change that as the row changes,  use a notation similar to $A$1.
13. To split a workbook sheets into separate excel files following code from below link worked for me.
   
https://jen3ral.wordpress.com/2012/06/29/excel-save-worksheets-as-separate-files/

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

14. You can populate a sheet from ODBC data source.  You must have the sheet active and then click Data->Get External Data -> Get Other Sources....
15. To auto-populate value by incrementing the value from previous row in same column, use  folrmula    =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)+1

16. Macro to export an excel worksheet as pipe delimited file.

https://optimalbi.com/blog/2015/07/16/how-to-export-an-excel-file-to-pipe-delimited-file-rather-than-comma-delimited-file/

Sub Export_TXT()
Dim r,c, enregistrement, rows, cols
Set a = CreateObject(“Scripting.FileSystemObject”).CreateTextFile(“Export.txt”, True)
rows = Worksheets(1).UsedRange.Rows.Count
cols =  Worksheets(1).UsedRange.Columns.Count
For r = 1 rows
   enregistrement = “”
   For c = 1 To cols – 1
    enregistrement = enregistrement & Worksheets(1).Cells(r, c) & “|”
   Next c
   enregistrement = enregistrement & Worksheets(1).Cells(r, cols)
   a.WriteLine (enregistrement)
Next r
a.Close
End Sub

The macro was given on above link. It worked after  slight modification.

17.  Many times data have leading or trailing spaces. It is very easy to find out those values in excel. Use formula based conditional formatting.

https://superuser.com/questions/901917/is-there-a-way-to-set-excel-up-whereby-trailing-spaces-are-visible

The formula given on this link worked for me [ =OR(RIGHT(A1,1)=" ",LEFT(A1,1)=" ")  ]


18.  You can paste only the  values from the cells  without the formulae , using Paste Special -> Value .

19. To apply a formula to all the empty cells below  ( until a non-empty cell is encountered in same column  or empty cell in left column )   ...just double click the pointer which you normally drag to apply the formula to adjacent cells.