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.
The lazy way to combine monthly sheets
|
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 video, built for exactly the kind of work that eats up a controller's or analyst's month. My favorite part is a trick with two empty sheets. Add a new month anywhere between them, and it shows up in your combined list without you touching the formula. One note: if your data is large or messy, Power Query is still my go-to. It keeps your results separate from the raw data and cleans everything up along the way. VSTACK is for when the data's already clean and you want something lightweight right in the sheet. Before we jump into Geeky News, here's a word from our sponsor: ๐ค Geeky Newsโ๏ธ Microsoft is merging its two Copilot apps into oneThe consumer Copilot app and the Microsoft 365 Copilot app are becoming a single app with one name, one icon, and one address. The merged app also combines your personal and work accounts into one joint but walled-off experience. You can switch between them, and different background colors should tell you which one you're in, so you don't accidentally paste company data into your private chat. Let's hope the colors are loud enough. ๐ It's been rolling out on mobile and web since August. Rollout on Windows and Mac should be starting right about now. If you use the consumer Copilot app on your phone, you'll need to download the updated version yourself. Everyone else gets switched over automatically. ๐ Google Sheets gets a better pivot table editor and more room to growโCalculated fields in pivot tables now get their own formula editor, with a field picker instead of manual typing and live error checking as you build the formula. Google Sheets also doubled its cell limit, from 10 million to 20 million cells per spreadsheet, covering new, existing, and imported files. Still a way off Excel's 17 billion cells. ๐ค Did You Know?Center Across Selection is finally in the ribbon. But it's still an extra click away, hidden under Merge & Center. The good news is: the new location means we can now pin it to the Quick Access Toolbar. Just right-click over the command to add it and have it always at your fingertips. ๐ Bringing the concepts togetherAlison has spent over 20 years as a finance coordinator for a non-profit. She took Fundamentals of Financial Analysis and wrote this: Congrats, Alison. Proof that it's never too late to go deeper. See you next week, Leila When you're ready, here are some ways we can help: ๐ Join 500,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.
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...
Budget versus actual is one of those reports that follows you around if you're in Controlling, FP&A or creating any type of management report for your company. I built versions of it for years. The numbers were never the difficult part. Putting it together so it actually communicated something was the tricky bit. Management always wants to see things from different angles, so it had to work by month and by region. How could I fit it all on one page? Actuals, plan, previous year, variances,...