Resources
How to highlight a column
To highlight a column may be tricky because there is no built-in option in Excel's Conditional Formatting feature. Here, you can find out how you can do this dynamically using Excel formulas. Steps Begin by selecting rows by clicking on column letters Open...
How to highlight a row
To highlight a row may be tricky because there is no built-in option to in Excel's Conditional Formatting feature. Here, you can find out how you can do this dynamically using Excel formulas. Steps Begin by selecting rows by clicking on row numbers Open...
How to highlight duplicate values in a data set dynamically
It’s often a good idea to highlight duplicate 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 =COUNTIF(absolute data range reference, relative cell reference)...
How to return an entire row
To return an entire row you need to use 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...
How to return an entire column
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...
How to calculate cumulative loan interest over a period of time
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...
How to highlight the top values in a data set dynamically
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,...
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( ...