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.
Is it goodbye to pivot tables? π±
Welcome to this week's edition of Between the Sheets! If you were forwarded this message, you can get the free weekly email here.
Today we're covering:
- two powerful new Excel functions
- AI Copilot in your Office apps
- What happened to Bing Chat?
Excel GROUPBY & PIVOTBY Functions
And do they BEAT Pivot Tables? π€
Pivot tables are very popular in Excel, especially with managers.
If you've made a report in Excel, you've probably used pivot tables.
But recently, Microsoft released 2 new functions: GROUPBY and PIVOTBY that can do a very similar thing.
In my latest video, I demonstrate their biggest advantages over pivot tables.
I walk you through the function's arguments and show you what to watch out for.
The basic syntax and functionality are straightforward and easy for an Excel newbie to master.
Advanced Excel users can take it even further, combining GROUPBY or PIVOTBY with other functions (for example to customize how date is displayed) or writing their own LAMBDA functions to create custom aggregations.
The results are returned as dynamic arrays - i.e., they spill. This way you just need ONE single function to do your entire analysis.
You can take raw data like this...
...and turn it into a meaningful report with a single formula (here in cell N2).
Pivot Tables are really useful in data analysis, especially when dealing with large data sets through Power Query. But these new functions offer great possibilities.
They are currently available to Excel Insiders as preview. You'll have to be patient until they roll out to your Office 365 version.
βTurn Over a New Page in 2024: Read More with Shortform!β
As we dive into 2024, many of us have a common goal β to read and learn more.
But, let's be honest, finding the time can be tough. That's where Shortform comes in!
Imagine diving into the world's best nonfiction, getting key insights without the overwhelm. π€
Shortform isn't just about quick summaries. It's about connecting with the heart of each book, giving you the knowledge and inspiration to apply in your life. Whether it's understanding habits in "Atomic Habits" or exploring resilience in "Grit," Shortform brings these ideas to life.
Special Offer Just for You: As a "Between the Sheets" reader, you get an exclusive deal β 3 months off their annual plan at shortform.com/leila!
Join the Shortform community and transform your reading into a journey of discovery and growth.
A heartfelt thank you to Shortform for sponsoring todayβs newsletter and for making our reading goals more achievable and enjoyable. π
π€ Geeky News
βοΈ Copilot for Microsoft 365 more widely available
When Copilot for Microsoft 365 became generally available in November last year, this only really included big corporates that could commit to a minimum of 300 user licenses.
Now, Microsoft is taking away the minimum license requirement and expanding availability to small and medium businesses.
Copilot integrates generative AI into the Microsoft 365 products and services. It can generate a PowerPoint presentation based on your text prompt, help you analyze data in Excel, draft email replies in Outlook (as well as coach you to write more effective emails), or summarize Teams meetings you've missed, among others.
In the commercial version, it offers secure integration of internal company data into the model through Microsoft Graph, making the AI answers more relevant to your business.
βοΈ Copilot Pro for individuals
Starting now, if you have a Personal or Family Microsoft 365 license, you can also use AI in your Office apps by subscribing to Copilot Pro.
It's not exactly like the version for businesses (it doesn't include Graph or work with Teams), but it still has great AI features for Office. Plus, you get first in line to use the newest OpenAI model (right now, that's GPT-4 Turbo) even when it's busy. You can also make your own AI tools called GPTs, just like with ChatGPT Plus.
Even if you don't have a Microsoft/Office 365 subscription, you can sign up for Copilot Pro. You won't have AI mixed into your Office apps, but you'll still enjoy a smooth AI experience on different devices, faster performance when it's busy, the GPT Builder, and you can create more AI images in higher quality.
π€ Confused about all the Copilots on offer?
Since Microsoft renamed Bing Chat to Copilot, and now with introduction of a new Copilot product, it's easy to get lost in the offering.
So, let's make it simple: now, there are three main Copilot options to choose from.
π§ββοΈ The free Copilot (previously Bing Chat) is your standard AI chatbot. You can use it without a Microsoft account (although logging in gives you additional features). In addition to the standalone website, it's also built into the Edge browser and included with one of the latest Windows 11 updates.
Then there are the monthly subscriptions - one for individuals and one for organizations.
π¨ββοΈ Copilot Pro is designed for individual users. It enhances the free Copilot features, providing better performance and integration with Office for anyone who chooses to subscribe for $20 a month.
π©ββοΈ Copilot for Microsoft 365 is available as an add-on to commercial Microsoft 365 (or Office 365) licenses for a monthly surcharge ($30 per user). It's the most complete feature package, with focus on data security.
There's also Copilot in Power BI, but available only with a Premium Power BI/Fabric subscription.
π Power Stories
Thank you to Marconi for this great feedback on LinkedIn of our Fundamentals of Financial Analysis course.
Congratulations on your new certificate, and wishing you continued success!
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...