How to simplify complex calculations with custom functions
Excel Tips
Did you know that you can transform the way you can build models, reports and schedules with custom functions? This episode guides you on how they make complex calculations cleaner and easier to maintain. You’ll also learn practical ways to document, test and reuse these functions for consistent results. In this episode, an Excel expert will: Explain how to create custom functions using the LAMBDA function Outline how the LET function works Demonstrate creating a loan schedule “mega formula” Cover useful functions like...
info_outline
Smarter, faster, easier: Here’s what’s new in Excel
Excel Tips
In this episode of Excel Tips, we explore the latest Excel updates that can help to boost productivity and simplify workflows for finance and accounting professionals. Listeners will learn: How to use TRIMRANGE for flexible range selection What’s new with pivot table formatting and an exciting update on auto-refresh How the TRANSLATE function works for multilingual data The simplified syntax for BYROW and BYCOL functions How three new Regex (regular expressions) functions can use patterns to identify data. ...
info_outline
Unleash the power of Python in Excel
Excel Tips
In this third and final episode of a three-part series, we explore how the integration of Python unlocks advanced tools for analysis, automation and visualisation. Listeners will learn: How Python connects with Power Query to extract and transform data Ways to preview and filter information using Pandas functions Techniques for reshaping horizontal data into usable formats with melt() Python in Excel can really help your data workflows. It allows you to work with connection-only Power Queries, cleanse and reshape messy datasets and...
info_outline
Python in Excel: Practical applications
Excel Tips
In this second episode of a three-part series, you will build on the basics from part one to gain a clear understanding of how to use Python directly within Excel cells. This includes using the Pandas library, for example, to merge datasets and create data-rich outputs with minimal code. You’ll gain the skills to: Learn key differences between Excel formulas and Python syntax Discover practical coding shortcuts and structure for writing Python in Excel Deploy a step-by-step process to create a merged table with sales and GST...
info_outline
Getting started with Python in Excel
Excel Tips
Unlock the potential of Python in Excel with this essential first episode in a three-part series. Designed for accountants, finance professionals and Excel power users, this episode introduces the basics of integrating Python code directly into Excel cells. Learn how Python enhances Excel’s capabilities for data handling and visualisation without replacing core Excel functions. You’ll discover how Python in Excel works and how code is processed using Microsoft’s Azure platform. Understand the key steps to get started, including how to...
info_outline
Excel’s GROUPBY and PIVOTBY explained
Excel Tips
Excel’s new functions, GROUPBY and PIVOTBY, offer a smarter way to build reports. Instead of relying on traditional pivot tables, these new tools allow you to summarise datasets using dynamic, formula-based solutions that update automatically when the underlying data changes. In this episode: Understand the key differences between GROUPBY and PIVOTBY See how these tools can save time and reduce manual errors Explore practical applications for accountants and analysts Discover how to modernise reporting with flexible,...
info_outline
Better share price reports with Excel’s new tool
Excel Tips
Need to turn stock market data into smart, professional reports? Discover how to use Excel’s new and powerful Stock History function to quickly transform raw share price data into clear, insightful reports. Learn how to streamline reporting, visualise trends and make better-informed decisions with ease. In this episode, gain the skills to: Access daily, weekly or monthly data, including volume, opening/closing prices and highs/lows Set up stock codes, choose custom date ranges and define intervals with ease Use Power Query to import real-time stock exchange...
info_outline
Elevate your Pareto analysis skills with Excel
Excel Tips
The Pareto principle is a powerful concept for business. In this episode we’ll explore this principle (also known as the 80/20 rule) and learn how to apply it effectively in Excel for smarter, data-driven decisions. Discover how to pinpoint vital data – such as the 20 per cent of clients generating 80 per cent of your revenue as an example – to create valuable insights. We’ll also dive into Excel's powerful analytical tools, including Pareto charts, the UNIQUE function, SUMIFS, and the power of PivotTables to bring your Pareto analysis data to life. ...
info_outline
Unlock the power of Microsoft Forms in Excel
Excel Tips
What if you could seamlessly collect and analyse data right inside Excel? In this episode, learn more about Microsoft Forms, a powerful tool in Microsoft 365 (which, by the time you read this may be renamed Microsoft 365 CoPilot). Discover how Forms integrates directly into Excel, making it easier than ever to gather feedback, conduct quizzes, and streamline processes like expense approvals and recruitment. For fast reference, use the episode timestamps: 0:00 – Introduction to Excel Tips podcast 0:10 – Overview of Microsoft Forms 0:30 –...
info_outline
New tool explained: boost your efficiency with checkboxes
Excel Tips
Ready to unlock a hidden gem in your Microsoft Excel subscription? In this episode you’ll discover the power of checkboxes with practical examples to explore how this new function can streamline your workflow. This includes how to use TRUE/FALSE values in formulas and conditional formatting to dynamically control calculations and visuals. Learn from examples such as inflation adjustments in a budget model and applying a strikethrough format to a to-do list. Additionally, gain insight into how checkboxes are used in the logical test of the IF...
info_outlineTune in to discover how mastering custom functions can help simplify two complex issues with Excel formulas.
Improve your problem-solving skills by harnessing the power of custom functions. For fast access, use these timestamps:
- 0:20 - Custom functions, episode overview.
- 1:39 - Summing spill ranges.
- 2:58 - Using prefixes in functions.
- 4:07 - Setting up a switch function.
- 5:31 - Financial year custom functions.
- 6:27 - Setting up a financial year function.
- 8:09 - Using custom functions across multiple files.
- 8:53 - Custom functions overview.
Listen now.
Host: Neale Blackwood CPA has more than 20 years of experience as a Microsoft Excel educator. He is the author of more than 200 INTHEBLACK articles as well as a book, Advanced Excel Reporting for Management Accountants.
You can catch up on a previous episode that explained custom functions. There is also an accompanying article to this podcast episode.
CPA Australia publishes four podcasts, providing commentary and thought leadership across business, finance, and accounting:
Search for them in your podcast platform.
You can email the podcast team at podcasts@cpaaustralia.com.au