Skip to main content
AmigoFish

MyOnlineTrainingHub

MyOnlineTrainingHubVideo

Format:
YouTube channel
New videos:
Weekly
Typical length:
about 10 minutes
Latest video:
Not rated yet

About this show

Teaching you Excel and Power BI skills that will get you noticed and promoted. For more, check out my courses here: https://www.myonlinetraininghub.com/courses

Covers: spreadsheets, microsoft excel, excel formulas, data analysis, excel templates, business reporting, copilot AI, excel tutorials

Videos come from the channel's public YouTube feed. The artwork is the newest video thumbnail. Last updated . Names, artwork and text belong to their publishers.

Recently on this show

Summary of recent videos, drawn from the publisher's feed.

MyOnlineTrainingHub is a video channel teaching Excel and Power BI skills for work. Recent videos cover unusual Excel functions, a free automatic shift calendar template, checks for AI-generated spreadsheets, and a subscription audit dashboard.

Report a problem with this summary

Cosmos

Similar by topic and creator

See the full Cosmos

Similar shows

See all Education shows

Explore more

Videos

  • I Bet You’ve Never Used These Excel Functions (But You Should)

    · 15 min

    11 weird Excel functions you've never heard of that solve real problems. ⬇️ Download the example file here and follow along: https://bit.ly/strange26file 👩‍🏫 Advanced Excel Formulas course: https://bit.ly/strange26course Excel has over 500 functions, and some of the weirdest ones are surprisingly useful. In this video, we explore 11 lesser-known formulas that solve unique spreadsheet challenges—from automated unit conversions and hidden formula comments to an unlisted date formula you won't find in Excel's function menu. For each formula, you'll learn what each argument means, real-world use cases, and key quirks to keep in mind. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 00:00 Hidden Excel functions you probably haven't seen 00:39 №1: Unit conversions without Google 02:35 №2: Reveal formulas and spot errors 03:52 №3: Find sneaky text pretending to be numbers 05:42 №4: Auto-document your workbook 08:36 №5: Hide notes directly inside formulas 09:40 №6: Roman numeral conversions 10:24 №7 & 8: Convert numbers into words 12:09 №9 & 10: In-sheet translation 12:34 №11: The hidden function Excel won't list 14:59 5 Excel functions every professional should know #Excel #ExcelFunctions #ExcelFormulas

    Not rated yet
  • Automatic Shift Calendar in Excel for Any Year (Free Template)

    · 10 min

    Build an automated shift schedule in Excel in 10 minutes. ⬇️ Download the example file here and follow along: https://bit.ly/shiftroster26file 👩‍🏫 Advanced Excel Formulas course: https://bit.ly/shiftroster26course 🎬 MORE FREE EXCEL TEMPLATES, full playlist here: https://bit.ly/templates26playlist Managing repeating shift patterns manually can be a huge headache, especially when you have to rebuild your calendar every single year. In this tutorial, you’ll learn how to build a fully automated, reusable shift work calendar in Excel. We'll use modern dynamic array formulas to map out any shift pattern across the entire year, automatically adjusting for leap years and starting days. Plus, you’ll see how to add conditional formatting to highlight different shifts and weekends, so your roster is clear, clean, and easy to read. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 00:00 What we're building 00:28 Setting up the calendar grid and inputs 01:09 Generating dynamic dates 03:18 Hiding dates from other months 04:19 Creating the shift pattern formula 07:21 Highlighting working days 08:00 Adding visual icons and design touches 09:09 Testing different years and shift patterns 09:31 Free habit tracker template #Excel #ExcelTemplate #ExcelCalendar

    Not rated yet
  • Before You Send That AI Report, Run These Excel Checks

    · 14 min

    Audit ChatGPT, Claude, Copilot, and other AI-generated spreadsheets in Excel to catch hidden mistakes and build automated verification checks for reliable reports. ⬇️ Download the example file and cheat here: https://bit.ly/checkai26cheat 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/checkai26course Large language models like ChatGPT, Claude, and Microsoft Copilot are incredible at generating complex Excel formulas, VBA macros, and financial summaries; but they frequently introduce subtle hallucinations, misaligned ranges, and logic errors. Relying on them without a structured review process puts your data and credibility at risk. In this video, we break down a complete audit workflow to stress-test AI-generated workbooks before sharing them with leadership or clients: • Formula and logic auditing: identify misaligned ranges, off-by-one errors, and silent calculation bugs. • Automated validation: set up dynamic reconciliation checks, checksum rows, and error-flagging formulas. • VBA & script review: safe testing methods to verify AI-written macros before running them on live workbooks. • Best practices for AI prompts: Refine prompt structure to get cleaner, more verifiable spreadsheet outputs from the start. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 00:00 The hidden danger of AI mistakes 00:31 Testing Copilot’s report 01:27 Reconciling summary numbers 02:53 Checking breakdown charts 05:35 Finding source data errors 07:53 Quick data profiling with Power Query 10:21 Catching duplicate rows 11:33 Spotting silent formula bugs 13:23 Verifying business logic and assumptions 14:06 Prompting strategies to prevent future errors #Excel #ExcelAI #ExcelTutorial

    Not rated yet
  • ULTIMATE Subscription Audit & Dashboard in Excel (FREE Template)

    · 9 min

    Easily audit and cut unnecessary subscriptions with this simple Excel tracker. 🔗 Download the free template here and follow along: https://bit.ly/subsaudit26file 👩‍🏫 Master Excel & Power BI with my courses: https://bit.ly/subsaudit26courses 🎬 MORE FREE EXCEL TEMPLATES, full playlist here: https://bit.ly/templates26playlist Small recurring monthly costs can add up fast. In this step-by-step tutorial, you'll learn how to build an automated subscription audit tracker in Excel to see exactly where your money goes, identify which services to cancel, and project how much you could save over time. Whether you're an Excel beginner or looking to sharpen your spreadsheet skills, this project is simple, practical, and easy to follow. What you’ll learn: - Table setup & design: how to organize your subscriptions, costs, renewal dates, and payment methods cleanly. - Interactive checkboxes: add simple check marks to flag which subscriptions you plan to keep or cancel. - Automated summary cards: use core formulas to calculate total monthly and annual spend dynamically. - Visual data bars: apply conditional formatting to spot high-cost subscriptions at a glance. - Category breakdown: group your spending by category using dynamic Excel functions and a simple donut chart. - Future savings projection: see the real impact of cutting unused services using compound growth formulas. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 00:00 Why audit your subscriptions 00:48 Setting up the table 01:41 Adding checkboxes and totals 02:17 Summary metrics and formulas 03:36 Adding progress bars 03:58 Grouping costs by category 04:36 Adding a breakdown chart 05:37 Finding what to cancel 06:42 Seeing how much you can save 08:46 Build a complete personal finance tracker #Excel #ExcelTemplate #ExcelFinance

    Not rated yet
  • 9 Excel Tricks I Use Every Week That Almost Nobody Knows

    · 10 min

    Excel can find mismatched numbers, move columns with one drag and even read your cells aloud. Try these 9 useful Excel tricks to save time on everyday tasks. ⬇️ Download the example file here and follow along: https://bit.ly/surprisingly26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/surprisingly26course See when to use shortcuts such as Ctrl+\ and F4, and how built-in tools like Fill Justify, Speak Cells and Sheet Views can help with checking data and editing worksheets. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Excel tricks hiding in plain sight 0:20 Find mismatched numbers instantly 1:04 Move columns with one drag 2:36 Let F4 repeat the work 3:41 Rearrange text with Fill Justify 4:56 Fit two headings in one cell 5:44 Make Excel read your numbers aloud 7:42 Filter without disrupting everyone else 8:23 A drop-down list without the setup 9:04 Open Excel's hidden data entry form #Excel #ExcelTricks #ExcelTutorial

    Not rated yet
  • The Excel Navigation Bar People Keep Asking Me About

    · 13 min

    Build a custom Excel navigation bar and find your way around large workbooks without scrolling through endless sheet tabs. ⬇️ Download the example file here and follow along: https://bit.ly/navigation26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/navigation26course Finding the right sheet is only part of the problem in a large Excel workbook. Sometimes you remember a project number but not where it appears. Other times, you need to check the same few figures across different sheets or help a colleague find their way around your file. In this tutorial, I use an 18-sheet workbook to show you practical ways to navigate between worksheets, jump straight to important cells and create clear routes through your data. You’ll see how to switch sheets with keyboard shortcuts, search sheet names in the Navigation pane and turn Find All into a clickable list of matches across the workbook. The Name Box and Go To help you jump to specific locations, while named cells work like bookmarks you can return to without remembering cell addresses. The Watch Window also doubles as a live navigation list: keep important values in view and double-click an entry to jump to its cell. For shared files, hyperlinks make a project list clickable and give each project sheet a simple route back. Then we build a custom navigation bar using shapes and hyperlinks, with no VBA required. You’ll learn how to align the buttons, link them to worksheets, copy the menu across your key sheets and give each sheet its own highlighted tab. Freeze Panes keeps the buttons visible as you scroll, and hiding the standard sheet tabs gives the workbook a cleaner, more app-like appearance. The demonstration uses Excel desktop on Windows. Shortcuts and available features vary by platform and version; the Navigation pane and Focus Cell shown are Microsoft 365 features. The custom navigation bar is built with manually linked and formatted shapes. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Stop getting lost in large Excel files 0:24 Switch sheets without endless scrolling 1:41 Find what you need across the whole workbook 3:11 Jump to, and bookmark, important cells 5:23 Keep your key numbers one click away 6:57 Create clickable routes between your sheets 8:32 Build your own navigation bar, without VBA 12:46 More ways to use HYPERLINK #Excel #ExcelNavigation #ExcelTips

    Not rated yet
  • Microsoft Doesn't Want You to Find This Excel Form

    · 6 min

    Use Excel’s hidden data entry form to add, find and update records without VBA or scrolling across a wide table. ⬇️ Download the example file here and follow along: https://bit.ly/dataentry26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/dataentry26course Entering data into a wide Excel table often means scrolling back and forth just to complete one row. Excel already has a built-in form that brings the fields together in one window, with nothing to design and no VBA code to write. Select a cell in your data and press Alt, then D, then O in Excel for Windows. The form uses your column headings as field labels and each row as a record. It works with an Excel Table or a regular range with column headings. You’ll learn how to add records, move between fields, browse existing entries and update values directly in the worksheet. Formula results appear in the form without an editable text box, so you can enter data without typing over those formulas through the form. Finding a record is just as useful. Combine criteria across several fields, search using the beginning of a text value, or use wildcards such as keyboard to find text anywhere in a product name. Comparison operators help narrow the results further. I also show you how to add a Form button to the Quick Access Toolbar so you don’t have to remember the shortcut. The built-in form supports up to 32 columns and simple text-entry fields; it doesn’t provide custom drop-downs, checkboxes or date pickers. Confirmed record deletions cannot be undone with Ctrl+Z. The shortcut and toolbar steps shown are for Excel desktop on Windows. This is a form for working with worksheet records, not an online form for collecting responses. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 A quicker way to enter Excel data 0:38 Open the hidden Excel data entry form 1:08 Enter data without typing over formulas 1:50 See one complete record at a time 2:06 Correct an entry without hunting for its row 2:41 Find the exact record you need 3:47 The deletion you can’t undo 4:00 The limitations 4:37 Become an Excel expert 5:03 Another way to open the hidden form 5:43 Take your data entry a step further #Excel #DataEntry #ExcelTips

    Not rated yet
  • Bye-Bye Cells as We Know Them? Excel's Big NEW Feature!

    · 17 min

    Excel's new Lists and Arrays feature lets a single cell hold multiple values or an entire table. See how to filter individual items and calculate with the data stored inside those cells. ⬇️ Download the example file here and follow along: https://bit.ly/listsarrays26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/listsarrays26course This video was recorded using the Beta Channel preview for Microsoft 365 Insiders. These features may not be available in your version of Excel. I use supplier categories and candidate skills to show how lists keep related values together while letting you find individual items. You'll also see how to store a CSV dataset in one cell and calculate with its contents. We start with creating lists using Ctrl+J, then build up to: - Filtering individual list items, including how OR and AND conditions work. - Expanding lists with FLATTEN and combining it with UNIQUE, SORT and XLOOKUP. - Checking for matches with HAS, HASANY and HASALL. - Returning product lists by category with GROUPBY. - Summarizing customer satisfaction scores with PIVOTBY. - Calculating purchase order totals from nested arrays using LET, MAP and LAMBDA. I also cover the integration gaps demonstrated in this preview, including PivotTables and Power Query. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 An entire table inside one Excel cell 0:38 The basics: lists, arrays and nested arrays 2:52 Filter the items inside a cell 4:46 Turn lists into useful results with FLATTEN 7:09 Match one item, any item or every item 9:11 Analyze a dataset stored in one cell 11:48 Summarize survey scores with PIVOTBY 12:54 Advanced: calculate with nested arrays 16:26 What doesn't work with lists and arrays yet #Excel #ExcelTutorial #ExcelTips

    Not rated yet
  • NEW Copilot Features That Will Set You Apart in Excel

    · 11 min

    Set up Copilot in Excel to reuse your instructions, follow workbook guidelines and build recurring reports with shorter prompts. ⬇️ Download the example file here and follow along: https://bit.ly/copilot26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/copilot26course If you’ve been giving Copilot the same formatting corrections, explaining the same workbook or rewriting the same monthly reporting request, there’s a better way to set it up. In this Microsoft Excel tutorial, I show you how Personalization saves your working preferences, how a .Rules worksheet gives Copilot instructions for a particular workbook, and how custom skills let you reuse a process across different files. You’ll see a monthly sales summary example that groups revenue by region, calculates each region’s share and creates a table and chart. The reusable instructions are written in plain language, so this example doesn’t require you to write code. Next month, you can call the same skill on new sales data instead of rewriting the full request. I also cover reviewing a plan before Copilot edits your workbook, styling reports, and bringing in context from the web, Microsoft 365 work content, Power BI and other connected sources. A polished result still needs checking: saved instructions guide Copilot, but they don’t guarantee correct calculations. You’ll need a supported Microsoft 365 subscription with Copilot access. Features and menus can vary by account, Excel version, update channel and organization settings. Custom skills use a OneDrive folder, and work data connections require the appropriate access. Keep the .Rules worksheet visible so Copilot can read it. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Gave up on Copilot? Here’s what’s changed 0:23 One less hurdle before you start 0:38 Get that floating button out of the way 1:00 What you need to know 1:44 Feature №1: stop repeating the same corrections 2:34 Feature №2: give your workbook instructions 3:59 Feature №3: turn your prompts into skills 7:58 An AI report can still be wrong 8:38 Feature №4: connect Copilot to other data 10:49 Automate even more work in Excel #Excel #Copilot #CopilotTutorial

    Not rated yet
  • 7 Excel Functions That Build a Forecast That Updates Itself

    · 17 min

    How to forecast in Excel when every account needs a different method. 🆓 Get MindsHub free including 5 million tokens every month to get started: https://bit.ly/mindshub-moth ⬇️ Download the example file here and follow along: https://bit.ly/forecast26file Extending one trend line across every row is where most Excel forecasts go wrong. Sales might follow a growth rate, purchases move with revenue, and one odd month can drag a trend somewhere the business would never go. So this forecast uses nine methods, picked by what drives each account, and the whole thing recalculates when next month's actuals arrive. Online sales grow at a set percentage each month. Wholesale sales, vehicle expenses, interest and bank fees follow a linear trend, using FORECAST.LINEAR wrapped around FILTER so only the actual months feed the calculation. Shipping revenue, packaging materials and merchant fees are a ratio to sales, worked out from the historical relationship rather than typed in. Inbound freight and duties are a ratio to purchases. Salaries, superannuation, rent and depreciation come straight from the budget with XLOOKUP. Software subscriptions use a three month average built with LET, FILTER and TAKE. Utilities are the average of every actual month. Insurance, travel and repairs each use an average that excludes one outlier month, done by multiplying two logical tests inside FILTER. Professional fees repeat the same month from the previous quarter, because they spike quarterly. What holds it together is row 3. Every month is flagged Actual or Forecast, and every formula filters on that flag. Paste in next month's figures, change the flag, and the rest of the forecast recalculates on its own without a single formula being rewritten. The last section replaces that repeated logical test with a named formula called status, and Find and Replace swaps it into 101 instances at once. Worth knowing that formulas hidden in the Name Manager are harder for a colleague to audit, so think about who else opens the file. Built in Microsoft 365. FORECAST.LINEAR, FILTER, LET and XLOOKUP also work in Excel 2021, but TAKE does not, so the three month average needs a different approach there. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Why one forecast formula doesn't fit every account 0:56 Setting up the forecast sheet 1:32 Growth rate: 3% month on month 1:53 Linear trend with FORECAST.LINEAR and FILTER 3:28 Ratio to sales 5:06 Ratio to purchases 7:12 Ratio formulas for the remaining accounts 8:13 Straight from the budget with XLOOKUP 10:19 Three month average with LET, FILTER and TAKE 11:48 Average of all actual months 12:18 Average excluding an outlier month 13:09 Same month last quarter 14:33 Adding next month's actuals 15:15 Simplifying every formula with a named formula #Excel #ExcelForecast #ExcelFormulas

    Not rated yet
  • Build a Self-Updating Leaderboard in Excel (Free Template)

    · 10 min

    How to build a leaderboard in Excel that ranks and re-sorts itself. ⬇️ Download the example file here and follow along: https://bit.ly/leaderboard26file 👩‍🏫 Join my Excel Expert course: https://bit.ly/leaderboard26course 🎬 MORE FREE EXCEL TEMPLATES, full playlist here: https://bit.ly/templates26playlist Sorting a list largest to smallest tells you who is on top right now. Change one number and it's out of date. This leaderboard recalculates the rankings and rearranges the rows on its own, with no macros and every calculation visible on the sheet. The example uses sales against individual targets, but the same structure works for student marks, customer service scores, project delivery or anything else you can measure. If you don't have individual targets, rank the raw results directly. Attainment divides each person's result by their target, wrapped in IFERROR so a half-finished row shows zero instead of a #DIV/0! error. RANK.EQ turns that into a position, with the order argument set to 0 so the largest value ranks first. Equal results get equal ranks and the next rank is skipped, which is worth knowing if the leaderboard decides bonuses or prizes. PERCENTRANK then shows where each result sits across the whole group, so the top scorer reads 100% and the lowest reads 0%. The summary at the top uses SUM for total sales and target, LARGE to pull the highest attainment, and SMALL to find the cutoff for the bottom three. Both LARGE and SMALL take a position argument, so pulling the second or third best is a one character change. The interactive part is three data validation drop-downs for sort field, sort order and region. XMATCH converts the chosen field name into its column number. SWITCH converts Ascending and Descending into 1 and -1. SORT reorders the whole table by that column and direction, and VSTACK adds the headers back on top. The helper cells holding those numbers are hidden with white font. SORT needs Excel 2021 or Microsoft 365. VSTACK needs Excel 2024 or Microsoft 365, so without it you can type the headers in manually and use SORT on its own. A three colour scale on the attainment column makes performance readable at a glance. The thresholds are set to real numbers rather than lowest and highest value, so a colour means the same thing every time the data changes. At the bottom, SUMIFS totals sales and target for the selected region and COUNTIFS counts how many reps reached 100% of target. Change the region in the drop-down and those figures update too. After that, the only thing you maintain is the data entry table. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum #Excel #ExcelLeaderboard #ExcelTemplate

    Not rated yet
  • This Excel Trick Makes Your Tables Update Themselves

    · 8 min

    How to write one Excel formula that fills a whole column by itself. ⬇️ Download the example file here and follow along: https://bit.ly/darrays26file 👩‍🏫 Learn more with my Advanced Excel Formulas course: https://bit.ly/darrays26course 🎬10 Excel Formula Symbols You Wish You Knew Years Ago: https://youtu.be/WBuS4b2rujg Copying a formula down leaves you with a column of separate formulas, and any one of them can be overwritten with a typed value without anything on screen telling you. A figure stops recalculating, nobody clicks through the sheet cell by cell to check, and the report is wrong. Dynamic arrays work differently. You write one formula in the top cell and it spills down as far as the data goes, growing when a row is added and shrinking when one is deleted. Overwrite a result and you get a visible #SPILL error rather than a silent wrong answer. SEQUENCE numbers the list without you typing a single number. Replacing its hard-coded count with COUNTA makes the numbering adjust as tasks are added or removed. Wrapping SEQUENCE in TEXT with a "000" format and joining a prefix with & turns those into IDs like T-001. For due dates, one formula adds the entire start date column to the entire duration column, and the dot operator after the colon trims the trailing blanks so the result stops where the data stops. The hash operator is what ties it together. Put # after a reference and your formula points at the whole spilled range instead of one cell, so a days left calculation, an IFS returning Overdue, Due soon or On track, and a COUNTIF summarising those statuses all expand and contract on their own. A formula referencing a spilled range doesn't have to return one the same size, which is why that COUNTIF spills into just three cells. Now, the obvious question about Excel Tables. Tables do copy formulas down for you, which is useful, but you still get one separate formula per row and each can be overwritten. Keep your data in a table and put the dynamic array formulas outside it. Most of this needs Excel 2021 or Microsoft 365. The dot operator is Microsoft 365 only. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Why copying formulas down causes problems 0:35 Modern way of writing formulas in Excel 1:10 What you need 1:27 Number a list automatically with SEQUENCE 1:46 How spill ranges work 2:19 Make the numbering grow and shrink with COUNTA 2:49 Custom ID numbers with TEXT 3:26 Due dates for a whole column at once 3:42 Trim the blank rows with the dot operator 4:15 The hash operator for spilled ranges 4:46 If you want to write better Excel formulas 5:23 Using the hash operator 6:36 Dynamic arrays vs Excel Tables 8:07 The Excel trick I use every day #Excel #ExcelFunctions #ExcelFormulas

    Not rated yet
  • The Excel Data Cleaning Process I Use on Every File

    · 11 min

    Avoid reporting mistakes by mastering an end-to-end data cleaning process in Excel. ⬇️ Download the example file here and follow along: https://bit.ly/dcupdated26file 👩‍🏫 Master Excel & Power BI my courses: https://bit.ly/dcupdated26course Most Excel errors don’t trigger a warning, they just produce bad numbers silently. In this tutorial, you’ll learn a reliable, repeatable data cleaning framework to transform messy, unformatted raw exports into clean, audit-proof data ready for analysis and reporting. What you'll learn in this tutorial: - Data structuring: why converting raw ranges to Excel Tables immediately protects your formulas and references. - Fixing text & hidden characters: how to eliminate invisible spaces, trailing tabs, and non-breaking spaces using TRIM and CLEAN. - Resolving date & number format errors: quickly convert dates and numbers stored as text back into real values for accurate calculations. - Standardizing inconsistent entries: use wildcards and targeted Find & Replace to fix typos, department naming variations, and irregular codes. - Parsing & extraction: isolate key identifiers using modern text manipulation techniques like LEFT, TEXTSPLIT, and Delimiters. - Deduplication & missing data: spot redundant records, identify gaps, and handle blank rows without accidentally deleting critical rows. - Verification & QA: validate your cleaned dataset using a PivotTable sanity check before passing it into final reports or dashboards. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 The $32,000 silent error 00:48 Convert raw data to Excel Table 03:00 Clean hidden spaces with TRIM 03:55 Fix dates stored as text 05:20 Convert text numbers to real values 06:02 Standardize naming with Find & Replace 07:13 Split combined codes with LEFT 08:09 Remove duplicate invoices & fill blanks 09:06 Verify accuracy with a PivotTable 10:43 Next stop in data cleaning #Excel #DataCleaning #ExcelTutorial

    Not rated yet
  • Turn Your Messy Spreadsheet Into an Interactive Dashboard

    · 22 min

    Turn a fragile Excel report into an interactive dashboard that updates with one click. ⬇️ Download the example file here and follow along: https://bit.ly/fixes26file 👩‍🏫 Take my Excel Reporting Analyst program: https://bit.ly/fixes26course 👩‍🏫 Master Excel & Power BI with my courses: https://bit.ly/fixes26courses In this Excel tutorial, I rebuild a spreadsheet that breaks whenever new rows are added or data is moved. You’ll learn how to organize your Excel data correctly, prevent formulas and totals from breaking, and create an interactive dashboard that is easy to update. I also show you how to use an Excel Table, PivotTables, charts, and a slicer to turn a time-consuming report into a reliable reporting system. This tutorial is useful for anyone who creates recurring reports, maintains large spreadsheets, or spends too much time fixing Excel files after the data changes. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Why the spreadsheet keeps breaking 1:11 Restructure data into a tabular layout 5:52 Convert data into an official Excel Table 7:54 Plan the interactive dashboard 9:53 Link summary cards with PivotTables & GETPIVOTDATA 12:25 Build a dynamic Donut KPI chart 15:35 Build a timeline Column chart 16:43 Build a ranked Bar chart by project 19:21 Connect Slicers to all charts 21:45 Test the 1-click Refresh All 22:13 Build Excel dashboards in under 15 minutes #Excel #ExcelReport #ExcelDashboard

    Not rated yet
  • The AI Skill That's About to Change Finance Jobs

    · 9 min

    Get MindsHub free including 5 million tokens every month to get started: https://bit.ly/mindshubmoth ⬇️ Download the sample files and prompt: https://bit.ly/mindshub26file Every month, finance teams repeat the same Excel process. They download the latest data, copy it into last month’s file, update formulas, compare the results with budget, investigate variances, and rebuild the report. This work can take days, I know I used to do this back in my management accounting days, and while the numbers changed each month, the process stayed the same. So, in this video, I’m working with MindsHub to see whether it can take over these recurring workflows. But the real test isn’t whether it can produce one impressive file. It’s whether it can create a transparent process that we can review, reuse, and improve every month. I’ve linked the sample files and prompt in the video description so you can follow along. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum #Excel #FinancialForecasting #MindsHub

    Not rated yet
  • The Excel Combination I Use EVERY DAY (and you will too)

    · 9 min

    Use Ctrl+Enter in Excel to fill multiple cells, copy formulas, fill blanks, and more. ⬇️ Download the example file & quick reference guide here and follow along: https://bit.ly/ctrlenter26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/ctrlenter26course LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Use Ctrl+Enter in Excel 0:22 Update only visible cells 1:17 Fill formulas without changing formatting 2:43 Fill blank cells in seconds 4:14 Restore missing formulas safely 5:52 Update scattered cells at once 7:27 Enter formulas on multiple sheets 8:31 Keep the current cell selected 9:18 Excel tools you need to know #Excel #ExcelTricks #ExcelTutorial

    Not rated yet
  • How to Print Large & Small Excel Tables on A4 Paper

    · 12 min

    Fix common Excel printing mistakes and fit large or small tables neatly on one page. ⬇️ Download the example file here and follow along: https://bit.ly/print26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/print26course Printing in Microsoft Excel can be notoriously frustrating: columns spill onto extra pages, empty sheets print unexpectedly, and small data tables end up looking tiny. In this step-by-step tutorial, you will learn how to master Excel page setup and print settings so your reports look clean and professional every time. In this video, we cover: • Setting page size and orientation (A4 vs. Letter, Landscape vs. Portrait). • Defining and clearing the Print Area to prevent blank pages. • Adjusting margins, column widths, and page scaling. • Using Print Titles to repeat header rows across multi-page tables. • Inserting manual horizontal and vertical page breaks for neat sections. • Centering small tables on the page horizontally and vertically. • Creating dynamic, self-updating print ranges using formulas. • Navigating Normal, Page Break Preview, and Page Layout views. Whether you are printing physical handouts or exporting clean PDFs from Excel, these tips will save you time and paper. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 00:00 The problem with printing in Excel 00:35 Set paper size (A4 vs letter) 01:03 Eliminate blank pages and set the print area 02:29 Wide tables: change orientation to landscape 02:51 Long tables: adjust margins and column widths 04:41 Repeat header rows on every page (print titles) 05:20 Insert manual page breaks by category 07:09 Scale up small tables to fill the page 08:41 Center tables horizontally and vertically 09:19 Dynamic, self-updating print ranges 12:35 Key Excel settings to change next #Excel #ExcelTips #ExcelTutorial

    Not rated yet
  • Every Excel Person Needs to See This!

    · 10 min

    Try Zite with your own Excel data → https://try.fillout.com/mynda-treacy-01xr Free to start + 50% extra credits included. Using this link supports the channel at no additional cost to you. ⬇️ If you want to download the Excel Habit Tracker shown in the video, use the following link: All I want to do is check off that I did my 10,000 steps for today. But using the Excel mobile app is painful, panning left and right, zooming in and out, all just to tick one box. That’s what you get when you build a habit tracker in Excel, for example, and then try to use it on your phone. This tracker took me nearly 30 minutes to build and it uses 20 different Excel functions. While it works great on my desktop, it’s not ideal when I’m on the go. So, in this video I’m partnering with Zite to turn my spreadsheet into a proper mobile app. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Excel on your phone is painful 0:41 Turn your spreadsheet into an app 2:06 Plan the app before you build it 4:05 First look at the finished app 5:04 How it works behind the scenes 7:05 What an app can do that Excel can't 8:09 Get it onto your phone 8:43 Let AI analyze your data for you 9:38 The verdict: does it actually work? #Excel #Zite #HabitTracker

    Not rated yet
  • I Turned Excel Into the Ultimate Productivity System (Free Template)

    · 28 min

    Make a dynamic habit tracker in Excel that tracks daily habits, streaks, and your best day of the month, automatically. ⬇️ Download the free template here and follow along: https://bit.ly/habit26file 👩‍🏫 Become an Excel Expert with my course: https://bit.ly/habit26course 🎬 MORE FREE EXCEL TEMPLATES, full playlist here: https://bit.ly/templates26playlist Most Excel habit trackers are just a calendar full of tick boxes. This one does more. Tick off your habits, and it: automatically calculates your progress today, shows the habit that needs the most attention, tracks your current streak, and even highlights your best day of the month. Better still, change the month dropdown and the dates, day names, and the tracker update automatically. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 The habit tracker that does more than tick boxes 0:37 Set up the month selector and your habits 2:07 Dates that rebuild themselves every month 5:42 Checkboxes and the trick to tallying them 7:45 Auto-counting your current streak 11:15 Phases that show your daily progress 13:23 The KPIs: today, your best day, and what needs work 16:17 Charts that bring the tracker to life 20:29 Styling it into something you'll want to open 24:20 Smart touches: data bars, borders, and today's highlight 26:48 Recolor it all in one click, then make it yours 28:04 Finance tracker template #Excel #HabitTracker #ProductivityTracker

    Not rated yet
  • 5 Boring Excel Functions That Are Actually Brilliant

    · 11 min

    How to when use ABS, SIGN, REPT, TRUNC, and CELL functions in Excel. ⬇️ Download the example file here and follow along: https://bit.ly/bbbr26file 👩‍🏫 Learn more Excel functions and formulas with my course: https://bit.ly/bbbr26course REPT Function Tutorial: https://youtu.be/7vM2Y5PboWI These might be some of the least exciting function names in Excel. They sound more like something from a maths textbook than tools you would use in a real spreadsheet. But each one solves a surprisingly common problem, from fixing misleading percentage changes to making reports easier to read. Plus, they’re available in all versions of Excel. I’ll show you all five with quick examples you can try in the free practice file. LEARN MORE =========== 📰 EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up 🎯 FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/ 💬 EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum ⏲ TIMESTAMPS ============== 0:00 Five function names that sound painfully boring 0:26 ABS function 2:48 SIGN function 4:30 REPT function 5:44 TRUNC function 8:18 CELL function 11:41 The modern functions changing how people work #Excel #ExcelFunctions #ExcelFormulas

    Not rated yet