# Power Query Mash-up Function: Unpivot Columns using Table.Unpivot

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to unpivot columns in Power Query using mash-up function Table.Unpivot M Function translates a set of columns in a table into attribute-value pairs combined with the rest of the values in each row Table.Unpivot Syntax                     =Table.Unpivot( table, pivotColumns, attributeColumn, valueColumn) We want to unpivot the […]

# R1C1 Referencing Style: Switch Column Letters To Numbers Using ALT + FTF Shortcut

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to switch spreadsheet columns alphabet letters to numbers using R1C1 referencing. Let’s get started Open an Excel workbook and execute the command: ALT + FTF In the Excel Options dialogue box, check the R1C1 reference style Click OK In the caption above, we can see […]

Welcome to Excel Jet Consult blog post. In this short tutorial, we want to learn how to solve one of the most commonly experienced problem peculiar to Nigeria excel users. On several occasions, many professionals and excel users have called me to help them figure out how they can compel excel to accept the leading zero (0) when entering mobile […]

# Transfer Data From Multiple Workbooks To A New Workbook

Welcome to Excel Jet Consult blog post. In this post, we will learn how to write VBA code that will automatically pull data from different workbooks and sheets to a new workbook. Let’s get started The first thing we want to do is put the two Excel workbooks into a new folder. Below is the caption of the two Excel […]

# LARGE & AVERAGEIFS Functions With Logical Operators

Welcome to Excel Jet Consult blog post. In this post, we will learn how to combine LARGE & AVERAGEIFS Functions with Logical Operators. Let’s get started LARGE Function returns the k-th largest value in a data set LARGE Syntax                    =LARGE(array, k) AVERAGEIFS Function returns the average for the cells specified by a given set of conditions or criteria AVERAGEIFS Syntax        […]

# Using SUM & CHOOSE Functions To Calculate Total Sales

Welcome to Excel Jet Consult blog post. In this post we want to learn how to use the rarely known lookup family function – CHOOSE. We will wrap the CHOOSE formula inside sum function. CHOOSE Function chooses a value to perform from a list of values based on index number CHOOSE Syntax                =CHOOSE(index_num, value1, [values2], …) In the caption below, […]

# How to Enable Nigerian Naira Currency Symbol

Nigerian Naira currency symbol by default is not available to format numeric values. However, Excel makes it easy to permanently enable the local currency symbol. To insert Nigeria Naira currency symbol: In the Symbols group of the Insert tag, click on Symbol In the Symbols tab of Symbol dialogue box, select (normal text) from the Font dropdown Select Currency Symbols from […]

# Understanding Absolute Cell Reference

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about Absolute Cell Reference. An Absolute Cell Reference is a cell reference that contains dollar signs (\$) before column letter and row number (i.e. \$A\$1). The dollar signs ensure that cell \$A\$1 is fixed when formula is copied down or across using the fill handle. To apply […]