Tag: microsoft office 365

Did you know … you can tell who is speaking during a Teams meeting?

There are times when it is easy to tell who is speaking – there aren’t a lot of women in my group, so “the female voice” is usually me. My friend Richard is generally the only person with a New Zealand accent on any call (although someone who didn’t grow up in a Commonwealth country may have trouble distinguishing him from the guy from Australia). And after you work with someone for a while, you learn the voice and lexical nuances of colleagues. The rest of the time? I end up pausing the conversation to check who it was that volunteered to serve as my tester and clarify who is going to be getting back to me next week. In a Teams meeting, though, you can quickly tell who is speaking – and respond with a much friendlier “thanks, Jim, for offering to help”.

When you join a Teams meeting, you’ll see up to four large tiles with meeting participants. If there are more than five participants (you don’t show up on your own view!), the remaining people will be represented by smaller images in the lower right-hand corner of the screen.

When someone is speaking, their tile will be highlighted in a purply-blue and a brighter highlight circumscribes their image.

The four large tiles represent the most recent speakers, so you will notice who is in these four tiles change throughout the call. And, yeah, it’s possible for more than one person to be talking at a time – you’ll have multiple highlighted tiles.

There is another place to view who is speaking. On the right-hand column, click to enter the participant pane.

The current speaker will be bolded.

Bonus Features: Sometimes I’ll start a large call and have trouble getting everyone’s attention to start the call. In the participant pane, you can click “Mute all” to mute all participants. N.B. Any participant can do this – so don’t test it in the middle of a real discussion!

And just like meetings through the PSTN system or other web-meeting platforms, you’ll get the occasional person typing without hitting mute. Or speaking to someone who popped into their office. Or experiencing feedback on the connection. In Teams, it’s easier to identify who is causing a disruption – they are going to be highlighted as speaking.

Once you’ve identified the source of the noise, click the not-quite-a-hamburger-button next to their name and select “Mute participant”.

Did you know … you can save documents directly to Microsoft Teams?

You can create a document in a Teams file space (from “New”, select the document type); but, if you want to use a custom template (or if you just didn’t think of it and started the document on your computer), you can also save an Office 365 document to Microsoft Teams.

For the Teams file space to appear in the save dialogue, you’ll need to be following the SharePoint repository that underpins the file space. From the Files, select “Open in SharePoint”.

In the upper right-hand corner, click “Not following” to follow the site.

The change may not be reflected immediately on your computer – if your Teams space does not show up yet, wait an hour or two. Select “Save as” from the Files ribbon bar.

Click on “Sites – Windstream Communication”. The Teams space you followed will show up in the SharePoint sites list. Click on the team name.

Then select “Documents”

From there, you will see the name of each channel. Select the appropriate one, then navigate to the location you want to store your document. Give the document a name and click “Save”

The document will be saved directly to your Teams space.

Did you know … there are ways to split data in Excel?

Applications can generate data in formats that aren’t quite useful – glomming multiple fields together to make something unusable. And asking people to type information can yield inconsistent results – is my name Lisa Rushworth, Lisa J Rushworth, or just Lisa? Excel has several functions that allow you to produce consistent, usable data (without copy/pasting or deleting things!)

Flash Fill

Flash Fill will try to figure it out for you. Add an empty column (or more) and manually type one or two values. On the “Data” ribbon bar, select “Flash Fill” and Excel will use the data you’ve entered into the row to figure out what should go in the rest of the row.

The guesses aren’t 100% accurate – especially if your information is not consistent – but it’s a lot easier to delete the handful of things that are obviously not zip codes …

Than to work out a formula that extracts the same information

Text to columns

Text to columns uses the fixed-length file and delimited file import wizard on a column of data – essentially treating that column as a file to be imported. In this example, a DateTime value is provided in a way that Excel only sees it as a string. And, frankly, I am not interested on the exact hundredth of a second the event occurred. What I really want to do is group these creation dates by day, so all I need is the date component.

If you want to retain all the data, you’ll need to insert empty columns to the right – otherwise the data being split out can overwrite existing data. In my case, I only want to keep one of the new columns.

Highlight the column that holds your data. On the “Data” ribbon, select “Text to columns”

Select if the column should be split based on a fixed width definition or a delimiter and click ‘Next’

Indicate the proper delimiter – in this case, I need to use ‘Other’ and enter the letter T. A preview of the split data will appear below – make sure it looks reasonable. Click “Next”.

For each new row, you can specify a data type. Or leave the type set to “General” and Excel will try to figure it out.

If you do not need to retain the data, select “Do not import this column (skip)”. Click “Finish” to split your column.

Voilà – I’ve got a usable date value.

Notice, though, I have lost my original data. If you want to retain the original data, create a copy of the column. In this example, I want to know how many e-mail addresses use each domain, but I want to have the e-mail addresses in a recognizable and usable format too.

Text to columns will still replace the values from the selected column. But the copy will contain the original text.

You can even use Text to columns to sort out odd data that doesn’t actually get split into multiple columns. In this example, negative values have the minus sign after the number … which isn’t actually a negative number and isn’t usable in calculations.

Pick a delimiter that doesn’t appear in your data, and you’ll only have one column. When selecting the data format, click “Advanced”

Make sure the “Trailing minus for negative numbers” checkbox is checked and click OK.

And we’ve got negative numbers

Right, Left, Mid, and Search Functions:

You can also use the Search function in conjunction with Right, Left, and Mid to extract components of column data. In this example, we have first and last names. Since there are a few middle initials in there, we cannot just split on the space character.

These formulae aren’t perfect – Mary Ann will have ‘Mary’ as a first name – but

Working out where to start the text extraction and the number of characters to extract can get complex. I’ll usually include the Substitute function to simplify things a little – the zip code, in this case, is whatever is left over after we find the city and state.

Producing columns with the city, state, and zip code from the ‘Location’ column.

Did you know … you can lock “Format Painter” on?

How many times have you clicked a second time expecting to “paint” your format only to realize the format painter is a one-click deal-e-o. Well, it’s not — you just have to know the trick to ‘locking’ it on. Double click the format painter button — now you can paint as many things with the format as you like.

Did you know … Microsoft Teams Chat can help you find messages others post into Teams spaces?

I am a member of multiple Teams, and I can remember that Keith posted something about creating a Q&A a few days ago … but I don’t remember where he posted that message. I cannot reply to it until I find it. Search can help — chat conversations are searchable. But did he type QnA, Q&A, Q and A … 

Instead of clicking through all of the channels in all of my Teams spaces trying to find a single post or working my way through the various ways of phrasing “questions and answers”, I can look at my chat with Keith. Click the “Activity” tab. Now I am looking at things Keith has posted to our shared Teams spaces in the past two weeks.

The Team and channel into which the activity was posted is included before each message. An icon indicates if the activity is a reply to an existing thread or a message starting a new thread.

You can click on any entry in the activity log.

Your Teams client will show you the message in its context – you are in the correct Team and Channel, and the message is briefly highlighted. This makes replying to the message we found in the activity feed quite quick.

What if you’ve never chatted with the person? Start a new chat and type in their name. You don’t have to send a message to them (although I could totally see myself writing “ignore this message – I just needed to get you listed in my recent conversations”), just click away and there will be a draft chat with them. Click on that draft chat, and you’ll have an “Activity” tab.

Did you know … Microsoft Office programs can grab a screenshot for you?

You’ve encountered some odd error in an application and need to send IT support a picture. Or you’rewriting documentation. There are lots of reasons you need a picture of your computer screen. You can hit the “Print Screen” button on your keyboard (even hold Alt and hit print-screen to isolate the image to the active window). But did you know Microsoft Office programs can do that for you? On the ribbon bar, select “Insert” and locate “Screenshot”

Click on one of the “Available Windows”, and an image of the window will be inserted into your Word document, Excel spreadsheet, Outlook e-mail, or PowerPoint presentation.

Use the “Screen Clipping”selection to grab part of a window. Minimize all of your Windows. Bring up the Window of which you want an image. Now bring up the Office document into which you want the image inserted. Use Insert => Screenprint => Screen Clipping, and wait a minute. Your Office document will be minimized, your screen will get washed out, and you’ll have a cross-hair instead of a mouse pointer. Click and drag to draw a rectangle around something. When you release the mouse, whatever is in that rectangle will be pasted into your Office document.

Wait – what about those rectangles I use to highlight the image? From the ribbon bar, select “Insert”and “Shapes”. I took a University course where debugging screen shots had to have the “important bit” highlighted with a red square – that stuck with me. You’ve got an array of shapes and colours available. Pick one. Draw the shape over your image – yes, it looks like the shape covers the important part. Draw it anyway. While the shape is still selected, click “Format” in the ribbon bar. Select “Shape Fill”

Select “No Fill” (you could also use a highly transparent fill colour if you’d prefer).

Click “Shape Outline” – pick a colour, and if the line is not thick enough select “Weight” to increase the line width.

When I’m writing documentation with a lot of images, I’ll still use an image editor and ‘print screen’. There are filters that just don’t exist in the Office image editors – sometimes I want to selectively blur screen text so my work conversations are not included in documentation. Sometimes I want to create a composite image. But for small documents – showing someone the error I get on their web site, “click here, type this” – using a single application is efficient.

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 😊

Do you know … Teams Activity View?


The very first icon on the left-hand navigation menu, “Activity”, isn’t just a listing of all unread Teams activity. This view provides a customized view of important Teams communications, allowing you to focus on the most important communication first. 

This isn’t a list of every thing that has been posted to every one of your Teams spaces. It doesn’t even include chat messages sent to you –new chat messages will show up as a red circle with a message count on the“Chat” view icon.

So what shows up in the Activity feed? Missed calls – missed calls are only displayed in your Activity feed. Clicking on the entry will display a chat with the caller; you can reply with a chat message or click the phone icon to return their call.

Posts with @mentions – both your individual mentions and mentions for Teams of which you are a member – will appear in the Activity feed.

Beyond that, you control what appears in your feed. Posts to channels you follow will appear in your feed. To follow a channel, click the“Teams” icon. Click the not-quite-a hamburger menu next to the channel name and select “Follow channel”.

When messages are posted to the channel, you’ll see a red circle with the number 1. This indicates that there is one thread with unread post(s). There may be a bunch of replies in that thread, but the thread is only counted once. This doesn’t mean replies won’t be highlighted – if someone replies to a thread you’ve already read, that thread will again be counted as a thread with unread post(s).

You can click on an entry to display the specific thread. Clicking on a reply will focus on the reply – which helps identify what part of the thread you haven’t seen.

If a channel becomes prolific and irrelevant to you, you can simply stop following the channel. Click the not-quite-a hamburger menu next to the channel name and select “Unfollow this channel”. Anything from the channel in your feed will remain there, but new activity in the channel will cease appearing in your Activity feed.

In addition to a feed of activity from other individuals, you can use the activity feed like the “Sent Items” in your mailbox. Click the inverted caret next to “Feed” and select “My Activity”. You’ll see two weeks of your Teams posts.

Did you know … you can use mini-charts to visualize Excel data?

Using charts and images, data visualization, clearly and efficiently communicates data. But when you’re trying to visualize statistics for several items, your chart can be anything but clear and hardly efficient to read. In this example, I’ve created a line chart depicting the monthly score for eight different people. While you can pick out obvious high or low performance, there’s not a whole lot of information being communicated here.

Did you know Excel can create mini-charts, known as “sparklines” to visualize individual statistics and compare statistics across items? Select the data that you want to compare. From the Insert ribbon bar, look for the “Sparklines” section. I am going to use a “line” style sparkline.

The data range will be selected. Enter the range where you want the mini-charts to display – this can be the row under your data or the column next to your data, or it can be some completely different location.

By default, the y-axis range for each mini-chart depends on the values of the data contained in the chart. This makes comparing the charts a little difficult – the scale is different. In the example below, scores in the 30’s don’t look different than scores in the 80’s.

Click on one of the mini-charts, and a “Design” tab will appear on the ribbon bar. Select it. Under “Axis”, change the minimum and maximum values to “Same for All Sparklines”.

Now you can see how individual performance varied as well as compare individuals.

Blank values will show up as broken lines in the mini-charts. If you do not want to display a gap, return to the “Design” ribbon bar and select “Edit data”. Select “Hidden & Empty Cells”

Select what you want instead of gaps – you can treat null values as zero or have a line drawn between the values on either side of the missing value.