In this article, we will learn how we can define a range name in Microsoft Excel 2010 and 2013. There are two techniques to define a range name. Let’s take an example to understand how we can define a range … Continue reading
In this article, we will learn how we can define a range name in Microsoft Excel 2010 and 2013. There are two techniques to define a range name. Let’s take an example to understand how we can define a range … Continue reading
In this article, we will learn how we can automatically define the names for ranges in lists in Microsoft Excel 2010. Let’s take an example to understand how we can define the names for ranges automatically. We have a workbook … Continue reading
In this article, we will learn how we can use defined range name while calculation in Microsoft Excel. Let’s take an example to understand. We have a workbook in which we have 3 lists in 3 columns. Column A contains … Continue reading
Problem: Columns A & B contain two lists of values. We want to create a formula that will check whether there is any duplication of values within either list (blank cells are to be ignored). Solution: Use the COUNTA, SUMPRODUCT, … Continue reading
The offset function returns a reference to a range that is offset a number of rows and columns from another range or cell. The syntax is : OFFSET( range, rows, columns, [height], [width] ) range is the starting range from … Continue reading
To automatically Define Names according to the labels in the top rows cells and in the left columns cells: 1. Select any cell in the data area and select the Current Region by pressing Ctrl+Shift+* or press Ctrl+A. 2. Select … Continue reading
If you want to select range of cells – without using Mouse seletion Or Shift key, you can use F8 key as briefed below :- Suppose you wish to select the range A1 to D5. Select cell A1 and press … Continue reading
Updating the size of a source data range is necessary in order to update the reference in any formula using range Name, especially when using a PivotTable Report. To automatically update a range Name: Insert the following formula into the … Continue reading
Example 1: Add a formula for calculating the number of the previous year: = YEAR(TODAY())-1 To define a Name that returns the result of a formula: 1. Press Ctrl+F3 for the Define Name dialog box. 2. In the Names in … Continue reading
Why it is highly recommended to delete unnecessary/unneeded range Names: Large number of range Names makes it more difficult to locate a specific Name. Range Names create references and unwanted links To find unnecessary/unwanted range Names: 1. Select a cell … Continue reading
