This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function. You may also want to read: Excel check duplicate values using Conditional Formatting Excel delete duplicated data in consecutive rows Excel assign sequence number to duplicate records Suppose you have two sales transactions on the same date but you don't have any transaction timestamp (...

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

This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values. Excel VBA Range PasteSpecial Method In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula. In Excel VBA, Paste Speical is done through Range.PasteSpecial...

This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA. Excel VBA set print area To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property. ...

This Excel tutorial explains how to freeze panes in Excel spreadsheet and freeze panes in Excel VBA using FreezePanes property. Freeze Panes in Excel spreadsheet To explain how to freeze panes in Excel VBA, first I have to explain how to freeze panes in Excel spreadsheet. Freeze Row The most common kind of freeze panes is to freeze the first row as it contains the header of the column. So that...

This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column. You may also want to read: Excel VBA reformat merged cells to row Excel VBA separate line break data into different rows Excel VBA unmerge columns automatically For some systems, when you export a non-Excel report (e.g. PDF) to Excel format, some columns could be merged in order to fit the wi...

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. You may also want to read: Excel automatically select specific columns using Custom Views and Query 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 techn...

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