1. https://www.youtube.com/watch?v=WZeXjCXqELM
2. https://www.youtube.com/watch?v=pwZ44kAeiOo
3. https://www.youtube.com/watch?v=ksl4FkO6nF8
4. https://www.youtube.com/watch?v=BltMN9OF1qA
5. <No video>

Excel and Google Sheets are fun. However, if you don’t possess a lot of logic around it, they can be quite daunting. Knowing where all of the aesthetic functions are. What they mean. How you can use them. The moment you figure out that you should be separating out your data types into different cells for analysis purposes. It even makes me think that many of the functions that have been developed were for completed for the purpose of pseudo-programmers to clean the data in their spreadsheets so that they could analyse them, e.g. TRIM, SUBSTITUTE, LEFT, RIGHT, MID, etc.

When you are seasoned with Excel/Google Sheets, sometimes it can be difficult to create solutions to your problems, especially in an efficient manner that doesn’t slow your processor down at scale — something that I am constantly seeking to improve in my own proactive way.

Here’s the list:

1. How to Find Missing Number in a List by Louis Deconinck,
2. How to use XPath query, IMPORTXML & Google Sheets by Learn Google Sheets & Excel Spreadsheets,
3. How to Get a List of All Worksheet Names Automatically by How To Excel At Excel.Com,
4. Using Excel’s Advanced Formula Environment by MyOnlineTrainingHub, and
5. How XMATCH is Extremely Helpful in your Business

1. How to Find Missing Number in a List by Louis Deconinck

How many times have you been in a situation where you’ve looked at a list of unordered values, scanned it, decided to order it, then thought….’alrighty, what’s missing?’ I’ve had this scenario more times than you think. …well, not really, but I love the concept.

I can’t even remember why I was searching for this kind of topic (my mind probably went off on a tangent), but I came across this amazing person with this amazing formula, explained really nicely. Throughout the course of the video, they break down the problem of arranging all of these values in order and how to find the values that aren’t present. They even list out the values that are not present!

Here is their proposed (final) solution:

LET(range, B2:B4,UNIQUE(VSTACK(range,SEQUENCE(MAX(range)-MIN(range)+1,1,MIN(range),1)),,TRUE))

Let’s break it down:

Click on this link to get a brief summary of the LET function. Otherwise, due to the number of references to a range, they’ve decided to use this approach for this process. They’ve assigned the range B2:B4 as the ‘range’

LET(range, B2:B4,

Returns unique values that appear exactly once in the stacked array. If an element appears more than once, it will not be included in the result. In our example, since all values appear more than once, the result would be an empty array

UNIQUE(

Vertically stacks the original range (B2:B4) and the sequence generated in step 2 into a single column. In this case this would result in 2, 3, 4, 2, 3, 4.

VSTACK(range,

Sequence generates numbers starting from the minimum value in range to the maximum value in range. For example, if range is B2:B4 containing values 2, 3, 4, the sequence would be 2, 3, 4.

SEQUENCE(

MAX(range)-MIN(range)+1,

1,

MIN(range),

1)

),

,

TRUE)

)

2. How to use XPath query, IMPORTXML & Google Sheets by Learn Google Sheets & Excel Spreadsheets

Back before starting this blog, I started looking into IMPORTHTML and IMPORTXML as a means to “webscrape”. I used to do it for Wikipedia articles that I had an interest in playing around with. However, it took me ages, for some reason, to learn how to traverse through websites. In 2007, I started looking at how to code websites, specifically using HTML and CSS. I get it, but I don’t get it.

I classify myself as visually dyslexic. That’s not a term or anything, but it’s the way that I perceive some things. If I look at a visual artwork, a website, or …anything visual, it doesn’t compute in my brain. If I attempt to recreate something by hand or digitally, it will definitely not look like the thing I’ve attempted to create. Everyone, even myself, would put it down to the lack of training, intent practice, and reflection. However, I really struggle to explain what’s happening visually. Block diagrams? Totally fine. Artistic interpretation? You’ve got the wrong person.

This kind of stands when it comes to websites. I totally understand how websites are built (from the HTML perspective) and how JavaScript has been injected into it over time. But, to draw information from it, the concept took me a while to really grasp. However, after watching this video (above), they were able to explain it so well and had a pretty natural scaffolding into pulling out different levels of data/attributes. I ended up revisiting it for the GPS information in this blog post about how I created my 8/8 Learn Data Storytelling post.

3. How to Get a List of All Worksheet Names Automatically by How To Excel At Excel.Com

Whilst helping my friend out with their Excel query, I thought about how they could automatically have text output of all worksheet names in their Sheet. I love popping onto YouTube to see if someone’s already made a video about the topic. One of the first entries was this little gem.

Over the years, I am developing different aspects of my Excel knowledge. I love uncovering different corners of Excel, that end up being the size of an apartment building. Take Excel’s Solver function as an example. That bad boy is STACKED with opportunity. Similarly, the method for listing worksheet names efficiently streamlines the process and enhances user experience, ensuring interactions don’t disrupt the workflow.

The video breaks down a straightforward approach to achieve this, utilising formulas within the Named Range function. Here’s a concise summary of the method:

1. Use of Named Ranges and Formulas: The video introduces a clever way to use Named Ranges along with specific formulas to loop through all the worksheets in a workbook and list their names in a designated worksheet. This approach is incredibly useful for large workbooks with many sheets, saving time and effort.
2. Efficiency: The method is designed to be efficient, ensuring that the list updates automatically when worksheets are added or renamed. This dynamic approach prevents the need for manual updates, which can be prone to errors.
3. User-Friendly: By incorporating user input, the solution remains adaptable to different needs without compromising the process’s integrity. Users can easily implement the code and see immediate results, enhancing their productivity.

This video is a perfect example of how a simple, elegant solution can significantly improve your workflow in Excel. I always love learning new tricks that make Excel even more powerful and efficient. Whether you’re a seasoned Excel user or just starting, mastering these techniques can make a world of difference in your data management tasks. Exploring such innovative solutions has been a part of my journey with Excel, continuously uncovering new functionalities that transform how we work with data.

4. Using Excel’s Advanced Formula Environment by MyOnlineTrainingHub

I’ve been watching Mynda for a long time now. They produce great content and in a digestible way. I enjoy watching her, alongside Leila Gharani when new features of Excel are released. I was excited to see this new addition to Excel as it is building upon what we are seeing in the digital landscape; more user-friendly interactions with AI.

What I enjoy about this addition to Excel is what I believe this next round of AI has been developed for — tools that assist you in making decisions. Although we can ask a machine some questions and they can report back, this can only be to a certain level of understanding within their bounded rationality. LLMs still aren’t fantastic with mathematics as they are built from text more so than numerical logic. But, what this video shows it the scaffold of humans learning alongside AI.

5. How XMATCH is Extremely Helpful in your Business

The XMATCH function in Excel is a game-changer. It helps you find the position of an item within a range or array with remarkable flexibility and precision, surpassing the traditional MATCH function.

How to Use XMATCH

The syntax of XMATCH looks like this:

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Here’s a breakdown:

• lookup_value: The item you’re searching for.
• lookup_array: The range where you’re looking.
• match_mode (optional): How you want to match:
  • 0 (default): Exact match.
  • 1: Exact match or next larger item.
  • -1: Exact match or next smaller item.
  • 2: Wildcard match.
• search_mode (optional): The search direction:
  • 1 (default): First to last.
  • -1: Last to first.
  • 2: Binary search (ascending order).
  • -2: Binary search (descending order).

Imagine you have a list of products in cells A2 and you want to find “Widget”:

=XMATCH(“Widget”, A2:A10)

If “Widget” is in cell A4, it returns 3 because it’s the third item in A2.

Why XMATCH is Extremely Helpful in Business

Quickly locating specific data in large datasets is crucial for business analysis. XMATCH does this efficiently, saving time and effort. With options for exact, approximate, and wildcard matches, XMATCH handles variable data effortlessly, making it indispensable for dealing with real-world, messy data.

Combine XMATCH with other functions like INDEX to perform advanced lookups and data extraction. For example, find a salesperson’s name and retrieve their sales figures easily. Create reports that automatically update as data changes. XMATCH ensures your reports reflect the latest information by accurately referencing data points.

The robust matching options in XMATCH reduce errors common with less flexible functions, ensuring accurate data analysis and reporting. In essence, XMATCH supercharges your data lookup capabilities, supports complex analysis, and enhances efficiency and accuracy in business data management.