Excel Power Tools – The Future of Excel

For years now Accountants and bookkeepers around the globe have used Excel as their Spreadsheet  tool of choice.  It offers massive flexibility, speed and is as accurate and reliable as the users. But to this day, some Excel users never go beyond the basic totting and cross totting of rows and columns. Excel has come […]

SWITCH Function in DAX for Excel Power Pivot and Power BI

The SWITCH function in DAX can be used to replace nested IF statement.  It is both easier to read and easier to write. SWITCH was introduced in PowerBI in Nov 2016 and it is also available in updated versions of Excel 2016 By the end of this article and examples you will Understand the syntax […]

Vlookups with text in excel

How to carry out a VLOOKUP within Text in Excel

In a previous article we covered The Basics of VLOOKUP.  VLOOKUP is a very versatile function that can be combined with other functions.  When you learn to combine VLOOKUP with with other functions it makes it super powerful. In this article we will look at combining VLOOKUP with TEXT functions. Lets quickly refresh some basics […]

How to Parse Custom JSON Data using Excel

How to Parse Custom JSON Data using Excel

Excel is a powerful tool that allows you to connect to JSON data and read it.  However sometimes this data might require a little manipulation to be fully understood and analysed in Excel.  In this article you will learn What is JSON Data How to Connect to JSON data from Excel How to Parse simple […]

Learn to use VLOOKUP in Excel

The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup The table of data below contains 4 columns.  A Product Number, the Supplier, the quantity […]

Simple Explanation of Machine Learning shown with Excel – Part 1

Every wondered how Machine learning works?  In this 2 part article we are going to explain machine learning with Excel Introduction The world is full with a lot of data, pictures, music, text, video, spreadsheets.  Machine learning brings the promise of delivering meaning from that data.   Human’s learn from experience. if we touch something […]

How Use Excel T.TEST – Was a Marketing Campaign Successful?

Like z-tests, t-tests are calculations used to test a hypothesis, but they are most useful when we need to determine if there is a statistically significant difference between two independent sample groups.  In this article we will look at T.TEST in Excel. A t-test asks whether a difference between the means of two groups is unlikely to […]

TEXTJOIN and Array IF Statements

Looking up and Combining text using TEXTJOIN and Array IF Statements.

Looking at the table of data below, how would you go about looking up the order number and pulling the name of all products sold on that order into the one cell as shown in cell B19 in the image below? Looks complex right?  But not really with the use of TEXTJOIN and Array IF […]