Generate Captions from a Video with Microsoft Stream 📽️📃

If you have been working with videos and recordings recently, you may have been asked to generate captions, or procure a “vtt” file with captions for a video.  There are services, like rev.com, that you can hire to create these caption files… but before you do that, you may want to check out this option that you already have in your Office 365 suite in an application called Microsoft Stream.

Stream captions

Note: For WSU Faculty and staff: you can also utilize Panopto to export captions. Contact the Office of Instructional Resources for more information.

Before we jump in, thank you to Rebeca for her question about this and for inspiring this Byte!

Warning: AI Ahead

This application uses Microsoft’s AI (Artificial Intelligence) to generate captions; it is the same software you use when you use captions in PowerPoint or Teams.

If you have ever used this feature in those applications, you may have noticed that the translations are not always perfect, and sometimes, unfortunately, Microsoft has trouble generating captions at all… long story short, be sure to double check the file that is generated from this process for accuracy.

Microsoft Stream

1. To access Microsoft Stream, first log into your Office 365 account. There is a shortcut in the upper right of your myWSU screen:

Office 365 link in myWSU

2. Once you have logged in, click on on the waffle at the upper left…

MS Office online, waffle

3. And find Stream in the app list. If you don’t see Stream here, click on More Apps at the bottom of the dropdown.

MS Office Applications in waffle

Upload a Video

1. In Stream, in the ribbon at the top, select the My Content dropdown, and Videos.

My content dropdown, videos

2. Look at the upper right of the screen for options to either click and Drag the videos into your stream, or Browse your machine.

My Content view, drag files here selected

Important: the default for videos you upload is to make them Public to anyone at WSU. If you would like the video to be private, toggle the Permissions arrow, and deselect Allow everyone in your company to view this video.

Permissions: uncheck allow everyone in your company to view this video

3. Your video will take several minutes to process, and you will receive an email notification once this process is complete.

Downloading Caption File

1. Once your video has finished uploading and processing, click on the Pencil next to the file name to access details of the video.

Edit video pencil

2. On the right side of the screen, under Options, and Captions, select Download File.

Download Captions

3. This will generate a VTT file for your video, and an excellent starting place for your caption file. You can double check these for accuracy and make alterations, as needed.

VTT file view

4. Now… how to apply the VTT file to your video will vary depending on your video application, but look for a symbol that looks like a word bubble or the letters “cc”. Below is where the feature lives in Window’s free video application.

Subtitles in Microsoft's free video application

Playing the Video in Stream with Captions

It is worth mentioning that you can also play your video within Stream with these captions applied.

  • Revisit My Content, Videos and select your video from the list to play it.
  • You may have to toggle on the “cc” button at the lower right for the captions to appear on the video.

Captions in Stream Video

Thoughts?

I would love to hear how you use this feature! Are the captions fairly accurate for you? Did this save you any time or money? Feel free to drop me a line.

Word Editor Tools: Refine Essays and Check for Similarity to Online Sources 📃

Hey the semester is almost over! Whether you are a student working on a final paper, or a professor about to read one (or twenty), you might want to check out Word’s improved Editor Tools.

Formal Writing options, as described.

About Office 365

You will need Office 365 to have this feature, and even within 365 there are various update schedules, so the feature may look a little different.

Here is a hack: if your desktop application of Word does not have the Editor tool, or it is pared down from what I show you below, try uploading the document to OneDrive and opening it in your browser, or if you can alternatively upload the document to Teams and open within the Teams application to see the full feature.

Editor

You can find the Editor tool on the right side of the Home tab and on the Review Tab.

Home tab, Editor button

When you select the Editor, a menu will appear on the right side of your document.

In addition to the old Spelling and Grammar checks that you know and love, there are a ton of new goodies to help you refine your document. Scroll down to look at refinement suggestions, like Clarity and Formality.

Editor on right side of the screen with spelling, grammar, and refinements

Speaking of Formality, you can set the level of writing to Formal, Professional, or Casual, to see varying suggestions.

Formal Writing options, as described.

Details and Settings

Select the suggested Refinements to see further explanations and choose to change or ignore the suggestions.

Conciseness explanation within the Word Editor.While reviewing refinements, you can also change Settings and ask Word to check (or not check) for specific Refinements.

Settings button Grammar settings list.

Check for Similarities to Online Sources

There is a new ability to check the similarity between a Word document and content on the web.

In the Editor pane, scroll down to the Similarity section. Select Check for similarity to online sources.

Check for similarity to online sources button

The document below, for instance, has some potentially problematic text.

Similarity reads: "59% of your text is similar to online sources"

Review the similarities to see where Microsoft has found similar content online.

Similarity checker provides links to locations online where content is similar

Document Stats

One more feature worth mentioning in the Editor tool is Document Stats.

Document stats button

Take a look at the Document Stats to see Readability Statistics, like counts, averages, reading grade level, passive sentences, and Flesch Reading Ease.

List of stats, as described in text.

Thoughts?

Are you planning to use the new and improved Editor tools? I would love to hear from you! By the way, this is a feature you will want to keep an eye on; there will be more to come in future updates. Until then, have a fantastic end of semester, and happy writing!

Top 5 Microsoft Office Tools for Students 💻

As a Microsoft trainer who is also a returning adult student, I have been meaning to write about this topic for ages… Here are my top 5 favorite tools in Microsoft Office for students. These are in random order, because there was no earthly way to pick a favorite!

GIF demonstrating dictation tools

1. AutoSave Important Documents

Have you ever spent hours typing a paper without saving? Sometimes we get away with this sort of thing, and other times… Word, or your computer, might “encounter a problem,” leaving you stranded, with nothing to show but lost time and effort.

Consider using the AutoSave feature in Word by saving your paper to OneDrive.

  • Open up an Office file (for example Word). You will notice an Autosave slider at the upper left of the screen.

Autosave slider

  • When you toggle that on, you will be prompted to save your file to OneDrive.

OneDrive prompt

  • Once you do this, your document will constantly save automatically every time you make a change.

"Saving..." message

Also, by saving your document to OneDrive, you will be able to access your file from any computer or mobile device. Win Win!

2. Dictation Tools

Think about those classes where you have to do a lot of reading and are trying to simultaneously take notes while reading.

Take a look at how quickly I was able to take notes for my fictional CARB101 class, below:

GIF demonstrating dictation tools

Here is how you can try out this feature, and one caveat, you will need Office 365 for this one:

  • Open up a Word document (or some versions of OneNote).  On the right side of the Home tab, click on  Dictation.

Dictate button

  • As you are reading your textbook, read out loud the passages that you would like to include in your notes.

Pro Tip: there are commands within the dictation tools for punctuation, moving to the next line, and so on. If you want to see those, click on the question mark next to your Dictation tools:

Question mark menu with additional dictation commands

3. OneNote: Organize Class Notes and Documents

There are so many ways you can utilize OneNote as a student. In fact, I had so much to say about OneNote that this was difficult to summarize, but here goes:

  • Note Taking: Most obviously, OneNote is an excellent way to organize your class notes:

Notebook for a class

  • Files: You may also find OneNote to be a handy place to organize class handouts, syllabi, and so on. Try inserting content as a File Printout (Insert Tab):

File Printout button

This will serve to attach a file to your notebook, with the addition of a handy readable printout within the note itself.  Here is my fictional syllabus, as an example:

Syllabus attached as a printout

  • Printout File Options: This printout method will work with Word, Excel, PowerPoint and PDF files. Maybe your professor has shared a series of PowerPoints, for instance. Insert as a printout!

PowerPoint inserted as a printout

  • Search Features: OneNote has robust Search features, particularly the Windows desktop version of OneNote.
    • Bonus: when you insert documents as a printout, the content within the attachment is also searchable. Search tools are either found on the upper right of your OneNote screen, or on Mac and online versions, at the upper left via a spyglass.
    • OneNote also has OCR technology, meaning it can search the text within pictures.

Search feature searching within powerpoint printout

There is much more to say about OneNote, but this will get you launched!

4. Word: Citation Manager

You may have heard of EndNote and Zotero, but did you know that Word has a built-in citation manager? Word’s citation manager will allow you to save sources, cite sources in a  variety of formats, and also create an instant Works Cited/Bibliography in a variety of styles.

Bibliography dropdown

 

I have an entire entry on the Citation Manager in Word here: Word: Citations and Bibliography.

Fair warning: You do still have to double check yourself for typos!

5. Word: Style Sets

Will you be writing copious papers for one class, with specific formatting guidelines that will apply to all of these papers? For instance, “all papers must be in Times New Roman, 12 point font, double spaced.” You could set up each paper with these settings one by one, or you could utilize Word’s Style Sets so that you can apply all these special settings to future papers with a click of a button.

Step 1: Changing Existing Styles

  • Open up a blank word document. Instead of changing text by hand, in the Home tab, Styles group, right click on the Normal style.

Right click on Normal style

  • Select Modify.

Modify

  • In the popup screen that appears, make the various font customizations: in this case, Times New Roman, 12 point font, double spaced.

Formatting options

Note: you can do more advanced formatting in the Format dropdown in the lower left.

Paragraph options

… like hanging indents, spacing, etc.

Paragraph options

Step 2: Save the new style

  • Go to the Design tab, Document Formatting group, and click on the arrow at the lower right.

Design tab, dropdown

  • Select Save as a New Style Set.

Save as new style set

  • Choose a name (perhaps the class name), and click Save.

File name, save button

Step 3: Access the style

Going forward, you can apply these exact font settings to a new Word document:

  • Go to the Design tab, Document Formatting group, and click on the arrow at the lower right.
  • You now have a Custom section with your presets ready to apply to the new document.

Custom styles

In other words, with just a couple clicks, you can jump right in to typing your paper with the exact prescribed formatting.

Thoughts?

Do you think you will use these tips as a student? Or even as a non-student? I would love to hear what you think!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Deana Beek

Daniella Blair

Cheyenne Dealy

Cheyenn Power User

Janise Eck

Janise Power User

Shelly Ellis

Shelly Power User

Word: Translation Tools

Do you need to translate a document in a hurry? You might check out Microsoft’s translation tools… you have an array of languages at your fingertips. Let’s take a look.

Demonstration of translation tools described below

How to Translate a Word Document

Here I am looking at a fictional syllabus for my fictional class. The syllabus is written in English, and I would like to translate it into Vietnamese.

CARB101 Syllabus in English

1. Start in the Review tab of the ribbon. Select the Translate dropdown. You will see options to either translate a selected portion of your document, or the entire document. Here I chose Translate Document.

Review tab, Translate dropdown, Translate Document selected

2. The Translator menu will appear on the right side of the Word screen. Here you can select your starting language (or allow Auto-detect to determine this), and then your translation language.

From language: autodetect, To language: Vietnamese selected

3. Once you have selected languages, click Translate.

Languages selected, Translate button visible

A new word document will appear in front of your original document with your translated content.

CARB101 syllabus translated

Note: No translation is perfect, particularly machine generated translation, so it is possible there will be some mistakes or imperfections in the results.

More Applications

You will see these Translation tools in your other Microsoft Office applications, like PowerPoint, Outlook, and Teams. This is an incredible tool to help enhance communication.

Thoughts?

Do you think you will use these translation tools on your Office documents? I would love to hear from you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Mumenah Burnshire

Outlook: Generate a Link to a Calendar Event 📅

With growing numbers of virtual conferences last year, there is little wonder that iCalendar links are more popular than ever. The idea behind  these special files/links is to create a seamless experience for your end users to add an event to their calendar by simply clicking on a link.

GIF of someone clicking on link to generate calendar event

Perhaps you want to post a series of links on a conference webpage so attendees can easily sign up for sessions, or maybe you just enjoy the ease of emailing links to a large recipient list rather than sending individual invitations. Let’s explore how these work.

But first, thank you to my friend Carolyn for inspiring this Byte with this excellent question. 😊

About iCalendar Links

The word “iCalendar” might remind you of an application made by Apple, but this is actually something different. iCalendar is also a media file type that can transmit information about events; like date, time, and location. This file can be opened by others and be used to communicate scheduling information to a wide variety of calendar types; including Outlook, Google Calendar, Apple Calendar, Yahoo Calendar, and many others.

The steps lined out below will cover how to accomplish this with Outlook Desktop Applications.

1. Create a Meeting

  • Start by creating a New Meeting in your Outlook Calendar. Be sure you select New Meeting, not appointment.

New Meeting button in Outlook

  • Select the time and date as you would like it to appear on someone else’s calendar. Because you have selected Meeting, rather than appointment, this event will automatically adjust to the end user’s time zone.  Add any comments that you would like them to see, including Zoom links.

Meeting details completed in an Outlook invitation

  • Even though you have no recipients, you can click Send on this invitation; this will save it to your calendar without attendees.

2. Save the event in an iCalendar Format

On Outlook for Windows/PC

  • Double click on the newly created meeting in your calendar to open it back up. Go to the File tab

File tab

  • On the left menu, select Save As.

Save As button

  • Save to your desktop (or somewhere easy to find). Be sure that Outlook is saving this as an “ics” or iCalendar Format.

Save As screen, iCalendar format selected

  • The file will appear on your Desktop, or whichever save location you  selected in the previous step.

iCalendar event on desktop

On Outlook for Mac

This process is even simpler for Mac users.

  • Find the event on your calendar, and click and drag it to your desktop.

Apple Mac screen, click and drag event to desktop

  • For Mac users, the click and drag feature will create an .iCalendar file automatically.

iCalendar event on mac desktop

3. Upload the iCalendar Event

Now you just need a place to store this file. Your steps may vary from here, depending on the location where you are storing it. Treat this file like you would any other file you are uploading to share on a webpage, like a document or a download.

  • In my case I uploaded it to my WSU Omni webpage.

Omni Campus upload button…I did this just for the purposes of generating a link. From here, I could right click and copy the link for use in other locations, like web pages, or emails.

Copy link on right click menu

Note: You may also chose to store your iCal file in OneDrive, or SharePoint, or any other location where a public link can be generated to an uploaded file.

  • Incidentally, if you are a WSU employee and are interested in learning more about how to create Omni pages or upload documents, Media Resources has some very helpful trainings and information.

The End User Experience

In my example, I inserted the link to my iCalendar file into a conference webpage. Maybe you would choose to do this for each of the conference sessions…

GIF of user clicking on link which generates an Outlook event

When your end user clicks on this link, they will be prompted to open the file with their default mail program (most support iCalendar events).

Open File Prompt

This will open as a meeting to be accepted. Although your name will be on this event as the creator, you will not receive notification when they accept this meeting.

Event invitation with option to accept

The event will now appear on their calendar with all the details you created.

Calendar showing event added

Thoughts?

What do you think about this trick? Do you think you will create iCalendar links for your events? I would love to hear from you. Feel free to drop me a line!

Congratulations, Power Users!

Congratulations to all our Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Kristen Watts

Excel: Custom Dropdown Dashboard List with New FILTER Function 📊

Have you heard about the fantastic new Functions for Excel in 365? XLOOKUP, SORT, FILTER, UNIQUE, to name a few. Speaking of unique… a particularly unique case came up recently presenting the opportunity to create a dashboard of sorts using Excel’s new FILTER function. I had to share this with you, because this was a very cool setup. We ended up with a sleek custom list on one sheet that, prompted by dropdown selection, pulled data from the source into an array formula. Read on for more information…

Filter in action

Before you do, I want to thank Brittany for this question (along with her patience with several tests and fumbles) for inspiring this byte.

Download Workbook

Here is a copy of the workbook used throughout this writeup: FILTER Function Example Workbook

There are three sheets in this workbook:

  • Favorite Color: filters source list by favorite color
  • Interest: filters source list by interest: this one searches a list of values within the cells
  • Source Data

Source Data: The Background

Source data: list of students and information in Excel

This case involved a sheet of Source Data that contained student data: names, details, etc.

We needed to give end users the ability to isolate a list of student names by a specific student interests. A filter or slicer would not work in this case, as there were multiple potential values for “interest.” A student could list one, or several. We wanted to create a dashboard sheet with a dropdown list that would allow users to select an interest and generate a list of results.

I have also seen this sort of situation occur with exported Qualtrics survey results, if multiple responses are allowed. So if you are a Qualtrics fan, you will want to know about this!

Data Validation

Start with the dropdown selection that the unique list will reference:

  • Click into the cell where you would like the dropdown to appear. In our case, A2.
  • In the Data tab, select Data Validation

  • In the Data Validation popup, change Allow to List, and either list the items you would like to appear in the dropdown, or select cells that contain your list.
    • In this case, type the list out, separated by commas.
    • Be sure the spelling matches the source data. This will be important for the formula step.

List selected, source contains list seperated by commas

And voila, there is the dropdown list! On to creating formulas…

Dropdown list for favorite color

By the way, you can read about some more cool Data Validation tricks here: Dynamic Dependent Dropdown Lists.

Also, we talk about Data Validation in more detail Excel Advanced Formulas training, so I would love to see you in a session if you want to learn more about this feature.

=FILTER Function

The =FILTER Function is new with Office 365. This is an array formula, so it will fill into the cells to the right and below where you build the function.

As an introduction, start with the Favorite Color sheet, a more straightforward example.

  • The formula is built in C2. If you have the example file, click into C2, and take a look at the formula bar.
  • The anatomy of the  =FILTER Function is: =FILTER(Array,include,[ifempty])

=Filter function being built in C2, as described below

    • Array is the cells you want to return if the conditions are met. In this case, Columns A through E in the source data.

Source Data, Columns A through E selected as Array

    • Include is the filtering statement. Where would you like Excel to look, and what value is it looking for? In this case, the source data sheet, “Favorite Color” column should equal the value in A2, where Data Validation lives. For the example file it looks like this: “Source Data”!D:D=’FavoriteColor’!A2
        • Note: because the source data is in a table, the range is named “Favorite Color”, instead of being a column name.

Filter function, as described in text

    • The last step is the easiest. If empty: what value would you like to appear if there are no filter results? Perhaps “No Students Listed”, or even empty quote (“”) to leave blank.

Notice how, although we built the formula in C2, it extends into the cells beyond. The beauty of array formulas!

Favorite color selected, list has filtered

And thanks to Data Validation, adjusting the =FILTER formula will be easy.

GIF showing the filter in action

If you are interested in reading more about=FILTER, here is what Microsoft has to say: Microsoft’s Writeup about =FILTER.

Special Case: Search/Filter by Text that Contains a Value

I mentioned previously that there was an additional complication with our case, as the results appeared within a column, with multiple values listed for “interest.” So a simple filter would not have yielded results…

Interests as lists within cells

One excellent source for Excel dilemmas like this is the site ExcelJet. They had an excellent solution to this quandary that I will explain more below, but here is their article: Source: ExcelJet

Solution

Their solution was to nest =ISNUMBER and =SEARCH into the =FILTER function. They explain in fantastic detail in their article above, but this combination of functions will effectively ask Excel to search if a value exists in the contents of the cell at all, even with other values present in the list, as in our case.

Here is the formula for our situation, built in C2 of the Interest sheet.

=FILTER(‘Source Data’!A:E,ISNUMBER(SEARCH(Interest!A2,’Source Data’!E:E)),”No Students Listed”)

=FILTER formula, as described in text

The generic formula is:

=FILTER(rng1,ISNUMBER(SEARCH(“txt”,rng2)))

See the Interest tab in the Workbook Download above to see this formula live and in person.

Filter in action

Contending with Zeros

If you have worked with array formulas before, you may have noticed that, when there are no results, a “0” is returned. For instance, if the address cell is blank in the source data, it will look like this in the =FILTER result:

0 in address column

You can employ a custom number format here if you would like the 0s to display blank.

1. Select the entire column.

2. Under Number formats –> More number formats, select Custom

3.  Under Type, insert this code: 0;-0;;@

More number formats menu, custom selected, code listed below inserted

Much better!

0 is now blank space

Thoughts?

What do you think about this trick? Do you think you will find a use for this combination of features and the new =FILTER function? I would love to hear from you. Feel free to drop me a line!

Congratulations, Power Users!

Congratulations to all our Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

PowerPoint: Presenter Coach (📢New Feature)

Do you ever wish you could obtain some objective feedback on a presentation before the big day? PowerPoint’s new Presenter Coach feature might not be exactly like rehearsing with a real live human… but, incorporating Microsoft’s increasingly impressive AI features, it is quite possibly the next best thing. Let’s take a look.

Prompt" Try speaking a little faster

Presenter Coach

The Presenter Coach is currently only available for people using Office 365 (both Windows and Mac versions).

To Rehearse with Coach, visit the Slide Show tab, left of center, in a new group called Rehearse.

Slide Show tab, Rehearse with coach

When you select this button, you will be taken to a presentation view, with an option at the lower right to Start Rehearsing. Here you can also toggle on or off real-time feedback.

Start rehearsing button with show real time feedback box checked

Real Time Feedback

In an endeavor to show you a wide variety of real time feedback prompts, I provoked my coach with a plethora of bothersome speaking tendencies.

The coach warned me when:

  • I was speaking too quietly…

Prompt: "too quiet"

  • I was using too many filler words…

Prompt: "Try to not use too many fillers like 'umm'."

  • I was speaking too slowly…

Prompt: "Try speaking a little faster."

  • Or too quickly…

Prompt: "try speaking a bit slower."

Report

You can rehearse as long as you would like: you don’t have to rehearse our entire presentation; maybe you only want to examine a portion of your presentation.

When you escape out of presentation mode, you will be taken to a Rehearsal Report.Rehearsal Report, as described in text below

This report will tell you information like:

    • Total time and number of slides
    • Your use of fillers
    • Your use of sensitive phrases
    • Average words per minute
    • A graph with the average pace of speech
    • Originality,” or how much you added to the text printed on the slide

You also might be revisited by information that the coach prompted you with during your rehearsal, like my overusing of filler words, below.

Rehearsal report: zoomed in on notice that too many fillers were used.

If you are interested, here is a more all encompassing list from Microsoft on what the Presenter Coach is analyzing:

Suggestions from Presenter Coach

Thoughts?

I can’t wait to hear what you all think of this new feature! Drop me a line, and let me know what you think.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Excel: Dynamic Dependent Dropdown Lists

In Advanced Excel training, you learned about creating dropdown lists using Data Validation. That was pretty handy on its own… but what if you would like to create a dynamic dependent dropdown list?

For example, below I would like to select a Dessert Type in Column A, and have only those specific desserts appear in my secondary list in Column B.

Two sets of dropdown lists

This can be an incredibly useful tool; imagine lists of cities by state, or students by class, and so on. Lets see how we can set up this slightly more complicated Data Validation.

Exercise File

Interested in following along? Use the exercise file below.

DependentDropdownExercise

Primary List

When you attended Excel: Advanced Formulas with me, you learned all about Data Validation. The first part of this setup will feel just like what we did in training.

1. Select all of Column A.

2. In the Data tab, select Data Validation.

Data tab, data validation

3. In the popup:

      • Under Allow, select List. 
      • Click into the Source box
      • Select the cells with the dessert types: E1 through J1. Click OK.

All the cells in column A now have a dropdown list of dessert types.

Desert Type dropdown

Dependent Dropdowns

So far, this is nothing new… but remember that your goal was to create a dependent dropdown list: i.e. when someone selects “Cake” as a Dessert Type,  you would like only the cake options to appear, and so on.

Named Ranges

1.  Select the entire range that contains the lists: E1 through J12.

    • Notice how Cake has more options than any other type: we will address that later, but for now, cast as wide a net as possible to include all the desserts.

Lists selected, as described

2. In the Formulas tab, Defined Names group, select Create from Selection.

Formulas tab, create from selection

3. Check Top Row and click OK.

Top row selected

This is going to create named lists based on that top row. Which could have a variety of uses… one will be what we will do next.

Apply Data Validation

For Column B, similar to the first step, you want to again apply some Data Validation, but this time, the options you want to appear need to be based off of what users select in Column A.

1. Select all of Column B.

2. Revisit Data Validation: go to the Data tab and select Data Validation.

Data tab, data validation

3. Change Allow dropdown to a List, just like in the first Data Validation

4. For Source, instead of selecting cells, insert the formula =INDIRECT(A1).

    • This is going to reference cell B1 to look at A1, and through the magic of Relative References (also discussed in Advanced Formulas training), each subsequent row will reference the cell to the left of it to access those named lists you established in the “Named Ranges” step.

Data Validation screen, as describe in text

5. If you receive an error message that the Source “evaluates to an error,” click Yes.

    • Don’t worry, this error is not an issue; the formula is currently evaluating blank cells in Column A and doesn’t know what they reference.

Error message

The fun part: try it out!  Select a Dessert type in column A, and look at the customized options that appear.

Secondary dropdown list working in Excel file

We still have work to do, though. Remember how we have lists of different lengths? This is especially apparent when you pick a shorter listed dessert type, like Confections.

This leads to some trailing blank spaces in those named range lists. We will get rid of these blank spaces in the next step.

Another secondary dropdown, showing blanks

By the way, if it looks like you can’t see all your options in that secondary list, there is a slider bar on the right… At this stage, you might have to scroll up on some of the shorter lists. Again, we will fix that shortly.

Dropdown list slider

Remove Blanks

You might remember this trick from a previous Byte, which covers how to Remove Blank Rows in a Document:

Excel: Remove All Blank Rows in a Document

In this case, we are going to remove a few blank spaces with the goal of cleaning up those dependent dropdown lists that contain blanks.

1. Select the range of cells surrounding your lists.

Lists selected, as described

2. In the Home tab, find the Find and Select dropdown and select Go To Special

Find and Select dropdown, go to special highlighted

3. Select Blanks and click OK.Blanks radio button selected

4. Right click over the selected blanks and click Delete. Right click menu, delete highlighted

5. Select Shift cells up and click OK.

Shift cells up selected

That takes care of the rogue blanks in the dropdowns in Column B. Much better!

Secondary list displayed, blanks no longer appear

Cleaning up the Header Row

This is optional, but if it bothers you that your header (in this case cells A1 and B1) both contain unnecessary dropdowns with Data Validation, you can remove those as a last step.

  1. Select Cells A1 and B1
  2. Go to Data tab, Data Validation.
  3. Select Clear All in the lower left, and click OK.

Data Validation Screen

One More Thought

In real life, you would likely set this up with your source lists on a separate, hidden sheet. That would look much cleaner than having all the lists in view, like we did here. But for the simplification of this example, I included them all on one sheet here.

Thoughts?

What do you think, will you use dependent dropdown lists in your workbooks? I would love to hear from you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

    • Caroline Beach

Teams: Breakout Rooms

Breakout Rooms in Teams: I know so many of you have been awaiting the addition of this feature before you were comfortable moving classes and meetings from Zoom to Teams.

Now that Teams Breakout Rooms have launched (hooray!), lets take a look at how they work…

Breakout rooms icon in the menu

Creating Breakout Rooms

You will need to start your meeting in order to access the Breakout Rooms feature. At this time, you cannot set up rooms in advance of the meeting.

In your meeting, you will find a button for Breakout Rooms in the upper right menu of your Teams meeting, represented by two squares.

Breakout rooms icon in the menu

The Breakout Rooms button will toggle out a menu on the right side of your Teams Meeting screen.

In this screen you can control:

A. The number of rooms
B. Assignment of members: automatic vs manual assignment
C. When finished press Create Rooms.

Note: Unlike Zoom, clicking Create Rooms will not move your participants. There will be an additional step.

Create breakout rooms prompt with room settings

Once you create your rooms, a side panel will appear showing the rooms that have not yet been opened. There are a few additional options you can set before you open the rooms.

Breakout rooms screen

Options and Settings

In the upper right of the Breakout Room screen is an ellipses (…). Click on this to access Rooms Settings.

Ellipses options, "rooms settings"

Here you can control:

  • Whether participants will move automatically to rooms when you open them (this defaults to being on).
  • Whether participants can return to the main meeting on their own (this defaults to being off).

Settins: Automatically move participants, and option to allow them to return to the main meeting

Start Rooms

Once your Breakout Rooms are set up and you are ready to move participants, click Start Rooms.

Note: Unlike Zoom, it can take some time, around 30 seconds or so, for attendees to be moved into their rooms.

Start rooms prompt

After the rooms have been started, you can join your attendees in specific rooms by clicking on the ellipses next to each room and selecting Join Room.

Ellipses next to room: join room option

Returning Participants to the Main Room

In the Breakout Rooms menu, where you opened the rooms, will be a new option to Close Rooms. This will return all the participants to the main meeting.

Close rooms button

If you had toggled on the ability for attendees to return to the main meeting on their own, they will have a new option in their Meeting menu:

  • In addition to a Leave button will be a Return button.
  • Be sure they know to click Return if they would like to come back to the main meeting. If they click Leave, they will leave the entire meeting.

Return button added to attendees' breakout rooms

Want to know more?

This post just scratches the surface of breakout rooms. If you would like to see more details and options, take a look at this helpful writeup from Microsoft: Use Breakout Rooms in Teams Meetings

Thoughts?

What do you think, will you use Breakout Rooms in Teams? Or maybe you have used them already? I would love to hear how the feature is working for you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Corey Herl
  • Evan Ohlman

Teams: Polls During a Teams Meeting

Happy New Year, friends! Here is something to celebrate: the ability to launch Polls during a Teams Meeting.  What a fantastic way to add interactive elements to your virtual Meetings! Incidentally, you may have explored the Polls feature in Zoom; the Teams Polls are a very similar concept (but even cooler in my opinion, though of course I am biased). Let’s check it out.

Polls in a meeting

1. Create a Meeting

Start by Creating a Teams meeting however you are comfortable; either by utilizing the Calendar app in Teams, or via the Teams plugin in Outlook. Send this invitation as usual.

Important: You must invite at least one person to this meeting. Otherwise, it is just an event on your calendar, and won’t be treated the same way in Teams.

Meeting with one attendee invited

2. Add Forms to the Meeting

After you have sent the meeting invitation, find your meeting in the Calendar app in Teams. Click on the event, and select Edit.

Edit button in calendar app

In the upper right of your meeting, click on the + sign…

plus sign at top of meeting

Select Forms. This will add a new Polls tab to the top of your meeting’s Edit screen, along with the ability to use Forms to create polls to your meeting.

Forms app in tab list

You may be prompted through some tours about how to use Forms, particularly if this is your first time utilizing this feature.

3. Create Polls

Once you have added Forms to your meeting, you will be prompted to Create New Poll.

Create new poll button

This is very similar to the Forms polls we talk about in Teams Essentials, for use within your Teams (app) posts… though notice you have a few options at the bottom regarding anonymity, sharing results, and whether you would like to allow cohosts to control the polls as well.

Poll created in forms

You can create multiple polls if you would like. They will all be listed in your new Polls tab in the Edit view of your meeting.

List of polls created

4. Launch Polls

It’s time for your meeting! During the meeting, you will notice a new Forms button in the toolbar at the upper right.

Forms button

Click on this button to pop out a side menu with the polls you created prior to the meeting. Each of these can be Launched via a button at the bottom of the poll question.

Polls in a meeting

When you launch the poll, attendees will see voting options in the center of their screen:

Poll launched, options visible

… followed by the results of the poll:

Poll results

If you would like to add additional polls, there is also an option to Create New at the top of the polls list.

Create new button in polls meeting view

After each poll, toggle the menu underneath the question and close, export results, or delete the poll.

View options dropdown

Accessing Results

After your meeting, return to the Edit view in the Calendar app to access the Polls tab. Here you can view or export results.

Meeting edit view, results in polls tab

If your polls were not anonymous, the exported results will show the names and votes of participants.

Results in excel file

Adding Forms During a Meeting

What if you have already joined your meeting, and realize you would like to utilize Forms to take a Poll? No problem.

Now that your Teams Meetings launch in a new window, leaving the rest of your Teams app free, you can easily minimize your meeting window to access the Calendar app in Teams. Then follow the exact same process to edit the meeting and add a Forms tab. Forms/Polls will become immediately available in your meeting.

Thoughts?

What do you think? Do you think you will start using  polls during your Teams meetings? I would love to hear what you think and how you use this feature!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Meredith Dwyer
  • Yumi Foster
  • Amy Drassen Ham
  • Ellen Walker