This formula will help you in finding out the value in the last cell of a column. =LOOKUP(2,1/(ISNUMBER (C1:C100)) ,C1:C100)
How to extract number only from a text in Excel
The following long formula can help you to extract only the numbers from the text strings, please do as this: Select a blank cell where you want to output the extracted number, then type this formula: =SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(–MID(A5, ROW(INDIRECT(“1:”&LEN(A5))), 1)) * ROW(INDIRECT(“1:”&LEN(A5))), 0), ROW(INDIRECT(“1:”&LEN(A5))))+1, 1) *10^ROW(INDIRECT(“1:”&LEN(A5)))/10) and then drag the fill handle to fill the […]
Search function in Excel
You can check the template for how INDEX formula enables the search function within the worksheet using a textbox. Download template with full access (Password: jafarpp.in)
Create a slicer in an existing PivotTable
Click anywhere in the PivotTable for which you want to create a slicer. On the Analyze tab, click Insert Slicer. In older versions of Excel, click the Options tab, and then click Insert Slicer. In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer. Click OK.A slicer is displayed for every field that you […]
Auto Save Excel File After Cell Value Change
A macro-enabled excel file can save automatically when changing any cell value. See the below given VBA code for autosave : 1. Right-click the sheet tab that you want to autosave the workbook after entering new data, and then choose View Code from the context menu, in the opened Microsoft Visual Basic for Applications window, copy and paste the […]
BMI Calculator
Calculate Your Body Mass Index. Body mass index (BMI) is a measure of body fat based on height and weight that applies to adult men and women. Enter your weight and height using standard or metric measures.
Suicide of An Excel File!
This is not a story, an amazing VBA code to protect your highly confidential excel file from theft/miss use. If you have developed a workbook which you want to self-expire by a certain date, such as a demonstration model or containing information or usefulness that will be outdated, you can program a workbook to delete […]