Excel convert column letter to column number vise versa

This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel. Excel column letter to column number / column number to column letter conversion Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet. For example, for Cell D1, column number is 4, while column letter is D. Excel convert column number to column letter To convert column number to column letter, make use o...
Read More

Excel Vlookup second matched value or specific occurrence

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or specific occurrence In traditional Vlookup, you are allowed to vertically lookup the first value that ma...
Read 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 ...
Read 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] )...
Read 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...
Read 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://...
Read More

Excel Custom Format in Format Cells

This Excel tutorial explains how to use Excel Custom Format in Format Cells to format date, time, number, text, conditional formatting, leading zero, etc. You may also want to read: Excel Extract Time from Date Time Excel Display Time from Date Time What is Excel Custom Format? Excel guesses the most appropriate format for the cells when you type something in a Cell. For example, if you type 31/1/2014, Excel guesses it would be a Date, Format is dd/m/yyyy, but it would not guess it is...
Read More

Excel maximum number of digits workaround

Excel maximum number of digits In Excel spreadsheet, there is a limit for storing a number in a Cell, which is 15 digits (15 numbers) regardless of whether the numbers are decimal places. Excel call this "15 significant digits of precision" which adheres to "IEEE 754". It is not difficult to understand why Excel limits the number of digits, because some numbers are infinite, such as π number (3.1415....), it is not possible to use infinite computer memory to store infinite numbers. We need to c...
Read More

Auto Open Excel with Windows Task Scheduler

Auto Open Excel with Windows Task Scheduler Windows Task Scheduler allows you to trigger an event in Windows when specific criteria is met. In this tutorial, I will not detail what you can do with Task Scheduler (since there are too many features), instead I will focus on how you can auto open Excel with Windows Task Scheduler. Since I am using Chinese Windows but I want to write this article in English, all the screenshots are copied from the below website. http://www.sevenforums.com/tut...
Read More

Excel 3D Sum to Sum the same Cells in different worksheets

This Excel tutorial explains how to use Excel 3D Sum to Sum the same Cells in different worksheets. You may also want to read: Excel VBA loop through worksheets in the same workbook What is Excel 3D Sum Excel 3D Sum to Sum the same Cells in different worksheets. You can achieve the same task without 3D Sum, but 3D Sum can simply your formula. The name "3D Sum" is not a Function name, you cannot use a Function like =3DSum() , it is just a special way to use SUM Function. Before you know ...
Read More