Mini-dashboard with FILTER and CHOOSECOLS

Hi - Dave here. Happy Friday! One of the coolest things about the new functions in Excel is how easily you can build a mini-dashboard report with a single formula. For example, in the worksheet below, the goal is to display projects over 90,000, sorted by value in descending order. In addition, we only want the name of the project and the value. This is all done with this formula in cell F4: =CHOOSECOLS(SORT(FILTER(B9:G20,E9:E20>90000),4,-1),1,4) The beauty of this approach is that the source data remains untouched and can be updated or replaced at any time. Plus, because we are using a formula, any updates will appear instantly. [Create a mini-dashboard with a single formula]( [[Download the workbook and read the full explanation](] Excel has been changing very quickly in the last few years and it's easier than ever to do things like filter, sort, and transform data with simple formulas. FILTER, in particular, is a powerhouse function that can solve many hard problems. This example also shows how the newer CHOOSECOLS function can be used to simplify data. I've also included an even simpler formula based on the TAKE function. Combined with the SORT function, TAKE is an excellent tool for creating lists like "Top 3 projects", "Top 5 salespeople", etc. Note: this example requires Excel 365. However, I've also included a Pivot Table solution on Sheet 3, which will work in any version of Excel. Excel formulas We maintain a list of over 500 working formulas [here](. If you need more structure, we also offer [video training](. Have a great weekend! Dave [View this email in a browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2024 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](