by Ilker | May 29, 2018 | Excel Tips & Tricks
This article shows how to calculate running count by using COUNTIF and COUNTIFS functions. The running totals help you to track number of value occurrences in your data. Syntax =COUNTIF(expanding range of lookup values, lookup value again) Steps Add a new column to...
by Ilker | May 29, 2018 | Excel Tips & Tricks
This article shows you how to get the nth match with VLOOKUP formula. Unfortunately, Excel doesn’t have a built-in function to find any value beyond the first match. Syntax Unique lookup value: =lookup value & COUNTIF(expanding range of lookup values, lookup...
by Ilker | May 25, 2018 | Excel Tips & Tricks
Do you have a data table where you need to pick all the highest, lowest, best, worst, or equal to 2? COUNTIF, and its bigger brother COUNTIFS formulas can help sum the cells of your choice! In this article, we’re going to show you how to count the number of...
by Ilker | May 24, 2018 | Excel Tips & Tricks
Excel’s regular SUM function includes all values in a range even if they are hidden by a filter or the right click Hide feature. This can be quite tedious when trying to create interactive spreadsheets that can utilize such controls. Using he SUBTOTAL function...
by Ilker | May 24, 2018 | Excel Tips & Tricks
Moving things like your data tables, calculations, and the user interface to separate sheets is often a good idea to not confuse your users and give spreadsheets a nice look. However, as you add more sheets, it can get hard to keep track and you might want to check...
by Ilker | May 22, 2018 | Excel Tips & Tricks
Excel has several built-in features that help analyze data. For example, you can count the number of columns in your data model by selecting those columns, and you will see the count on the bottom right corner. However, this is a repetitive task, one that can be...
by Ilker | May 22, 2018 | Excel Tips & Tricks
Excel has several built-in features that help analyze data. For example, you can count the number of entries in your form by selecting those rows and you will see the count on the bottom right corner. However, this is a repetitive task, one that can be handled with a...
by Ilker | May 21, 2018 | Excel Tips & Tricks
Dropdown type of inputs are very common in Excel models, but dependent dropdowns take it to the next level, and are dynamic. This means that available options of the dropdown menu will change based on other selections. This article will show you how to create...
by Ilker | May 21, 2018 | Excel Tips & Tricks
We often find data in a raw, unorganized form that contain empty cells. In this article we’re going to show you how to remove blanks from a list and create a new table using only formulas. Syntax =IFERROR(INDEX(list range,SMALL(IF(ISTEXT(list range),ROW(rows...
