Article 20: How can I avoid complex formulae in Excel (by integrating Excel with a relational database)

Summary

Recently I was given the opportunity to teach SQL Server to Exploration Geologists.? As part of that course, I discussed the advantages of integrating Excel with relational databases (RDBMS).? This article focuses on the advantages from an Excel viewpoint.? A later article will focus on the advantages from a RDBMS viewpoint.

Introduction

I have often integrated Excel with RDBMS.? Indeed, I have done so for over thirty years, and I was also teaching this approach (informally) at JKMRC (a mineral processing research centre).? As I became more familiar with how professionals used Excel, I realised that integration was uncommon.

Many Excel users have a one-stop-shop attitude when using software.? That is, they like to use Excel to do ‘everything’.? Indeed, one of my main criticisms of many Excel courses is the common theme of getting participants to become Excel gurus rather than to encourage participants to apply the most appropriate software for their needs.

Those who use RDBMS tend not to be as singularly focused as Excel users – but often ‘look down’ on using Excel.? This is often because they judge Excel as a poorly developed RDBMS rather than identifying the potential strengths of integration.

Background to RDBMS

Now when we talk about RDBMS we need to recognise that there are many different systems.? I like Access (which I regard as very easy to learn); however, it appears that Access is no longer as commonly used as other systems.? However most RDBMS use SQL (Structured Query Language).

Recently, I gave a 5-day course in SQL Server (via Deka Dynamics).? I found the jump from Access to SQL Server quite a challenge. I appreciate the assistance from the participants, as well as Hiran De Silva and Hugo Bernachea.? ??SQL Server is very powerful and more suited to: multiple users, large databases and multiple databases.

I also found that using SQL Server could be simplified if one sought guidance on specific functionality using ChatGPT (also taught in the course).

Figure 1 Providing an SQL Server course in Burkina Faso

Excel formulae can be difficult to understand

?Now let us first recognise that Excel has different ‘layers’.? Most people who use Excel for filtering data will end up using functions such as:

• HLOOKUP
• VLOOKUP
• MATCH
• INDIRECT
• INDEX

Although these functions are very useful, they are also a recipe for: overuse and the creation of complex formulae.?

Consider the following formula for example:

Figure 2 Example of a ‘typical’ complex formula in Excel

I would suggest that many Excel workbooks have formulae at this level of complexity; I also suggest that such formulae are difficult to understand.? I hope you agree.

Here I call the above approach the Excel lookup approach.

Excel as an RDBMS

There are other methods available in Excel such as:

• Excel tables
• Power Pivot
• Power Query

So over time, Excel has developed relational database capabilities; although I would be very reluctant to say that Excel can now be used as an RDBMS.? A previous article discussed some of the issues surrounding the Excel Data Model (Ref. 1).

It should also be noted that Excel does not use SQL (although SQL can be applied using VBA).

Advantages of using dedicated RDBMS

So here I will directly discuss the advantages of using RDBMS compared to native Excel (from an Excel viewpoint).

By using an RDBMS one can create: Queries.? (There is some inconsistent use of terms from different RDBMS. i.e. A Query in Access is called a View in SQL Server.)

Whilst Queries use SQL (structured query language), at least Access and SQL Server have user interfaces that allow Queries to be built simply (and without knowing SQL).

Once a Query is created it is often easy to understand.? It is normally much easier than using the various Excel lookup functions.

So the combination of Excel and RDBMS allows much of the complex functionality in Excel to be outsourced.

In a future article (being prepared), I will discuss how to connect RDBMS with Excel using VBA.

Figure 3 Typical data flow for integrating a database (from an Excel viewpoint).? Data from Excel is sent to a database, SQL queries are performed, and the data is sent back to Excel.

Action plan

As stated, I am now offering a 5-day intense RDBMS course that also includes advanced Excel strategies for comparison purposes (Excel tables, Power Pivot, Power Query). If your organisation uses RDBMS and Excel but is not sure how to integrate the two approaches this course is strongly recommended, and I would appreciate discussing your needs.

References

1.?????? LinkedIn article, What is better to use: ‘Power Query Data Model’ or ‘Microsoft Access’?