Tag: Microsoft Office

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.

Did you know … you can reply to an e-mail message with a meeting request?

Sometimes it is easier to take a few minutes, get everyone together, and talk about something. Switching from an e-mail thread to a meeting invitation, though, means you’ve got to copy/paste all of the recipients and provide a message summary so attendees have a clue about what you want to meet. Did you know that you can reply to a message with a meeting request? All message recipients are included in the invitation, and the message content is copied into the meeting request.

Web Mail:

Click the drop-down next to reply and select “Reply all by meeting”

A new meeting request will be constructed – complete with attendees (addresses in he ‘to’ line become required attendees, cc’s become optional attendees), a meeting subject, and the entire e-mail thread in the meeting body.

Outlook:

In Outlook, click on the “Meeting” button in the ‘Home” ribbon bar.

Again, a meeting request is created with attendees, subject, and message content.

Anything you can do in a manually created meeting request can be done here – if you want to add a Teams meeting space or set up recurrence … this is a normal meeting request, it’s just got a lot of information pre-populated.

Did you know … you can change Word’s built-in Styles?

Using Styles in Word has some advantages – one-click to apply a variety of format options, the “Navigation” tool provides quick access to “heading” items, the automatic table of contents uses “heading” items too (and you can instantly update automatic table of contents data as new content is added and page numbers change) – but what can you do if the predefined text format doesn’t fit your document?

Themes

Under the “Design” ribbon bar, you will find an array of themes.

Selecting a different one changes the colors, font faces, font weight, and font sizes used throughout the document. You can change your document to look like this

Or this

Customize Styles

What if the styles still don’t fit your document? I, as an example, prefer my headings bolded and sub-headings both bolded and italicized. You can customize a theme to match your specific preferences.

On the ribbon bar, select “Home”. In the “Styles” section, right-click on the style component you want to change and select “Modify”.

Modify the style component as desired – change the font face, make it bolder, change the size, change the color, add a little more space between lines, whatever you want. Click the box to ‘Update Automatically’ and, if you want to use this customization in other documents, select the radio button that says ‘new documents based on this template’. Click “OK”.

Sections of your document using that style component will be updated. I have customized all of the style components – normal, headings, title and subtitles, quotes, etc.

On the ribbon bar, select “Design”. Click the “Themes” drop-down and select “save current theme”.

If you want to use your theme on every document you create, click “Set as Default”.

Click ‘Yes’ to confirm the change.

Did you know … Outlook can display the time in multiple time zones?

In one of my prior jobs, I worked in Boston. I had colleagues in Hawai’i. Scheduling a meeting was a mental undertaking – 8AM in Honolulu is 1PM in Boston (and I had to count through Alaska, the west coast, the mountains, the next one over, and then me all.the.time). Beyond the time wasted figuring out what time it is elsewhere … you forget to think about it when you’re in a hurry. I’d book the guys in Hawai’i for mid-morning meetings at dark-o-clock, and the guys in Hawai’i would schedule mid-afternoon meetings that were 8PM for me. The Outlook calendar can show two time zones concurrently – both reminding you that time zones are a ‘thing’ and quickly showing you what time it is over there.

Click “File” on the ribbon bar

Select “Options”

Select “Calendar” from the left-hand navigation bar. Scroll down and find the ‘Time Zones’ section. Check the box to ‘show a second time zone’, and select that other time zone. I add a label both to my time zone and the secondary one. Click OK. If you have the monthly update channel, you’ll be able to select a third time zone too.

Now check out your calendar:

Did you know … you can ignore entire conversations in Outlook?

You can! Of course, you don’t want to ignore important conversations; but we’ve all been accidentally included on message (or been caught up in the dreaded reply-all blizzard) and been inundated with messages that really can be ignored.

Within the Outlook client, click on one of the messages. On the left-hand side of the ‘Home’ ribbon, click “Ignore”

Or from within the message, “Ignore” appears on the left-hand side of the “Message” ribbon bar.

If you haven’t previously selected “Don’t show this message again”, you will see a warning that the entire conversation and all future messages will be moved to “Deleted Items” … click “Ignore Conversation”

If you change your mind, all of those messages are in “Deleted Items” and you can easily move them back.

If someone changes the message subject, those messages become a new thread that you’d need to ignore again. When you’ve been erroneously included on some message, the subject rarely changes … but I usually have to block five or six different threads in reply-all blizzards.

Did you know … you can increase the maximum number of “undo” operations in PowerPoint?

I am a big fan of “undo” – highlighted something to copy it but missed the ‘ctrl’ part of ctrl-c? Undo! Editing an image and drew a line the wrong place? Undo! Change some verbiage and regret the modifications? Undo! (I’ll generally copy the stuff I’ve added into a new document before I start hitting ctrl-z {the keyboard shortcut for undo} and incorporate a few of the new ideas into the original text.) Occasionally, you run out of undo-able operations. If you are saving to OneDrive, SharePoint, or Teams, you can use the version history to get back to your original content. But did you know that PowerPoint allows you to increase the number of undo operations available?

Click “File” on the ribbon bar and select “Options”

Select “Advanced”. Under “Editing options”, you will see a maximum number of undos – this value defaults to 20.

You can increase it up to 150 – although higher numbers can adversely impact performance, so stick to a lower number unless you really want to undo a hundred operations!

Did you know … you can use Microsoft Teams outside of work too?

There are a lot of tools we use at work that are silly overkill in your personal life – I don’t want to open a Remedy ticket for every squeaky hinge! But some of our tools are quite helpful away from work too – making flyers in Word or using Excel to keep track of the softball league standings. “Is Teams useful in my personal life” seems like a purely hypothetical question – it’s not like I can invite the rest of the Parent Teacher Organization to join us here in Teams (and even if I could, that’s hardly an appropriate use of company resources!). But did you know Microsoft offers a free version of Teams?

Signing up for a free account, you don’t get access to all of the Teams features we’ve got here – you cannot schedule meetings, record meetings, there isn’t a Planner board, you don’t have access to the full suite of Office 365 applications. But you do have a Teams space, can use the integrated apps and connectors, have some file storage space, have persistent chats in channels, and can even have group audio/video calls.

To sign up for a free Teams account, visit https://products.office.com/en-us/microsoft-teams/free

Don’t use your company e-mail address to sign up – this will need to be your personal address.

If you don’t already have a Microsoft account associated with the e-mail address, you’ll be asked to create a new Microsoft account.

Otherwise you’ll be asked to sign in to your Microsoft account.

Supply your name, organization name, and country of origin. Read the terms of use and privacy statement. Assuming your use complies with the terms of use and the privacy policy is acceptable, click “Next”

It will take a few minutes for everything to be set up. Once your personal Teams organization is built, you can invite others to join. Click on your avatar in the upper right-hand corner of the screen and select “Manage org”

Click “Invite others to your org”

And enter their e-mail addresses

They will get an e-mail message inviting them to join your Teams org.

You’re ready to start using Teams – add tabs to websites your group commonly uses, set up connectors, create new channels, chat, video calls, share files. You can even add new Teams to your organization.

Everyone added to your org is automatically able to access the default Teams space (the one with the name of your org). Teams spaces you create can be private or public, just like at work – but you can also select “Org-wide” which automatically joins any newly-added individuals to the Teams space.

Did you know … you can search chats, files, and people in Microsoft Teams?

One drawback to retaining all of your chat and team discussion content is that there’s a LOT of content … which makes it challenging to find a specific discussion or comment. Luckily, Teams data is easily searched. At the top of your Teams application (or website), there is a long gray bar. Click in it and type a word or phrase to begin searching. Using multiple words will find conversations containing both words, to find a phrase place the phrase “in quotes”. Hit enter.

You’ll see messages that contain the words or phrase – notice this includes both chat messages and channel discussions. Each search result has a reference letting you know where the discussion is located, and you can click on the item to switch to the chat or channel discussion.

If your search returns too many results, click on the little funnel – you can refine your search results with a filter – a specific individual, a time frame.

In addition to searching chat messages, you can search files. Just click on “Files” and you’ll see files with names or content that contain your search terms. Again, you can see where the file is located, and you can click on the file to preview the file.

Click on “People” and you’ll find, well, people who work here. This is a name search – you cannot search for “stats” and see people with whom you’ve had discussions about statistics. Search for a last name, a first name, or a name in “last, first” format.

If you click on a person, you’ll see the conversations you have had with them, as well as any shared files, an org chart for their position in the company, and their channel posts for the past two weeks (‘Activity’).

If you haven’t chatted with them before, you can start a new conversation too.

Did you know … Microsoft Word can generate random text for you?

As I’ve been writing these “Did you know” blog posts, I’ve needed sample data to demonstrate how a function works. In Excel, that’s easy enough as there are RAND() and RANDBETWEEN(iLow,iHigh) functions. But how do you get sample text in Word? I used to just paste in part of a public domain work (as a cultural aside – the twenty years of copyright protection added under the Sonny Bono Copyright Term Extension Act are up this year, so the list of public domain works is expanding again). But what if you don’t have the original Anglo-Saxon Beowulf laying around?

Microsoft Word has a rand() function too – type =rand(#Paragraphs,#Sentences)

The example here generates two paragraphs that are five sentences each.

Hit enter – the formula is replaced with random text.

(This works in PowerPoint too). If you want some different text, try the lorem(#) function — the number in the parenthesis is the number of paragraphs — that uses the lorem ipsum paragraph as the content source.