Excel Macro remove space in prefix and suffix

Posted on June 10, 2015September 10, 2015 by Wyman W

This Excel tutorial explains how to use Excel Macro to remove space in prefix and suffix in all worksheets. Excel Macro remove space in prefix and suffix Most recently I performed data validation on Excel spreadsheet in which the data contained a lot of spaces in prefix and suffix. I guess this happened because they delimited the data string using space. For example, seperating first name from full name. Suffix with space is not possible to identify by eye, but I had to remove all spaces ...

Excel data validation and combo box

Posted on June 7, 2015February 22, 2016 by Wyman W

This Excel tutorial explains how to create Data Validation (drop down box) and Combo Box (Form Control / ActiveX Control). You may also want to read: Excel Indirect Function to convert text to Reference Excel difference between Form Controls vs ActiveX Controls Excel Data Validation and Combo Box If you want to design an Excel template to restrict users to input specific values instead of free text, you need to create a drop down box for users to choose values from. There are three...

Excel convert column with Date and Text to Date

Posted on June 3, 2015June 7, 2015 by Wyman W

This Excel tutorial explains how to convert a column with Date and Text to Date. Excel convert column with Date and Text to Date Excel has a built-in Function DateValue, which converts a Date in Text format to date serial. What if the column contains both Text and Date? If you use DateValue Function on Date, #VALUE! will return. So how can we convert Text and Date at the same time? Convert Text to Date in Notepad The below method is even faster than DateValue Function or Text to Column, it i...

Excel Range, Variance, Standard Deviation

Posted on May 31, 2015June 28, 2015 by Wyman W

This Excel tutorial explains how to measure variability using Range, Variance, Standard Deviation. You may also want to read: Excel calculate Covariance, Coefficient of Correlation Excel Range, Variance, Standard Deviation In the previous post, I have explained how to measure the central tendency using Mean, Mode, Median. In this tutorial, I will explain how to measure variability using Range, Variance, Standard Deviation. Variability is also known as dispersion, it is to measure of how ...

Excel PERCENTILE.INC vs PERCENTILE.EXC

Posted on May 25, 2015 by Wyman W

This Excel tutorial explains how to use Percentile related Functions, include PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC. Excel Percentile Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the location of observation at 40% of total data. Percentile is commonly used in salary survey t...

Excel central tendency (mean mode median)

Posted on May 16, 2015 by Wyman W

This Excel tutorial explains how to use mean, mode and median to measure Central Tendency. Excel Central Tendency Central Tendency is a statistics term to describe the central point of probability distribution. Central Tendency can be measured by mean, mode and median. This tutorial explains how to use Excel to measure mean, mode and median using Excel formula. Arithmetic Mean Definition Arithmetic Mean is also known as mean or average. Simply speaking, it is sum of all numbers and then div...

Excel Display Time from Date Time

Posted on May 13, 2015May 13, 2015 by Wyman W

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

Excel clustered column chart

Posted on May 10, 2015 by Wyman W

This Excel tutorial explains how to use clustered column chart to compare a group of bar charts. Excel clustered column chart Clustered column chart is very similar to bar chart, except that clustered column chart allow grouping of bars for side by side comparison. This tutorial demonstrates how to build clustered column chart. Example - clustered column chart Suppose we want to build a sales bar chart for Q1 to Q4 in 2015. We can simply build a bar chart that shows sales of each quarter in ...

Access Excel add leading zero

Posted on May 5, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix. Excel add leading zero (non-VBA) Number in Excel can come in two forms - Text or Number. For number in Number format, you may not add zero in prefix For number in Text format, you can add a zero in prefix In order to convert a Number to Text, add a ' in the prefix. For example, instead of typing 00123, type '00123 Sometimes you receive ...

Excel loop workbooks in folders and subfolders with FSO

Posted on May 5, 2015March 8, 2016 by Wyman W

This Excel tutorial explains how to loop workbooks in folders and subfolders with FileSystemObject (FSO). You may also want to read: Excel VBA Dir Function to loop through workbooks in folder Add password to all Excel workbook in folder Excel loop workbooks in folders and subfolders FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, since we are discussing how to loop workbook in folders and subfo...