Microsoft 365 training and content for modern digital workplaces
Author: Nate Chamberlain
Nate helps people communicate and collaborate better. He shares solutions and ideas on his blog at http://www.NateChamberlain.com and was awarded Microsoft MVP for Office Apps and Services in recognition of community contributions.
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 of values in Excel for a cell rangeDistinct count of values in Excel for a table column (referenced within the table)Distinct count of values in Excel for a table column (referenced outside the table)
Distinct count goal
Formula 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.
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!
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”
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.
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. 🥳🥂
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)
Open Microsoft Teams and select Calendar from the left
Double-click or edit the meeting for which you’d like to retrieve the meeting ID
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:
Open the People panel
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:
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.
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).
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):
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.
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
Multi-select the files you intend to move or copy, or “select all”
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)
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)
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.
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.
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.
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.
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.
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
Plan and configure network settings for Microsoft Teams
Identify licensing requirements for Microsoft Teams
Plan and configure security and compliance settings for Microsoft Teams
Plan and implement governance and lifecycle management for Microsoft Teams
Configure and manage external and guest users
Configure and manage Microsoft Teams devices
Creating and managing teams
Plan and manage channels
Manage chat and collaboration experiences
Manage apps for Microsoft Teams
Manage meeting experiences
Manage phone numbers
Manage Phone System for Microsoft Teams
Monitor and report on a Microsoft Teams environment
