Excel convert data from column to row

Excel convert data from column to row
This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row). You may also want to read: Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet Excel convert data from column to row Different database have different structure. Some database put similar data in the same column wi...
More

Excel Exact Function – compare case sensitive text / case sensitive lookup

Excel Exact Function – compare case sensitive text / case sensitive lookup
This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup. Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, "Peter" is considered same as "peter" in Excel. Vlookup also ...
More

Access Excel VBA convert number to English words

Access Excel VBA convert number to English words
Posted on
This Access Excel VBA tutorial explains how to convert number to English words in VBA. Access Excel VBA convert number to English words I want to share a custom Function I found in Microsoft Support website, which is to convert number to English words using Excel VBA (also applicable to Access). I want to highlight that the article is unlike other articles written by other contributors, this art...
More

Excel delete all pictures or delete all pictures in specific Cells

Excel delete all pictures or delete all pictures in specific Cells
Posted on
This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells. Excel delete all pictures When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to  delete all pictures...
More

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