Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:

“DataFormat.Error: Invalid cell value ‘#DIV/0!’.” 

You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.

It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.

=IFERROR(Your_Formula, Value_If_Error)

2018-10-10_09-12-39.gif

Speaking at SharePoint Saturday Kansas City this Saturday | Oct 13, 2018

chrome_2018-09-09_11-24-06.png

My local SharePoint Saturday, SPS Kansas City, is coming up THIS WEEKEND (#SPSKC). I’ll be speaking about one of my favorite topics – building a modern intranet:

Building the Intranet of the Future: Using SharePoint to Empower Collaboration

Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint. We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets. We’ll also cover rethinking forms and minimizing busy work by utilizing automation capabilities via workflow and designing intuitive navigation.

Hope to see you there!

MCSA: Office 365 Study Guide for Exams 70-346 & 70-347

Note: The MCSA: O365 Certification and exams 70-346 & 70-347 are being retired March 31, 2019. Learn more about what to expect here.


Having just passed both 70-346 and 70-347, I know how hard it can be to find helpful and relevant resources to help you prepare for the exams. I’ve compiled resources below I used to help me prepare and I hope they help you with your own studying. I’ve included nothing more or less than I used myself. The most important piece for me was the Lynda.com courses – I wouldn’t have passed without them. Good luck!

Required Exams

(check links for most recent requirements)

Books

Videos

Courses

Lynda.com

(free access for card-holders at many libraries, or free trial month for new users)

edX

Courses are free on edX. Just create an account.

Pluralsight

Get a 10-day free trial

Prep Tips from Nate

70-346 Skills Measured

70-347 Skills Measured

These are taken directly from the exam info pages. Clicking a link will take you to the respective section of the page to see specific sub-topics covered under each skill section. I’ve also included the “Preparation resources” provided by Microsoft under each respective section:
Provision Office 365 (15–20%)Plan and implement networking and security in Office 365 (15–20%)

Manage cloud identities (15–20%)

Implement and Manage Identities by Using Azure AD Connect (15–20%)

Implement and manage federated identities for single sign-on (SSO) (15–20%)

Monitor and troubleshoot Office 365 availability and usage (15–20%)

Manage clients and end-user devices (20–25%)

Provision SharePoint Online site collections (20–25%)

Configure Exchange Online and Skype for Business Online for end users (20–25%)

Plan for Exchange Online and Skype for Business Online (20–25%)

Configure and secure Office 365 services (20–25%)

Speaking at SharePoint Saturday Denver this Saturday | Oct 6, 2018

chrome_2018-09-09_11-12-00

I’ll be speaking this Saturday at SPS Denver (aka SPS Colorado) (#SPSColo). I’ll be presenting a brand new, magical session on content management, archiving and retention:

Content Wizardry 2018

SharePoint wizardry for content management, archiving & retention

Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!

Hope to see you there!

How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI

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

Continue reading “How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI”

How to get a free trial of Office 365 for home, business or enterprise

A free trial of Office 365 is a great idea if you’re:

  • Curious how it could help your team and want to do some testing as a group
  • Wanting hands-on experience with tools you’re interested in using
  • Needing to test something out in a safe, no-consequence zone before applying it to your actual environment
  • Studying for exams 70-346 or 70-347 (I recommend a Business Premium trial)
  • Considering changing licenses

Get a free trial today:

Continue reading “How to get a free trial of Office 365 for home, business or enterprise”

Solution: Excel drag to “fill” not working – value is copied, formula ignored

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:
2018-09-25_11-29-39.gif

Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”

What is the Related() function in Power BI?

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:

PBIDesktop_2018-09-25_10-32-08

Continue reading “What is the Related() function in Power BI?”

Change the default link type and sharing settings for SharePoint documents

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”

How to manually start crawls and reindex SharePoint content

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”