Excel dependent dropdown list | dependent validation list

Posted on June 4, 2017 by Wyman W

This Excel tutorial explains how to create dependent dropdown list where dropdown list values depend on another list value. Excel dependent dropdown list / dependent validation list Suppose you have a continent list in Excel (Asia, North America, South America, etc). When an user selects a continent from a dropdown list, the user can then select the second dropdown list which contains all the countries of your selected continent, so the country list is dependent on the continent value. For ...

Excel VBA find all Cells contain Named Range

Posted on December 26, 2016December 26, 2016 by Wyman W

This Excel VBA tutorial explains how to find all Cells contain Named Range using Macro. You may also want to read: Find all external links and broken links in workbook Replace Named Range with formula Find all Cells contain Named Range In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) We can refer to Name Manager to see the formula the Named Range is referring to, but it does not tell which Cells are using the Named Range. Therefore...

Excel VBA clear cell contents with ClearContents Method

Posted on October 15, 2015October 16, 2015 by Wyman W

This Excel tutorial explains how to clear cell contents / clear cell formats using Clear, ClearContents, ClearFormats Method. Excel VBA clear cell contents There are several kinds of "clear" related Excel VBA Methods for different scenarios. I will also explain how to clear single cell contents, clear merged cells contents, and clear blank cells. Clear single cell contents with ClearContents There are two ways to clear single cell contents (clear value in a Cell but not formatting) As...

Excel Delete Blank Rows

Posted on October 12, 2015February 26, 2017 by Wyman W

This Excel tutorial explains how to delete blank rows in Excel with VBA and without VBA. You may also want to read: Excel delete blank rows and blank cells before import into Access Excel delete duplicated data in consecutive rows Excel Delete Blank Rows Sometimes we have data that contains blank rows, one example is data with pivot table layout where data are grouped together and repeated items are blank. Previously I wrote a post about filling blank rows, this post is about delet...

Access Excel remove line break and carriage return

Posted on August 22, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text. You may also want to read: Excel VBA separate line break data into different rows Access Excel remove carriage return and remove line break Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspec...

Excel verify Text format and convert number to text

Posted on July 11, 2015 by Wyman W

This Excel tutorial explains how to verify Text format in a column. If it is a Number then convert Number to Text. You may also want to read: Excel verify Number format and convert Text to Number Excel verify Text format Three kinds of Cell contents are considered as Text 1) Non-number - such as alphabet, symbol 2) A mix of number and non-number 3) Number with small triangle on the top left of the Cell Number can be in two forms in Excel - Text and Number. If a Number is a ...

Excel consolidate all Comments in the workbook

Posted on June 26, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro. You may also want to read: Excel show all comments in worksheet Excel delete all comments in Workbook Excel consolidate all Comments in the workbook Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook. VBA Code - consolidate all comments Public Sub consolComments() counter = 1 ...

Excel show all comments in worksheet

Posted on June 23, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to show all comments and hide all comments in workbook. You may also want to read: Excel consolidate all Comments in the workbook Excel delete all comments in Workbook Excel show comments in worksheet Comment is a yellow dialog box that you can write comment regarding specific Cell. To insert a Comment, right click on a Cell and then select Insert Comment When we insert a Comment into a Cell, there would be a red triangle on the top right of ...

Excel verify Number format and convert Text to Number

Posted on June 22, 2015July 12, 2015 by Wyman W

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number. You may also want to read: Excel verify Text format and convert number to text Excel Cell Function to check Cell format, address, contents Excel verify Number format Number can be in two forms in Excel - Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell. By default, number in Text format aligns to the left while Number alig...

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