Excel Rank Function

This tutorial explains how to use Excel Rank Function to give a number a rank in a range of number. Syntax of Rank Function RANK(number,ref,[order]) Number Required. The number whose rank you want to find. Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. Order Optional. A number specifying how to rank number. If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order. I...
More

Excel Hyperlink Function

This Excel tutorial explains how to use Excel Hyperlink Function to add hyperlink to Cell and send email. Excel Hyperlink Function Excel Hyperlink Function is to add a hyperlink to a Cell. You can add hyperlink without using any Function. Right click on a Cell and then select Hyperlink to enter a web address or email address. With Excel Hyperlink Function, you only need to enter arguments for the Function instead of right clicking a Cell. This is useful if you are trying to create a ...
More

Excel GetPivotData Function

This Excel tutorial explains how to use Excel GetPivotData Function to get Pivot Table Data. You may also want to read: Create Pivot Table using Excel VBA Excel VBA refresh all Pivot Table or Pivot Cache Excel GetPivotData Function Excel GetPivotData Function is used to get data in Pivot Table. For example, in a Pivot Table above, if you type =G3 in a Cell, a formula will generate automatically. =GETPIVOTDATA("Salary",$E$1,"Department","IT","Name","John") If you drag the form...
More

Excel Cell Function to check Cell format, address, contents

This Excel tutorial explains how to use Cell Function to check Cell format, address and contents. You may also want to read: Excel ColorIndex Property Excel verify Number format and convert Text to Number Excel Cell Function to check Cell format, address, contents Excel Cell Function is a very powerful Function that tells various information of a Cell. Cell Function has a list of arguments regarding Cell information. Syntax of Cell Function CELL(info_type, [reference]) [referen...
More

Excel Indirect Function to convert text to Reference

This tutorial explains how to use Excel Indirect Function to convert text to Reference, and how to use in Data Validation. Excel Indirect Function Excel Indirect Function is to convert text to Reference (or cell value). For example, formula =A1 returns A1 value because A1 is considered as Reference, but ="A1" is considered as Text. Excel Indirect Function can convert the "A1" Text to become Reference. One popular use of Excel Indirect Function is to use in Data Validation where you convert ...
More

Excel Address Function to convert column number to letter

This Excel tutorial explains how to use Excel Address Function and how to convert column number to column letter. Excel Address Function Excel Address Function is used to combine column number and row number to return Cell address in Text format. Excel Address Function is most useful is converting column number to column letter, or use with Indirect Function to convert the Text address to Range Object. Syntax of  Excel Address Function ADDRESS( row, column, [abs_num], [a1], [sheet_text] )...
More

Excel MRound Function round to nearest multiple

This tutorial explains how to use Excel MRound Function round to nearest multiple Excel MRound Function round to nearest multiple Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we don't want the bonus amount looks better. For example, $11040 is rounded to $11000. If you fail to use Excel MRound Function, navigate to Developer &g...
More

Excel Subtotal Function to calculate Auto Filter value

Excel Subtotal Function to calculate Auto Filter value Excel Subtotal Function is a collection of aggregate function (Sum, Average, Min, etc), in which you can specify in argument which aggregate function to use. There are two major differences between Subtotal Function and normal aggregate Functions 1. Subtotal Function can calculate base on filtered value in Auto Filter, while normal aggregate Function calculates all values in a Range regardless of the criteria in Filter. 2. Subtot...
More

Excel Hyperlink Function to link to other cell, open website, open document

What does Excel Hyperlink Function do? Excel Hyperlink Function is to create  a hyperlink in a Cell. The hyperlink can be linked to external website or a Cell in a worksheet. In Excel VBA, there is a similar Method called FollowHyperlink, but it actually “go to” hyperlink instead of creating a link for user to click on. Syntax of Excel Hyperlink Function HYPERLINK(link_location, [friendly_name]) link_location The path of the file you want to link to. To link website, type "http://...
More

Excel EDATE Function to add month

What is Excel EDATE Function? Excel EDATE Function is to add (or subtract) specified months to a date. For example, adding 2 months to Jan 14, 2015 would be Mar 14, 2015. Syntax of EDATE EDATE(start_date, months) start_date A date to add months to months Positive integer to indicate to add month to start_date, negative to indicate subtraction By default, a Cell Format is "General" which does not display a date as date format (such as mm/dd/yyyy), instead date is displayed...
More