Category Archives: Excel in Action

(Kind of) Paginated report in #Excel

Posted on October 14, 2023 by MF

A picture tells a thousand words. This is what we want to achieve. It is only “kind of” a paginated report because it works only on the application (Excel) but not for printing. 😅 With dynamic array and new functions … Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , , | Leave a comment

Simple Basket Analysis – Formula approach with #Excel 365

Posted on March 25, 2023 by MF

The challenge – How many transactions with just Brand A and B together? In my previous post / video, I solved this problem with Power Query. If you are using #Excel 365, did you know you can solve this problem … Continue reading →

Rate this:

Posted in Excel 365, Excel in Action, Formula | Tagged , , , | Leave a comment

[Video] How to create a dependent dropdown list in #Excel

Posted on July 1, 2021 by MF

This is the follow up post from my previous post – How to create a dependent dropdown list in #Excel. As mentioned, it could be easier to illustrate the concept using a video, especially for the use of Dynamic Arrays. Here … Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , , , , | Leave a comment

How to create a dependent dropdown list in #Excel

Posted on June 27, 2021 by MF

…with and without Dynamic Arrays (which is available to Microsoft 365 users only) Got a question from a friend recently: Is it possible to show a different set of questions in a dropdown list depending on the answer input from … Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , , | 2 Comments

Input data on one worksheet… Get summary on another worksheet in #Excel

Posted on October 17, 2020 by MF

It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one … Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , | Leave a comment

SUM the last X non-blank values in a column in #Excel

Posted on September 27, 2020 by MF

Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He … Continue reading →

Rate this:

Posted in Excel in Action, Formula | Tagged , , , | 2 Comments

The power of Dynamic Arrays in #Excel 365

Posted on October 26, 2019 by MF

… in creating dynamic drop-down with conditions Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I … Continue reading →

Rate this:

Posted in Excel in Action, Excel Tips | Tagged , , , , | Leave a comment

Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

Posted on May 13, 2019 by MF

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post … Continue reading →

Rate this:

Posted in Excel in Action, Power Query | Tagged , | 5 Comments

Identify missing files in a folder with Power Query

Posted on May 1, 2019 by MF

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a … Continue reading →

Rate this:

Posted in Excel in Action, Power Query | Tagged | Leave a comment

Turn CSV data into insight like a pro with Excel 2016

Posted on March 3, 2019 by MF

Many people are not aware of how easy-to-use and powerful modern Excel is. I mean Excel 2016 or later. In this post, I will show you how to turn CSV data into insight in a just a couple of minutes like a pro. Techniques used: Flash Fill, Quick Analysis and two new chart types (Histogram and Pareto). Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , , | 2 Comments