Excel Tips

From trial balances to profit and loss statements, reporting sits at the  centre  of modern finance.   This episode explores how finance professionals can use DAX (Data Analysis Expressions) formulas and Power Pivot to build more powerful accounting reports in Excel.   Learn how to create scalable, maintainable reporting models using Excel ' s data model, while applying techniques that can also be...

Excel macros are far from obsolete.  And  here's  why.  This episode explores why  VBA s ( V isual   B asic   for  A pplications )  still   play   an important role  in workflow efficiency for accountants and finance professionals.   Key learnings:   Why macros still matter in modern Excel workflows  How VBA compares with Power Query  The limits of Power Query for in- situ  data  fixes  How AI can...

Most finance professionals have made this mistake at least once.   You open last month's file, update the numbers, hit save, and only then  realise  you should have saved it as this month's version of the file.  This is where templates can help.  In this episode, explore how Excel templates solve that problem while also improving consistency across your reporting.  Main learnings:   How Excel temp...

What if you could simplify Excel date calculations with one powerful custom function ?  Well , t his episode explains how .  You'll  learn:   How to handle different date increments including days,  weeks  and months in one function  Using  the  SWITCH  function  to create  a  lookup  table   How the LAMBDA function can be deployed   Best practice  tips for this custom function   Listen now  to  i...

Messy, unstructured data.   It slows reporting, creates  errors ,  and tests everyone's patience.  Right ?  This episode breaks down how  the new  Regular Expressions (REGEX) work and how they help you  find  patterns in unstructured text, from email addresses to dates and hidden white space.  You will learn:   w hat  REGEX is and why it matters for accountants and analysts   h ow  to use REGEXEXT...

You may know  The Seven Habits of Highly Effective People  as a cornerstone of modern business thinking.   But what happens when those same principles  from this book  are applied to one of the most widely used tools in finance: Excel?   In this episode, we explore how the seven habits framework can  be applied   by  accountants and finance professionals  to  design  and  build Excel models.  The...

Excel Tips Summer Season Encore presentation from October 2025 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: H ow   to use TRIMRANGE for flexible range   selection     W hat's   new with pivot table formatting and   an exciting update on...

Handy n ew Excel features  are added  regularly  – but some  " older "  functions are still  particularly useful to your workflows. Smart power users know these  older  functions and their value  –  w hich is why t his episode  highlights  some of the  overlooked  Excel features that  are still valuable to  your spreadsheet  work today. Tips  include: How multiple windows  make it easier to copy a...

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 t...

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: H ow to use TRIMRANGE for flexible range selection     W hat's new with pivot table formatting and an exciting update on auto-refresh    H ow the TRANSLATE function works for multilingual data    T he s implifi...

In this thir d 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( )  ...

In this second episode of a three-part series, y ou 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...

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 dis...

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...

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, w eekly or monthly data, including vo...

The Pareto principle is a powerful concept for business.   In this episode we'll explore th is 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 int...

What if you could seamlessly collect and analy s e 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 approv...

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 e xplore how t his 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 . L earn from examples such as inflation adjustments...

Ever struggled to extract crucial financial data from PDF files into Excel?   As accountants and finance professionals, this can be a regular occurrence.   To help you, this episode explor es Microsoft Excel's Power Query feature . Y ou'll uncover how to seamlessly import data from system-generated PDFs like invoices and statements.   From tackling tricky formatting issues to cleaning up messy row...

Excel doesn't have a built-in timeline chart but we'll show you how to create one. In this episode you'll learn how using a scatter chart can help you build a timeline chart. For easy reference, here are the episode timestamps: 0:17 - About timeline charts in Excel 1:33 - Hacking the scatter chart to make a timeline chart 2:43 - Formatting dates using the range 3:32 - Universal formatting shortcut...

Calendars are your go-to tool for planning. In this Excel Tips episode, you'll learn the steps to create a single-month calendar from scratch. Discover how to combine advanced functions to build a two-dimensional calendar structure, and how to format your dates and highlight weekends and holidays. You'll also gain knowledge of how to customise the calendar to fit your specific needs. For quick ref...

Reports are crucial to your work, aren't they? Power Query is a valuable tool for importing many types of data into Excel to help you create these reports. However, it does have limitations. In this episode, you'll learn how to address one of Power Query's key shortcomings: user interactions. By using staging queries, reference techniques and range names, users can dynamically filter data within P...

Looking to improve your report-building skills? If so, this is the episode for you. Uncover tips on how to leverage multiple Excel features to create a system that effortlessly hides blank and zero rows in your reports. Also in this episode, you'll discover the subtotal function's superpower and learn how you can use it to help identify if a filter needs reapplying. You can use these handy skills...

Looking for ways to gain deeper insights from your sales and costs data in Excel? This episode explores the power of heat maps, a data visualisation technique often used for climate data, but equally effective for business analysis. For fast access, use these timestamps: 0:15 – Using heat maps for sales and cost information. 1:59 – Colour scales options and settings. 5:15 – Conditional formatting...

There's a misconception that Excel macros are outdated or unnecessary. In this episode we set the record straight and explore their continued value. Delve into three practical macros that can streamline various tasks in your Excel workflow. More importantly, you'll come away with the knowledge to decide when creating a macro makes sense for optimising your efficiency. For fast access, use these ti...