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.
Months in columns, headaches in analysis
|
Here's what I've noticed. The reports that look the cleanest in a meeting are almost always the hardest to work with. Here's an example of a meeting-friendly table: But wait until you need to create a Pivot Table or do quarterly or half-year analysis. Then it's better to have the data in long format: But how do you quickly turn wide into long? ๐ Here's the 1 minute formula videoโ Power Query is an option for sure. A few clicks and you're done. But if your numbers are already in the sheet, there's a quicker way. One formula, and it updates automatically. No refresh needed. ๐ค Geeky News๐ Google Sheets finally gets combo chartsLast week I gave Google grief for adding 3D charts to Sheets. This week they announced combo charts, and now we're talking. You can pair columns with a line, or split them across a secondary axis, which is exactly what you need for actual-vs-budget or total-vs-average comparisons. It also fixes an annoying Excel import bug: combo charts with a secondary axis used to lose that axis on import. Not anymore. Gradual rollout starts in August. ๐จ PowerPoint gets a design refreshPowerPoint is getting 13 new SmartArt layouts (timelines, "Meet the Team" slides, that kind of thing), 13 new themes, and a cleaner Design ribbon so Colors, Fonts, and Layout aren't buried three clicks deep. They also fixed the file-open screen so it's one progress bar instead of three fighting each other. Nice, but it's the SmartArt and themes people will actually notice. ๐ Planner gets reusable templatesYou can now turn any Planner plan into a template. Tasks, buckets, labels, checklists, attachments - all of it gets saved and reused. Publish it and share it with your Microsoft 365 group. The next time someone needs the same setup, they start from that instead of building it from scratch. Think onboarding, campaign launches. In our case, development tracker for new courses. ๐ค How to combine data with different headers?Let's say you need to combine data from different files, and each file names its columns differently. One says "Employee", another says "Name". Same data, different headers. That's the new challenge we just added to the Power Query Challenges pack. If you already have the pack, don't miss the new addition. It's going to keep growing too, so stay tuned for more drops. People who've worked through it keep saying the same thing: it's not about getting the right answer, it's about getting there the smart way. Peter put it well:
And Gabriele picked up something most people never touch: tweaking the M code the interface generates to get results you can't get from clicking alone.
A good reminder why practice matters. It's not about speed. It's about seeing things you'd otherwise miss. Have a great week ahead, Leila When you're ready, here are some ways we can help: ๐ Join 400,000+ members in our coursesโ ๐บ Get free tutorials on YouTubeโ ๐ฅ Train your whole team with our courses. Team pricing and progress tracking - reply for details. This newsletter contains affiliate links, which give us a small commission at no cost to you. 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...