Excel Display Time from Date Time

This Excel tutorial explains how to use Custom Format to display Time from Date Time. You may also want to read: Excel Extract Time from Date Time Excel Custom Format Excel Display Time from Date Time In my previous post, I have explained how to extract time from date time. What I mean "extract" is that the date is using formula to  completely remove date from date time. In this tutorial, I will explain how to use custom format to display Date Time in Time format. Custom Format to disp...
More

Excel find all external links and broken links in workbook

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro. You may also want to read Excel VBA Workbook LinkSources Method Excel VBA refresh closed workbook Excel find all external links and broken links in workbook There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article ...
More

Excel VBA Function sum colored Cell count colored Cell

This Excel tutorial explains how to sum colored Cell and count colored Cell in Excel worksheet. Excel VBA Function sum colored Cell and count colored cell In my previous posts, I have explained how to use ColorIndex Property to find the first colored Cell. In this Post I will create a custom Function to count colored Cell of a Range and sum colored Cell To recap the previous posts, you can click on the followings. Excel VBA custom Function Find the first colored Cell value VBA Excel...
More

Excel timer countdown for test using Application.OnTime

This Excel tutorial shows how to make Excel timer countdown for test using Application.OnTime You may also want to read: Excel VBA Application.OnTime Method to schedule event with timer Send Email in Excel VBA using CDO and OLMailItem Excel timer countdown for test using Application.OnTime If you are going to job interview, you may be asked to do an Excel test. I have been thinking it would be awesome to make a countdown in the worksheet to tell the interviewee how much time is left. ...
More

Excel VBA Application.OnTime Method to schedule event with timer

This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time. You may also want to read: Auto Open Excel with Windows Task Scheduler Excel automatically refresh pivot table Excel VBA Application.OnTime Method to trigger event at specified time You can do the followings with Excel VBA Application.OnTime Method - schedule a procedure to be run at a specified time in the future - schedule a procedure to be run after a specific amou...
More

Excel Fill Blank Rows or blank Cells in inactive Pivot Table

This Excel tutorial explains how to use the value above to fill blank rows (blank cells) in inactive Pivot Table in Tabular format. You may also want to read: Excel Delete Blank Rows Excel VBA reformat merged cells to row Fill Blank Rows or blank Cells Excel Pivot Table Fill Blank Rows In Pivot Table, when you group an item in column, the same grouping name does not repeat itself and leaving blank rows. In the below picture, Division contains blank rows. In Excel 2010 o...
More

Excel VBA Add Table AutoFilter cancel AutoFilter sorting

This Excel tutorial explains how to use Excel VBA to add Table AutoFilter, cancel Table AutoFilter, sorting Table. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Worksheet AutoFilter Excel VBA Add Table AutoFilter or Cancel AutoFilter Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note th...
More

Excel use If condition on aggregate Function using Array

This Excel tutorial explains how to use If condition on aggregate Function using Array such as Average, Median, Mean, Maximum, Minimum. Excel use If condition on aggregate Function using Array Some Excel formula require you to input a range cells as argument in order to calculate a value, such as Sum, Count, Average, Median, Mean, Maximum, Minimum. However, in those formula, you cannot use If Condition on the data Range before calculating Sum, Count, Average, Median, Mean, Maximum, Minimu...
More

Excel count substring in string or text

This Excel tutorial explains how to count substring in string (text). Excel count substring in string (text) Excel does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a Macro solution for your reference. Excel count substring in string using Len and Substitute Function Excel Len Function is to measure the length of text. Excel Substitute Function is to replace a substring in a text. To c...
More

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...
More