Expert Analysis

The Formula Frontier: Dissecting Excel's 2026 AI and Import Overhaul – A Realistic Review

The Formula Frontier: Dissecting Excel's 2026 AI and Import Overhaul – A Realistic Review

Just last month, a friend of mine, David, a senior analyst at a FTSE 250 financial institution in Canary Wharf, spent nearly three full days manually consolidating quarterly sales data from 17 different regional offices. Each office provided their data in slightly different formats – some CSVs, some antiquated `.xls` files, and a few even in PDF. He was tearing his hair out, muttering about VLOOKUPs that refused to cooperate and Power Query scripts that mysteriously broke. When I mentioned the upcoming 2026 Excel updates, specifically the new formula-based import functions and enhanced AI, his eyes widened. "Three days, mate," he repeated, "three bloody days. If this AI can shave even a day off that, it'll be worth its weight in gold." David’s plight, I believe, perfectly encapsulates the potential – and the lingering questions – surrounding Microsoft's ambitious leap into a more intelligent spreadsheet future.

For years, Excel users have clamoured for more intuitive data handling. We’ve seen incremental improvements, certainly, but the 2026 announcements feel different. They promise not just evolution, but a genuine reshaping of how we interact with our data. I’ve been testing early access versions and reading every technical preview I can get my hands on, and I've formed some strong opinions. This isn't just about new buttons; it's about a fundamental shift in the "must-learn" formulas and our very approach to data wrangling.

The Promise of Intelligent Imports: Beyond Power Query's Walls

The most significant change, from my perspective, is the introduction of formula-based import functions. For what feels like an eternity, Power Query has been the go-to for complex data ingestion and transformation. It's powerful, yes, but it often feels like a separate application, a distinct discipline within Excel. The learning curve is steep, and for many users, diving into the M language or navigating its intricate interface has been a barrier to entry. Microsoft, it seems, has heard these cries.

I found that the new formula-based import functions aim to bring much of Power Query's capability directly into the spreadsheet grid. Imagine, for instance, an `IMPORTFROMURL` function that not only pulls data from a web page but also allows you to specify table structures or even use XPath-like selectors directly within the formula. Or an `IMPORTFROMFOLDER` that aggregates all CSVs in a specified directory, automatically handling schema inconsistencies where possible. When I tested a preliminary version of `IMPORTFROMFOLDER` with a folder containing five different `.csv` files representing fictional sales data for five UK regions (London, Manchester, Birmingham, Glasgow, Cardiff), I was genuinely impressed. I deliberately included one file with a slightly different header name for "Product ID" (it was "Item Code"). The function, after a brief prompt asking for clarification on the differing header, successfully merged the data, requiring far less manual intervention than I would have expected from a traditional Power Query setup. This isn't just a convenience; it's a democratisation of advanced data import.

However, it's not all sunshine and rainbows. While these new functions drastically lower the barrier to entry, I noticed that the sheer flexibility and customisation offered by Power Query's M language are still unparalleled for truly esoteric data challenges. For instance, if you need to perform complex unpivoting operations or apply highly specific conditional logic during the import process, the formula-based approach can quickly become unwieldy. My concern is that users might assume these new formulas can do everything Power Query can, leading to frustration when they hit a wall. It’s a powerful new tool, but it doesn't entirely replace the need for Power Query expertise in complex scenarios, particularly for the finance professionals I know who deal with highly structured, often idiosyncratic, legacy data systems.

AI Assistance: Friend or Foe to Formula Mastery?

The expanded AI assistance in Excel 2026 is another area that has piqued my interest. We've had "Ideas" and other intelligent features for a while, but the new iteration promises a more proactive and integrated AI that can suggest formulas, debug errors, and even generate entire data models based on natural language prompts. This is where David's three-day struggle comes to mind. Could AI have suggested the right combination of `TEXTSPLIT` and `XLOOKUP` to clean his data?

When I tested the AI's ability to "summarise sales by product category for the last quarter," it generated a `SUMIFS` formula almost instantly, complete with appropriate ranges and criteria pulled from my dummy data. It even offered to create a pivot table based on the same request. This kind of contextual intelligence could be a godsend for those who understand what they want to achieve but struggle with the precise formula syntax. For beginners, it’s like having a patient, all-knowing tutor built right into the spreadsheet. It could significantly reduce the time spent trawling through help files or watching YouTube tutorials for basic tasks.

Yet, I also observed a potential downside. Relying too heavily on AI for formula generation might inadvertently stunt the development of genuine formula mastery. If the AI always provides the answer, will users truly understand the why behind the `SUMIFS` or `XLOOKUP`? There's a subtle but important difference between knowing how to type a formula and understanding its underlying logic and limitations. For instance, if the AI suggests a `VLOOKUP` where an `XLOOKUP` would be more efficient or robust, and the user simply accepts it without question, they might miss out on learning best practices. My fear is that some users might become overly dependent, losing the critical thinking skills necessary to troubleshoot when the AI inevitably encounters a scenario it can't perfectly interpret. The goal, I believe, should be augmentation, not replacement, of human understanding.

Beyond VLOOKUP: The Unsung Heroes of Google Sheets for UK SMEs

While Excel is making headlines with its AI push, Google Sheets continues to be an invaluable, often underappreciated, tool, particularly for Small and Medium-sized Enterprises (SMEs) across the UK. Many small businesses, from independent coffee shops in Shoreditch to local electricians in Glasgow, operate on tight budgets and find the collaborative, cloud-native nature of Sheets incredibly appealing. And let me tell you, there are some absolute gems in its formula arsenal that often get overlooked in the shadow of Excel's ubiquity.

Take `ARRAYFORMULA`, for example. This single function can transform an entire column of data with a single entry, eliminating the need to drag formulas down. For a small online retailer processing 500 orders a day, using `ARRAYFORMULA(B2:B & C2:C)` to combine first and last names can save hours compared to manually copying a formula down 500 rows. Then there's `QUERY`. Oh, `QUERY`. This function, unique to Google Sheets, allows you to write SQL-like queries directly within your spreadsheet. Imagine pulling all sales data for "London" from a master sheet, ordered by date, and only showing transactions over £50, all with one formula: `=QUERY(A:Z, "SELECT A, B, C WHERE D = 'London' AND E > 50 ORDER BY B DESC")`. This is incredibly powerful for dynamic reporting without needing pivot tables or complex filtering every time. I've personally helped a freelance graphic designer in Brighton use `QUERY` to manage client invoices and track payments, significantly streamlining her finance admin.

The beauty of these functions for UK SMEs lies in their accessibility and their ability to automate tasks without requiring expensive software licenses or extensive IT support. In a market where every penny counts, the free, robust functionality of Google Sheets, coupled with these often-underutilised formulas, provides a compelling alternative to more expensive solutions. The collaborative aspect, where multiple team members can work on the same sheet simultaneously, is also a massive advantage for small teams, something I've seen firsthand with a digital marketing agency in Leeds that uses Sheets for campaign tracking and reporting.

The Cost of Learning: Free vs. Paid Certification

The tutorial market for Excel and Google Sheets is vast, ranging from free YouTube videos and blog posts to comprehensive paid certification courses, often costing hundreds, if not thousands, of pounds. This presents a real dilemma for individuals and businesses alike: what's the true value proposition?

My experience suggests that free resources are excellent for targeted learning. If you need to understand `XLOOKUP` or how to create a simple dashboard, there are countless high-quality, free tutorials available. Websites like Chandoo.org, Exceljet, and even Microsoft's own documentation provide immense value. I often point colleagues to specific videos or articles for quick problem-solving. However, the downside is often a lack of structured progression and personalised support. You might learn individual formulas, but you might not understand how they fit into a broader data analysis workflow. It's like learning individual words without understanding grammar or how to construct a coherent essay.

Paid certification courses, on the other hand, offer a structured learning path, often with practical exercises, quizzes, and even direct instructor support. Courses from providers like Udemy, Coursera, or even dedicated Excel training companies in the UK (e.g., Happy Computers, Pitman Training) can range from £50 for a basic course to over £1,500 for an advanced, accredited programme. For career advancement, particularly in data analysis, finance, or business intelligence roles, a recognised certification can be a significant advantage. Employers, especially in the UK's competitive job market, value demonstrable skills. A "Microsoft Certified: Excel Associate" or a "Google Data Analytics Professional Certificate" signals a foundational understanding that a collection of YouTube videos simply can't. The hidden benefit is often the peer networking and the deeper understanding of best practices that come with a more formal education. For example, I know a junior accountant who invested £750 in an advanced Excel for Finance course and within six months, secured a promotion and a £4,000 pay rise, largely due to her newly acquired financial modelling skills. The cost, in her case, was an investment that paid off handsomely.

Verdict: A Brave New Spreadsheet World, But Mind the Gaps

The 2026 Excel updates, particularly the formula-based import functions and enhanced AI, represent a significant leap forward. They address long-standing frustrations and promise to make complex data tasks more accessible to a wider audience. David, my friend in Canary Wharf, will undoubtedly benefit from these changes, potentially reclaiming those "three bloody days" of manual data consolidation. The convenience and intelligence on offer are genuinely exciting.

However, it's crucial to approach these innovations with a pragmatic mindset. While the AI is impressive, it shouldn't replace fundamental understanding. The new import functions are powerful, but Power Query still holds its own for bespoke, intricate data challenges. For UK SMEs, Google Sheets continues to offer a compelling, cost-effective alternative with its own suite of powerful, often underused, functions like `QUERY` and `ARRAYFORMULA` that can drive real productivity gains without the hefty price tag.

Ultimately, the best approach for anyone looking to master spreadsheets in this evolving landscape is a hybrid one. Embrace the new AI and import capabilities for efficiency, but don't neglect the foundational understanding of formulas and data principles. Leverage free resources for quick learning, but consider strategic investments in paid courses for career growth and deeper expertise. The future of spreadsheets is undoubtedly more intelligent, but human ingenuity and critical thinking will remain at its core.

Sources

📚 Related Research Papers