XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlus’ bestselling courses are popular among financial analysts, CFO’s, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
You don’t need formulas to clean data
|
You know that feeling... …when someone sends you a DM and says, “Can you make a report out of this?” Empty rows. Inconsistent formats. Oh, and it's not in a pretty table. Plus you have values scattered across 17 different files. Of course. 🙃 No need to panic. You just need Power Query. You might be reading this and nodding because you already use Power Query. You know it's a lifesaver for: ✅ cleaning messy data ✅ combining files ✅ transforming dates, text, numbers - all without formulas! Plus it's super intuitive. Mostly click, click, click. In case you're not familiar with Power Query or just want a quick refresher, I made a very comprehensive beginner-friendly video. I cover all the three scenarios above. PQ is one of those things that, once you learn it, you wonder how you ever worked without it. Yep. Let Power Query do the boring stuff. You focus on the fun bits - like the actual analysis, dashboards… and coffee breaks ☕😉 Go deeper: Master Power QueryYou’ve seen what it can do. That’s just the surface. In our Master Excel Power Query course, you'll go from beginner to Pro, one hands-on lesson at a time. The wins range from saving hours (even days) of work... To becoming the go-to person for difficult data problems... To getting promotions and even "infecting" others like Julia shared: The biggest win for me is getting to share my new knowledge with my colleagues. Since starting the course there have been so many situations where I saw someone struggling to do something in Excel and could go "Let me help you with that." Most people were really relieved when I told them "Don't worry about how I did that; all you need to do is click refresh", but I also managed to 'infect' a few people with my new obsession and got them to learning too. It's our best-selling course for a reason. Power Query doesn't just transform your data. It upgrades the way you work. 🤓 Geeky News🕸️ Power Query in Excel for the Web gets betterNo, still no full data import in Excel Online 🙁 (we're waiting too! Last we heard, it is coming). Until now, you could only refresh anonymous data sources (local files, websites) on the web. Now, they've added the ability to refresh authenticated sources (SharePoint, SQL databases, APIs) as well. We like seeing movement there and hoping for full PQ support in Excel for the Web soon. Especially now, with Python relying on Power Query to bring over data. 🤖 ChatGPT Agent can run your computer for youOpenAI introduced a new agent that goes beyond chat. It can schedule meetings, prep research, even order lunch for you. You can kick off a task and come back to it later. It asks for permission before making any irreversible changes. (For now... 👀) Now rolling out to Pro, Plus, and Team users. Just type 🤔 Did You Know?There's a shortcut to quickly launch Power Query Editor: Alt + F12 Not a one-hander (unless you have a truly impressive hand span 🖖) but it saves you the extra clicks if you close the Queries & Connections pane. 👏 Take breaks. Keep building. Repeat.Shoutout to Liliya for completing Master Excel Power Query (Beginner to Pro)! Welcome to the Pro ranks! 🎊 Now that's a deserved summer break! Follow her advice: Don’t rush it. You’ve got lifetime access. Power Query builds on itself. The more you explore, the more confident you get. And yes, treat yourself after each section. Small wins = big motivation. Been there myself. Starting is difficult. Once you start it gets easier. But life happens and sticking with it can get hard. No need to force it. Take it one lesson at a time. Enjoy your holidays and see you next week, Leila Want more?▶️ Subscribe on YouTube 🖇️ Follow us on LinkedIn 🥇 Join 400,000+ students in our courses 📣 Want to sponsor Between the Sheets? Get in touch here. 📨 If you were forwarded this message, you can get the free weekly email here. This newsletter contains affiliate links, which give us a small commission on any purchase made at no cost to you. This helps us run Between the Sheets and bring you updates like this. Thank you for your support! |
Leila Gharani - XelPlus
XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlus’ bestselling courses are popular among financial analysts, CFO’s, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
When I worked in finance, the reports I had to build almost always came from more than one sheet. A lot of copy-pasting. These days one formula can do that for you. It's called VSTACK. It stacks your sheets into one list. It stays live, works in Excel Online, and anyone who opens the file knows exactly what's happening. Most people who use VSTACK, use it to glue two ranges together, and think that's it. But that's barely scratching the surface. I put together my top 6 VSTACK tips in this new...
The most common way people mess up their own spreadsheets is a button sitting right there on the Home tab. Merge & Center. It's one of the first buttons anyone learns in Excel, and it does exactly what it says: centers a title, makes a header look tidy. The trouble starts the moment someone uses it inside the data instead of just the header row. Try to sort... yep, it doesn't work. Try to sum a column with a merged cell sitting in the middle of it. Column E, for example: Exactly. You'd have...
What do you do when your data goes wide? Picture a sales table with one row per rep, but then Sales_Jan, Units_Jan, Sales_Feb, Units_Feb, and so on, all the way to December. It just keeps growing wider with every month you add. Try to filter by month, or build a pivot table around it, and you're stuck. Month isn't a value in this table. It's baked into the column headers. You need a way to turn the wide into long so that it's ready for analysis. And there's a Python function that does exactly...