Have a personal or library account? Click to login
Excel Insights Cover

Excel Insights

A Microsoft MVP Guide to the Best Parts of Excel

Publisher:Packt Publishing Limited
By:
Paid access
|Oct 2024

Unlock the full potential of Excel with advanced tips and techniques covering everything from formulas to VBA.

Key Features

  • Advanced Excel features, from custom formatting to dynamic arrays
  • Data analysis and visualization with Power Query and charts
  • Detailed explanation of VBA for task automation and efficiency

Book Description

Dive into the world of advanced Excel techniques designed to elevate your data analysis skills. Start with mastering custom number formatting, efficient data entry, and powerful formulas like INDEX MATCH. Explore Excel's evolving features, including dynamic arrays and new data types, ensuring you stay at the forefront of the latest tools.

The course then guides you through creating impactful charts for presentations and advanced filtering techniques. You’ll also discover the transformative power of Power Query, allowing you to manipulate and combine data with ease. With chapters on financial modeling and creative Excel model development, you’ll learn to solve complex problems and develop innovative solutions.

Finally, the course introduces you to VBA, teaching you how to automate tasks and create custom worksheet functions, equipping you with the skills to enhance your workflows. By the end of the course, you’ll have a robust understanding of Excel's advanced features, empowering you to handle any data challenge with confidence and creativity.

What you will learn

  • Master custom number formatting
  • Utilize INDEX MATCH effectively
  • Create dynamic arrays
  • Build advanced charts
  • Automate with Power Query
  • Develop VBA functions

Who this book is for

Ideal for intermediate to advanced Excel users, data analysts, and financial modelers. Readers should have a basic understanding of Excel. Prior experience with Excel formulas, charts, and data management is recommended.

Table of Contents

  1. Smart Uses of Custom Number Formatting
  2. Ctrl + Enter
  3. Auto-Magically Master INDEX MATCH (and Other Formulas)
  4. Relative Named Ranges – When Named Ranges Go Walkabout
  5. An Introduction to Excel's New Data Types
  6. A Look to the Future – Dynamic Arrays
  7. XLOOKUP Debuts in Excel
  8. Why the Love / Hate for Pie Charts?
  9. Intermediate Charting in Excel
  10. Creating Charts for Presentations
  11. Advanced Filter
  12. Power Query: Manipulate Your Data Like a Pro
  13. Combine All Files in a Folder
  14. Power Query M Code Approximate Match Lookup Formula
  15. The Power Behind the Boringest Sentence in Excel
  16. Understanding Context in Power Pivot
  17. Thinking Through the Modelling of a Seating Chart
  18. Financial Modelling
  19. Creative Excel Model Development
  20. An Introduction to Simulation in Excel
  21. Staying out of Trouble
  22. Make Your Own VBA Worksheet Functions
  23. An Overview of Modern VBA Best Practices
  24. About the MVPs
PDF ISBN: 978-1-83664-268-8
Publisher: Packt Publishing Limited
Copyright owner: © 2024 Packt Publishing Limited
Publication date: 2024
Language: English
Pages: 289
Related subjects:
Computer sciences,
Computer sciences, other

People also read