Welcome to Excel Jet Consult blog post. Recently, Microsoft rolled-out six new functions, one new chart, new theme in Office 2019 and 365. Undoubtedly, the new functions will simplify many difficult operations in Excel NEW FUNCTIONS IFS Function – evaluates whether one or more conditions is satisfied and delivers a value corresponding to the first TRUE condition. IFS Syntax                          =IFS(logical_test1, […]

Welcome to Excel Jet Consult blog post. In this post we will learn how to perform diagnosis to discover cell with data validation in Excel. Succinctly, Data Validation is a feature that enables users to control data that is entered in Excel. Data Validation is located in the Data Tools group of the Data tab In the data caption below, […]

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use one of the sophisticated functions in Excel named RANDBETWEEN RANDBETWEEN returns a random number between numbers specified. The function is elegant to generate random date. RANDBETWEEN Syntax                  =RANDBETWEEN(bottom, top) We want to generate random contact number and dates in columns B and C […]

Welcome to Excel Jet Consult blog post. In this post we will learn how to use the ADVANCED filter in Excel. Succinctly, filter allows users to narrow down the data they intended to see after a particular point in time. The ADVANCED filter button is located in the Sort & Filter group of the Data tab Note, the data we […]

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use LARGE and SMALL functions to display Top 8 and Bottom 8 values LARGE Function returns the k-th largest value in a data set LARGE Syntax                    =LARGE(array, k) SMALL Function returns the k-th smallest value in a data set SMALL Syntax                    =SMALL(array, k) In […]

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to create a custom user defined function in Excel VBA that will require three arguments We will open the VBA with ALT + F11 and insert a module In the code window, execute the lines of code as seen in the caption below In the […]

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

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

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