Excel: 10 Essential Skills for Analysts

Key insights

• Get Data / Live Web Data
  Use Excel's Get Data to import live web tables and external sources so you avoid manual copy‑paste.
  This keeps reports fresh with simple refresh steps.
• Tables & Name Manager
  Convert ranges to Tables to auto-format and expand formulas as data grows.
  Use the Name Manager to give ranges clear names and make formulas easier to read and maintain.
• Dynamic Arrays & Advanced Formulas
  Use dynamic arrays to return whole result sets from one formula instead of repeating it.
  Master functions like XLOOKUP, INDEX/MATCH and simple tricks (ampersand & for concatenation, the double dash -- for coercion) to build compact, fast formulas.
• Power Query & Power Pivot
  Use Power Query to clean and reshape messy data without coding, and use Power Pivot or the data model to handle large or relational datasets for faster summaries than regular sheets.
• Automation: Office Scripts, Macros & Specials
  Automate repetitive tasks with Office Scripts (modern cloud scripting) or traditional macros where needed.
  Use tools like Paste Special and Go To Special to speed editing and selective updates.
• Microsoft Copilot & AI in Excel
  Use Copilot to generate formulas, transform data with natural language, and speed report building.
  Learn prompt techniques so AI boosts accuracy and saves time in day‑to‑day analysis.

Video Snapshot

Kenji Farré (Kenji Explains) [MVP] recently published a practical YouTube video titled "Don't Fall Behind: 10 Excel Skills the Modern Analyst Should Know!" which walks viewers through ten capabilities that raise Excel from a simple spreadsheet into a full analytics workspace. In the video, Farré emphasizes both foundational tools and newer AI-driven features, and he demonstrates how to apply them in everyday workflows. Consequently, this piece summarizes the video’s main lessons and highlights the tradeoffs analysts must weigh when adopting each approach.

Core Skills Highlighted

First, Farré covers data ingestion and formatting, showing how Get Data and Power Query let analysts import and reshape live web data without repeated manual steps. He then stresses the importance of using Excel tables to automate formatting and preserve formulas as data grows. Therefore, analysts who adopt these techniques can reduce errors and speed up routine imports.

Next, the video moves to formula management, where Farré recommends the Name Manager to make ranges easier to understand and maintain, while also promoting dynamic arrays to avoid repetitive formulas. For advanced lookups and logic, he highlights modern functions like XLOOKUP and the creative use of symbols such as ampersands for concatenation or double dashes for boolean coercion. As a result, formulas become clearer and often faster to maintain.

Finally, Farré addresses automation and advanced tools: he contrasts classic macros with newer options like Office Scripts, and he explores pivot alternatives for rapid summarization. He also calls attention to special Excel commands like "Paste Special" and "Go To Special" that save time on one-off tasks. Importantly, he finishes by showing how to use Copilot in Excel to accelerate formula construction and data manipulation using natural language prompts.

Tradeoffs and Practical Challenges

Although these skills clearly boost productivity, Farré and this summary note several tradeoffs. For example, Power Query can handle complex transformations more robustly than cell formulas, yet it introduces a separate query layer that some teams must learn and manage. Therefore, organizations must decide whether to centralize transformations in queries or keep logic in worksheets for visibility.

Similarly, while dynamic arrays simplify many problems, they can create compatibility issues for teams still using older Excel versions. Meanwhile, AI tools like Copilot speed up routine tasks but may obscure the precise logic behind a calculation unless users inspect and validate the generated output. Thus, adopting new features requires balancing speed gains with maintainability and auditability.

AI in Excel: Promise and Peril

Farré positions Copilot as a powerful assistant that helps translate natural language prompts into formulas and data transformations. Because Copilot can generate complex queries and suggest patterns, it reduces the time spent on iterative debugging and can lower the barrier for less technical users. However, analysts must still verify results, since AI-driven outputs can sometimes be plausible but flawed.

Moreover, the video underscores a cultural challenge: teams must build processes that capture why a Copilot suggestion was chosen and how it fits into broader models. In other words, while AI boosts productivity, it also raises governance and reproducibility questions that teams should address through documentation and review standards.

Balancing Automation and Control

Farré’s recommendations encourage analysts to automate repetitive work, but he also warns against over-automation that sacrifices clarity. For example, converting many steps into a single query or script saves time but can make debugging harder when results change unexpectedly. Therefore, analysts should aim for modular solutions that combine readable formulas, well-named ranges, and query steps that complement one another.

Additionally, choosing between Office Scripts and traditional VBA hinges on the environment and team skills: Office Scripts aligns well with cloud-first workflows and modern tooling, whereas VBA still excels for legacy solutions that require deep workbook control. Consequently, teams should consider both the technical fit and long-term maintenance when picking an automation path.

Practical Takeaways for Analysts

To act on Farré’s advice, analysts should begin by mastering data import and cleaning through Power Query, then adopt tables and names to make worksheets self-documenting. Next, learning dynamic arrays and modern lookup functions will reduce formula complexity and increase resilience. Also, teams should pilot Copilot on noncritical workflows while building validation steps and documentation practices.

Finally, the video reinforces that Excel remains a central skill for analysts in 2025, especially when paired with AI and automation. Consequently, investing time in these ten areas yields faster reporting, clearer models, and better collaboration, provided teams weigh tradeoffs and establish governance for new tools.

Keywords

Excel skills for analysts, advanced Excel functions, Excel data analysis techniques, PivotTable tips and tricks, Power Query for Excel analysts, Excel formulas every analyst should know, Excel dashboard design, VBA macros for Excel automation