In this video tutorial, I talked about the new feature recently discovered in Officer 365 Insider. I called the new feature as In-cells Data Types. The new features enables users subscribed to Insider to easily type in at least 3 recognizable Stock Tickers in three contiguous cells to be able to generate more information. This same rule applies to Geography
Continue readingTag Archives: Data Analytics
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 […]
Continue reading
VLOOKUP in Excel and Power Query PBI
Welcome to Excel Jet Consult. In this tutorial, we want to look at how to perform vertical lookup in Excel and Power Query PBI. Let’s get started VLOOKUP in Excel VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from a column specified. VLOOKUP Syntax =VLOOKUP(lookup_value, table_array, […]
Continue reading
Understand One-To-Many Relationships in Power BI
Welcome to Excel Jet Consult blog post. In this tutorial, we want to take a cursory look at one-to-many relationships in Power BI. Creating relationships across imported multiple tables is important to perform accurate and reliable and easy-to-understand analysis for sound decision making. By default, Power BI is equipped to perform automatic relationship detection and creation between fact and dimension […]
Continue reading
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 […]
Continue reading
Locking Objects
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to lock objects in our report. Quite often, when we create our dashboard which involves so many steps, we’d like the report to be dynamic and not to be moved around or disorganized when interacting with the report. Power BI provides the functionality that enables locking […]
Continue reading
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 […]
Continue reading
Permanently Tabulate Pivot Table Report & Repeat All Item Labels
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to permanently tabulate Pivot Table report and how to repeats all item labels. Let’s get started. In the caption below, we have a Pivot Table report showing Sum of Total by States and Account Manager. Notice that, we have Row Labels in cell A3 where the […]
Continue reading
Rolling Average In Power BI Power Query
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to dynamically calculate Rolling Average in Power BI Power Query. Let’s get started. Statistically, Rolling Average or Running Average is a calculation that analyzes data points by creating a series of averages of different subsets of a comprehensive data set. In the caption below, we have […]
Continue reading
SAMEPERIODLASTYEAR DAX Function
Welcome to Excel Jet Consult blog post. In this video tutorial, we learn how to use the SAMEPERIODLASTYEAR DAX Time Intelligence function.
Continue reading