Excel is the bane of many professional's existence and it is a godsend for many others. For event organizers who are interested in managing an event, there are often tools better suited for event planning tasks than Excel. However, there are some scenarios where Excel (or other spreadsheet tools that function similarly like Good Sheets) is the ideal platform to manage contacts, analyze data and much more.
This article (the first in a series about using Excel) will show readers how to put various Excel formulas to use in order to accomplish common event planning tasks. If you have a question about Excel that wasn't answered in the article, chat us in the comments and we may include the answer in a future article!
1. COUNTIF Formula
=COUNTIF (Range, Criteria)
Let's assume I'd like to count the number of attendees visiting my event from the United States. I can use COUNTIF to simply count the number of times "United States" is mentioned in a specific row or column.
The formula would look like this: =COUNTIF (A2:A10, "United States"). This formula tells Excel to look in column A between rows 2 to 10 for the term "United States." Excel will place the total number of matches in the cell I have selected.
2. AVERAGEIF Formula
=AVERAGEIF (Range, Criteria, [Average Range])
With the AVERAGEIF Forumla I can calculate the average something that fits a specific criteria. Let's say that I want to find the average event registration price an attendee from the United States paid to come to my event.
I can use the AVERAGEIF Formula to calculate this: =AVERAGEIF (A2:A10, "United States", B2:B10). This formula tells Excel to look for the term "United States" in column A, then to average the corresponding numbers in column B.
3. CONCATENATE Formula
=CONCATENATE (TEXT 1, TEXT 2, ...)
Often when you export data to Excel or other spreadsheet tools, first and last name are in separate fields. If you want to join these or other pieces of information together in the same field, use Concatenate.
Assuming I want to join first and last name together I'll write: =CONCATENATE (D2, " ", E2). The ," ", between cells tells Excel that there should be a space between first and last name. To apply this formula (or any other formula) to the rest of the cells in the column, I can simply click, hold and drag down the formula by selecting the small box in the bottom right hand corner of the cell I've already applied the formula to.
4. VLOOKUP Formula
=VLOOKUP (lookup_value, table array, col_index_num, [range_lookup])
If you want to associate data in Excel, VLOOKUP is your best friend. This formula can work across different worksheets, which means it can make searching for "needle in the haystack" esque information much easier.
In this example, I am trying to find the registration code associated with a few different event attendees. To do this I'll write: =VLOOKUP (A2, L1:L19, 2, FALSE). The A2 tells Excel to look for the name "George Washington" in another part of this spreadsheet. L1:L10 tells Excel to look for the registrant name in that area of the spreadsheet, and "2" says that the information I want to associate with the registrant name is in the second column of the table array I've selected. Finally, "False" means that the event registrant name must exactly equal "George Washington." In most cases, you'll want to use "FALSE" when using VLOOKUP.
5. Insert A Smart Checklist
For many using Excel, there are number of hidden tools available that can turn the spreadsheet tool into an even more powerful platform. To activitate these tools go to: File (for PC) or Excel (for Mac) > Preferences > Ribbon & Toolbar > check off "Developer."
Then go to the "Developer tab" and click the check box icon. This will insert a check box in your spreadsheet. Usually you'll want to remove the text that accompanies the check box, to do that right click on the check box select "Edit Text" and delete the text. Right click the check box again and select "Format Control" to make sure the value is "Unchecked."
Now copy and paste the check box to all the cells you'd like it to appear in. After that, select a cell link for each check box. Once done and after using Conditional Formatting, you can build an event check in process that looks like the one below.
6. Calculate Days Remaining With IFERROR Formula
=IFERROR (value, value_if_error)
For organizers who would rather not use a dedicated event project management tool, than Excel may be an acceptable substitute in some cases. To keep everyone organized, adding a "days remaining" to complete a project column can be helpful to highlight projects that are overdue, in progress and complete.
To calculate this I'll use the IFERROR formula: =IFERROR (B2-$F$2, "Done"). This formula substracts the due date (B2) from today's date (F2) and either returns a value, or "Done" if the formula results in an error. Since the only conceivable error would be because the Due Date is filled with "Done" instead of a date.
Note that I "lock" the today's date cell by placing "$" in front of the column and row. If I failed to "lock" the today's date cell, Excel would autmoatically shift the location of the cell down one row at a time, F2 would become F3, F4 and so on as I applied this formula to the cells below.
To calculate today's date, I use the simple formula: =TODAY. To highlight the cells, I use Excel's conditional formatting functions, which can be accessed by selecting "Home" > "Conditional Formatting".
Next Steps To Mastering Any Event
As mentioned earlier, stop by the Bizzabo blog soon for additional articles about using Excel and other tools to manage tasks, analyize data and much more. While Excel can be a challenging tool to master, undoubtely it can help solve many common event planning problems. Try using these formulas to get a grasp on the event planning process.
For an even more robust tool, consider learning more about Bizzabo, which is an award winning event-success platform designed to help event organizers plan, market and measure events and conferences. Click the button below to learn more!