by Ilker | Jul 10, 2018 | Excel Tips & Tricks
This article shows you how to generate month list by formulas and how to display Excel month names instead of actual date values. We will use DATE, YEAR and MONTH formulas as well as Number Formatting. Syntax =DATE( YEAR( previous date ), MONTH( previous date )+1, 1)...
by Ilker | Jul 10, 2018 | Excel Tips & Tricks
This article shows how to calculate Excel table totals by using Excel Table which is one of the most underrated features in Excel. Steps Select a cell inside your table or whole range Press Ctrl + T or click Table icon under INSERT tab on the ribbon Make sure My table...
by Ilker | Jul 9, 2018 | Excel Tips & Tricks
Although Excel has a built-in sort feature, there are times you may need to sort data by formula or in a formula. This article shows how to use Excel sort function with SMALL and ROW functions. Syntax =SMALL( column of list, ROW()-ROW(row of list’s title)) Steps...
by Ilker | Jul 9, 2018 | Excel Tips & Tricks
Entering data into an Excel file manually carries the risk of incorrect entries. Using Excel data validation feature reduces and in many cases complete eliminates this risk. In this article, we are going to show how to combine COUNTIF function with Data Validation...
by Ilker | Jul 6, 2018 | Excel Tips & Tricks
Creating a dynamic Excel drop down list is very useful If you have a list that is updated frequently. This article shows you how to create a dynamic drop down list with the help of OFFSET and COUNTA functions. Syntax =OFFSET(title of list, 1, 0, COUNTA(column that...
by Ilker | Jul 4, 2018 | Excel Tips & Tricks
This article shows you how to get Excel cell address of a lookup result by using the CELL and INDEX functions. Syntax =CELL( “address”, INDEX( data, MATCH( lookup value for row, lookup range for row, 0 ), column index ) ) Steps Start with the =CELL(...
by Ilker | Jul 3, 2018 | Excel Tips & Tricks
Excel charts are one of the most used and easy to understand data visualization tools. Excel provides many chart types as well as numerous personalization options. One of the cool features is the ability to change number format in Excel chart. Steps Double-click on...
by Ilker | Jul 2, 2018 | Excel Tips & Tricks
Case sensitive count can not be handled easily using standard count formulas like COUNTIF or COUNTIFS. In this article, we’ll shot you how you can count case sensitive data by using a combination of SUMPRODUCT and EXACT functions. Syntax...
by Ilker | Jun 13, 2018 | Excel Tips & Tricks
The AVERAGEIFS function can calculate the average of values with multiple conditions. However, these conditions are linked with AND operator. But if you want to handle AVERAGE OR combination, AVERAGEIFS function will not work. This article shows how to calculate...
