# Fixing Improper Dates in Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, I will share with us how to solve the problem of improper date in Power Query. Let’s get started Quite often, I do get questions from people who have challenge of working with dataset that wasn’t correctly entered on Excel. Hence, I write this blog post to address the problem. […]

# Conventional Subtotalling in Excel and VBA

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform subtotal and the alternative way to perform the same operation using VBA. Let’s get started SUBTOTAL IN CONVENTIONAL EXCEL METHOD In the caption below, we have our dataset in Subtotal sheet tab We want to calculate the Net Salary for each of the Department […]

# SUMIFS/INDEX/MATCH VS. SUMPRODUCT Functions: HR Data Example

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how we can use SUMIFS/INDEX/MATCH and SUMPRODUCT differently to achieve the same result. Let’s get started In the captions below, we have HR Salary Data in Dynamic SUMPRODUCT and Dynamic SUMIFS sheet Note, we have list data validation in cells M2 (List of unique department), and […]

# Distinct Count In Pivot Table: Sales Example

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to count distinctive values in Pivot Table. Let’s get started In the caption below, we have a data set of 1040 transaction records. In addition, the data set is formatted in Excel Table using CTRL + T We want to ascertain how many unique Customers purchased […]

# Dynamic Loan Amortization Schedule

Welcome to Excel Jet Consult blog post. In this video tutorial, I demonstrated how to prepare dynamic loan amortization schedule. Do enjoy the short video

# Extract Leftmost and Rightmost Names using PQ M Functions

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the Power Query M Function to extract First and Last Name from Full Name. Let’s get started In the caption below, I have simple data in my Power Query. I want to extract the leftmost names i.e Phoebe, Tina Abiola etc to be my […]

# Excel Office 365: Ideas

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the new Ideas tool in Excel available to Office 365 users. The Ideas enables users to have a deeper understanding of data through high-level visual and summaries, trends and patterns. The Ideas tool is in the Home tab as seen in the caption below […]

# DAYS Function

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about the DAYS function and how we can use it to calculate dates between two dates. Let’s get started DAYS Function returns the number of dates between two days DAYS Syntax                                                       =DAYS(end_date, start_date) In the caption below, we want to calculate how man days that is in […]