## Table Formula in Excel (Something I didn’t Know Till Yesterday)

Table formulas were something that I discovered recently. Actually our reader m-b commented that he prefers to convert a range to a table and then employ table formulas instead of named ranges. That got me curious enough to explore them further and here's what I learnt. A Table in Excel A table is a feature in Excel that makes it easier to format and analyze a set of data points in a spreadsheet. Tables were introduced ... ## CELL Formula in Excel – How to Use CELL Formula and Examples

Posted on 14 November 2009 Syntax of CELL Formula Example of CELL Formula Possible Errors returned by the CELL Formula CELL formula returns infomation about the various attributes of a cell such as address, color, width, column / row postitions etc. CELL Formula Syntax in Excel The CELL Formula has two parts: CELL (attribute_type, cell_reference) attribute_type attribute_type specifies the attribute that you are trying to find. It can be color, column / row position, width, ...

## Drop Down List in Excel Made Easy

Posted on 08 October 2009 A drop down list in Excel allows users to pick a value from a restricted set. By providing user a choice and yet preventing impermissible values, drop down lists help make your spreadsheets interactive and foolproof. While there are more than 1 way to make them, the most commonly used method is using data validation. You can create both static and dynamic drop down lists in Excel - the later re-sizing themselves as newer values are added or existing ones ...

## INDEX Formula Excel – How to use Excel INDEX Function

Posted on 26 August 2009 Syntax of INDEX Formula Example of INDEX Formula Possible Errors returned by the INDEX Formula INDEX formula in Excel returns the cell at the intersection of a particular row and column within a range. The INDEX formula is typically used to locate a value in a given range by specifying its X-Y coordinates (i.e. the row and the column) within the range. INDEX Formula Syntax INDEX Formula has four parts: INDEX (range_to_search_in, row_position, column_position, range_number) ...

## DATE Formula Excel – How to use Excel DATE Function

Posted on 23 July 2009 Syntax of DATE Formula Example of DATE Formula Possible Errors returned by the DATE Formula DATE formula in Excel returns the number representing the date for a given day, month and year. The DATE formula is typically used to construct a date by providing the constituting parts such as day, month and year. The minimum and maximum values that a date can take is Office 2003 is January 1, 1900 and December 31, 9999 (if that existed). DATE Formula ...

## MATCH Formula Excel – How to use Excel MATCH Function

Posted on 13 July 2009 Syntax of MATCH Formula Example of MATCH Formula Possible Errors returned by the MATCH Formula MATCH formula in Excel returns the relative position of a value in an array or a range MATCH Formula Syntax MATCH Formula has three parts: MATCH (value_to_find, range_to_find_value_in, match_type) value_to_find value_to_find is the value which we are trying to find. It can be a number or a string. If the value_to_find is specified as a ...

Posted on 10 July 2009 Syntax of ADDRESS Formula Example of ADDRESS Formula Possible Errors returned by the ADDRESS Formula ADDRESS formula in Excel generates the address for a cell when provided a row and a column number. ADDRESS Formula Syntax ADDRESS Formula has five parts: ADDRESS (row_num, col_num, ref_type, ref_style, sheet_name) row_num row_num is the row number in which the cell for which you are trying to generate the address is located. It is ...

## Array Formulas Examples

Posted on 08 July 2009 Array formulas help you turn normal Excel Formulas into super formulas. Here are a few examples of Array formulas. These examples highlight the use of multiple conditions within a single array formula and how we can club multiple formulas in to a single array formula. Array formulas are powerful - no doubt about that. However before we go any further, one must look at the flip side to using array formulas. They take much more computational power and can slow ...

## Array Formulas in Excel – Excel Array Formula Syntax & Array Constants

Posted on 05 July 2009 Array Formulas in Excel An Array Formula in excel is a formula that uses arrays instead of single cell value as input. Excel Array Formulas can be thought of as many formulas packed into a single super formula. In this article we will take a look at the syntax of an array formula, its various parts, learn how to write a basic array formula and then graduate on to writing the more powerful versions of it. Before we begin, let ...

## COUNTIF Formula Excel – How to use Excel COUNTIF Function

Posted on 04 July 2009 Syntax of COUNTIF Formula Example of COUNTIF Formula Possible Errors returned by the COUNTIF Formula COUNTIF formula in Excel counts the numbers of cells in a range that meet the given condition. COUNTIF Formula Syntax COUNTIF Formula has two parts: COUNTIF (range, criteria) range range is the range in which the value that you are trying to find out is located. criteria criteria is the value whose occurence in the above range we would like to count. ...

## IF Formula Excel – How to use Excel IF Function

Posted on 04 July 2009 IF formula in Excel checks whether a given condition evaluates TRUE or FALSE. Syntax of IF Formula Example of IF Formula Possible Errors returned by the IF Formula IF Formula Syntax IF Formula has three parts: IF(criteria, value_when_true, value_when_false) criteria criteria is the condition that we would like to evaluate for being TRUE or FALSE. value_when_true value_when_true is the value that will be returned by the function when the above criteria evaluates to TRUE. This ...

## VLOOKUP Formula Excel – How to use Excel VLOOKUP Function

Posted on 01 July 2009 Syntax of VLOOKUP Formula Example of VLOOKUP Formula Possible Errors returned by the VLOOKUP Formula VLOOKUP formula matches a string against the 1st column of a range and returns any cell value from the matched row. VLOOKUP Formula Syntax VLOOKUP Formula has three parts: VLOOKUP (value_to_find, range_to_search_in, column_number_to_return, match_type) value_to_find value_to_find is the value that we would like to find. You can specify a string, a number or a cell ...

## GETPIVOTDATA Formula Excel – How to use GETPIVOTDATA Function with Pivot Table

Posted on 10 June 2009 Syntax of GETPIVOTDATA Formula Example of GETPIVOTDATA Formula Making GETPIVOTDATA Formula Dynamic GETPIVOTDATA formula is used with a pivot table to fetch values from it. GETPIVOTDATA Formula Syntax The GETPIVOTDATA Formula has the following syntax: =GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, ... FieldName N, Item N) However before we delve any deeper in the GETPIVOTDATA formula, let's get back to basics for a minute. Basic structure of a pivot table A pivot table is based ...

## How to generate a sample set with normal distribution in Excel

Posted on 09 June 2009 Normal distribution is one of the most commonly found distribution types in nature. The normal distribution is a continuous probability distribution where the data tends to cluster around a mean or average. If you were to plot the frequency distribution of a normal distribution, you will tend to get the famous inverted bell-shaped curve also known as the Gaussian function. Coming to the point, we are sometimes faced with situations where we would like to test out a hypothesis or carry ...

## Excel #N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM! Error

Posted on 20 May 2009 Have you ever encountered the #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors in Excel? Learn more about these errors and how to avoid them in simple terms. Why does Excel show #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors? #NAME? Error The #NAME error is generated when a cell tries to reference a non-existent cell call a non-existent function. If you enter a formula that does not exist ...

## Excel VBA For Do While and Do Until Loop

Posted on 21 April 2009 The VBA For Loop and the Do While loops are the two most important loops in the VBA language. They can be used to carry out a specific task in an iterative manner till such time that a condition is met (or the VBA loop is terminated). Download the worksheet with examples of the for, do while and do until loops in VBA The VBA For Loop The VBA For ...

## Excel Offset Function

Posted on 01 April 2009 The OFFSET function in Excel returns a reference to a range that is offset by a specific number of rows and columns from another range or cell. Before we begin, you can download the workbook containing a few examples of the offset function in excel here. The syntax for the Offset function in excel is: Offset( range, rows, columns, height, width ) range is the starting range from which the offset will be applied. Often it is a single cell but can ...

## VLOOKUP Function Error

Posted on 18 October 2008 You think you've entered the right formula and yet you get the dreaded #N/A error on the sheet.....well....here are some useful tips to get you through the vlookup blues! =vlookup(value_to_match, range_to_find_this_value, which_column_to_return , try_to_approximate_if_no_match) (Delimited) text being compared to numbers If you have imported data from a plain text file (a .txt file), your data may most likely have numbers which have been converted to string. Excel will still display the values ...

## 60 Useful Excel Formulas That Are Good To Know

Posted on 23 August 2008

60 useful excel formulas that are a good to know DATE = Returns the serial number of a particular date DATEVALUE = Converts a date in the form of text to a serial number DAY = Converts a serial number to a day of the month EOMONTH = Returns the serial number of the last day of the month before or after a specified number of months MONTH = Converts a serial number to a month NOW = ...