by Ilker | Mar 27, 2020 | Excel Tips & Tricks
SUMIFS like other …IF or …IFS function are great tools to aggregate data based on a set of conditions. The downside of this approach is that the criteria must be supplied manually, especially when you need to create a summary table. In this guide, we’re going to show...
by Ilker | Jul 11, 2019 | Excel Formulas, Excel Tips & Tricks
A dynamic drop down (also known as a dependent drop down) is a term for drop downs with a dynamic list. The list items of a dynamic drop down get updated according to another input which is usually another drop down as well. A common example is to have lots of items...
by Ilker | Jul 10, 2019 | Excel Tips & Tricks
A common way to populate lists or tables in Excel is using the INDEX function. However, you need to create a helper column or row, to define row and column numbers for the INDEX function. This way, the INDEX can populate return the values into the corresponding cells....
by Ilker | Jul 2, 2019 | Excel Tips & Tricks
Excel doesn’t have a built-in method to sort data randomly. Instead, you need to rely on formulas to sort a list of values randomly. Before dynamic array formulas, you needed to build a structure of formulas and helper columns to achieve this. Thanks to the...
by Ilker | May 14, 2019 | Excel Formulas, Excel Tips & Tricks
Sorting data can substantially help organizing data and generating reports. In Excel, you can sort data with a single click using the Sort icons under the DATA tab. However, this is a permanent solution, meaning that the sorted data will remain that way, and you will...
by Ilker | May 13, 2019 | Excel Tips & Tricks
Ledgers are crucial for keeping track of expenses, and managing the budget of any entity, be it a small house to an international company. Preparing a general ledger (GL) table can be a daunting task, especially when dealing with lots of category codes. If your table...
by Ilker | May 10, 2019 | Excel Tips & Tricks
Date lists are an important part of numerous Excel models. Excel can automatically recognize date-time values, format the cells, and generate a date list quickly with AutoFill or specific functions. However, date-time functions are focused on a generating a single...
by Ilker | Apr 15, 2019 | Excel Formulas, Excel Tips & Tricks
How to generate random numbers in ExcelYou can generate decimals between 0 and 1, and integers between certain limits using the RAND and RANDBETWEEN functions. RANDARRAY function is a new addition to randomization formulas, which is currently only available to a...
by Ilker | Feb 21, 2019 | Excel Formulas
With the dynamic arrays concept, formulas that return an array can spill the results to the neighboring cells automatically. However, this behavior conflicts with legacy implicit intersection formulas that use ranges (arrays). For the sake of backward compatibility,...
