How to depict an Infinite Series in Excel

 1. How to depict summation of 1/(2^n)

Here we assume that n is from 1 to 100. So we write in the formula

=sum(1/2^row(1:100))

Dynamically filter data from one worksheet to another

 1. Select range A1:O82 and press Ctrl+F3 > New > Name.  In the Name box, enter Dummy and click on OK.

2. Select range A1:O82 and press Ctrl+T.  Ensure that the "My data has headers" box is checked and click on OK
3. Save the file (assume on the Desktop for now)
4. Open a blank worksheet and go to Data > From Other Sources > From Microsoft Query
5. Select Excel files and click on OK
6. In the right had side panel, navigate to the folder where you saved the file in step 3 (Desktop in this case).  In the left hand side panel, select the specific Excel file which you saved in step 3 and click on Next
7. With Dummy appearing in the left hand side panel, click on the > symbol to transfer all columns of Dummy to the right hand side panel and click on Next
8. In the column to Filter box, select year and in the right hand side Year box, select Greater than Equal to and then select 1990
9. Click on OK Twice
10. Select "Return Data to Microsoft Excel" and click on Finish
11. In the Import Data box, click on OK

How to set up a sheet for Histogram

 Suppose we have a series as shown in A. so first we set up B

The formula for Bin and frequency is as follows:

The formula for cumulative frequency is as follows:

How to remove Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector

 here's how you fix it: Goto File in the upper left hand corner, then Options > Trust Center > Trust Center Settings > Privacy Options > then un-check the check box that says "Remove personal information from file properties on save", then hit OK. 

How to Extract the first letter of Every word in a sentence in excel

 Use this code

------

Function ExtractFirstLetter(text) As String

mystring = Left(text, 1)

For i = 2 To Len(text) - 1

If Mid(text, i, 1) = " " Then

    mystring = mystring & Mid(text, i + 1, 1)

End If

Next i

ExtractFirstLetter = WorksheetFunction.Substitute(UCase(mystring), " ", "")

End Function

--------------

Use it like this:

-------------------

How to Pull the distinct values from a column

 Regular distinct formula, just drag the cursor to get all the distinct values:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

Where:

• A2:A10 is the source list.

How to subtract month from a date

 You can use the EDATE function to quickly add or subtract months from a date. The EDATE function requires two arguments: the start date and the number of months that you want to add or subtract. To subtract months, enter a negative number as the second argument. For example, =EDATE("9/15/19",-5) returns 4/15/19.