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.
How to avoid spreadsheet chaos π±
In today's Between the Sheets:
- Another Excel blunder in the news
- Get your tickets to Global Excel Summit 2024
- Test drive our Power BI course for free
Excel mishaps and how to easily avoid them
It's been a while since the last Excel fiasco made the news when it reversed the results of internal party elections in Austria. Another cautionary tale recently came to light, although it dates back to 2021.
Here's what happened:
The UK office in charge of picking trainee anesthetists messed up because of Excel errors.
They had to combine score lists from seven different regions into one big list. Each region's list was different and not easy to blend together.
People in the office used different ways to put the lists together, like copying rows or using Excel's VLOOKUP function incorrectly.
The list from Wales was extra tricky because it didn't have scores, just rankings from 1 to 24. These rankings got mixed up as low scores in the big list, making it look like the Welsh candidates weren't good enough.
In total, 35 out of 400 candidates had problems because of this.
It got me thinking... All of this was easily preventable.
A consistent template with clear instructions and proper spreadsheet design would have saved everyone a headache.
If the incoming data was consistently structured, combining it would have been a breeze with the help of Power Query. No manual copy/paste needed, no risk of someone messing up a VLOOKUP formula.
Once set up, the human effort would be limited to saving the incoming results to the same folder (well, that also could be automated - for ideas on combining tasks across applications, check out our course: Automate with Power Query - Recipes to solve business data challenges).
And even with everything properly set up, it still pays to have rigorous data accuracy checks in place.
In this case, the score discrepancy was major enough that it should have been obvious but was still missed, likely due to the sheer amount of data.
Perhaps AI tools like Excel's built-in Analyze Data could have brought attention to the anomaly.
Excel is a powerful tool - as long as it's used correctly.
β
βJoin the Excitement at Global Excel Summit 2024β
The Global Excel Summit 2024 is a must-attend event for all Excel professionals.
With a spotlight on Automation and AI, what you'll discover can transform how you handle data.
π Join us from February 6-8, either in person in London or virtually from anywhere.
I'll be in London at the event and hope to connect with you there.
Summit Highlights
π‘ Discover cutting edge AI tools that are set to redefine data analysis.
π€ Learn from 30+ global experts in Excel and Power BI.
π Earn over 30 CPD credits to boost your professional standing.
π₯ Network, meet, and connect with like-minded Excel enthusiasts from around the world
Special Offer Alert!
Use code LEILA on the checkout page and enjoy a 20% discount.
βJoin the Event and let's geek out over Excel & AI, connect with pros, and make memories!
β
π€ Geeky News
π Python in Google Sheets
If you're using Google Sheets and missed out on the excitement of Python coming to Excel, you now also get a chance to bring Python into your spreadsheets.
Neptyne has released a Python Add-on for Google Sheets, available to download for free from Google Marketplace.
It enables you to write your own Python functions and spill the results to cells. You get to control which Python components are available via a Package Manager.
π Outlook Calendar to show declined invitations
You can now keep events you decline on your calendar to retain access to the attached documents and any related information, while keeping the timeslot free.
You can enable it in Settings in the *new* Outlook for Windows, Outlook on the web, Outlook for Mac. It will also work in the Teams Calendar.
π Excel for the Web - easier formula building
Excel for the Web introduced a bunch of new features to make writing complex formulas much easier. They include:
- Insert function dialog box - similar to the familiar Formula Wizard from the desktop version, but with a more modern feel,
- Parentheses coupling - whenever you position your cursor inside a set of brackets, the corresponding closing bracket gets highlighted,
- Monospace font for better readability (recently introduced in desktop Excel as well),
- Value Preview that displays the result of any part of a formula when you hover your mouse pointer over it.
β
π Newsworthy
Are you curious to try Power BI but short on time?
Good news! We've put together a FREE QuickStart Power BI course.
You'll get to see what the Power BI hype is all about and decide if it's the right tool for you to learn right now.
You can complete this course in just over an hour; Spread it over 4 days in 20-minute bursts or go all-in at once.
Your schedule, your choice! π
π Sign up here. It's free (but we're not sure for how long we'll keep it that way!)
If you're a member of our "Fast Track to Power BI" course, you already know this content but if you have colleagues or friends who could benefit from an introduction to this awesome tool, it would be great if you share this link with them: https://link.xelplus.com/newsletter-powerbi-freecourseβ
There's already been a lot of interest and enthusiastic reviews keep coming in.
We're so grateful for such an incredible community of learners. Don't miss out on this opportunity.
β
π New Finds
We recently found Simple Sheets, a platform with user-friendly Excel templates used by a wide community, including Fortune 500 companies. We're exploring their templates and the early signs are promising. We'll share more in the coming weeks.
You can give their free bundle a try, and when you're ready to buy, enjoy a 15% discount using the coupon code LEILA - a special perk for our "Between the Sheets" readers.
β
π Power Stories
This review of the Fast Track to Power BI course from Yves Seybel really made my day:
|
|
Thank you Leila for this outstanding class. The rhythm, flow, and content are perfect. You were able to walk me through the complexities of the various BI interfaces and licenses and make them accessible to any learner. |
Congratulations on successfully navigating this learning journey π and thank you for the kind words. π
Got a success story about using your XelPlus skills? Iβd love to hear it! Hit reply to 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.
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.
Shared Excel files are great until your whole team needs to work on it at once. You filter for your tasks. Your colleague filters for theirs. Now your filter's gone. You filter again. They filter again. π€ It's like fighting over the TV remote, except it's a spreadsheet so more annoying. The fix: Sheet View. Go to View tab > Sheet View > New. You'll notice the column and row headers change to black. Give it a name. This way you can reuse it later. Just type over the "Temporary View". Now...
The files I used to have in my corporate job were pretty large. Like 40+ sheets. Some had more. I used to dread opening one of these during a presentation. Someone would ask me to pull up a specific sheet. And I'd be scrolling... checking... scrolling... It wasn't fun. Then someone showed me a simple click to see all the tabs in one place. I'd been using Excel for 10 years by that time and had NO CLUE this existed. That's just one of 4 methods I cover in this week's video, including one that...
Your Power Query file works perfectly. On your machine. Then someone else tries to use it and suddenly nothing refreshes because the folder path points to your laptop. This is why people move files to SharePoint. Same folder, everyone can access it, no more path issues. Great. You'll just use the SharePoint Folder connector and... π΅π« Ok, which path it's supposed to be? Here's the catch: Power Query shows you a giant list of URLs instead of folders. Well, there is a better way. And it takes...