Excel Tips and Tricks
How to convert a URL into a hyperlink automatically
Excel can detect URLs and convert them into hyperlinks when you press the Enter key on that particular cell. When working with a large data set with thousands of rows you may not want to go to each cell and repeat this process. Enter the HYPERLINK...
How to merge columns using formulas
Traditional Copy+Paste might do all you want, but when working with large tables that are constantly updated, it's a tedious task to merge columns of data. Using this formula combination instead will save you time and prevent errors. Syntax =IFERROR( ...
How to add a new line inside a cell
You add a new line into a cell and press Enter just like in any text editor. By doing so, Excel selects the cell below the previous one instead of adding a new line. But, how do you move on to a new line in the same cell? There are two ways to do this,...
How to combine data from multiple sheets
Although keeping the data on different sheets help organizing them in terms of look and feel, this approach may cause problems when you need to get data from all sources. VLOOKUP or INDEX functions can't really help here, and other methods to combine data...
How to loop through each character within a string
You've worked with text data before, used formulas, and created reports, but how do you up your game? Efficiently loop through text data using this simple VBA code to analyze or manipulate strings. Code Sub LoopThroughString() Dim counter As Integer...
How to transpose the rows and columns of a data table
Ever spent time to transpose rows and columns in a data set? It can be a very tedious job to switch rows to columns or columns to rows. It gets even harder if your data is updated frequently. Not anymore, for you can transpose rows and columns in a table...
How to create a random secure password in Excel
Managing and coming up with strong passwords can be challenging. Let us show you how you can create your own custom password generator in Excel. You can download our sample workbook below. Syntax Random Character Generation =RANDBETWEEN(0,9) (Numbers)...
How to shuffle a list of items in a random order
Do you have a list of items or names that you want to sort randomly? Using the RANDBETWEEN, RANK, and INDEX functions in conjunction, you can easily shuffle an item group for a random draw. Syntax =RANDBETWEEN( 1, number of list items ) + ROW() / very...
How to use ROMAN numbers
Excel has a function for converting regular (Arabic) numbers to Roman numbers. Syntax =ROMAN(number, type of conversion) Steps Type =ROMAN( Select or type the cell reference that includes number in Arabic form B3, Type the conversion type, or omit C3 Type...