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
Tricks
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