Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the KEEPFILTERS DAX Function to create Single and Multiple Columns Filters. Let’s get started. Definition of Function and Syntax KEEPFILRERS modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function. The KEEPFILTERS function allows you to modify this behavior. When you use […]
Continue reading
FILTER As Alternative To VLOOKUP, INDEX MATCH & XLOOKUP Functions
Introduction Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to use FILTER Dynamic Array Function to perform the same operation that lookup family functions such as VLOOKUP, INDEX MATCH and XLOOKUP will perform. Let’s get started Definition of FILTER Function & Syntax The FILTER Function filters a range or an array Syntax […]
Continue reading
COALESCE DAX Function
Welcome to Excel Jet Consult blog post. In this video tutorial, I demonstrate how to use the new COALESCE DAX function made available in March 2020 Power BI updates.
Continue reading
Create PivotTable From Multiple Consolidated Ranges
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn the old way of creating PivotTable from multiple consolidated ranges using the Office 2003 PivotTable and PivotChart wizard. Let us get started In the captions below, we have January: March Report of sales data showing Account Manager and the values for each Product which are Seafood, Beverages, […]
Continue reading
Summing Every Third Rows Across Multiple Columns
Introduction Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to sum every third rows across multiple columns. Let’s get started Sample Data In the caption below, we have our sample data we will use to achieve the technical calculation. Note, our data is formatted in Excel Table and we provided Sales_Data as the Table Name Calculation […]
Continue reading
Modelling Using New LET Function
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about how to use the newly released LET Function for modelling. Let’s get started Definition of Function & Syntax The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET […]
Continue reading
Advanced: Case-Sensitive EXACT Function with Double SUMPRODUCT
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use what has become my favorite function in Excel – SUMPRODUCT with rarely known Case Sensitive function – EXACT. I was inquisitive minded to discover whether it is possible to sum values based on text. That led me into about 20 minutes of trying to […]
Continue reading
How To Embed YouTube Video in Excel Workbook
Welcome to Excel Jet Consult blog post. In this tutorial, I will show us how to Embed YouTube Video in Excel Workbook. Let’s get started Steps Open the workbook to embed the video In the Add-ins group of the Insert tab, click on Get Add-ins In the Office Add-ins dialogue box, locate Web Video Player and click on Add and Continue In the caption below, we have the Web Video Player […]
Continue reading
How To Refresh PivotTable using VBA
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to refresh PivotTable using VBA. Let’s get started In the caption below, we have a sample data to the left and the supplementary data to the right. Note, the data is formatted in Excel Table. So, when we add the supplementary data to the main data, […]
Continue reading
Delete Blank Rows without impacting non-contiguous columns
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn of the hidden secret d deleting empty rows without the operation impacting the non-contiguous columns with data In the caption below, we have data in columns A to C with empty rows which we want to delete. We also have data in columns E to H which, […]
Continue reading