Microsoft 365 training and content for modern digital workplaces
Sometimes you want to know how many times words and phrases appear across multiple columns. For example, if we want to know how many times “Cat” OR “Mouse” appears in each row, we would get:
| Column 1 | Column 2 | Column 3 | Word Count |
| Cat Yarn | Mouse | Cat Dog | 3 |
| Dog Cat | Horse Hay | Dog | 1 |
| Cat Cat | Cat | Mouse | 4 |
A free trial of Office 365 is a great idea if you’re:
Get a free trial today:
Continue reading “How to get a free trial of Office 365 for home, business or enterprise”
Note: Video of solution at bottom of post.
A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:
Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”
RELATED() pulls values from different tables based on relationships established in the relationships tab. This is useful and makes it so we don’t necessarily need to append or merge tables to get the same result.
For example, we can create a new calculated column in our fact table Sales that pulls in related information from two other tables without needing to append or merge tables:
Continue reading “What is the Related() function in Power BI?”
By default when a user chooses to share a document, a selection has already been made to share that document with anonymous access, restricted organizational access or only with specific people (requiring entry of recipients) depending on who set up your tenant. If no changes were made, chances are the links default to anonymous access. A user can change this setting to restrict access before sharing but for newer users in a hurry, it’s likely anonymous links could be being created and shared incidentally because time isn’t being taken to review the alternative options.
Here’s how you, as an administrator, can change the default settings to something more secure without (or with) removing anonymous capabilities.
Continue reading “Change the default link type and sharing settings for SharePoint documents”
After you’ve made changes to managed metadata or the search schema you may wish to re-crawl your content sooner than the next scheduled crawl. You can do this across all site collections (on-prem/server), one site at a time (O365 or on-prem) or individual lists and libraries (O365 or on-prem).
Continue reading “How to manually start crawls and reindex SharePoint content”
I just found out that Microsoft Stream now allows users to live stream events (preview). Once an event is finished, the recorded stream includes face detection so users can skip ahead to a certain speaker as well as automatic and searchable closed captions. Whenever you have an opportunity to stop paying for a third party product and use what you get included in your tenant, I think it’s worth giving it a try.
Note: Video tutorial at bottom of post.
Continue reading “Use Microsoft Stream to live stream your events”
Power BI tries to be helpful and auto-detects data formats such as dates, decimal numbers and integers. However if you’re dealing with zip codes, employee IDs, product IDs, etc. that have leading zeroes, you may not want them auto-converted to integers/number format which removes the leading zeroes. Here’s how to get them back.
Now a book!
Based on his 100th blog post, Nate Chamberlain presents “The ABCs of SharePoint: 26 ways SharePoint can enhance your digital workplace”. Can you think of something in SharePoint for every letter of the alphabet? This book will help you fill the gaps teach you more about things you could implement in your own existing SharePoint environments. From Automation to Zone Templates, Nate shares 26 features and topics that set SharePoint apart from any other collaborative platform.
SharePoint Designer & Microsoft Flow allow for endless possibilities in automating business processes.
Iconography is important in digital workplaces. Your users should, on any one page, be able to recognize where they are and what they can do easily by observing navigational elements (breadcrumbs, menus, etc.) and buttons/icons. Instead of simple text links, use buttons/images to give a more polished look that provides for quick visual recognition.
Check out Stacy Deere-Strole’s “Quick Wins: #4 – Promoted Links” to learn of a quick anyone-can-do-it method of adding icons/buttons to your pages using the Promoted Links app. You can also add a hyperlink to any image to create a single “button” experience.
Content Types are essentially templates for lists and libraries. While your list may have 15 columns, content types allow you to show only certain fields conditionally. For example, a person may submit an item with 4 fields and a workflow can change the content type to an “Admin” content type which might “add” two additional fields for approval and comments.
In Document Libraries, content types allow attaching any office file as a template to each content type. This means when a user clicks “New” their options could be “Vacation Request”, “LOA Request”, “Time Off Request”, etc. And each content type could have its own separate workflow process, metadata, views, etc. But they all exist in one library.
Document sets are great for collecting many files that all share the same metadata. For example, when working with projects all documents associated with it would have the same client, vendor, organization, project number, project manager, contact info, etc. The document set works as a sort of folder, and all documents added to it then get the same metadata automatically. You can also set a document set to include certain templates or links by default so that upon creation, a document set already has all the forms, templates and folders it needs.
SharePoint, especially SharePoint Online, allows for embedding all kinds of items in your pages:
Whether a customized PowerApps form or an out-of-the-box SharePoint form, every list and library is able to give users a “form-like” experience for completing metadata. You could, for example, have a list for time off requests and use the default newform.aspx page or you could use SharePoint Designer (server/on-prem) or PowerApps (O365) to create custom new item forms. Each content type (see “C | Content Types” above) can have its own new item form as well.
The following is an example of a custom on-premise or classic form created with SharePoint Designer. See How to make a floating “attachments” block for SharePoint forms to see how it was done step-by-step.
Governance is an essential component of any digital workplace. Governance typically involves a group of individuals with different experience levels, and different departments/business processes. These individuals help guide decisions around appropriate usage and standards for your intranet and its content.
Check out How to create a SharePoint Governance Plan (includes template) for ideas on how your governance committee could get started in creating a more consistent, clean and user-friendly intranet with priority and focus on relevant content and its maintenance.
“[Hub sites] are the ‘connective tissue’ you use when organizing families of team sites and communication sites together.”
Hub sites are relatively new and available in Office 365. Think of them as buckets in which you’ll organize your existing sites, or landing pages for groups of sites. Learn more here.
I would venture to guess that intranets are the number one usage of SharePoint. With all the possibilities in automation, content management and strategy (including retention/archiving), communication and document collaboration it makes it a natural choice for companies needing more than just shared documents. And with Office 365, your intranet is everywhere with you thanks to the SharePoint mobile app and mobile compatibility.
Check out my presentation “Building the Intranet of the Future: Using SharePoint to Empower Collaboration” presented at SPS Omaha in 2018.
Being able to customize sites, pages or just lists with your own javascript and JSON is a must for many. On classic/on-premise sites, you can implement your own javascript and css. Check out this example of using javascript and jquery to create customized, floating headers for SharePoint list.
In the modern experience, you’ll need to learn about SPFx and can use JSON to do things like conditional column formatting.
Knowledge management is just as important as the technology we use to contain it. If you haven’t read Content Strategy for the web, you need to. It’s one of my absolute favorite resources I turn to repeatedly. I was surprised at how ready SharePoint was to incorporate the practices and concepts in this book – it’s a perfect pair.
This book helped me recognize the importance of nurturing good content management practices as well as developing and maintaining efficient content managers. It clearly defines separate content and people concepts and leaves you with many ideas you can begin exploring or implementing right away to improve your organization’s knowledge management, no matter the scale. And it’s a fun read – it’s written with light humor which makes an otherwise ordinary topic an exceptional read.
So if you want better search, less frustrated users, relevant content, effective and active archiving and retention policies, a sense of content ownership and clearly defined roles this book will help you get there.
Lists and libraries are the main, most frequently used, web parts (see W | Web Parts below) in SharePoint.
Lists function like spreadsheets, and can create a sort of “dashboard” where you use list views to display the most important data from each list item/form submission and then you can open the item to see full detail. You can attach documents to list items, but it’s not easy to later pull all those attachments or move them. A library might suit your process better if you’ll have lots of attachments.
Libraries contain documents (of all types) and are basically an upgraded version of lists. The emphasis is placed instead on the actual document to which you can assign metadata just the same. Thanks to co-authoring, workflows, versioning and item-level permissions, libraries in SharePoint are at the forefront of digital collaboration.
Metadata is a love letter to your intranet’s future. By setting up metadata (additional columns in lists and libraries) you’re making it possible to:
Navigation is critical to user-adoption. As mentioned in “B | Buttons & Iconography”, users should be able to recognize where they are, what they can do and where they can go from any page on your intranet. You can use a combination of visuals and menus to achieve this. Also consider placing emphasis on search in SharePoint – it’s perhaps the most efficient way to find what you’re looking for if maintained and set up correctly.
Make sure your menus are consistent. No matter what page a user is on, the navigation should be similar, if not identical, throughout. It’s nice to customize and personalize, but it’s nicer when a user knows just where to look for similar information across different departments.
SharePoint integrates seamlessly with other O365 apps to create a one-stop shop experience. Whether embedding, building workflows or customizing content you’re likely to experience any of the following apps within your SharePoint environment:
In SharePoint you can use item-level permissions, site or object-based permissions or any combination of the two. But of course it’s best practice to minimize the breaking of inheritance and try to manage permissions on a group basis rather than individuals.
For example, at LMH we manage our security groups in AD rather than SharePoint so that when people leave, we don’t need to remember to remove them from multiple SharePoint groups as well since they’re already being deactivated in AD. But find what works best for you and your sysadmins and make sure you’re on the same page in routine management of those groups.
One of my favorite things about SharePoint is how it’s security trimmed so that people won’t even see links to sites or find search results to things they don’t have permission to access. Everyone in an organization could, in theory, have a completely custom and different experience thanks to search, permissions and the office graph.
As mentioned before, search could well be the best way to navigate in SharePoint. But aside from search, you can also use the content search and content query web parts to search for certain content across your collection(s) and display them in a block on any page. For example, maybe you want to aggregate all calendar items from multiple sites into one list on your home page. Or you could link a button to the most recent document uploaded to a library, such as always linking to the current lunch menu.
You can also customize search and build your own search results display template, filters, etc. and limit the scope of a search. For example, I’ve built mini-search pages for video libraries to give departments a YouTube-like experience which only searches their videos and has custom search refinement panel on the left with specific metadata to filter results.
Stay up-to-date on what’s been released, what’s coming soon and what’s in development by watching the SharePoint Roadmap.
There are many free opportunities for professional development near you. SharePoint Saturdays bring in industry experts, including many MVPs, MCTs, etc., to share what they know and love. Learn from the pros, at no cost to you, and take home some new knowledge (and swag).
If you have a bit of a budget, check out Microsoft Ignite, SharePoint Fest, or SPTechCon.
Matt Wade has done a phenomenal job listing many of the upcoming SharePoint events (free SPS events as well as paid registration conferences) on his site. You can subscribe to his calendar or download the listing here. You can see me at these upcoming events.
One of my largest use-cases is project management in SharePoint. People are very interested in task lists that can email you and remind you when things are overdue.
Workflows in SharePoint Designer can assign tasks which are posted to the Workflow Tasks or Tasks list in SharePoint. You can set overdue reminders on these as well in SharePoint Designer that send at a regular interval until completed. Those assigned can also reassign tasks.
Even without workflow, you can have any number of task lists you manage in SharePoint and associate that list with a timeline. It’s an easy way to share with your team what’s going on and who is responsible for what. Pair a list with a timeline or Gantt chart for an added “wow” factor.
Analytics are incredibly important for measuring the success (or lack thereof) of content and communication. It’s also nice to understand popularity trends. Out of the box, SharePoint has some usage and search query reports (site settings –> popularity and search reports) but you’ll want to pair this with Google Analytics or another analytics service to get more robust insights on user trends and behaviors.
By activating versioning, you’re allowing users to see what changes have been made to metadata, compare the current version of an item or document to a previous version, or restore a previous version. You can also allow both major (published) and minor (draft) versioning.
Web parts are the building blocks of SharePoint pages. These can be static or dynamic, for display or for interaction. Depending on whether your page is modern or classic, you have different options:
You might be editing XML and XSL if you’re on-premise or using server and attempting to change a look or layout the old fashioned way. If online/O365 you’ll likely be modifying the SharePoint Framework (SPFx) instead.
If on-premise/server, you may be creating custom layouts using zone templates in SharePoint Designer, or just wanting to learn more about web part zones.
Lookup columns include workflow status columns, traditional lookup columns to other lists, and person/group columns. This includes the two default people fields “Created by” and “Modified by”. If your list has more than 12 of these, you may receive the following error:
“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator.”
In SharePoint Online, you’re not able to increase the lookup column limit. Lists created prior to the June 2013 CU update are capped at 8 lookup columns, while those afterward are allowed 12.
However, on-premise SharePoint (server) allows you to change this limit to your heart’s content.
Continue reading “How to increase lookup column limit in SharePoint lists”
