Areef's Blog | Areef Ali & Associates

20.0 25/07/23 Dynamic array formulas – Automated variance reporting

Recently, I was asked by a seminar participant, if it was possible to create a Variance report which updates automatically when new data is added without having to refresh the reports. Truth is, you have to hit a refresh button or close and reopen the file for this to update using Excel’s Power query or Pivot table reporting techniques. However, if you build your report using dynamic array formulas then you can update your report automatically – no refresh needed.

Please check the brief video below I did below on how to create the report. You can download the solution file from here

19.0 23/05/23 Fuzzy logic matching

We typically use vlookup, index & match or xlookup formulas to update our data and reports when we have to get data from another sheet / file.

Unfortunately, these formulas do not help us when we need to compare and match items between different lists when the matching items/field values are slightly different.

We can use a fuzzy logic matching routine to do this. Please check the brief video below I did below showing how we can use in different business scenarios. You can download the exercise and solution file from here

18.0 03/01/23 Excel data Wrangling Automation: Macros vs Power Query vs Dynamic Array Formulas

There are many data automation approaches available in Excel. Up to 2012, in most instances, I would have relied on macros. Since then, we have newer techniques emerging in Excel – Power Query & more recently, Dynamic array formulas. I tend to prefer these newer approaches for audit trail purposes and ease of repeatability with other data sets.

I did a 5-6 minute video on each method showing how you can do the same thing i.e. automate the restructuring of a small data set (approx. 800 rows) to create a database suitable for a mail merge.

Which method do you prefer?

Macros

Power Query

Dynamic Array formulas

17.0 03/10/22 Pdf files – Extract, cleanse & append multiple pages with Excel

Excel Office 365 users have been able to connect, extract & manipulate data from Pdf files since at least June 2020 using Excel’s ETL tool – Power Query. This feature is also in Power BI. See below the short video I did on how to use it and apply some of the routines to extract and fix a 5-year financial dataset using Excel

16.0 04/09/22 Excel AI data extraction from pictures

Microsoft released to current channel users of Excel Office 365 a feature to allow you to get data from hardcopy documents or from tables on websites that are not accessible using Excel’s Power Query feature. Once you take a picture or a softcopy of it to Excel’s clipboard you should be able to retrieve the data using the new AI extraction interface. Please see the short video I did on it below

15.0 07/08/22 Date restructuring using new formulas

Microsoft released some new formulas earlier this year for Excel Office 365 users which are useful for data restructuring. If you have a Current Channel version of Excel you should have these formulas already.

In the 1st video we look at a new dynamic array formula MS introduced called textsplit, how to nest it with other dynamic array formulas and regular formulas to restructure data.

The 2nd video covers 2 new text formulas – textbefore and textafter. These can be used to overcome two limitations noted with the textsplit formula.

See the sample exercise file here. You can use it to practice the exercises shown in the videos below:

14.0 07/04/22 Updated “Finding Success” Guide

As some of you may know, I am passionate about helping others to find their success. My personal experience is that if you implement the advice offered in the guide which follows you are guaranteed to find your own success.

One small request – if you find it beneficial please pay it forward – share it with others who you feel can benefit from it.

You can download the detailed guide to help you on your journey from here. I usually run this as a short private session for organisations.