How to get a distinct count of unique values in Excel

Note: Video demonstration at bottom of post.

In some applications, like Power BI, you can get a distinct count of unique values in a column in just a click or two. But of all the functions in Excel, there isn’t a straightforward “Distinct Count” unfortunately.

There are, however, the following two functions that, when used together, give the desired result:

  • COUNTA – Counts the number of cells in a range that are not empty
  • UNIQUE – Returns the unique values from a range or array

So, we’re listing all the unique values we have in a range or column by using UNIQUE and then nesting it within the COUNTA function to count the number of values returned. Here are three examples:

Distinct count goalFormula example
Distinct count for a range of cells=COUNTA(UNIQUE(E19:E26))
Distinct count for a table column when referenced within the same table=COUNTA(UNIQUE([Project]))
Distinct count for a table column when referenced outside the table=COUNTA(UNIQUE(Table1[Project]))

Be sure to replace the cell range, column name, and/or table and column name in the formulas with your own.

A video demonstration of this formula in use is embedded below.

Give your Microsoft 365 skillset a boost at 365 EduCon & PWR EduCon Chicago Sep 26-30

I am so excited to be presenting two workshops and three general sessions at the upcoming 365 EduCon and PWR EduCon conferences happening simultaneously in Chicago September 26-30.

  • WRK109 – Unboxing Microsoft 365: Make the most of your productivity toolbox
  • WRK502 – Build the intranet of the future using SharePoint Online
  • OFF103 – Goodbye, TwoNote: A OneNote refresher
  • PWR203 – How (and when) to use calculated and lookup column types in lists
  • OFF303 – How well do you know the Office desktop apps? 70 min of tips & ideas to wow your co-workers

I’ve included the details for my workshops (including video previews) and sessions in the rest of this post. I hope to see you there!

👉 Learn more and register at https://365educon.com/Chicago

💵 Use code CHAMBERLAIN to save $100


Workshops

I’ll be presenting two workshops at 365 EduCon Monday-Tuesday. Full details and previews below.

WRK109 | Unboxing Microsoft 365: Make the most of your productivity toolbox

  • Full day | Mon, Sep 26 from 9a-5p | Room W184A
  • Audience: Introductory/Beginner or Evaluators

Join Microsoft MVP and certified trainer Nate Chamberlain in this full day workshop for newer Microsoft 365 users or evaluators looking to better understand all the possibilities Microsoft 365 brings to your organization.

Attendees will learn about all the apps and latest features in Microsoft 365 that enable both individual and group productivity including SharePoint, Office 365 apps, Outlook, OneDrive, To Do, Teams, Lists, Planner, Viva, and more.

We’ll go beyond definitions to explore specific business use cases, tips, and FAQs such as:
• Microsoft 365 overview: What’s in my toolbox?
• Understand the relationships between apps, and how permissions and Microsoft 365 groups power your modern work
• Co-authoring documents, version history
• Requesting files from others, sharing files, and managing access in OneDrive and SharePoint
• Teams vs SharePoint vs OneDrive
• Teams conversations vs chats vs emails
• How do the newer Viva apps fit in?
& more!

Workshop preview for “Unboxing Microsoft 365”

WRK502 | Build the intranet of the future using SharePoint Online

  • Half day | Tue, Sep 27 from 9a-12:30p | Room W181C
  • Audience: Introductory/Beginner or Evaluators

Intranets built in SharePoint Online are running on the galaxy’s best platform for enterprise communication and collaboration.

In this half-day, beginner-friendly workshop, we’ll learn about SharePoint but with an intranet focus. We’ll cover tips and ideas for planning and structuring an intranet, best practices for facilitating internal communication and collaboration, how Teams and Viva Connections play into things, digitizing forms, and using the Power Platform to create, automate, and visualize throughout SharePoint.

Let’s create an intranet!

Workshop preview for “Build the intranet of the future”

👉 Register at https://365educon.com/Chicago

💵 Use code CHAMBERLAIN to save $100


70-minute technical sessions

I’ll also be presenting three general sessions Wednesday-Friday. Details below.

OFF103 | Goodbye, TwoNote: A OneNote refresher

  • 70 mins | Wed, Sep 28 from 1:40p-2:50p | Room W185BC
  • Audience: Introductory/Beginner or Evaluators

In 2022, OneNote moves from two separate apps (Win 10 and desktop versions) to a single version, based on the desktop version (i.e. OneNote 2016).

In this session we’ll reintroduce ourselves to OneNote, learn some exciting tips and tricks along with business use ideas, and discuss what’s new and what’s coming for OneNote.


PWR203 | How (and when) to use calculated and lookup column types in lists

  • 70 mins | Thu, Sep 29 from 2:00p-3:10p | Room W185D
  • Audience: Introductory/Beginner or Evaluators

Whether you’re using lists in Microsoft Lists or in SharePoint, you may have noticed that not all column types are shown as options when adding columns via the modern UI. Calculated columns and lookup columns are quite impressive and powerful but they take a couple extra clicks to configure.

In this session, we’ll explore both column types, when and how to use them, and you’ll leave with a reference guide including many formulas across several categories to get you started.


OFF303 | How well do you know the Office desktop apps? 70 min of tips & ideas to wow your co-workers

  • 70 mins | Fri, Sep 30 from 12:30p-1:40p | Room W185BC
  • Audience: Introductory/Beginner or Evaluators

There’s more to Word, Excel, PowerPoint, Outlook, and OneNote than what immediately meets the eye.

In this session, we’re not learning the basics – we’re learning the lesser-known gems and useful tools tucked away inside these common apps. We’ll cover topics including image background removal, small app settings that make a big difference, cross-app integrations, illustrations vs images, Rehearse with Coach, and much, much more.

Note: This will be based on the Microsoft 365 version (not 2019 or earlier) of the Office desktop apps. While there will be some cross-over, there are features that will be covered that are exclusive to the most up-to-date version.

👉 Register at https://365educon.com/Chicago

💵 Use code CHAMBERLAIN to save $100

Celebrating four years as a Microsoft MVP in Microsoft 365 apps and services

This is the fourth consecutive year I’ve been awarded as a Microsoft MVP in the Microsoft 365 Apps & Services category. 🤩

What does that mean?

Microsoft presents the MVP award annually to individuals who have regularly made high-quality contributions to the tech community. Sometimes this is forum participation, conference and user group presentations, books, blogs, videos, code and solutions, social media, or any number of other ways a person has evangelized and helped drive adoption and understanding of Microsoft 365.

I focus my contributions on Microsoft 365 productivity and collaboration apps like OneDrive, SharePoint, Teams, Power Automate, Power BI, and more. Specifically:

And all of that’s in addition to my regular, full-time day job.

To become an MVP, start tracking your contributions and reach out to an existing MVP or Microsoft employee that you’ve established a relationship with to secure a nomination. If we’ve crossed paths or worked together, I would be honored to nominate you.

It has been a true pleasure and privilege to be a part of this amazing community for four years now. Here’s to the next year of content, experiences, and connections. 🥳🥂

Where to find your Microsoft Teams meeting ID and passcode

You may have noticed your Microsoft Teams meeting invites now include meeting IDs and passcodes, and there’s a new Join with an ID button on Calendar. In this blog post I’ll cover several meeting ID-related topics for meeting organizers:

  • Find your Teams meeting ID and passcode
  • Find your Teams meeting ID and passcode during a meeting
  • Add someone to a Teams meeting already in progress
  • Microsoft Teams meeting IDs vs join links

In a rush? Here’s the quick answer:

  • Not in a meeting yet? Open the event on your calendar and you’ll find the meeting ID and passcode in the event details
  • In a meeting already? Go to More | Meeting info

Find your Teams meeting ID and passcode (when not already in the meeting)

  1. Open Microsoft Teams and select Calendar from the left
  2. Double-click or edit the meeting for which you’d like to retrieve the meeting ID
  3. Copy the Meeting ID and Passcode from the meeting details

Note that you can also get the join link from the same section if you prefer. The contents you see here as the meeting organizer (join link, ID, passcode, and call-in info when relevant) is the same contents any invited participants received. They can find this on their calendar by opening the event and viewing its details.

Location of meeting ID and passcode in meeting details (click to enlarge)

Find your Teams meeting ID and passcode during a meeting

To find your Teams meeting join link, meeting ID, and passcode during a meeting, select More | Meeting info.

More | Meeting info in a Teams meeting (click to enlarge)

From here, you can scroll to the bottom for the join link, meeting ID, and passcode.

The location of your meeting’s link, ID, and passcode during a meeting (click to enlarge)

You can select Copy join info at the top of the same panel which resembles the following and includes both entry options as well as call-in info when you’re licensed appropriately. This might be handy to paste in a quick Teams message or email for last-minute requests:

An example of the contents copied to your clipboard when you click Copy join info (click to enlarge)

Add someone to a Teams meeting already in progress

Now let’s imagine you’re in the middle of a Microsoft Teams meeting, and you’d like someone to join you. If they’re in your organization’s directory, you can simply:

  1. Open the People panel
  2. Type in their name and select Request to join next to it when it appears

However, you can’t invite external participants as easily because no matches show up.

No matches found when searching for an external person during a meeting (click to enlarge)

So for external people, use the method described at the beginning of this post (More | Meeting info) and send the join link, meeting ID, and passcode to the individual you’re asking to join. If the link doesn’t work for them and they want to use the ID to join, they’ll go to Calendar in Microsoft Teams, select Join with an ID and enter the info you provided.

A user’s screen when entering a meeting ID and password (click to enlarge)

If they don’t have a Microsoft Teams account to utilize Calendar, there will also be a public join webpage soon where they can enter the meeting ID and passcode and join.

Microsoft Teams meeting IDs vs join links

When it comes to secure meetings, links and IDs are about the same. If someone forwards the meeting invite to a stranger, they can attempt to join the meeting. If you’re concerned about this, perhaps because you’re sharing NDA content in a meeting or just don’t want strangers hopping in, be sure to utilize the “People I Invite” lobby settings so you can decline any unfamiliar or uninvited persons attempting to enter.

Join links specifically are perfectly fine to continue using and they are very difficult for a bad agent (with the intention of crashing your meeting) to guess. Imagine trying to guess something this long and complex, even with bot assistance:

https://teams.microsoft.com/l/meetup-join/19:meeting_MGZlO23U4YjItOTE2MS00ZTSkLWIyMGUtNGRiMWRiYjc4ZjU2@thread.v2/0?context=%7B%22Tid%22:%225a447937-4988-4622-ab53-g1e384ec3c1a%22,%22Oid%22:%22c3db5526-5334-4d0a-ba50-bece903d370%22%7D

Join links are quick and easy for invitees to use. Click the link and you’re at the pre-join screen making sure your devices are setup properly.

Meeting IDs also take you directly to the pre-join screen, and may be a better option if someone is having difficulty using the link you provided. Perhaps their firewall is preventing the link from working or there are other restrictions on their machine that don’t allow them to utilize links normally. Meeting IDs give them an alternative entry method that isn’t dependent on an https:// url.

Why is there a passcode? Without a passcode, a stranger could simply start guessing meeting IDs (which are much easier to guess than the long, complicated join links) and they could pay you and your colleagues an unwanted visit.

Best practice would be to send people both options so they have a backup method if needed. Your meeting invites automatically include both already, and if you’re in a meeting attempting to add someone who wasn’t originally invited, you can go to More | Meeting info and select Copy join info which will copy both options to your clipboard for those last-minute invites via messages or emails.

Power Automate solution: OneNote action errors involving invalid or inaccessible Notebook Keys and sections

When using OneNote (Business) in a Power Automate flow, you may be attempting actions such as Create section in a notebook, Get sections in notebook, or Create page in a section but getting errors when trying to select the relevant notebook and section.

And depending on what you’re using as the Notebook Key and/or Notebook section value(s) you may get any of the following specific errors:

  • Error; the requested notebook or section may have been deleted or is otherwise inaccessible.
  • Invalid notebook key
  • The specified resource ID does not exist.
  • The section id is invalid. If a custom value was entered, please try selecting from the supplied values.

I’ll show you how you may be able to solve this issue in this blog post by using a custom value for Notebook Key. To insert a custom value for Notebook Key, use the dropdown arrow in the Notebook Key field and select Enter custom value.

Enter custom value option for Notebook Key (click to enlarge)

Now you can type text freely. You’ll need to format your notebook key one of two ways, depending on whether it’s a personal (OneDrive for Business) notebook or a shared (SharePoint/Teams) notebook. Both solutions are below.

Solution #1: OneNote Notebook key API format for your own notebooks (stored in OneDrive for Business)

For OneDrive notebooks, such as the default one you get like Nate @ Contoso, format your notebook key as seen below, replacing highlighted parts with your own notebook name, organization URL, and email address (with underscores instead of the usual symbols).

Nate @ Contoso|$|https://contoso-my.sharepoint.com/personal/nchamberlain_contoso_com/Documents/Nate @ Contoso

Still not working? Your organization may have a .com added (even if you don’t see it in your notebook name). Try adding .com to your notebook name in both locations (beginning and end):

Nate @ Contoso.com|$|https://contoso-my.sharepoint.com/personal/nchamberlain_contoso_com/Documents/Nate @ Contoso.com

Solution #2: OneNote Notebook key API format for shared notebooks (stored in SharePoint and used there or in Microsoft Teams)

For shared notebooks, such as the default one you get with every Microsoft Teams team or SharePoint team site, format the notebook key as follows. Be sure to replace bold components of the key with your own notebook name, organization URL, and notebook location:

Notebook Name|$|https://COMPANY.sharepoint.com/sites/SITENAME/NOTEBOOK LOCATION/NOTEBOOK NAME

For example, all default notebooks are stored in a SharePoint site’s Site Assets folder so a complete Notebook key for a notebook like that may resemble the following (yes, you can leave the spaces in notebook names):

Mark 8 Project Team Notebook|$|https://contoso.sharepoint.com/sites/Mark8ProjectTeam/SiteAssets/Mark 8 Project Team Notebook

Or if it’s not the default notebook, and it was created in a document library a couple folders deep, it might resemble the following. Just replace Shared Documents with the name of the library, and replace the folder structure as appropriate:

Policies and Procedures|$|https://contoso.sharepoint.com/sites/Compliance/Shared Documents/Folder 1/Folder 2/Policies and Procedures 

As long as you enter the key correctly using either solution, your flow will connect to the notebook properly and, when relevant, the Notebook section dropdown will refresh and allow you to simply select the section you want rather than entering an API URL.

Notebook section dropdown functioning properly with a correct Notebook Key (click to enlarge)

Additional troubleshooting when sections are still not appearing

If you are certain you followed the instructions above correctly, made no typos, and you used the correct type of key format based on the notebook’s location (OneDrive or SharePoint), and you’re still seeing “Could not retrieve values…” for Notebook section, it might be a simple fix.

Just cut (Ctrl+X) and paste (Ctrl+V) the Notebook Key you entered into the field again and it may refresh and fix the second dropdown.

References

OneNote (Business) – Connectors | Microsoft Docs

How to move all OneDrive for Business files to a shared location

Sometimes people need to move or copy many files at once from their OneDrive to a shared folder or location. This is sometimes simply for reorganizing and sharing content more efficiently. Other times, it’s because a user learns that OneDrive is tied to individual employees so when they leave, their OneDrive will be gone as well. What files do you currently have in your OneDrive that others would need if you left the company?

Does your team use Microsoft Teams regularly? Every team in Microsoft Teams has a SharePoint site that supports it – so moving files to a site connected to a team makes those files more easily accessible via Teams as well.

If you need to perform a bulk move like this, you can individually select what you want to move or select all files in your OneDrive for Business and move or copy them to a shared location, such as your team’s SharePoint site. The steps are listed below, or a video demonstration is available at the bottom of the post.

How to move all your files from OneDrive to SharePoint

  1. Multi-select the files you intend to move or copy, or “select all”
  2. Select Move to (or Copy to if you wish to keep a version of your own)
Select all files | Move to actions (click to enlarge)
  1. Find the site/team under Quick Access to which you’re moving files (you may need to click More places…)
    • You can create a new folder (as seen in video at bottom of post) if there’s not a great existing option for keeping them organized
    • Each site/team may have multiple libraries – use the library switcher next to the site name at the top to choose a different library
Document library switcher when moving or copying files (click to enlarge)
  1. Select Move here and wait for the move to complete before closing your browser

In this video, I’ll show you how to move all OneDrive for Business files to a document library on a team-connected SharePoint site.

Imagine Cup and Imagine Cup Junior 2022 winners

I was honored to be a judge again this year for one of my favorite community initiatives: Imagine Cup Junior. Imagine Cup Junior is a technology-centered competition hosted by Microsoft for teams of 1-6 students ages 13-18. Judges this year were asked to evaluate teams’ contest submissions using a standardized rubric for the theme, AI for Good. I am so very inspired by the ingenuity and heart behind the solutions presented.

Check out the top 10 winning teams for Imagine Cup Junior 2022 and their innovative, AI for Good solutions that earned them a top acknowledgement.

Many who compete in Imagine Cup Junior later compete in the main Imagine Cup competition (teams of 1-4, for ages 16+). You can learn more about Imagine Cup here and watch this year’s World Championship ceremony that first aired during Microsoft Build earlier this year.

Congratulations to this year’s winner, V Bionic, and to all teams for their creativity and passion for making the world a better place for all.

Cloud Conversations: MS-700 Managing Microsoft Teams Exam Guide (Recording)

I had the honor of joining the incredible Peter Rising and Azure McFarlane for a recent episode of Cloud Conversations to discuss, among many things, the recent release of our MS-700 exam guide’s second edition. Successfully passing the MS-700 exam will earn you the Microsoft Teams Administrator Associate certification.

Check out the recording here or embedded below. And be sure to connect with Cloud Conversations socially:

The second edition of our popular MS-700 exam guide is available to buy now:

How to build automated approval processes utilizing Power Automate with Microsoft Teams (Recording from MN365 Spring Workshop Day 2022)

Last month I participated in my very first Minnesota Microsoft 365 User Group Workshop Day and had a wonderful time. Thank you to the organizers for all their hard work, and to everyone who attended and engaged during my session. And for those who missed it, you can now find the description and recording below. 🥳

How to build automated approval processes utilizing Power Automate with Microsoft Teams

We’ve all seen approval processes in a single organization built and implemented in a variety of ways. Sometimes, it’s a signature on a paper form. Other times it’s an email, a thumbs-up on a message, a column change in SharePoint, etc. So how can we standardize these approval processes using Microsoft Teams?

In this session, we’ll do a deep exploration of Approvals powered by Power Automate and used with Forms, SharePoint, Outlook, and Microsoft Teams. You’ll see how to create custom, shared approval request forms your whole team can use, as well as email-only approval processes for those simpler needs. We’ll also cover some best practices for rolling out new processes, training users, and understanding approval history and management.

You can watch the recording here (90 mins) or embedded below:

Also be sure to connect with the Minnesota Microsoft 365 User Group socially:

And thanks to MN365 for the speaker gift! 🤩

MS-700 (Managing Microsoft Teams) exam guide SECOND EDITION (May 2022) now available with 168 practice questions

Last year, fellow MVP and MCT Peter Rising and I collaborated on the first edition of an MS-700 exam guide. I’m pleased to share that our second edition reflecting the most up-to-date exam objectives and content is now available. It has over 400 pages and 168 practice questions, making it easy to find and study what’s most important when you need it.

Book cover for our MS-700 exam guide.

You can order our 2nd ed MS-700 exam guide here.

Successfully passing the MS-700 exam earns you the Microsoft 365 Certified: Teams Administrator Associate certification. This is an excellent way to prove skills and proficiency to employers (current and prospective) and also challenge yourself with keeping up-to-date on administering one of Microsoft 365’s core collaboration and communication apps.

I am super appreciative of my co-author, Peter Rising, as well as our reviewers Dan Rey, Yves Habersaat, and Vivek Vinod Sharma who helped make sure this exam guide is the best available. I also want to thank our reviewers from the first edition, Linus Cansby, Adam Deltinger, Amanda Sterner, and Mike Swantek for their foundational contribution that helped us get this far.

And lastly, thank you to all of the amazingly talented staff at Packt who welcomed us to this opportunity and worked with us to create and refine something we are so proud to share with the community.

MS-700 exam guide (2nd ed) table of contents

  1. Plan and configure network settings for Microsoft Teams
  2. Identify licensing requirements for Microsoft Teams
  3. Plan and configure security and compliance settings for Microsoft Teams
  4. Plan and implement governance and lifecycle management for Microsoft Teams
  5. Configure and manage external and guest users
  6. Configure and manage Microsoft Teams devices
  7. Creating and managing teams
  8. Plan and manage channels
  9. Manage chat and collaboration experiences
  10. Manage apps for Microsoft Teams
  11. Manage meeting experiences
  12. Manage phone numbers
  13. Manage Phone System for Microsoft Teams
  14. Monitor and report on a Microsoft Teams environment
  15. Troubleshoot audio, video, and client issues
  16. Mock exam
  17. Mock exam answers and explanations
  18. Answers (and explanations) to practice questions

Order our MS-700 exam guide here.