by Ilker | Apr 25, 2018 | Excel Tips & Tricks
To return an entire column you need array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET. How to return an entire...
by Ilker | Apr 23, 2018 | Excel Tips & Tricks
Your ultimate financial calculator Excel can help find out your monthly payments on a new loan. The function for this task is CUMIPMT and it can calculate cumulative loan interest over a period of time. Syntax =CUMIPMT(annual interest rate/12, periods by month, loan...
by Ilker | Apr 23, 2018 | Excel Tips & Tricks
It’s often a good idea to highlight the top values in a data set to help easily identify the outliers. Here, you can find out how you can do this dynamically using Excel formulas. Syntax =relative cell reference >= LARGE(absolute data range reference, top...
by Ilker | Apr 20, 2018 | Excel Tips & Tricks
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 function. Syntax...
by Ilker | Apr 20, 2018 | Excel Tips & Tricks
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( ...
by Ilker | Apr 19, 2018 | Excel Tips & Tricks
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, Pressing...
by Ilker | Apr 19, 2018 | Excel Macros, Excel Tips & Tricks
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 can be...
by Ilker | Apr 18, 2018 | Excel Macros, Excel Tips & Tricks
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 Dim mystring...
by Ilker | Apr 18, 2018 | Excel Tips & Tricks
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 using a...
