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 | Jun 25, 2019 | Excel Tips & Tricks
Excel can get the stock market data from 60 different stock exchanges around the world. However, this information will not refresh automatically, and currently you need to refresh stock data manually, or write a VBA code to automate the process. In this article, we...
by Ilker | May 22, 2019 | Excel Tips & Tricks
Excel is one of the best platforms to do stock quote analysis. However, you traditionally had to pull this data either manually, or using third party tools. Now, you can do this using built-in Excel features if you are running Office 365. In this article, we are going...
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 | 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,...
by Ilker | Feb 21, 2019 | Excel Formulas, Excel Tips & Tricks
With the introduction of the dynamic arrays and spill concepts, Microsoft has also released a spill range operator (#). This operator is designed to reference spill ranges dynamically.For example, the cell D3 has =UNIQUE(B3:B16) formula which spills the return values...