Category: Office 365

Did you know … you can use Microsoft Excel to count the number of records within a range?

I’ve been generating reports to track our Microsoft Teams adoption – how many people are using Teams, how many messages are being sent in Teams, how many Teams are there. Some of these metrics have easily visualized count-per-unit-time summaries available. Some, like the number of Teams, do not.

Team Created On
Directory Services 1/19/2017
App Proxy 1/19/2017
LDAP 1/19/2017
ADFS 1/19/2017
Nagios 1/19/2017
File Cluster 1/19/2017
Exchange Online 1/19/2017
Active Directory 1/19/2017
Commvault 1/19/2017

But it’s easy to turn a list of groups and creation dates into visualizable data. Paste the data into Excel. To find the number of items where “Created On” falls in a range, we need to be able to define that range. 01 January 2017 is easy enough, but how do you get the end of January? Excel has a function, EOMONTH, that returns the last day of a month.

Date is any date object. Offset is an integer number of months prior (negative numbers) or after (positive numbers) Date for which you want the last day of the month. I can list the dates to start and end quarters with =EOMonth(Date,2). With 01 January 2017 in cell D2, the last day of January is =EOMonth(D2,0)

 I don’t want to type01 Feb, Mar, April … flash fill and the fill handle need a few values before they can figure out the rest of a sequence. But I can use the last day of the month to get the first day of the next month – just add one! With 31 January 2017 in cell E2, I want =E2 + 1 in cell D3. (Yes, there are other ways to do this – probably dozens.)

Now that we’ve got a formula for the start and end of the month, just fill down to produce the ranges we need to see how many Teams were created each month. Then we just need a formula to do the counting for us. I use the COUNTIFS function.

=COUNTIFS($B$2:$B$1000,”>=”&D2,$B$2:$B$1000,”<=”&E2)

Counts the number of items in the range $B$2:$B$1000 (the cell range is static as the formula is copied elsewhere, hence the

Fill down – you’ll see the range remains static, and the comparison is to the D and E columns on the current row.

Voila – easily visualized data. And a graph 😊

Did you know … Excel can transpose data?

Have you ever gotten data where the column headers are along the left-hand side and each record is a column? I’m sure there are people who prefer each record to be a column too, and they probably hate my data where the headers run along the top and each row is a record. Did you know Excel can swap between these two formats?

Here I have a spreadsheet where each column is a record. Highlight and copy the records. Yes you can use ctrl-c to copy … but there’s no way to make a screen-print of my keyboard actions.

Click elsewhere – below the data you copied, on a new sheet, even a new spreadsheet. Right-click & expand “Paste Special” – find the icon for Transpose and click it.

Look – now each row is a record, just like I wanted. You can even delete the original data if you want – the pasted information will still be there.

Did you know … there are keyboard shortcuts in Microsoft Teams?

I know modern software is driven by graphical user interfaces, but as an old-school Unix admin (there were loads of interface choices – Bourne shell, c shell, korn shell, bash shell!) it’s weird to take my hand away from my keyboard just to turn a bit of text bold or move to a new field. And Microsoft has done a decent job of standardizing keyboard shortcuts across their applications – ctrl-b will toggle “bold” pretty much anywhere (even Teams!)

But … within your Teams client (even the web client) hold the “Ctrl” key and type a full stop (.) and look – special keyboard shortcuts!

There’s even a link at the bottom for all of the shortcuts on Windows and Mac. I can hit ctrl-shift-1 to flip over to my Activity feed; ctrl-shift-3 puts me back in the Teams chat section.

Did you know … there’s version history for Microsoft Teams files too?

There is — which is obvious once you start thinking about how Teams data is stored. The “Files” tab is a pretty front end for a SharePoint document library, and document libraries store version history. The problem is I didn’t know a good way to walk an end user through accessing that document library. I’d generally do a screen sharing session with the user & navigate them to the right place myself. And then I saw this — on the Files tab, there is an “Open in SharePoint” button. You don’t need to drill down to find the specific file you want to revert – as long as you are on the proper channel, we’ll be able to get to the document.

Voila! A new tab opens and shows you the SharePoint document library that underpins the Teams Files tab. Now drill down until you find the file for which you are looking.

Click on the not-quite-a hamburger menu – the one between the file name and modify time.

Select “Version History”

To view the previous version, click on the hyperlinked modify timestamp. To restore the previous version, hover your mouse over the modify timestamp of the iteration you want.

On this menu, “View” will show you some information about the file – not actually view the file. Select restore “Restore” to replace the current version (the one that shows up in Teams) with the selected – you’ll be asked to confirm that you want to overwrite the current version.

Once the document has been restored, you’ll have a new entry on the version history pane – so you can even revert your document reversion if needed.

 

Did you know … Screen Sharing in Teams Chats Is Rolling Out!

I’ve been tracking an RFE for screen sharing in Teams chat — it’s super-simple in Skype, and while it’s possible in Teams (schedule a meeting), it isn’t a one-click simple process. But today, we’ve got a new button in our chat sessions — start sharing your screen!

I don’t see the option in the web client on Firefox or Chrome, but I hope it is coming there too.

Did you know … Excel can change the case of text?

But before we go there, did you know you can have Excel show your formulae instead of data? It’s great for showing how you’ve done something in Excel 😊 On the “Formulas” ribbon bar, click “Show Formulas”.

I get information with all sorts of strange caPitAlizaTioN –often the “I didn’t know I had my caps lock on when I hit caps lock to give you all upper case” lISA RUSHWORTH casing. Or the “I didn’t bother to hit shift so it’s all in lower case” lisa rushworth. And even the “I had caps lock on when I fixed something, but the rest is in lower case” lisa rusHWorth.

It’s not aesthetically pleasing, but some systems have case sensitive sorting – Sally is alphabetically before lisa. Some systems use case sensitive strings to collate – and “lisa rushworth”and “Lisa Rushworth” are two distinct records. And have I mentioned it’s not aesthetically pleasing?

But I’ve got an Excel formula that will create consistently capitalized data.

See:

In addition to using LOWER(), UPPER(), and PROPER(), you can combine these with LEFT() and RIGHT() to convert different parts of the string to different cases – as in my example of setting just the first letter capital.

Left(A14,1) takes the first character from the left of the value in A14. LEN(A14)-1 is the length of the value in A14 minus one.RIGHT(A14(LEN(A14)-1)) then takes one less than the entire string length from the right of A14 (i.e. everything except the first character).

Did you know … you can organize your Microsoft Teams list?

You’ve been using Teams for a while, created a few Teams, been added to even more … and you realize that all of these Teams and channels have created clutter of their own. Teams are listed in the order you joined them. Channels are listed in the order they were created. And those first couple of Teams spaces you played around with? All at the top!

But the Teams interface – both the client and the website – allow you to drag Teams around to reorder them. Simply click and hold over the Teams listing you want to move and drag your mouse – there will be a “ghost” listing that tells you where the Teams listing will appear when you release the mouse button.

OK, that’s a little better – now the Teams spaces for my groups are at the top.

That’s still a lot of channels, though. If you remove ‘favorite’ designation from a channel, it will collapse into an expandable menu. Click the hamburger menu next to the channel, then click “Remove favorite”

That’s a LOT better – those channels are still available, just click on “# more channels”

You can remove the favorite designation from a Teams space as well – it will no longer be so prominently displayed in your Teams list. Click on the hamburger menu next to the Teams space listing and click “Remove favorite”

Down to one page! If you want to check one of the hidden Teams, just click “More”

And if you want to get notifications when an important-but-infrequently-used channel has some activity, click the hamburger menu next to the channel and select “Follow this channel”. New posts will be listed in your Activity feed (and e-mailed to you if you’ve set up e-mail notification for followed channels).

Did you know … you can use markdown in Teams messages?

Teams supports markdown within messages – type something like this

And you actually see this:

You’ll know it’s working because the markdown is converted as you type it (pasting a marked down string into Teams doesn’t work — you’ll have the literal characters and not the implied formatting)

You can even type a backtick (`)

To get an inline code block

You can insert multi-line code blocks as well. It’s a little trickier to get a code block in a message since you’ve got to use shift-enter to move to a new line then type three back ticks.

After you type the three back ticks, your cursor will be in the code block. Enter will now move to a new line instead of sending the post.

They’ve got a bug where you cannot do anything after the multi-line code block … but you can always reply to your post if you’ve got something to add.

As of this writing, you cannot paste markdown text into the message and have the formatting rendered. You can paste content into the multi-line code block composer, but you cannot paste *things* with ~markdown in them~ and see “`
pretty output

Did you know … you can edit the tag for Teams @ mentions?

Well, you can ? Not generally worthwhile if you are only mentioning one or two people, but if you need to flag half a dozen people, the list of names starts getting REALLY long.

Click in the middle of the name – your cursor will be placed within the name element.

Use backspace or delete to remove one of the name components – backspace will remove the surname and comma, delete will remove the given name.

The links are maintained, so the individual gets flagged with the post.

Does it matter if there are three Bob’s on there? In the previous case, no – I’m trying to garner the attention of a bunch of people and direct them all to the same text. But if you have a bunch of people in your Teams group with the same name … it’s probably better to leave their full name in place or delete the given name!

Did you know … you can push GitLab notifications to Microsoft Teams?

Microsoft Teams offers a lot of connectors which allow you to see external notifications right in a Teams channel, and you can push GitLab notifications to Teams. Determine the channel into which you want the notifications posted – I decided to make a new channel just for GitLab notifications, but you can use an existing channel too. Click the hamburger menu next to the channel name and select “Connectors”

Locate “Incoming Webhook” and click “Add” next to it

There’s not much to see here – just click “Install”

Provide a descriptive name for the webhook and click “Create”

Scroll down and copy the webhook URL

Click “Done”. Now go to your GitLab project. On the sidebar, scroll down to “Settings” and select “Integrations”

Scroll down to the “Project services” section.

Locate “Microsoft Teams Notification” and click on it

Check the box to activate the integration. Select the events for which you want information pushed to Teams

Remember that URL we copied? Well, here’s where you paste it in. You can elect to receive notifications for only the default branch or all branches. You can elect to receive all pipeline notifications or only broken pipeline notifications. Click “Test settings and save changes”

If everything worked, you will see a banner indicating that the Microsoft Teams Notification was activated.

Now do something in your GitLab project – commit a code update, create an issue, add a page to the Wiki … anything that you’ve selected to trigger notifications.

And … check out your Teams channel: