 In this video, I will show you how to automate the entire process of conducting online training with breakout rooms and using the power of Microsoft Excel pivot tables and slices to summarize and analyze the success of your training program. This is an advanced training requiring good skills in both Zoom and Microsoft Excel. So if you're feeling adventurous, let's get started. I recently helped to organize an online training for Toastmaster club officers using Zoom. The training consists of main education sessions conducted for all attendees and breakout sessions for training on specific club officer roles such as president, VP education, etc. The training program awards credits to members for attending this training session and participants have the option to choose which training program they would like to attend. After the program, we need the attendance data by division to assign credits to the participants by uploading on the Toastmasters website. This is what I did to automate everything. Start by scheduling your meeting in Zoom. Let me give a name to my meeting. Let's call it D20 club officers training. Select the date and time the event will be conducted and the duration of the event. And this is very important. Make sure you select registration required. Zoom has a robust registration process. I have an entire video on creating meetings with registration, branding and custom fields. I will include a link to that video in the description. I highly recommend you check that out if you are not comfortable with the registration process. Once you save your meeting, you can scroll down and we can see that registration is now enabled for this meeting. Click on the edit button and here we can customize the registration process, whether you want the registrations to be automatically approved or whether you want to manually approve each and every registration and other customizations that you can do for your meeting. The important part for us is the questions. When you enable registrations for your Zoom meeting, email and first name are mandatory and the last name is optional, but you can make it mandatory by clicking here and you can include other standard fields available in Zoom. For example, let's say I want to include the country and I want to make it mandatory. The important part for us is the custom questions. If we go back to our training requirements, we can see that we need the club name, we need the role that the participant will attend and all the data needs to be organized by division. So coming to the custom questions, I have created four custom fields, the division, the club name, the role and an additional field. If the participant filling the registration form has to choose from a list of finite choices, for example, like division, we have nine choices. You can enter it as a single answer and that will appear as a dropdown for the person filling the form. The same applies to roles because we have finite number of roles, only seven roles. So we can define them also as a single answer and give an option if the person is not belonging to one of the roles, then he can choose I'm not a current XCOM member. When it comes to a field where the choice is large, for example, a club name, we have more than 100 clubs. If I made it as a single answer, it would be very difficult for the participant to select his correct club name by scrolling through a big list. In this case, I have chosen the option short answer, which means they have to type in the club name. Now this has both advantages as well as disadvantages. The advantages, it's very easy for someone to fill out the registration form. The disadvantages, each person will type the club name differently and that will require some kind of manual corrections from our site. You have to weigh the pros and cons, whether you should make it a single answer or a short answer. Similarly, if you want to capture any additional information, we can also make that as a short answer and make it as an optional field, not a mandatory field. And we are set. The next thing I like to do is to take this registration link and run it through a URL shortener. You can use any URL shortener of your choice, but I like to use Bitly, bitly, and you can click the link here and click the shorten button. And now we have a simple URL that we can share in our PR posters or other material. If I go to a browser and paste the link, it will take me to the registration form that we have just created. To learn how we can do this branding, do check out my other video. As we can see, the division appears as a single answer, which means I have to choose from a drop-down list. The same applies to the XCOM role that I had defined, the seven choices plus an additional choice. The club name has to be entered. And the last field that we had created, as you can see, it's an optional field. We don't have the red asterisks next to it, which means I can leave it blank. And click the register button and that's all there is to it. Once participants start registering for the event, you can monitor the number of registrations by going to the registration section and click the view button. This will give you a list of all the people who have registered for the event. But that's a simple way of doing it. This is an advanced training. So we must do it the advanced way, which is to download the registration data in Excel and to use pivot tables to quickly analyze our registration data. To do that, go to your Zoom account in the report section, select the meeting and choose the registration report for the date of your event. Select the date on which your event is scheduled and click on the search button. In my case, it is already showing it to me here. So all I have to do is click on the generate button. And since we have selected automatic registration in our setup, I can choose all registration and click continue. This will generate the report and click on the download button to download it on your PC as an Excel file. Once the file is downloaded, it will appear at the bottom of your screen. Click on it and that will launch Excel. So this is my registration data, the first name, last name, email, the registration time, the division, the club name, role, whatever we had defined in the registration form. And as we had expected, because we had given the club name as a text field to make it easy for the participants to fill up the registration form, there will be differences in the way the names are entered. And we can correct this in the Excel file. Now using the power of Microsoft Excel, we can analyze this data in different ways. How many registrations are there from each division? How many registrations are there per role? I want to answer these questions just like that. Go to insert and pivot table Excel automatically identifies that this is a table. And you can see it has highlighted the table with the green lines. We will take the analysis into a new worksheet and click OK to understand how many divisions have registered. You can click on the division and drag it to the rows. Identify a field that is unique. Usually that will be the email ID. And if I take this field and I drag it into the values, automatically Excel will understand because it's not a numeric field that we want to count it. So it will count the number of email IDs per division. And voila, I have the registration report by division. If I want to further analyze by division, which roles have registered, select the XCOM role and drag that under columns. And it will show you division wise, role wise, how many have registered. You can use this data to understand what's happening in the registration process, whether we need more registrations or whether we should stop the registrations. The next part of our training is the event itself. And that is perhaps the easiest part of this training because as per the diagram that we had created, we need a waiting room and we can enable that by clicking on security and say enable waiting room. We need seven breakout sessions. So we need to create seven breakout rooms. And we can do that by clicking on the breakout room button and choose the manual option and click create. The first breakout room is created by default, but I can name it. And let's say as per the diagram, we wanted it to be president and then define the remaining six breakout rooms as per naming convention of your choice. Click add room and let's call this VP education, VP membership. So these are the seven breakout rooms that we need for this training session. And the only thing we need to do as moderators is click on the options and make sure that we take the first choice, which is allow participants to choose room. And what this does is when you open the breakout rooms, the participants will get an alert on their screen showing them all the seven breakout rooms and they can click and choose the breakout room of their choice. If you wish to allow participants to return to the main session anytime, you can enable that. And do not check the third option because participants should not automatically be moved to the breakout room, but they should move at their own pace and choice. And that's all there is to it. When your meeting starts, conduct it as a regular zoom meeting. The main sessions will start and when it is time for participants to move to the breakout rooms, all you have to do is click open all rooms. And your job as a tech moderator is essentially done. The final part of this training is to tabulate the attendance data. And this is the most difficult part because we have registration data and we have attendance data, but how do you connect them together? Let's see. You need to go back to zoom and download two reports. The first report we have seen already the registration report that you find in the reports section under meeting. Download the latest version of this report after the event has concluded because that will give you the most up to date registration data. Next, go back to the report section and this time we will download the usage data, which is the actual attendance of our meeting. Click usage. Next, select the date on which your event was conducted. Then click the search button and it should give you your event. We can see how long the event took and how many participants were there. Click on the link which shows you the number of participants. And we have an option to download this report in two forms. One is a detailed report which shows you every participant, what time they logged in, what time they logged off. And that is too detailed for our needs. What we need is a summary report which is the unique users which show us the name of the person, his email ID and how many minutes this person was in the zoom session. Click export and like the previous report, it should download it on your PC as an Excel file. So now we have two Excel files, the registration data and we have the attendance data. Let's open both the Excel files and see what to do next. So I have opened both the Excel files. On the left is the registration data. On the right is the attendance data. How many minutes each participant was there in the zoom session with their email ID. From the registration file, I don't really need the registration time or the approval status. So let me delete both these columns. So I have the names, emails, the division, the club, the XCOM role and on this side I have the email and the duration. The field that links both of this together is the email ID because that's unique. Ideally the registration file should have more rows than the attendance file. If I scroll down I can see I have 191 rows which means 190 registrations but if I look in the other file and I scroll down I can see 216 and that doesn't make sense because only those who have registered could have attended our meetings and here it shows us that there is more records than registrations. So there must be some problem in the attendance data and I need to fix that first. The problem is if a person has logged off and logged in again and given slightly different names then zoom will consider it as separate records but if I summarize the attendance time by email ID I should be fine. And how can I do that? Again I can use the power of the pivot tables to summarize the attendance data by email ID. Click on the attendance data, click insert and click pivot tables. Excel will automatically identify the table data and I will create a new worksheet. In this I will enter the email ID and I will select the total duration and place it under values and this will give me the total duration summarized by each email ID and this should be less than 190. And if I scroll down I can see it is 160 rows. To make my analysis easy I'm going to copy this data, press Ctrl C to copy and I will go to my registration worksheet and add a new sheet here and right click anywhere and say paste values. So now I have in the same excel file a registration data in one sheet and the attendance data in the other sheet. Let me rename the sheet as attendance. Now if I go to the registration report I can find out which person attended the session and how long they were there in the session. To do that let me add a new column here and I'll call it minutes attended. Now to tell excel to put the number of minutes against each of the email ID we are going to use a formula called vlookup which is a way of saying that look at this email ID in this sheet and bring me the attendance data in the registration file. For those of you who don't know what is vlookup it's very simple formula type equal to and vlookup open the brackets and for the lookup value we will go for the email ID which is column C here. So I will type C2 and then I will go to the attendance data to do that I will click on the attendance sheet select the whole table a1 to b160 with a comma and tell excel to get me the value from column number two which has the attendance minutes. So let me type two and I want an exact match not approximate match so I can just type false. So this is how the vlookup formula works you take the email ID you search it in the attendance data and you bring the second column which has the attendance minutes press enter. Now all I have to do is copy this formula to the rest of the rows but before I do that I have to make a small change to this formula and this is related to the attendance data which has the data from a1 to b160 and as I copy I want to tell excel not to change anything here it should always be a1 to b160 and we do that by putting a dollar sign in front of a and 1 and b and 160 absolute values instead of relative values press enter and now I can safely copy this formula to all the cells and it brings me for every person how many minutes they have attended this session beautiful the only problem is for people who did not attend it brings this error which says not available the data is not available because he did not attend and instead of having that what I would like to do is I would like to put zero there are many ways to do this but the way I like to do it is to use a function called if is and a that means there is no value available for this whole set then simply put zero else copy this formula that should do it if I now copy this formula to all the rows then it will show zero for people who did not attend the session one final thing I like to do is to make sure that I have really picked up all the attendance data and the way I can do that is first of all let me summarize the total minutes attended by everyone by going to the bottom here and putting a sum function so 17,884 minutes is the total attendance I'm getting in this sheet let me go to the attendance sheet and summarize that too if I say some I get 18,087 so there is a small difference let me see how much is the actual difference so let me put the total attendance from here and if I say equals to this minus this I see there is a difference of 203 minutes somewhere and the answer lies in the host the host is the only person who does not register for this event and login through the registration link he is the person who launches the meeting so let me go to the attendance data and check that by searching for 203 minutes and I can see that this is the ID of the host the person who created this event and there is no registration record for that person so now I know that the attendance data is perfect all I have to now do is to create the final attendance data to be uploaded to the Toastmaster website how do I do that once again select insert pivot table go to the new worksheet to generate the attendance report I can select division the xcom role as the column and the email ID as count you may want to filter the attendance to make sure there is a minimum criteria so for example let's say I put the minutes attended in the filter it will appear as a filter for me and if I click on this arrow I can see there are some people who have attended for less than 10 minutes or less than 20 minutes so if you want to eliminate this as non attendance all we have to do is select multiple items and remove this attendance from the final report so minimum 20 minutes let's say is the requirement and if I now click okay now I can see only 150 attendees from 190th that means many have attended the session for a very brief period of time if I want to include the club name in this report select the club name and drop it in the rows and now we have the report by division by club the club name needs to be cleaned up but this is something we need to do as part of a cleanup process if you want this data in table format you can click on design and report layout and choose show in table form and this is easier to manage and the last thing is I did mention that I would talk about slices and slices is a way of filtering your data and you do that by clicking on pivot analyze and insert slicer and let's say I want to insert a slicer for the XCOM role and what I can do with slicer is select only those roles that I want to include in my attendance data so it will exclude the non XCOM members very cool so that was an entire overview of creating online events using zoom right from registration running the event and then getting the attendance and tabulating the results I hope you found this resource useful and I wish you all the best