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.
I finally stopped wrapping everything in FILTER
|
I always build my formulas the same way. I include extra rows below my data, just in case someone adds more next month. And every time, I get a column with zeros. A blank row that should be invisible suddenly shows up as zero in the spilled list. Annoying, but there are ways around it. My go-to was to wrap in a FILTER. Recently it got a lot simpler. One function. Or even better, one character. Add a dot before or after the colon in your range, like A2:.A100, and Excel quietly drops the blank cells from the result. No formula needed. Want it as a proper function instead? That's TRIMRANGE(A2:A100). Same result. I just find typing the dot faster. And it stacks nicely with other functions. A rolling 12-month chart. A clean UNIQUE list without a stray zero at the bottom. Same trick, same dot. ๐ Stop blank cells from messing up your spilled rangesโ ๐ Course: Excel Essentials, now on LinkedIn LearningWhen LinkedIn asked me to revise their Excel Essential Training on the LinkedIn Learning platform, I was excited to take on the challenge of distilling the essentials in under 5 hours. A lot of people who use Excel every day never actually learned it. They picked it up in pieces. A formula here, a shortcut someone showed them once, years of getting by. This course is for them. It's also for someone who's never opened Excel before and wants to start right instead of patching it together later. ๐ Use the link in my post to access the course free for 24 hours. No LinkedIn Learning account needed. ๐ค Geeky News๐งโ๐ซ PowerPoint Live gets a refresh buttonTwo new features for presenting in Teams. Refresh updates your deck to the latest version mid-presentation with one click - no resharing, no losing your place. Explain lets attendees select unfamiliar text on a slide and get it broken down by Copilot, without interrupting you to ask. Refresh works for anyone with a Microsoft 365 subscription. Explain needs a Copilot license too. I don't know if it's just me, but it's getting difficult keeping track of everything that needs or doesn't need a Copilot license. ๐ค Teams finally lets you check your mic before joiningA new button on the pre-join screen tests your speaker and mic before you're actually in the meeting. Plays a tone to confirm sound, records your voice and plays it back, then gives you a quick yes-or-no on both. Rolling out on desktop first. Zoom has had it for ages. About time Teams caught up. ๐ Power BI gets a date picker (Excel when?)โDate slicers in Power BI have a new picker mode that updates itself. Set it to a relative range, like "last 12 months," and it rolls forward automatically as your data refreshes. Viewers can still switch to a manual date or range if they want, no separate slicer needed. Currently in preview. Turn it on under Options > Preview features. If you've ever opened a report on the first of the month just to nudge a date filter forward, this one's for you. ๐ค Did You Know?You might know the shortcut for AutoSum: Alt + = (alt equals sign). You just have to press Enter for the sum of the numbers above to calculate. Or do you? Alt + = = (alt equals equals) does the same thing in one move, no finger jumping to a different key. Thanks to Ian Schnoor for sharing this tip at the Global Excel Summit. ๐ Stood out among the teamAmila completed the Master Excel Power Query: Beginner to Pro. Here's what happened next: That's the thing about getting good at this stuff. You don't have to push for recognition. You just raise your hand, deliver, and people notice. See you next week, 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...