Dynamic Array FILTER by Month & Quarter

Welcome to Excel Jet Consult blog. In this tutorial, we are going to learn how to use the elegant dynamic array FILTER Function to filter dataset by Month and Quarter. Let’s get started Video Sample Data In the screenshot below, we have sample dataset for filtering by month. We are going to use the same dataset for for filtering by […]

Continue reading

ADVANCED Filter Super Trick to Reorder Entire Columns without Criteria

Welcome to Excel Jet Consult blog post. In this final tutorial on ADVANCED filter, we are going to see how to reorder columns without applying any criteria. Let’s roll Video Sample Data In the screenshot below, we have the same dataset use in the previous tutorial. If you look at it closely, you will discover that the main dataset has […]

Continue reading

3 Hidden Tips & Tricks You Probably Don’t Know About ADVANCED Filter

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn about the 3 Hidden Tips & Tricks You Probably Don’t Know About ADVANCED Filter. Let’s get started Video Sample Dataset In the screenshot below, we have our sample dataset with five columns. In addition, we have a drop down list in cell M6 that contains […]

Continue reading

Author LAMBDA Custom Function using New Advanced Formula Editor

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn about the newly released Advanced Formula Editor which is available as an Add-in the Excel Desktop, Mac and Online platforms. Let’s get started Video Advanced Formula Environment The Advanced Formula Environment allows users to create, modify, reuse and import formulas from URL and text. The […]

Continue reading

Calculate Sales Commission for Weekdays & Weekends using Power Query M Code

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to calculate Sales Commission for Weekdays and Weekends using Power Query M Code. Let’s get started Video Sample Data In the caption below, we have Sales Data that is officially formatted as an Excel Table and fSalesData has be given as Table name Load Data to […]

Continue reading

New Automatic Searchable Dropdown in Excel [No More Formula-Based Solution]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to create automatic searchable drop-down in Microsoft Excel without using formulas and VBA. Let’s get started Video Sample Data In the captions below, we have Sales Data in the Data sheet and unique Payment Type in the Table sheet respectively Create List Data Validation […]

Continue reading

Conditional Formatting using XLOOKUP and FILTERS Functions in Excel for the Web

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to use XLOOKUP & FILTER Functions to apply conditional formatting to our Sales Dataset. Let’s dive in. Video Sample Data In the screenshot below, we have Sales Data in the XLOOKUP and FILTER sheet tab. Our goal is to use XLOOKUP Function to format […]

Continue reading

Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]

Welcome to Excel Jet Consult blog post. In this Excel tutorial, we will how to create COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS calculation using Power Query and Worksheet Functions. Video Sample Data In the caption below, we have the Sales Data that is officially formatted as an Excel Table. fSalesData is given as the Table Name Load to Power Query […]

Continue reading

Searchable Drop-down in Excel for the Web [New Feature]

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to create dynamic searchable dropdown in Excel for the Web. Let’s get started Video Sample Dataset In the screenshot below, we have our sample dataset that consists of three columns in the Data sheet – Account Manager, State and State Abbreviation. The data is formatted […]

Continue reading