Tag: Microsoft Office

Did you know … Microsoft Teams will mute your microphone when joining a large meeting?

Several offices ago I sat across from a L.O.U.D talker. A loud talker who was constantly on a conference call. I’d try to remember to mute my line before joining a bridge but didn’t always remember. Random background noise isn’t so bad when you’re about to talk to two or three close colleagues, but background noise becomes inundating when the twenty-something people in my department all join a bridge.

Microsoft Teams helps you avoid a deluge of background noise in your call. When you join a “large” meeting –more than five people already in the meeting – your microphone is muted as you join. A notification is displayed prior to joining the meeting to remind you the microphone will be muted.

You can click the microphone button in the meeting control bar to un-mute your microphone and begin speaking.


Did you know … you can insert 3D models in PowerPoint presentations?

Select “Insert” from the ribbon bar, then click the drop-down on “3D Models”. You can browse models from Remix 3D (“From Online Sources”) or models stored on your computer (“From a File”).

You can insert Filmbox (fbx), Object (obj), 3D Manufacturing (3mf), Polygon (ply), StereoLithography (stl), and Binary GL (glb) files.

STL files are commonly used when designing objects for 3D printing – here I’ve included a camera/telescope adapter.

Click on a 3D object and there is a rotation control

By changing an object’s orientation, you can use the “Morph” transition to rotate the object during your presentation. Right-click on the slide that contains your 3D object. Select “Duplicate Slide”.

Rotate the object on the new slide. Select “Transitions” from the ribbon bar, and select “Morph” as the transition.

When you play your presentation, the object will rotate as PowerPoint transitions between the two slides.

Now I know there aren’t a lot of uses for rotating 3D unicorns in business presentations, but manufacturers can embed 3D renderings of their products. Many architectural firms are providing 3D building designs that can now be included in PowerPoint presentations.

Did you know … you are talking on mute?

Well, probably not *right this second*. But we’ve all heard — err, not heard — someone else do it. We’ve all done it too. The Teams desktop client helps you avoid saying “Sorry, I was talking on mute”.

In the Teams desktop client, you will see an alert when your microphone is muted and sound is detected. Maybe you’re talking to someone who stepped into your office — just ignore the warning. If you’re trying to speak to the meeting, this is a great way to avoid having to repeat your entire thought because no one else heard it the first time!

Did you know … your mouse can be a laser pointer in PowerPoint?

This works both in “Reading View” and when you are playing a presentation. To enable “Reading View”, select “View” from the ribbon bar and click “Reading View”.

To play your presentation, select “Slide Show” from the ribbon bar and click “From Beginning” (or “From Current Slide” to begin the presentation from the current slide).

Hold the CTRL key, then left-click and drag your mouse. You’ll see a red circle which can be used to draw attention to elements of your slide.

If you want a different color “laser”, select “Slide Show” from the ribbon bar and click “Set Up Slide Show”.

Click the drop-down next to “Laser pointer color” and select the desired color. Click OK.

Now when you hold the CTRL key, left-click and drag the mouse … now there’s a blue laser pointer.



Did you know … you can remove duplicates in Excel?

I use Excel’s COUNTIF function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it’s easy enough to get the list of IF’s to count. But when I need to find the occurrence of different text strings, I need a unique list of the strings first. “Remove duplicates” quickly exactly what I need.

In this example, I have a list of all employees and contractor’s departments and titles – I want to know how many people are in each department and how many people have each title. Removing duplicates modifies the data, so the first step is to make a copy of the spreadsheet. Highlight the data. Select “Data” on the ribbon bar, then select “Remove Duplicates”

Select the column(s) where you want to remove duplicate data. This could be exact duplicates across multiple columns (e.g. the unique “City, State” combinations), or (in this case) I just want a unique list of departments. Click OK.

A summary will be displayed showing you how many records were removed and how many unique values remain.

Now that I have a complete listing of departments, I can use my COUNTIF function to show how many employees and contractors are in each department.

Remove duplicates only deletes records within the highlighted data. Here, I have a list of all employee titles next to the department and count info we just created. If I highlight just the ‘Title’ data and click “Remove Duplicates”, the department and count information is left unchanged.

Now I have a unique list of titles as well.


Did you know … Microsoft Teams has a GUI text editor?

I like keeping my fingers on the keyboard, so I like using markdown in Teams messages (had to learn it for GitHub anyway!). The fact that hitting enter sends my posts in Teams? Generally awesome. I am not, however, the most succinct person; and a long series of thoughts is difficult to read as one continuous paragraph.

And using a new paragraph can serve to highlight a sentence without resorting to big bold text.

You can use shift-enter to move to a new line. Enter will still send your message.

But Teams has a GUI-driven composition mode — just click “Format” — that allows you to easily compose multi-line messages. In this editor, enter doesn’t send the message. It just moves the cursor to the next line.

There are a lot of formatting options available too. Basic typographical emphasis can be added to your text, and anything you type into the ‘Subject’ section will automatically be large, bold text.

The little highlighter icon will highlight text.

The underlined “A” changes the font color.

The icon with two A’s controls the text size.

So you can add really tiny or larger text.

Allowing you to use smaller or larger text.

You can create a bulleted list by clicking the icon with bulleted lines (or a numbered list by clicking the one with numbered lines). To end the list, either click the icon again or hit enter twice.

The quotation marks highlights text as a quote (two enters returns you to normal paragraph format here too), and hitting the drop-down next to “Paragraph” provides a list of pre-formatted text options.

A really cool feature for programmer-types – click the ‘code snipped’ icon.

A new composition window will be displayed – click the drop-down text to “Text” and select the programming language.

Text formatting will be applied to your code – the code I paste into Teams looks exactly like it does in my IDE.

When you have finished composing your message, you can click the little paper aeroplane to send your message. Or, if you prefer keeping your hands on the keyboard, hit ctrl-enter.


Did you know … Outlook can share OneDrive files for you?

Collaborative document editing in Teams and SharePoint is a huge time saver – instead of trying to merge multiple versions of a document together, we can all edit the same document (we can even edit it at the same time). OneDrive offers the same benefit, but it’s a bit of a hassle going into OneDrive, setting up sharing, and then sending people a link to the document. But Outlook handles this for you.

I have a private file saved to my OneDrive for Business space.

In your message, select ‘Attach’ and then ‘Cloud locations’.

You will see your OneDrive for Business files – select the file(s) that you want to share and click “Next”.

You will see the file as an attachment to the message – the OneDrive cloud logo lets you know that the ‘attachment’ is actually a link to a OneDrive document. Address and send the message as you normally would.

Check OneDrive again, and you will see that the document is shared.

The recipient will see an attachment to the message, and they will be able to view and edit the file.


Did you know … Excel can automatically highlight data for you?

Reading through large tables of data is inefficient – it’s time consuming, error prone, and just not a heap of fun. Graphs are one way to visualize data – allowing you to quickly spot trends, outliers, etc. Excel offers another way to visually enhance data to make it more comprehensible – conditional formatting. Where some charts and graphs obscure the underlying data, conditional formatting allows the exact value to be quickly identified.

Highlight your data. On the ribbon bar, select “Home” and click the drop-down for “Conditional Formatting”.

Select the logic to determine which cells are highlighted – we’ll go through a few examples here, but click around on your own! To highlight cells that are higher than some value, select “Highlight Cell Rules” and then select “Greater Than”.

In the window that appears, enter the number and select the colouring scheme. The prepopulated number will be the average of the highlighted data. The changes are applied as you select formatting options, so you have an idea what it’ll look like ahead of time. In this case, there are still a lot of values higher than 125. I could increase my number to reduce the number of highlighted cells. When you have finished composing your formatting rule, click OK.

And the format is applied to your data. You can apply multiple formats – add another format to turn anything below 25 green, make values between 100 and 124 yellow. Whatever you want.

If you need to change your formatting rules, click on the “Conditional Formatting” drop-down and select “Manage Rules”.

If your rules do not appear, change “Current Selection” at the top to “This Worksheet”.

You can also define custom rules. From the “Conditional Formatting” drop down, select “New Rule”.

Again, select the logic used to determine which cells are formatted. Here, I am highlighting duplicated values. Click “Format” to define how the highlighted cells should appear. Click “OK” to apply the formatting to your spreadsheet.

Now every duplicated record is in green with a strike through the value.

Formatting rules can be nuanced – here I am creating a custom formatting rule that uses a three-colour gradient based on where a value falls within a range.

Now you can quickly compare each value by it’s colour.


Did you know … Excel can use maps to visualize data?

I remember visiting my uncle at a NASA design lab sometime in the mid-80’s – it was a huge cavernous room that he explained used to house the computer. A computer his graphing calculator could draw circles around. It was a powerful visual reminder how quickly computing technology advances – components are smaller, more powerful, and simpler to use.

More than two decades ago, I wrote a visualization application that presented a graphical representation of the geographic distribution of records. Which is a long way of saying it showed where something happened to a lot of people. The application was part of a cooperative effort between the FBI and local law enforcement – a data mining project meant to identify serial offenders across jurisdictional boundaries I wanted to be able to visualize where different types of crime were occurring and identify anomalies, so I built a program to do so. It took months to develop and took hours to crunch values and draw a map. The first time I used Excel to visualize frequency distribution on a map, I thought of that NASA computer room. What used to take a high-end Unix server with a RISC processor and tonnes (for the time) of memory – not to mention an entire summer of code development – is clickity-click and done on my little laptop. And the results are nicer:

How do you create this type of visualization? First you need data with something that is mappable – the example here is going to show the office locations listed in PeopleSoft. Click within the data set.

On the ribbon bar, select “Insert” then select “3D Map” in the “Tours” section.

If you have not used it before, you will be asked to enable data analysis service.

A new window will be displayed – select the column you want to map. Here, I am using zip codes, which is mapped to the “Postal Code” field in my spreadsheet. If your fields do not map automatically, you will need to click the drop-down next to a location data type and select the appropriate column.

There are different types of visualization – here, I have switched to a “heat map” where the color of the blob represents how many records fall into this zip code. It is a quick way of identifying clusters – hot spots.

You can control the look of the map as well – here, I have switched to a flat map and added location labels.

If you would like to include a copy of your map in another program – say, this Word document – select “Capture Screen” from the ribbon bar. You can also create a video to show an animated view of your map (zooming in on specific locations, rotating the globe to see people over in Mongolia)

After you’ve clicked “Screen Capture”, just paste and an image of your map will be inserted into your file – see!

Going A Little Farther:

Data isn’t perfect, and even when the data looks good it may not map properly. My sister used to live on a street in New Jersey that does not exist on a map. The post office affirmed it was the correct address, but UPS and FedEx claimed it didn’t exist. It was funny to me, but I wasn’t the one trekking two kids down to the neighbor on the main road who nicely accepted packages for her. She moved before they ever got the address situation sorted, but I’ve got first-hand experience with addresses that don’t map in some systems but are perfectly fine in others. Why do I mention this? The map visualization provides a “Mapping confidence” statistic – it is the percentage that appears above the box where you select the location data to be mapped. 98% is pretty good – there are a handful of records that don’t appear on the map … but the data I am presenting is a decent representation of our employee office locations. A low percentage would indicate that your map does not accurately convey your data.

What if my map confidence level is low? Click on the map confidence value to see what didn’t map. There are some marked with a result that is questionable – spot-checking them, 03109 is Manchester NH and 10001 is New York, NY. The one with no resolution, according to the US Postal Service lookup isn’t a valid postal code. If your data is wrong, fix it 😊 In cases where the data is right but the application isn’t confident about the location, you can add additional data to make the address more specific (here, I might increase the confidence by having the zip+4, or including the street address in my data set).

You can filter data in your map – first we’ll need some field on which to filter. Here, I’ve added the employee’s department to my data set.

On the right-hand pane, expand “Filters”. Click “Add filter”.

Select the column on which to filter data. A unique list of values will be presented – you can scroll through it or start typing the value to search. Once you find what you want to display, click the check-box before the value.

Now we are visualizing where people in my department work.

If your data is hard to see – records are distributed out fairly evenly across the map – you can increase the area of influence to make smaller clusters easier to identify. Scroll to the bottom of the right-hand pane and drag the “Radius of influence” slider to the right. If you have very clustered data, you can drag the slider to the left to turn a large red blob into a more nuanced visualization.

When you have finished visualizing your data, click “File” on the ribbon bar and select “Close”.


Did you know … you can cancel sending an e-mail in Outlook?

Anyone who has mis-addressed a message or hit ctrl-enter and prematurely sent, well, some of a message has probably found the message “recall” option. Unfortunately, “recall” depends on the recipient having “Automatically process requests and responses to meeting requests and polls” enabled, cannot recall the message if the recipient has a rule that moves the message out of their inbox, and doesn’t work if the recipient isn’t using Exchange. Many times, your attempt to recall a message yields another message like this:

Which is better than someone thinking I meant to send them half of a thought that stopped mid-sentence. But it’s not what I expected. You can, however, configure Outlook to delay sending messages … allowing you a little time to cancel the message.

Outlook Client:

On the ribbon bar, click “File”. On the ‘Account Information’ screen, click “Manage Rules & Alerts”

Click “New Rule…”

In the new rule wizard, select “Apply rule on messages I send”

Do not select any conditions – just click “Next”.

You will see a warning that the rule will be applied to all messages you send – click “Yes”.

Click the check-box before “defer delivery by a number of minutes”, then in the text below click the hyperlinked ‘a number of minutes’ and enter the number of minutes you want to delay sending messages. Use a small number – if you close Outlook before the message is sent, it will not be sent until you re-open Outlook! (Plus it’s confusing if you’re on a call with someone, tell them you are sending them something, and it doesn’t actually send for fifteen minutes). Click ‘Next’ to continue.

Give your rule a descriptive name, then click “Finish”.

You will be warned that the rule will only run if Outlook is running – click OK. If you routinely use Outlook on two different computers, you’ll need to create this rule on both computers.

Now when you send a message, you will see a counter next to “Outbox”. The message will sit there for the time you specified, then it will be sent. Once the message is sent, the counter will disappear.

If you want to stop the message from being sent, click on “Outbox”.

Right-click on the message – you can select “Move” and move the message back to your “Drafts” folder or you can delete it.

Outlook Web:

Click the “Settings” gear in the upper right-hand corner of your screen.

Click on “Mail” to display the mail-related settings.

Expand “Automatic processing” and click on “Undo send”

Click the radio button to select “Let me cancel messages I’ve sent for:” then click the drop-down to select how long sending will be delayed. Pending messages won’t be sent if you close your browser or put your computer to sleep – they’ll still be there when you open Outlook again. Click save.

Now when you send a message, it will be deferred in your “Drafts” folder for the selected time period. While the message is deferred, you will see a “Cancel send” option in the upper right-hand corner of your Outlook Web screen. If you don’t want to send the message, just click “Cancel send”.

The message will be opened to allow you to continue editing it. You can save it as a draft or discard it as well.