Tag: Microsoft Office

Did you know … you can perform an “unmatched query” in Excel?

I mentioned yesterday that we’re creating groups based on the upper level manager through whom individuals report. Since my groups are based on the upper level managers, I need to be able to identify when a new individual pops into the list of upper level managers. Real upper level management doesn’t change frequently, but unfilled positions create gaps in the reporting structure. I call the manager before the gap the highest-ranking person in that vertical and that individual’s reporting subtree becomes a group.

Determining if values from one list appear in another list is easy in Microsoft Access – it’s an unmatched query. I’d rather not have to switch between the two programs, and I was certain an Excel formula could do the same thing. It can!

The formula is:

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

And it does flag any manager from column H that does not appear in my list of upper level managers.

I am also able to filter my spreadsheet to display only records where the upper level manager does not appear in my summary table.

What is my formula doing? It is a combination of three functions

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

It starts with the IF function – a logical comparison – which is used as if(Test,ResultIfTestIsTrue, ResultIfTestIsFalse).

If the test is true, “Not in Manager Summary” will be put into the cell. If the test is false, nothing (“”) will be put into the cell.

The test itself is two functions. I’ve documented the VLOOKUP function previously, but briefly it searches a range of data for a specific value. If the value is found, it returns something. If the value isn’t found, it returns N/A.

In conjunction with the VLOOKUP, I am using the ISNA function. This function is a logic test – it returns TRUE when the value is N/A and FALSE otherwise.

So my formula says “Look for the value of cell H2 in column A of the SOA6MgrSummary tab. If the result is N/A, put ‘Not in Manager Summary’ in this cell, otherwise leave this cell empty”.

 

Did you know … Excel can create a sum of values where a condition is true?

For a project, we need to divide the entire company into groups. I chose organizational structure because it’s easy – I can determine the reporting structure for any employee or contractor, and I can roll people into groups under which ever level of manager I want.

The point of making groups, though, is to have close to the same number of people in each group. While I can use COUNTIFS to count the number of people who report up through each manager, I need to add those totals for each group of managers to determine how many individuals fall in each group. How many employees are included in Group 0?

This is actually quite easy – just like count has a conditional counterpart, countifs, sum has a conditional counterpart sumifs

The usage is =SUMIFS( Range Of Data To Sum, Range Of Data Where Criterion Needs To Match, Criterion That Needs To Match)

You can use multiple criteria ranges and corresponding criteria in your conditional sum — =SUMIFS(SumRange,CriterionRange1,CriterionMatch1,CriterionRange2,CriterionMatch2,…,CriterionRangeN,CriterionMatchN).

I only have one condition, so with a quick listing of the groups, I can add a column that tells me how many individuals are included in each group.

Bonus did you know – instead of specifying a start and end cell for a range, you can use the entire column. Instead of saying my “Range of data to sum” is B2:B101, I just used B:B to select the entire “B” column.

Viewing the values, I can see that my group size is not consistent.

As I adjust the group to which the manager is assigned, these sums are updated in real-time.

 

Did you know … you decide what appears in your Teams activity feed?

Have you noticed that some new Teams messages show up in your activity feed and others do not? The Teams activity feed is meant to highlight Team messages that are important to you. How does Teams know what is important to you? It doesn’t … Teams needs you to tell it what is important to you.

Normally, Teams conversation activity doesn’t appear in your activity feed. When my test account posts a message into the “General” channel …

I see the channel is bolded, indicating new activity; but I do not have any indicators on my Teams or Activity tiles.

What is included in the Activity feed?

Messages to which you’ve replied:  When you reply in a thread, your reply tells Teams that the thread is important to you (just liking a post doesn’t count … you’ve got to actually reply. There are enhancement requests to include some provision for following a thread without replying and muting notifications on a thread to which you’ve replied).

The test account added a new reply after I’d posted my reply. In addition to seeing the channel name in bold, my Activity tile indicates there is one unread message.

Followed Channels: If you want to receive notifications for all threads posted to a channel, follow the channel. Click the ellipsis next to the channel name and select “Follow this channel”.

Now when the test account posts a new thread …

I have an indicator on my Activity tile.

Team At-Mentions: You’ll also see an activity item when the Team is at-mentioned in a post (that’s why at-mentioning the Team is a really effective way to bring attention to your post).

In addition to the activity indicator, I also see an “@” symbol on the Teams tile indicating that a Team has been at-mentioned. And a little number next to the channel that tells you how many unread Team or Channel mentions are in the channel.

Some Channel At-Mentions: What is the difference between at-mentioning a Team and at-mentioning a Channel? When you at-mention a Channel, only people who have favorited the channel will see activity alerts.

Usage Tip: If you break your Team up into channels that aren’t deeply interconnected, individuals can favorite and follow the channels where they are actively involved and check other channels when they’ve got some time. You can at-mention the channel for important messages without overwhelming the whole team with alerts.

While the channel is marked as bold to indicate unread messages, I don’t get any notification about the at-mention. How do you mark a channel as a favorite? Just click that star to the right of the channel name (favorite channels also appear in the initial channel list so you don’t have to expand “X more channels” to see them).

Now a message which at-mentions the channel …

alerts me. A lot 😊

 

Did you know … you can edit and delete Teams messages?

Typos happen – especially in quick, “instant messaging” type communication tools like Microsoft Teams. There is a spell checker to identify mistakes that aren’t words.

But spell check doesn’t catch everything.

Beyond mistyped characters, my thread would be nicer if I had included a subject! And even if my message was perfect when posted, processes change and information becomes incorrect. Because Teams conversations are persistent, out-of-date instructions are going to turn up in search results. Instead of creating a new thread with the updated instructions, I return to the old post and edit it. Changed posts do move to the bottom of the channel (and create an activity alert for individuals who follow the channel). In private chats, edited messages do not appear as new activity, so I’ll copy my updated message and post it into the chat again.

How do you edit a message? In either the chat or channel conversation, click on the ellipses in the upper right-hand corner of your message (these controls only appear when your mouse is over the message block).

Select “Edit”

Your message is open in the basic editor. To add a subject, click the “Format” button to open the expanded editor.

Edit your message – add a subject, correct typos or update content. When you are done, click the check-mark to save your changes. Or if you no longer wish to edit the message, click the “X” to cancel.

And, yes, people will be able to see that you edited the message. They won’t see what you edited … but the phrase “Edited” will appear right next to the message timestamp. What about messages that no longer apply? Put in the wrong place? You can a message them to explain why it is no longer valid. Or you can delete it. To delete a message, click the ellipsis in the upper right-hand corner of your message and select “Delete”.

While there is no “are you sure?” prompt, you can undo the deletion. Note that the “This message has been deleted” banner remains in the thread. So until there’s better delineation between ‘reply’ and ‘start a new thread’, everyone is going to know you replied in the wrong spot 😊

What about someone else’s message? You can only edit messages you have posted. In private chats, only the person who sent the message can delete it. In channel discussion, Team owners can delete any message (including messages posted by a bot or connector).

 

Teams Debugging

The Teams desktop client is an Electron application – which means you can debug the Teams client just like any other Electron application. To set up debugging through Chrome, open Chrome to chrome://inspect Configure network targets. Click “Configure”

Add an unused port to be used for Teams debugging.

Run Teams with the remote debugging flag port set to the same port you added above. E.g.

%userprofile%\AppData\Local\Microsoft\Teams\current\Teams.exe –remote-debugging-port=51555

Now you’ve got access to the Electron app in Chrome. Click on “Inspect” for the thread you want to watch

You can add break-points in the code to pause program execution.

You’ll see a “Paused in debugger” indicator when a breakpoint is reached. You can resume or step over using this indicator, or you can use the DevTools debugger – the DevTools debugger also allows you to proceed one step at a time through program execution (F9).

Through the debugger, I was able to identify the source of the weak little notification ding

https://statics.teams.microsoft.com/hashedassets/audio/Teams_Notification_Secondary-a8621153.mp3

Which means I can redirect this URL … really anything that matches the URL up through Teams_Notification because I expect the alpha-numeric at the end to change and I’m sure there’s a primary notification 😊 … and make the notification noticeable.

Looking through the package, I see eighteen different ‘ringtone’ type MP3 files, and only three lines of code that use them. Hopefully this is an indicator that MS has begun development of some user-selectable notification sounds in the desktop client.

nonMeetupRingAudio = new Audio(data.assetsPath + ‘audio/ring.mp3’);

meetupRingAudio = new Audio(data.assetsPath + ‘audio/meetup_ring.mp3’);

screenshareRingAudio = new Audio(data.assetsPath + ‘audio/screenshare_ring.mp3’);

 

Accessing MS Teams Log Files

There are two sets of log files that we can use to troubleshoot Microsoft Teams issues.

Debug Logs On Windows, these are accessed by holding CTRL + ALT + SHIFT + 1 … on OSX holding Option + Command + Shift + 1 — both the desktop and web client will download three files. In the web client, you’ve got to tell it to save each file individually. The desktop client automatically stashes the files in your downloads folder (sorry, OSX folks, not a clue where your files are!).

Bootstrap log: Teams desktop also has bootstrap logs at %appdata%\Microsoft\Teams\logs.txt This file is generally useful for launch failures, authentication failures, and issues where the app is restarting. Once Teams is started, only background authentication ‘stuff’ is logged here.

If you’re inclined to read them, the debug client log is JSON formatted text followed by lines with timestamp, message level, and the message. War (Warning), Err (Error), Inf (Informational) messages appear in the log. When my Internet connection goes flaky, I get “War” messages with timeouts. But I’ve also seen really strange errors about the back-end Skype call not being found (AFAIK, Skype and Teams share a back-end calling infrastructure. The Skype back-end was upgraded for the Teams launch, but it’s a shared resource).

I pull the log file into Excel and split it into columns with the timestamp, severity, and log data. You can use

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

to convert an ISO8601 timestamp into a value on which Excel can perform calculations. You can also just reformat it by replacing ‘T’ with a space and removing the ‘Z’ https://assets2.jiveon.com/core/2016.3.10.4.179277c/images/emoticons/happy.png

For some activities, you can isolate the end-to-end transaction. This means you can also calculate how long the transaction took. At-mentions are great because they’ve got an obvious start (search text entered … length 0 means just the @ symbol was used. You’ll see different lengths depending on what the user actually types) and an obvious end (dropdown is shown for X search results). There’s also a single remote call (calling atMentionsService.SearchForUserPrifileInChannel) and response (scope.processSearchResults) where you can determine delay introduced outside the local computer.

When performing calculations in Excel with DateTime objects, the result is in unit days. To display the results in seconds, multiply this by 86400 (number of seconds in a day, which anyone who ever administered Bind zone files will be able to tell you off the top of their head … otherwise 24 * 60 * 60)

In the at-mention above, it took a little over half a second to complete and all of that time was the network call.

 

Did you know … there’s a Teams bot to create a quick Forms survey?

Microsoft Forms offers a nice mechanism for collecting survey results, and I often post Forms links to my Teams chats and meetings. But that means I’ve planned for the survey – it takes some time to build the survey, after all! For ad hoc surveys, I’ve been using a third-party Teams app, Polly. Unfortunately, Polly isn’t an approved place for storing company information … so while I’m happy to ask where people want to get lunch or if anyone needs a quick break, I don’t want to ask questions that contain company proprietary information.

Forms has a Teams bot that quickly creates a quick one-question survey. You’ll need to have Forms installed in your Teams space. Click on the “Store” icon, search for Forms, and select Microsoft’s Forms.

Select the name of the Team to which you want to add Forms and click “Install”.

Click “Setup” next to “Bot” to add the forms bot to your Teams space.

Now you can at-mention Forms and create a quick one-question survey. ** This works fine if you are using the Teams desktop client. In the Teams web client, adding the question removes the at-mention link (the @Forms text changes from purple to black). To create quick forms in the web client, I have to type the question/answer bit first, then hit ‘home’ to get to the front of the message and add @Forms **

Help will be displayed to remind you of the question/answer format.

Type your question and answers and send the message.

Forms will create a new post with your survey.

Survey results will be updated in real-time in the thread.

If you want to view detailed results (or export the results to Excel), visit https://forms.office.com The Forms bot creates a “Group form”, so you’ll need to select the “Group forms” tab. Click on the Teams space where you posted the form.

You’ll see the form – they’re readily identifiable because the form name starts with “<at>Forms</at>” followed by the question you posted. Select the form and you can view response details and open the response results in Excel.

One oddity – if you host a meeting in the Teams space, you can at-mention Forms to create a survey in the meeting chat. The response from the Bot – where people vote – does not appear in the meeting chat because the bot response is a new thread.

Team members will find the survey as a new thread in the channel.

This is a little confusing to me, so I just send the message to create the survey in the channel instead of using the meeting chat. Using the meeting chat would, however, associate the survey with the meeting because the message which prompted the form creation will appear in the meeting chat.

 

Did you know … you can use emojis in Teams channel names?

Beyond being fun, emojis are great for quickly finding something – instead of reading all of the channel names, I can quickly scan to find the video icon or little balloon.

To add a new channel, click the ellipsis next to the Team name and select “Add channel”

In the channel name field, hold the Windows key on your keyboard and type a full stop (period, dot, “.”). Now you’ll have an emoji keyboard.

Select an emoji and type some text.

Alphabetically, an emoji is first – so “🎞Training” will sort before “A Channel”. You can still at-mention channels with emojis in the name. For this to work well, make sure you have spaces around the emoji character. When I have a channel named “🎈Party Planning”, I cannot use @party as the at mention – Teams tells me it doesn’t find any matches.

I can use @planning to find my channel.

If I rename the channel to “🎈Party Planning” with a space between the balloon and ‘Party’, I can at-mention @party and find my channel.

And, yes, you can rename existing channels (except for the “General” channel which cannot be renamed) to include emojis – click on the ellipses next to the channel and select “Edit this channel”.

Hold the windows key and press . to bring up the emoji keyboard. Select an emoji and save the changes to your channel.

Did you know … you can use emojis in Teams channel names?

Beyond being fun, emojis are great for quickly finding something – instead of reading all of the channel names, I can quickly scan to find the video icon or little balloon.

To add a new channel, click the ellipsis next to the Team name and select “Add channel”

In the channel name field, hold the Windows key on your keyboard and type a full stop (period, dot, “.”). Now you’ll have an emoji keyboard.

Select an emoji and type some text.

Alphabetically, an emoji is first – so “🎞Training” will sort before “A Channel”. You can still at-mention channels with emojis in the name. For this to work well, make sure you have spaces around the emoji character. When I have a channel named “🎈Party Planning”, I cannot use @party as the at mention – Teams tells me it doesn’t find any matches.

I can use @planning to find my channel.

If I rename the channel to “🎈Party Planning” with a space between the balloon and ‘Party’, I can at-mention @party and find my channel.

And, yes, you can rename existing channels (except for the “General” channel which cannot be renamed) to include emojis – click on the ellipses next to the channel and select “Edit this channel”.

Hold the windows key and press . to bring up the emoji keyboard. Select an emoji and save the changes to your channel.

Did you know … you can restore a deleted Teams file?

Oh no! I’ve accidentally deleted a document from my Teams files! Can someone restore it for me?!?

Yes! I can restore it for me – I can even restore documents others have deleted from our shared Teams file spaces. From the “Files” tab, click “Open in SharePoint”

You’ll see the documents that weren’t deleted … that doesn’t help! But click on “Recycle bin” on the left-hand navigation bar.

There it is! Click to select the file.

Then click “Restore”

In the upper right-hand corner of the web site, you will see a status message indicating that the document is being restored.

When the restore completes, click away from the “Files” tab and return to it. Voila! The document is back 😊