Excel Beyond the Cells

What comes to mind when you think of "Excel beyond the cells"? Throughout my data analysis journey, one tool that has been full of surprises is Excel. Most of us get to a point where we master the layout, learn several functions, and feel unstoppable. But then you are presented with a workbook containing some nested XLOOKUPs that throw an error at the slightest inconvenience. That is exactly when things get ugly.

1. TAMING THE FORMULAS
Let's start with formulas, the foundation of almost every workbook. When formulas are left untamed, the result is unwanted errors in the workbook. While XLOOKUPs are better compared to old VLOOKUPs due to their ability to check left and right, they become difficult to deal with when left nested blindly. To avoid errors in such formulas, the best practice is to use the IFERROR safety net.

A visual comparison of XLOOKUPs and VLOOKUPs

Example of an XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array,"Not Found")

Natively, XLOOKUP has its own way of dealing with an error using the fourth argument written as "Not Found" in the example. The argument makes sure that the formula returns a value instead of #NA.
When a formula is expected to search multiple sources, IFERROR can be useful for gracefully falling back to another lookup. However, it should be used carefully because it can also hide unexpected errors.

Example of a nested XLOOKUP utilising an IFERROR safety net
=IFERROR(XLOOKUP(A2, Table1_Lookup, Table1_Return), XLOOKUP(A2, Table2_Lookup, Table2_Return))

Visual representation

2. POWER QUERY
Once formulas become difficult to manage, the next logical step is automation with Power Query. Power Query is a robotic tool in Excel that eliminates the need to transform data manually. From deleting blank rows to adding custom columns and splitting columns, Power Query is the right tool for any data cleaning task. The most practical and enticing part is the fact that all these steps are recorded. When new data comes in, the data is cleaned with a single click of the refresh button.
Visual representation

3. Pivot tables (The storyteller)
A pivot table takes thousands of rows and condenses them into a clickable summary. Analysis that can take hours to complete using formulas is completed in seconds using pivot tables. They are vital in creating summary tables and slicers in Excel dashboards, this creates a fully interactive and usable dashboard that can effectively support decision-making.

Combining this and Power Query, a developer can create a self-sustaining ecosystem where the data is refreshed and fully updated on the dashboard

In conclusion, Excel remains one of the best tools for exploring and understanding data. From basic analysis that can be handled using basic formulas to complex analysis that needs Power Query and Power Pivot, Excel is the go-to tool for entry-level analysts. However, as datasets grow, analysts need to use specific tools like Power BI for visualization, which is something I will explore more in my next article.