This Excel tutorial explains how to delete duplicated data in consecutive rows (delete the value, not delete row). You may also want to read: Excel delete blank rows Remove duplicates in text Excel delete duplicated data in consecutive rows In conventional Pivot Table layout (Tabular), all data are grouped together and duplicated data would not repeat. Division Department ...

This Excel tutorial explains how to create Cartesian product with Excel Query. You may also want to read: Microsoft Access create Cartesian product with Cross Join Create Excel Query and update Query Create Cartesian product with Excel Query In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combinati...

This Microsoft Excel tutorial explains how to create Excel Query, create Join Table, update Query, add Query criteria. Microsoft Excel create Query Similar to Microsoft Access Query, Excel allows users to create Query through graphical user interface, which means you don't need to have technical skills to write any SQL statement. Although Microsoft Excel has the capability to do that, Access und...

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE. Excel VBA use Inputbox to select options In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case. In the below example, I create three options in the...

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA. You may also want to read: Excel show all comments in worksheet Excel consolidate all Comments in the workbook Excel - delete all Comments in Worksheet In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right cl...

This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet Excel compare time or apply condition on timestamps When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare. If the two timestamps have different dates, we can still minus the timestamps to compare. But if we only wan...

This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable. You may also want to read: Function, Sub, Method, Property Difference between Dim and Set Scope of local variable / global variable / private variable Procedure Level When you declare a variable inside a Sub Procedure or Function (local variab...

This Excel tutorial explains how to change default delimiter of CSV files from comma to other symbols. You may also want to read: Excel VBA convert CSV to Excel Excel change default delimiter of CSV files When you open a CSV file in Excel, you can see all columns are delimited by comma, you don't have to define which character you want to use to delimit the row. It is because the default d...

This Excel tutorial explains how to convert CSV to Excel in Excel VBA. You may also want to read: Access Excel FSO File Methods Import Chinese CSV to Excel Convert CSV to Excel in Excel VBA CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. Thi...

This Excel tutorial explains how to copy each worksheet to new workbook in Excel VBA. You may also want to read: VBA Worksheets.Copy Method to copy worksheet VBA copy workbook or file Excel VBA copy each worksheet to new workbook Suppose you have a workbook that contains multiple worksheets, now you want to copy each worksheet to a new workbook. In this post, I will demonstrate how to d...