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.
"to do" to "completed"
|
Excel automation is not just VBA, Office Scripts, or Power Automate. Simple Excel formulas can provide the same effortless experience for the end user. In today's Between the Sheets, weβre exploring:
π Tracking Progress in ExcelLast week, we released a batch of Excel Practice Exercises to let you hone your Excel skills. Some were curious about the techniques we used to let you track your progress automatically. Progress is tracked in three different ways:
I've put "exercise" in brackets because you can apply the same ideas to project tracking. Here's a little peek behind the curtain. π€« In the exercise files, you can check off tasks as you finish them. We used the "old" checkboxes, because the cool new ones aren't widely available yet (and will not be available in standalone versions, like Office 2021 or older). The old form control checkboxes need a bit more work. But we've got you covered with step-by-step instructions in this article. The core principle is the same. Checked and unchecked state corresponds to TRUE and FALSE values. Once you have TRUE/FALSE values assigned to cells, you can use it in formulas. That's it - that's the secret. Here's how you can use it to track progress:
="Completed " & COUNTIFS(A5:A9, TRUE) & "/" & COUNTA(A5:A9)
The REPT function repeats a character or symbol a specified number of times. I've used this technique before to create McKinsey-style lollipop charts. But it's also a handy way to show progress. Seeing the progress right in front of your eyes can be quite satisfying. π By the way, did you hear about our summer challenge? If you complete all 3 exercise packs (30 exercises in total) and
you get a chance to win my upcoming Pivot Table Masterclass (scheduled release in September). We will choose 10 lucky participants in a random draw. You have the whole of August to participate. π€ Geeky Newsπ» Improved File Explorer and Taskbar in Windows 11A new optional update for Windows 11 is out. And it includes some welcome improvements to the File Explorer and taskbar. If you decide to install it, you'll get the ability to:
BTW, did you know you can do it directly from the taskbar? No Task Manager needed. Go to Settings > Systems > For Developers and enable End Task. π Better tables in Word for the webTables in Word are a pain to work with. But they're about to get better, at least in Word for the web. No word on the desktop version as yet. You can now use visual cues to easily insert and move rows and columns. Position the cursor where you want to add a new row or column. Wait for a + icon to appear and click on it. To move, hover the cursor over the column or row until you see a group of four dots. Select the four dots and drag and drop the column/row where you want it. This is similar to the recent updates to the grid in Excel Online. This behavior is currently available to Office Insiders. It should be available to all users in September 2024. π€ AI-enhanced web search with Bing and SearchGPTBing was closely connected to Microsoft's leap into AI from the start. Before "Copilot", there was "Bing AI". Now Bing AI is back with generative search. It now offers AI-generated answers alongside regular search results. It's currently available to a small group of early adopters. Meanwhile, OpenAI launches a prototype search engine, SearchGPT. It will also organize and summarize the search results using generative AI. OpenAI's large language models also power Microsoft's AI solutions. It will be interesting to see how the competition between them shakes up. Both will also compete with Google, whose launch of AI Overviews revealed some limitations of generative search. π Power StoriesI love hearing how you implement the knowledge from my courses into your daily life. So it was fantastic to hear from Maria, who completed Master Excel Power Query and immediately put it to work. I'm glad that the queries you've built are saving you time and effort. And congratulations on your new role as Director of Finance! Excel skills by themselves are not enough to guarantee a promotion. But they definitely help. π
Got a success story about using your XelPlus skills? Share the details, and inspire other students π 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.
Let's say you're waiting for colleagues to submit the latest data. You need to check if the files came in. And if the data is complete. So you start opening files one by one. It's tedious. And completely avoidable. When Microsoft released the new IMPORTCSV function, I wasn't ready to throw Power Query out the window. It still handles far more sources and does things this new function simply can't. But it got me thinking. What if you could peek at a CSV without fully loading it? Just enough to...
Excel's chart formatting menu is dangerous. Not because it's bad. Because it gives you too many options. And some of them have no place in a professional report. Take this social media trend: pasting images directly into your chart bars. Looks creative. Gets lots of views on YouTube. But would you put that in front of your manager? In just a few extra clicks, the same data can look like this: Same logos. Matching brand colors. Just used with intention. π Watch: How to build this chart in just...
Excel remembers things you teach it. That's not AI. It's a Custom List. And it's been hiding in Excel Options the whole time. That's how you get to automatically fill down months or days of the week. And you can build your own: team names, department codes, project phases - anything you type over and over. Excel learns the order too. So "Mon, Tue, Wed..." or your custom categories fill in automatically. The list lives on your device, not in the file. Set it up once. Use it in every workbook,...