Filter even or odd rows in Excel

This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.

I say near-duplicate because if your values truly are duplicates, you can just use the Remove Duplicates function.

So to filter odds and evens, we’ll add a new column to the right of our data and set the formula to:

=ISEVEN(ROW())

This evaluates the data’s row number as even or odd and will return TRUE if the number is even or FALSE if not. Double-click the handle in the lower right of the cell to fill down.

Then we filter the column for just TRUE (evens) or FALSE (odds). You can right-click the value you want to keep > Filter > Filter by Selected Cell’s Value

Click to enlarge

Remove duplicate values from an Excel column

A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.

1. Select the column, or the values from which you’re removing duplicates.
Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog.
2. Go to Data > Remove duplicates (in the Data Tools panel)

3. Confirm if your column has a header (column title or not) and click OK.

4. Excel will confirm the number of duplicates found and how many remain. Click OK.

Adjust column widths in classic SharePoint lists using CSS (including “Quick Edit”/datasheet views)

Once upon a time two years ago (two years?!?) I shared how you can adjust SharePoint column widths in traditional views using JQuery.

But adjusting the same widths in datasheet mode (quick edit, for example) is a bit different. After a bit of fiddling around, I found an answer that will allow you to adjust column widths for both standard and datasheet view types using just CSS.

The difference is in how you reference the column names in the css:

  • Standard (catches both filterable and non-filterable columns such as multi-line text): th.ms-vh2-nofilter div[DisplayName=’Column1′],th.ms-vh2 div[DisplayName=’Column1′]{…
  • Datasheet: th[Title=’Column1′] {…

Everything that follows that first line is the same in both types of views, fixing a minimum and regular width property for the column(s).

Standard Views (not quick edit)

<style>
th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Datasheet/Quick Edit Views

<style>
th[Title='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

All Views

So if you’re including just one script reference for all views in your list, you’d be safe to include all formats in the script.

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Multiple columns

For multiple column width adjustments, just include another block for each column as seen here:

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
th[Title='Other Column'],th.ms-vh2-nofilter div[DisplayName='Other Column'],th.ms-vh2 div[DisplayName='Other Column']{
    min-width:200px!important;
    width:200px!important;
}
</style>

Join me at SharePoint Fest Seattle Aug 19-23, 2019

I’m honored to be among many fantastic presenters for SharePoint Fest Seattle. When first learning SharePoint, SharePoint Fest Seattle was the first big conference I attended. I was an administrative associate at the time. I remember attending a workshop on SharePoint Designer workflows by Vlad Catrinescu and thinking it was the coolest thing I’d ever seen.

Each night of the conference, I’d stay up late working on things I’d learned or ideas I’d gotten during the day. After the conference, I dove all in and ended up working full-time in SharePoint. My job transformed into a SharePoint Business Analyst role. Fast forward, now I’m a Microsoft MVP, Microsoft 365 Certified Enterprise Administrator Expert, author, blogger, user group leader, and a SharePoint Systems Engineer at LMH Health.

I’m continually wowed and re-energized by all that’s coming out of Microsoft. And I’m excited to be on the other side of the podium for a few sessions this year, sharing some of my favorite topics. And, as always, I’m looking forward to sitting in the audience as well for what is certainly one of the year’s best professional development opportunities.

Come discover how SharePoint Fest Seattle can expedite your career growth through professional development and inspiration. Register today at https://www.sharepointfest.com/Seattle/ and use code Chamberlain100 to save $100 off your pass.

I’ll be presenting these three sessions, as well as sitting at the “Ask the Experts” booth a couple times. I hope I’ll see you there!

INT102 – 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, rethinking forms and minimizing busy work by utilizing automation capabilities in SPD workflows and Microsoft Flow and designing intuitive navigation.

CM202 – 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!

BV303 – You’re the one, OneNote!

Are you looking for more productivity in your life? Sticky notes leaving you…unsatisfied? 

Sit back and enjoy this journey through the sweetest productivity story ever told, where you’ll be inspired to take “note” of new ways of working more efficiently day-to-day with OneNote. When it comes to working smarter and not harder, OneNote is the one.

Solution: “The embed code is invalid because the source of the embed content is not allowed” error when embedding Microsoft Stream video in SharePoint

A user recently ran into the following error when attempting to embed a Microsoft Stream video on a SharePoint 2016 site:

“The embed code is invalid because the source of the embed content is not allowed.”

To resolve this, you must be a site collection administrator.

Go to site settings and select “HTML Field Security” under “Site Collection Administration.”

By default, SharePoint allows embeds from YouTube, Bing, Vimeo, and Microsoft but we need to add web.microsoftstream.com to the list.

Now try to embed your video again, and it will work.

SharePoint search results showing wrong title

I recently ran into the issue of a document appearing in search results that didn’t use the name field OR the title field. I was perplexed by this until checking the search schema for the “Title” field. In an attempt to be helpful, there’s a property called MetadataExtractorTitle that was given higher preference than the actual title field. To fix this, I simply had to bump it down the list a bit.

Scenario

The document in question is a SharePoint Governance meeting agenda named SP Governance – 2017-11-17.

It appeared correctly in its library, which is to be expected:

And a look at its properties revealed there was no Title value, meaning it would default to the document name.

However, when searching for “SP” I found the document listed as “Agenda.” This was used because the MetadataExtractorProperty found “Agenda” within the document as a potential title (as the first line of the document).

Solution

Note: You must be at least a site collection administrator.

Go to Site Settings at the top level of the site collection for the document library.

Choose “Search Schema” under Site Collection Administration (not just “Schema” under search – that’s only site level)

Search for title and edit the property

Move “MetadataExtractorTitle” down until it’s beneath ows_Title. Click OK when finished.

Click to enlarge

Checking your work

After fixing the schema, go back to the document library and re-index it to check. (Library Settings –> Advanced Settings –> Reindex Document Library)

This will have the library re-crawled during the next incremental crawl (interval depends on administrator settings). Alternatively, you could trigger it immediately or run a full crawl.

Once the crawl has run, try your search again. Your items should now have a correct title when appearing in search results.

Run Google Chrome as a different user to test

In an on-prem environment, it’s convenient to be able to run Chrome as a test user with general permissions instead of my admin permissions. This possibility makes it so I don’t need to remote to another machine or log out and in with another account just for a simple check.

Using Internet Explorer? Here’s how to do the same with that.

If you have a shortcut to Chrome on your desktop (not your task bar), skip ahead to step two.

1. Search “Chrome” from the start menu, right click and select “Open File Location”

2. Hold “Shift” on your keyboard and right-click the Internet Explorer icon. Select “run as different user”

3. Enter the credentials for the second user (your screen/prompt may look different) and click OK/Login. In some cases, you may be prompted to enter these more than once.

Chrome will now run as if the other user is logged in.

You can also use the “Check permissions” feature in SharePoint to see which groups a user belongs to for a site or resource, and which abilities/privileges they have.

Run Internet Explorer (IE) as a different user to test

It’s often helpful in our on-prem environment to be able to run IE as a test user with general permissions instead of my admin permissions. This possibility makes it so I don’t need to remote to another machine or log out and in with another account just for a simple check.

Using Google Chrome? Here’s how to do the same with that.

If you have a shortcut to IE on your desktop (not your task bar), skip ahead to step two.

  1. Search “IE” from the start menu, right click and select “Open File Location”

2. Hold “Shift” on your keyboard and right-click the Internet Explorer icon. Select “run as different user”

3. Enter the credentials for the second user (your screen/prompt may look different) and click OK/Login. In some cases, you may be prompted to enter these more than once.

IE will now run as if the other user is logged in.

You can also use the “Check permissions” feature in SharePoint to see which groups a user belongs to for a site or resource, and which abilities/privileges they have.

Check permissions for an individual or group in SharePoint

If you want to see which groups a user belongs to, or how an individual is granted (or restricted) access to a particular site or resource, use the “Check permissions” button in Site Settings –> Site Permissions (or any advanced permissions page).

SharePoint 2016 example of checking permissions
SharePoint Online example of checking permissions (see specific allowances)

Have you been pwned? Here’s how to update your Microsoft password(s) and upgrade your security

The word ‘pwned’ (pronouned pōn’d) was more present in written works around 1800, likely because of typos due to the proximity of “p” to “o” on keyboards. We know it in a modern context as an actual term, reclaimed by video game communities to mean utterly defeating an opponent (Dude, you got pwned!) or in cyber-security conversations meaning an unwelcome party gained ownership of your credentials through a data breach or hack.

The appearance of ‘pwned’ in written works from 1800-2008. Source: Google Books Ngram Viewer

With data breaches and hacker activity being more prevalent, it’s more important than ever to protect yourself and your information from being pwned.

Have I been pwned?

There are several free ways to find out if you’ve been included in a data breach or credential publishing.

  • HaveIBeenPwned.com lets you enter your email address to see which breaches you were discovered in (if any)
  • CreditKarma.com has identity monitoring for the email address you used to register. It’s a free service, and will tell you if you email address surfaced in any breaches or credential leaks and any associated passwords. Go to Resources –> Identity Monitoring
  • LastPass.com’s Security Score will show you specifically which sites have been compromised so that you can change those specific passwords. It’ll also help you fix the “duplicate password” issue where you use the same password for everything AKA one data breach gives bad actors access to all your stuff.

What’s a secure password?

UseAPassPhrase.com demonstrates that pass phrases (not passwords) are significantly more secure than traditional abc123! type passwords. The site will tell you how long it would take hackers to figure out (crack) your password using bots.

  • The typical password requirements you’ll find might have you make a password like ‘sPlib197!’ which will only take 149 days to crack.
  • While phrases are generally more secure, you have to be careful to select a phrase that is NOT logical in structure. For example, a logical sentence like ‘I love kitties!’ would take 18 days to crack. But illogical collections of words like ‘stopping plots argument received’ would take 467,000 centuries to crack. Throw in a capital letter and a special character and you’re set for a few lifetimes.

How to update your Microsoft passwords

Personal accounts

1. Log in at account.microsoft.com

2. Click on Security

3. Click Change Password and follow prompts

Work or school accounts

Note: Your organization may have a different method for updating your password. This is the general, out-of-the-box update method:

1. Go to portal.office.com/account/

2. Click on “Security & Privacy” then “Password”

3. Follow prompts

Upgrade your security (enable two-factor authentication)

Two-step, or multi-factor, authentication helps guarantee it’s actually you logging into one of your sites. You’ll find this security option on all kinds of services including Google (gmail), Microsoft (outlook), and your banking sites. I highly recommend setting this up on every site you’re able to.

What is it? Basically, when you attempt to login using your credentials as usual (or someone who stole your data is attempting to log in as you), you’ll either use the Microsoft Authenticator App to approve a login from your mobile device or you’ll receive a text code to enter into a prompt to show that not only do you know your credentials, but you acknowledged the login from a phone number or mobile device you registered.

Personal accounts

1. Log in at account.live.com/proofs/manage/additional

2. Select “Set up two-step verification” (learn more about two-step verification)

3. Follow the prompts to device’s number as an additional layer of security

4. Return to the same URL in step 1 and click “Set up identity verification app” which will have you install the Microsoft authenticator app on your phone. When someone attempts to log in using your credentials, you’ll get a push notification to approve or reject the attempt before they’re allowed access. (learn more about using the authenticator app)

5. Follow the prompts to install and activate the app

Work or school accounts

You administrator handles the setup of multi-factor authentication. This article does a great job of explaining how to enable it for your users.