Channel Title: Access Analytic Channel Description: Amazing Excel and Power BI Solutions. Perth, Western Australia. Our aim is to help you make decisions with confidence. Let us know if there's topics you'd like to cover and please subscribe and click the notification item to keep informed when we release new videos. About Us... Jeff Robson, Wyn Hopkins are the directors of Access Analytic Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting. Wyn is a Microsoft MVP and was once upon a time a Chartered Accountant with PricewaterhouseCoopers in the UK. He published the book Power BI for the Excel Analyst (a beginners guide to Power BI) in July 2022: https://pbi.guide Find out more about us and the team here https://accessanalytic.com.au/about-us-2-2/ Both Jeff and Wyn would love to connect up with you on LinkedIn https://www.linkedin.com/in/wynhopkins/ https://www.linkedin.com/in/jeffrobson/ <video> How to Stop Excel changing text to dates and removing leading zeros Description: Did you know you can now stop Excel from automatically making those annoying changes to your data? Thanks to a a little known update in Excel 365, you can take control of formatting issues like disappearing leading zeros in phone numbers, unwanted date conversions, or long numbers turning into scientific notation. It’s all about one simple tweak in the settings. This step-by-step guide shows how to disable these automatic changes causing frustration. With this update, typing phone numbers, codes, dates, or long numbers is hassle-free. Make sure you’re using the latest version of Office 365 or Excel 365 to unlock this life-changing feature. Thank you, Excel team, for listening to users and making this possible! The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> 5 Excel Tricks They Won't Believe Description: Impress (or prank) your colleagues with five jaw-dropping Excel tricks that blend creativity with functionality. From generating personalized images in cells using Lambda functions to creating a broken sheet illusion, these clever hacks are guaranteed to spark curiosity. You'll learn how to create an "AnyImage" function to dynamically display themed icons, a "GridImage" trick for generating grid-based graphics, and even manipulate number formatting to show misleading results. Plus, master the XLL function for clickable XLOOKUP links and the ingenious broken sheet hack that turns a screenshot into a convincing sheet background. Whether you're looking to amaze coworkers, teach advanced Excel concepts, or just have fun, these tricks provide a perfect mix of entertainment and education. Dive in to uncover the magic of Lambdas, creative formatting, and Excel's hidden potential. Don’t forget to download the example file and try it out yourself! The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ How I did it: 00:00 The tricks demonstrated 03:25 Trick 1 : AnyImage 05:38 Trick 2: GridImage 08:45 Trick 3: Wrong Numbers 09:18 Trick 4: XLL - XLOOKUP LINK 10:53 Trick 5: Broken Sheet File: https://accessanalytic.com.au/5-excel-tricks-to-wow-your-colleagues/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Power BI Hiding Hidden Pages - One Minute Warning Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> USERELATIONSHIP and multiple dates in Power BI Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> THE Easiest Multi Level Drop Down List ( easy Double XLOOKUP technique ) Description: An awesome simple explanation of HOW TO BUILD a robust multi row dependent drop down list in Excel I realised after recording that Mark suggested this exact solution at the start of our UNPIVOT podcast and I didn't realise until listening back to the podcast a few days later. Hear the discussion https://youtu.be/OFNDtXxrk0g ⏬ Download the file: https://aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/Es1QX19t3ElLmyoMq_hntlwBDLD_D97_7ztOlRPVfSTqMQ?e=buxjaM Mark’s video https://youtu.be/L3jWMoE6KnI?si=b5HxJRYcZBAciU37 My previous video of “easiest dependent drop down list” https://youtu.be/U3WnM2JCrVc?si=ldYgW3oatoJt-0to 00:00 the aim 00:48 table set up 02:13 range name set up 04:00 the magic XLOOKUP : XLOOKUP 09:00 conditional formatting to flag invalid choices 11:12 if you can’t rely on a sorted table The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Power BI On Object Interaction - Boosting Efficiency or still too flawed? Description: Discover why it’s time to embrace On Object Formatting in Power BI! Initially, this feature had its flaws, but recent improvements make it a must-try tool for efficient report design. In this video, you’ll learn how to activate On Object Formatting and see why it’s superior to the traditional methods. From intuitive double-click formatting to the ability to seamlessly swap fields without losing formatting, the feature enhances productivity while keeping the familiar side panel intact for users transitioning from older workflows. The tutorial also highlights useful settings like disabling the default auto-arrange feature, which can complicate visual adjustments, and shares practical tips for navigating occasional glitches. D. Let’s get this tool even better by sharing feedback with Microsoft! The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Excel Tables - Tutorial ( Excel's 2nd best feature ) Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ 00:00 Start 00:26 Intro 01:39 Simple Features 02:55 Referencing Columns 04:20 Slicers 04:46 Totals 05:48 BEST Features including Auto-Expand 06:36 Better Formulas 09:28 Pivot Source 11:00 Drop Down List Source 12:51 Power Query Source 14:00 Final tips </video> <video> List Compare in Excel with FILTER XMATCH Description: Finding mismatches between lists is a common challenge in accounting and data analysis, whether you're managing invoices or tracking items across datasets. This tutorial demonstrates how to compare lists efficiently in Excel using functions like FILTER, XMATCH, and logical operators such as ISNUMBER and NOT. You’ll learn how to identify differences between two lists, such as missing invoices or discrepancies between datasets, with practical examples and step-by-step guidance. The focus is on leveraging the powerful capabilities of Excel 365 or Excel 2021/Excel 2024 or later enabling dynamic and accurate analysis. Through scenarios like identifying removed names or mismatched invoices, you’ll discover how to streamline your workflow. This tutorial empowers you to clean, analyze, and organize data effortlessly, saving time and reducing errors. Stay tuned for the complete walkthrough and tips to enhance your Excel skills! The file: https://aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/EijQxfNZzYJAl8IHR1YaUe8BGVBATyY-qRIwM3_AR9mElw?e=bTOWjP The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> How to Identify Rows with Missing Values using Power Query Description: Learn a simple technique to identify and filter rows with missing data in Power Query! In this quick tutorial, you’ll see how to transform records into lists, use the List.Contains function to spot null values, and apply a simple filter to keep only the relevant rows. With just a few clicks, you'll master the underscore (_) operator and streamline your data-cleaning process in Excel. Get ready to supercharge your workflow with this practical technique! Perfect for anyone working with messy datasets, this tip will save you time and effort in managing your data like a pro. The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Power BI INFO.VIEW.MEASURES - Share Measures with End Users Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Excel Avoid Offset and use RowAbove Description: Great tip from @operlandsen in the comments: You can define the name to refer to this formula: =!A1 (assuming the active cell is in Sheet1 cell A2). Then you can keep the Workbook level scope for the name and it will work in all worksheets. ————— 👀In this video, you’ll uncover a practical and efficient way to use named cells, specifically the ROWABOVE approach, to tackle running totals and conditional formatting in Excel while avoiding the pitfalls of the OFFSET function. The tutorial starts with a common error in running totals c and presents a clever fix using the N function combined with ROWABOVE, demonstrating how to create robust running totals that dynamically adjust when rows are inserted or deleted—eliminating the dreaded #REF! errors. The magic lies in defining and leveraging named ranges like ROWABOVE and applying these to both calculations and conditional formatting. This makes formulas traceable and avoids the inefficiencies of volatile functions like OFFSET. The video also covers conditional formatting tricks to highlight discrepancies dynamically, enhancing usability and auditing capabilities. Whether you’re a spreadsheet wizard or a casual user, these tips are sure to boost your Excel skills! The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Microsoft Fabric Setup Guide for Beginners Description: Are you new to Microsoft Fabric and overwhelmed by terms like CUs, bursting, smoothing, and reserved capacity? This video provides a step-by-step guide to demystify the process of setting up Microsoft Fabric. Learn about buying capacity, assigning it, and understanding key concepts like bursting and smoothing to optimize your usage. From navigating Azure Portal to Installing the metrics app this tutorial simplifies everything, even for non-technical users. You’ll explore how to buy reserved capacity and the difference to pay-as-you-go, set up workspaces, and analyze capacity needs with Fabric's metrics app. By the end, you'll have clarity on the initial set up of Fabric. What is Fabric: https://youtu.be/Fbm5g2-Q2Qk&list=PLlHDyf8d156VDobBIk13o4mZLk19DbV81 Start / Stop Capacity: https://youtu.be/T-9v_ggyop0&list=PLlHDyf8d156VDobBIk13o4mZLk19DbV81 Other Resources ---------------------------------- Data-Marc Blog on reserved capacity https://data-marc.com/2024/09/09/purchasing-fabric-capacities-as-reserved-instance/ Microsoft Docs on reserved capacity https://learn.microsoft.com/en-us/azure/cost-management-billing/reservations/fabric-capacity?WT.mc_id=M365-MVP-5002589 Metrics App https://learn.microsoft.com/en-us/fabric/enterprise/metrics-app/?WT.mc_id=M365-MVP-5002589 Documentation https://learn.microsoft.com/en-us/fabric/enterprise/?WT.mc_id=M365-MVP-5002589 -------------------------------------- 00:00 Introduction 02:30 Buying Capacity 07:17 Assigning Capacity to a Workspace 08:26 Reserved Capacity (40% discount) with annual commitment 12:00 What are Capacity Units / Seconds 13:00 Smoothing and Bursting and Throttling 17:50 Getting the Metrics App The team and I at Access Analytic develop Power BI , Excel and Solver Budgeting solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Excel Table Traps: Avoid Common Excel Pitfalls with These Tips! Description: Are you using tables in Excel but sometimes find them more confusing than helpful? This video uncovers the "Table Traps" that can cause unexpected issues, like hidden formulas reappearing, total rows behaving unpredictably, and frustrating formula references that refuse to stay locked. With practical solutions and tips, you'll learn how to handle these quirks confidently and efficiently. From mastering table features like drag-and-drop totals and managing dropdown lists to implementing custom fixes for name-scoping errors, this guide equips you with everything you need to avoid common pitfalls. Plus, discover time-saving hacks, including setting up quick access toolbar icons and clever formulas for detecting hidden filters. Simplify your Excel workflow and conquer tables like a pro! The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ 00:31 Ghost Formulas 02:51 Locking Table References 07:03 Filtering Tips 10:51 The Named Table bug The ISFILTERED formula =LET(_tblName, tblSales2, IF( AGGREGATE(3,4,_tblName)=AGGREGATE(3,5,_tblName)," ✅ no filter", "⚠️ FILTERED!" ) )=LET(_tblName, tblSales2, IF( AGGREGATE(3,4,_tblName)=AGGREGATE(3,5,_tblName)," ✅ no filter", "⚠️ FILTERED!" ) ) Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Excel Copilot - Clean Data Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video> <video> Power BI Header Icons - TURN OFF Description: The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ Links: https://themes.powerbi.tips/themes/properties https://github.com/PBIQueryous/PBIThemes/ https://github.com/deldersveld/PowerBI-ThemeTemplates The Theme Code { "name": "Access Analytic", "textClasses": { "callout": { "fontSize": 30 } }, "visualStyles": { "*": { "*": { "visualHeader": [ { "showCommentButton": false, "showCopyVisualImageButton": false, "showDrillToggleButton": false, "showDrillRoleSelector": false, "showDrillUpButton": false, "showDrillDownExpandButton": false, "showFilterRestatementButton": true, "showFocusModeButton": false, "showFollowVisualButton": false, "showOptionsMenu": false, "showPersonalizeVisualButton": false, "showPinButton": false, "showSeeDataLayoutToggleButton": false, "showSetAlertButton": false, "show": true, "showDrillDownLevelButton": false, "showSmartNarrativeButton": false, "showVisualErrorButton": false, "showTooltipButton": false, "showVisualInformationButton": false, "showVisualWarningButton": false } ] }, "slicer": { "*": { "visualHeader": [ { "showFilterRestatementButton": false } ] } }, "advancedSlicerVisual": { "*": { "visualHeader": [ { "showFilterRestatementButton": false } ] } } } } } Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ Connect with me https://wyn.bio.link/ </video>

Contacts and Links

Description

Amazing Excel and Power BI Solutions. Perth, Western Australia. Our aim is to help you make decisions with confidence.

Let us know if there's topics you'd like to cover and please subscribe and click the notification item to keep informed when we release new videos.


About Us...
Jeff Robson, Wyn Hopkins are the directors of Access Analytic

Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting.

Wyn is a Microsoft MVP and was once upon a time a Chartered Accountant with PricewaterhouseCoopers in the UK.
He published the book Power BI for the Excel Analyst (a beginners guide to Power BI) in July 2022: https://pbi.guide

Find out more about us and the team here https://accessanalytic.com.au/about-us-2-2/

Both Jeff and Wyn would love to connect up with you on LinkedIn
https://www.linkedin.com/in/wynhopkins/
https://www.linkedin.com/in/jeffrobson/

Videos from channel