Welcome to Excel Jet Consult blog post. In this Excel video tutorial, we will learn how to convert numbers to proper time format using Power Query. Let’s get started Video Sample Data In the screenshot below, we have transaction data which is officially formatted as an Excel Table using CTRL + T and renamed as Data. Our goal is to convert […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to transform Report to Dataset using Power Query. Lets get started Video Sample Report In the caption below, we have Sales Report that shows the Account Managers name, Product Name, Payment Method, Sales Amount for January to March, which is Quarter One, April to […]

Welcome to Excel Jet Consult blog post. In this tutorial, we going to learn how to combine TEXTBEFORE & TEXTAFTER Function with SUMIFS in Excel Video Sample Dataset for the First Example In the screenshot below, we have the first sample dataset which contains Full Name. So, what we wanna do is to use the new TEXTBEFORE and the TEXTAFTER […]

Welcome to Excel Jet Consult blog post. In this tutorial, we shall consider additional two out of the 14 newly released function by the Microsoft Excel team. Let’s proceed Video VSTACK Function The VSTACK Function appends data to the first blank cell at the bottom of a range. Syntax =VSTACK(array1,[array2],…) The VSTACK function syntax has the following argument: array    The […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to look at the brand new TEXTSPLIT, TEXTAFTER & TEXTBEFORE functions. Let’s get started. Video TEXTSPLIT Function On March 16th 2022, Microsoft Excel Team released 14 brand new functions which you can read here. The TEXTSPLIT Function splits text strings by using column and row delimiters. The […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to use the SWTICH and the FILTER Functions to create a dynamic report. Let’s get started Video SWITCH Function The SWITCH Function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there […]

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 […]

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 […]

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 […]

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 […]