Excel assign sequence number to duplicate records

Excel assign sequence number to duplicate records
Posted on
This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function. 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 (but records are in ascending order of time), you may want to add a number to each transaction so that you can easily identify and filter the ...
More

Excel dependent dropdown list | dependent validation list

Excel dependent dropdown list | dependent validation list
Posted on
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...
More

Excel VBA Range PasteSpecial Method

Excel VBA Range PasteSpecial Method
Posted on
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...
More

Excel VBA set print area

Excel VBA set print area
Posted on
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. ...
More

Excel VBA freeze panes using FreezePanes property

Excel VBA freeze panes using FreezePanes property
Posted on
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...
More

Excel VBA unmerge columns automatically

Excel VBA unmerge columns automatically
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...
More

Excel delete duplicated data in consecutive rows

Excel delete duplicated data in consecutive rows
Posted on
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 ...
More

Create Cartesian product with Excel Query

Create Cartesian product with Excel Query
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...
More

Create Excel Query and update Query

Create Excel Query and update Query
Posted on
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...
More

Excel VBA use Inputbox to select options

Excel VBA use Inputbox to select options
Posted on
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...
More