Excel Functions

This category holds all the posts which are related to Functions which are very useful while writing Macros.

COUNT and COUNTA Function

COUNT(): COUNT is a Excel inbuilt formula. It takes CELL RANGE as an input and give the total count of the Cells which are having Numeric Values. Important: COUNT counts only those cells which are having "Numeric" Values. It ignores those cell which are having non-numeric values. Syntax: =COUNT(Cell Range) Input Parameter: Cell or Cell Range ("A1:B2") or ("A1") Output: [...]

By |Categories: Excel Functions|0 Comments

How to Create Drop Down List in Excel

Drop down list is made in Excel Cell to limit the Entry in to a Cell from a predefined set of values. It means you are restricted to select one value out of set of Predefined values. On creating a drop down list on a cell or cell-range, one drop-down will be displayed with set of predefined values, on selecting [...]

File Name Formula

There are few instances when we want to store the File Name or Current Workbook Name in a particular Cell. This is very easy. This can be done by using Cell() function as shown below: Syntax: =CELL("filename") Returned Value: C:\Users\Vish\Desktop\[Test.xlsx]Sheet1 Note: The problem with this is that it gives the complete path including Drive and folders with Sheet Name. To [...]

Hide Horizontal or Vertical Scrollbar in Excel Workbook

In one of the Article, i had shown how to hide or display the Horizontal and Vertical Scroll Bar of an Excel Workbook. Now here I am going to show how can we DISPLAY or HIDE, Horizontal or Vertical Scroll Bar in an Excel Workbook. Excel 2003 || Excel 2007 Excel 2003 In Excel 2003, follow below steps to hide [...]

Restrict to fixed text or integer length in the given range of cells

Follow below steps to Restrict to fixed text or integer length in the given range of cells: 1. Select the Cell Range where you want to put the restriction 2. Go to "Data" Tab in Excel   3. Click on Data Validation Option and Select "Text Length" from the Drop down as show below:   3. Select the Data option [...]

Filter by Cell Color in Excel

In Excel 2007/2010, it is very easy to filter rows by Cell Color. In Excel 2007/2010 we have inbuilt functionality to do so. There can be two way to do this Filter by Color. Follow the below Steps to Filter by Cell Color in Excel 2007/2010: 1. Select the Range where you want to apply the Filter. […]

DateAdd() Function

DateAdd() Function This function returns a date value based on adding an interval of time. It will be more clear by going through the following […]

Wait Function

Even more wait for Gane of thrones New Season??? Noooo !!! Who in real life would like to wait… especially for Game Of Thrones 😀 No one would like to wait for any thing in real life 🙂 Waiting For Game Of Thrones Season – 7 Sometimes, while doing Automation in Excel Macro we require a “Wait” or “Sleep” function, which will hold the execution of the next statements until the specified time. Following is the Syntax, of Wait […]

Page 4 of 41234