Search icon Close icon
Search icon
Subscription
0
Cart icon
Close icon
You have no products in your basket yet
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
Exploring Microsoft Excel’s Hidden Treasures

You're reading from  Exploring Microsoft Excel’s Hidden Treasures

Product type Book
Published in Sep 2022
Publisher Packt
ISBN-13 9781803243948
Pages 444 pages
Edition 1st Edition
Languages
Concepts
Author (1):
David Ringstrom David Ringstrom
Profile icon David Ringstrom
LinkedIn
David Ringstrom exclaimed “Well, this is a stupid program, you can’t do anything with it” the first time that he launched Lotus 1-2-3 in 1987, unaware that pressing the slash key displayed the menu. That moment sealed his fate as he is now a nationally recognized spreadsheet expert. In 1991, David started a spreadsheet consulting practice that he still runs today. David has taught over 2,000 webinars and published hundreds of articles, all on Excel, and he imparts spreadsheet skills to thousands of college students each year. He is the author or coauthor of five books and the technical editor of over 40 books. He is a certified public accountant and a graduate of Georgia State University and has served in the United States Navy.
See other products by David Ringstrom

Table of Contents (18) Chapters

Preface 1. Part 1: Improving Accessibility
2. Chapter 1: Implementing Accessibility 3. Chapter 2: Disaster Recovery and File-Related Prompts 4. Chapter 3: Quick Access Toolbar Treasures 5. Chapter 4: Conditional Formatting 6. Part 2:Spreadsheet Interactivity and Automation
7. Chapter 5: Data Validation and Form Controls 8. Chapter 6: What-If Analysis 9. Chapter 7: Automating Tasks with the Table Feature 10. Chapter 8: Custom Views 11. Chapter 9: Excel Quirks and Nuances 12. Part 3: Data Analysis
13. Chapter 10: Lookup and Dynamic Array Functions 14. Chapter 11: Names, LET, and LAMBDA 15. Chapter 12: Power Query 16. Index 17. Other Books You May Enjoy

The Spilled Range Operator

The average spreadsheet user spends a lot of time managing formulas in spreadsheets, particularly when new data is added to a list. The Table feature, which I discuss in Chapter 7, Automating Tasks with the Table Feature, offers one approach for eliminating this manual task. A second approach involves using the Spilled Range Operator (#) to create formulas that expand into more rows or contract into fewer rows based on changes in your data.

Nuance

You can only use the Spilled Range Operator in conjunction with formulas that reference results generated by a dynamic array function. This means the Spilled Range Operator is not available in Excel 2019 and earlier. If you use the Spilled Range Operator to reference data that is not in a dynamic array, the formula might return zero or a #VALUE! error.

The =SORT(UNIQUE(FILTER(C3:C12,D3:D12>H2))) formula in G6 of Figure 10.22 combines several concepts from this chapter to generate an alphabetical list...

lock icon The rest of the chapter is locked
arrow left Previous Chapter
Chapter 1 of 18
Next Chapter arrow right
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Sign up now
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Start free trial
Renews at £13.99/month. Cancel anytime}

Authors (1)

Profile icon David Ringstrom
LinkedIn
David Ringstrom exclaimed “Well, this is a stupid program, you can’t do anything with it” the first time that he launched Lotus 1-2-3 in 1987, unaware that pressing the slash key displayed the menu. That moment sealed his fate as he is now a nationally recognized spreadsheet expert. In 1991, David started a spreadsheet consulting practice that he still runs today. David has taught over 2,000 webinars and published hundreds of articles, all on Excel, and he imparts spreadsheet skills to thousands of college students each year. He is the author or coauthor of five books and the technical editor of over 40 books. He is a certified public accountant and a graduate of Georgia State University and has served in the United States Navy.
Read more
See other products by David Ringstrom

Personalised recommendations for you

Based on your interests and search pattern
Left arrow icon
C++ Programming for Linux Systems
C++ Programming for Linux Systems
This book covers the essential system programming tools and helps you explore the features of C++20. It emphasizes important details to maintain code quality and tackle everyday challenges of developing software for high performance, optimization, and more.
Sep 2023 9 hours 36 minutes
Expert C++
Expert C++
Discover advanced programming techniques, the latest features of C++17 and C++20, and best practices for memory management, debugging, testing, and large-scale application design with Expert C++. Ideal for experienced developers advancing to proficient programmers and building professional-grade C++ applications.
Aug 2023 20 hours 8 minutes
iOS 17 Programming for Beginners
iOS 17 Programming for Beginners
iOS 17 Programming for Beginners, Eighth Edition is your comprehensive guide to learning the art of iOS app development. Whether you dream of creating the next chart-topping app or simply want to enhance your programming skills, this book is your trusted companion on this exciting journey.
Oct 2023 20 hours 8 minutes
Developer Career Masterplan
Developer Career Masterplan
Written by industry experts that have spent the last 20+ years helping developers grow their career path towards senior developer positions and beyond. This book provides a comprehensive guide, sharing examples and stories from their global careers. By the end, you’ll have the knowledge to create a clear career progression plan as a technical professional.
Sep 2023 10 hours 20 minutes
Refactoring with C#
Refactoring with C#
In Refactoring with C#, you’ll explore the process of safely refactoring modern .NET code using Visual Studio features, advanced unit tests, AI assistance, and custom Roslyn analyzers.
Nov 2023 14 hours 28 minutes
Python Real-World Projects
Python Real-World Projects
Amplify your developer journey by curating a dynamic project portfolio that outshines traditional resumes. Delve into the Python realm through immersive projects, mastering core concepts while constructing comprehensive modules and applications. From data acquisition prowess to impactful data visualization, Python Real-World Projects arms you with essential skills to beat the competition.
Sep 2023 15 hours 56 minutes
The MVVM Pattern in .NET MAUI
The MVVM Pattern in .NET MAUI
The MVVM Pattern in .NET MAUI enables developers to master MVVM principles and effectively apply them to .NET MAUI. This book uses real-life examples and covers complex problems to help you successfully apply MVVM with .NET MAUI to confidently develop robust and high-performing cross-platform apps.
Nov 2023 12 hours 52 minutes
Extending Microsoft Business Central with Power Platform
Extending Microsoft Business Central with Power Platform
Extending Business Central with the Power Platform is a step-by-step guide for Business Central professionals to create solutions that automate business processes, explain complex workflow approvals, and integrate with hundreds of other systems, without traditional development. It’ll guide you in customizing Business Central with Power Platform.
Aug 2023 15 hours 16 minutes
Extending Microsoft Business Central with Power Platform
Extending Microsoft Business Central with Power Platform
Extending Business Central with the Power Platform is a step-by-step guide for Business Central professionals to create solutions that automate business processes, explain complex workflow approvals, and integrate with hundreds of other systems, without traditional development. It’ll guide you in customizing Business Central with Power Platform.
Aug 2023 15 hours 16 minutes
Quantum Computing Algorithms
Quantum Computing Algorithms
The book emphasizes intuitive ideas behind quantum algorithms in ways that other books don’t cover, striking a careful balance between no math and too much math. To get the most from this book, you should be comfortable with basic algebra and writing simple computer code. No prior understanding of quantum physics is needed to get started.
Sep 2023 11 hours 24 minutes
Python – Complete Python, Django, Data Science and ML Guide
Python – Complete Python, Django, Data Science and ML Guide
Unlock Python's full potential with this 50+ hour course! From programming to web and game development, data manipulation, and machine learning, gain the skills required to succeed in various Python-related careers. With practical tasks, hands-on experience, and a strong foundation in Python, you'll be ready to tackle real-world challenges and take advantage of the many opportunities this versatile language offers.
Nov 2023 50 hours 30 minutes
Python – Complete Python, Django, Data Science and ML Guide
Python – Complete Python, Django, Data Science and ML Guide
Unlock Python's full potential with this 50+ hour course! From programming to web and game development, data manipulation, and machine learning, gain the skills required to succeed in various Python-related careers. With practical tasks, hands-on experience, and a strong foundation in Python, you'll be ready to tackle real-world challenges and take advantage of the many opportunities this versatile language offers.
Nov 2023 50 hours 30 minutes
Right arrow icon