I need to programmatically parse an Excel file where items are grouped with arbitrary group sizes. We don’t want the person filling out the spreadsheet to need to fill in a group # column … so I’m exploring ways to read cell formatting so something like color can be used to show the groups. Reading the formatting isn’t a straight-forward process, so I wondered if Excel could populate a group number cell based on the cell’s attributes.
While it is possible, it’s not a viable solution. The mechanism to access data about a cell cannot be accessed directly and, unfortunately, requires a macro-enabled workbook. The mechanism also requires the user to remember to update the spreadsheet calculations when they have finished colorizing the rows. While I won’t be using this approach in my current project … I thought I’d record what I did for future reference.
We need to define a ‘name’ for the function. On the “Formulas” tab, select “Name Manager”.
Select ‘New’
Provide a name – I am using getBackgroundColor – and put the following in the “refers to” section: =GET.CELL(63,INDIRECT(“rc”,FALSE))
Now we can use this name within the cell formula:
Select the rows for your first group and change the “fill color” of the row.
Repeat this process to colorize all of your groups – you can re-use a color as long as adjacent groups have different colors. Notice that the “ColorGroup” values do not change when you colorize your groups.
On the “Forumlas” tab, select “Calculate Now”
Now the colorized cells will have a non-zero value.
The trick to understanding this is knowing that “Meeting Notes” are, for some reason, Wiki pages and not OneNote documents. There are two types of meetings — those held in a Teams channel and those held outside of a channel — and the ability to get a useful link to the Meeting Notes depends on which type of meeting you have.
Meetings in a Teams Channel:
When your meeting is in a Teams channel, you can use the ellipsis to grab a link to the Meeting Notes location in Microsoft Teams.
This link points to the “Meeting Notes” tab created in the channel. That tab is available without a link, too — so I can access the meeting notes just by going to the channel where the meeting was held.
Meetings Outside of a Teams Channel:
The meeting notes wiki file is stored in your OneDrive. You can find that file by searching your OneDrive for the name of the meeting. In this example, I have a meeting titled “Super Important”. You can right-click on this and select “copy link” to grab a link to the file.
The problem is that it’s an MHT (basically a self contained web page) file. I can give you a link to the file, but it’s not a convenient link to a OneNote page like you’d expect. For some reason, Chrome wants to save it as an EML (email) so the file opens in Outlook (or change the extension to MHT manually). Firefox keeps the MHT extension, and the file opens up in a browser so you can view the notes.
In January, Microsoft expanded Teams Chats to 50 people. I’ve heard from a few individuals who wanted to be able to chat with more people — essentially to use Teams to send broadcast messages to a lot of people. Last week, Microsoft upped the limit for private chats to 100. Hopefully they’ll extend the Graph API to allow applications to initiate those chats because adding 100 people to a chat seems like it would take a while!
If you hover your mouse over the upper right-hand corner of a post – where the little thumbs-up used to be
You’ll see a reaction bar. Click one of the emojis to “react” to a post.
Now you’ll see reactions on a post instead of just thumbs-up.
When a post receives different reactions, you’ll see icons for each reaction and a number showing you how many people selected each reaction.
Teams announcements are another way to bring attention to a specific post. This doesn’t address the desire to pin a post so it’s always visible in the channel (click the link and vote if that’s something you want to do too).
When you are in the advanced editor (click “Format” or use Ctrl-Shift-I), you will see a drop-down to change conversation posts to an announcement.
When creating an announcement, the editor will have a banner at the top. You can put text in the banner and customize the banner background. Click either the color selector or the image selector in the bottom right-hand corner of the banner.
You can upload a custom image – you’ll want something that is a long, horizontal rectangle. Select “Upload an image” and select the file you want to use as the background.
You’ll probably need to crop the image – you can adjust which portion of the image is shown and zoom into the image as needed. Click “Done” to accept your crop selections.
Compose the rest of the message as normal – you can add a sub-heading and any of the message content available in regular posts. Post the announcement
The post will have a little megaphone logo (this doesn’t show up as a filter option yet, but I expect it will be added in the future) and the banner will make your post stand out in the conversation listing.
They make your post stand out with a caveat – just like marking all of your posts as important, announcements lose their efficacy when every post is an announcement. Use sparingly!
I use the Teams web client as my primary Teams application – I like it, but it’s also important to be able to identify where the web client falls short of full Teams functionality. There is one big “missing” feature to me – screen sharing. I can view shared screen, but I cannot remote control shared screens and cannot share my own screen.
But now you can share your screen in a Teams web client. You have to schedule a meeting, and you still cannot perform remote control operations in the web browser. But it’s awesome to be able to show someone what I’m working on or let a tech support engineer follow along as I reproduce an error.
To share your screen from a Chrome browser, first open https://teams.microsoft.com in Chrome. Schedule a meeting and join it. Open the sharing control panel.
Select “Screenshare”
Select the appropriate tab to share your entire screen, a single application, or a single Chrome tab. Select the screen/application/tab that you want to share and click “Share”.
Voila! You are sharing your screen. Click “Stop sharing” when you are done (or just disconnect from the meeting).
My inbox has 7,582 messages in it. This fact doesn’t bother me at all because “Unread: 2” indicates what still needs to be addressed. I mark a message as unread to keep them in my “needs to be worked on” queue, so seeing thousands of messages in my inbox doesn’t feel like an overwhelming pile of outstanding requests.
Some people move messages from their inbox – either deleting the message or sorting it into an appropriate folder – and, for them, the item count is their “needs to be worked on” list. My mom is one of those people – she gets a little stressed out just seeing the pages (and pages, and pages!) of messages in my inbox.
I mention this because it never bothered me that the Teams chat list is cluttered with the last 30 days of private chats, chats from within meetings, Planner notifications. Nothing is in bold, there’s no activity indicator on the Chat tile … to me, that says “you’re all done here”.
But that’s not true for everyone. Some people see the pages of conversation history and subconsciously see a bunch of messages they still need to address. Or they see clutter — “when you’re done with it, put it away” and this isn’t away! If you don’t like to have dozens of finished conversations hanging out for a month, you can hide them. Hiding conversations does NOT delete the messages – if you receive a new message from the individual or address a new message to them, your previous chat history will still be there. Hidden chat messages are still displayed when you search for information. But hiding chats reduces the number of “recent” conversations displayed in Teams.
To hide a conversation, move your mouse over a listing and click the ellipsis which appears.
Select “Hide” from the menu.
The hidden chat record will no longer be listed in your recent conversation history. Repeat as needed with the rest of your chat history.
If the person sends you a new message, you will see an activity indicator on the “Chat” tile and the conversation will pop back into your recent conversation listing. If you address a new chat message to the person, the conversation will pop back into your recent conversation listing. You can hide the conversation again when you’ve finished the discussion.
As we are upgrading groups to Microsoft Teams, we need to be able to identify which activities need to be performed each week. While highlighting today’s date is a start, it is better to identify which tasks need to be performed in the upcoming week so we can plan ahead.
To accomplish this, I use a conditional formatting rule. It highlights all of the date values that fall between today and seven days in the future. How? In conditional formatting, you can use a formula to determine which cells to format. My selection rage is E2 through J20, so the conditional formatting formula is based off of the E2 cell.
The formula AND’s to IF functions. If the difference between the cell date and today is less than 8 (less than 8 days in the future) AND if the difference between the cell date and today is greater than or equal to zero (today or a future date), the rule evaluates to TRUE and the highlighting is applied.
=AND(IF((E2 – TODAY()) <8,1,0),IF((E2 – TODAY()) >= 0,1,0))
The result – every activity we need to plan for in the upcoming week is highlighted.
