Microsoft Excel is in every event planner's toolbox, especially when it comes to editing and managing guest lists. Although everyone uses Excel, the level of efficiency varies.
In this article, we are going to show you 12 basic but useful Excel workflows that can effectively help you to reduce your time managing guest list data.
1. Navigating your guest list with the CTRL key
Instead of moving the scroll bar up and down, or scrolling endlessly with your mouse wheel, do you know how to jump between the first row/column and the very last one? The answer is in front of you on your keyboard. Press "CTRL + Direction" keys to jump straight to the end of your spreadsheet.
2. Deleting empty rows at once
You have deleted a single empty row many times but doing it one by one for a very large guest list can be a tedious task. Use the Ctrl key or utilize Excel's filter function to remove all blank rows from your spreadsheet. Watch the video for a step by step guide.
3. Auto-fill columns with the same values
This video quickly shows you how to auto-fill columns with the same values (A,A,A,A), or number sequences (e.g. 1,2,3,4 and 2,4,6,8). Just fill in the first two cells, mark both of them and drag down the bottom right corner of the cell to fill all cells below accordingly.
4. Faster Copy & Paste
You may already know how to speed up copy & paste by using the keyboard "Ctrl + C" and "Ctrl + V", but did you also know you could paste even faster by simply pressing "Enter"?
5. Split data into two columns
Event management systems often require separate first name and last name columns, while you may receive guest lists with a "full name" column instead. You can use Excel's "Text To Columns" feature to split up the full name column.
6. Merge columns into one column
Similarly, sometimes you may want to merge the first name with the last name column or any other additional columns. In this case, you can use the "Concatenate" formula.
Pro tip: After merging columns, the applied formula breaks when it is copied from one sheet to another or uploaded to a guest list software. To avoid this, always copy and paste your merged column as Values.
7. Fixing and formatting phones numbers
When receiving guest lists, you may often see phone numbers that are formatted incorrectly. This can cause errors when you try to upload the guest list to a CRM system or check-in app. For example, some phone numbers have spaces in between and some do not; some phone numbers have country codes in different formats etc. Check out this short video to learn how to fix all these formating issues with just a few clicks and formulas.
8. Highlighting & removing duplicates
A common task when managing guest list often includes identifying, cross-checking and removing duplicate values. Excel's highlighting features gives you the tool to do just that.
9. Highlighting & removing duplicates in a large guest list
When managing a large guest list, you may want to remove the duplicates with just a few simple clicks. In this case, you could use the "Remove Duplicates" function to delete all duplicated data.
10. Extracting a list of unique values
Quite often, you most likely need to extract information quickly out of a given guest list. A common task includes "how many companies have registered up to this point?" We cannot just sum up the company column because there may be guests who registered from the same company. This short video shows you how to quickly extract the total number without any duplicates.
11. Assigning a random number to each guest
Sometimes you may want to assign a random number to all guests to give them their personal QR code value, a unique ID number, or lucky draw number. Instead of assigning numbers to the guests manually, you can use the "RAND" or "RANDBETWEEN" formulas to assign random numbers to each guest.
Pro tip: be aware that this function could generate duplicate values, therefore, it is recommended to highlight potential duplicate values after generating the random numbers.
12. Get the number of total guests assigned to each table
When managing seated events, you may often add table number column to your guest list. After assigning a table number to each guest, you may want to double-check how many guests are assigned to each table. Instead of checking this manually, you can use the "Subtotal and Count" feature to let Excel count and display them all for you.
Full Episode
Read more here for useful Productivity Tips and how to work more effectively.
Central Pacific is a Hong Kong-based event technology company. We provide innovative solutions for guest list management, entry management, and guest engagement solutions (e.g. lucky draw and live polling). Contact our event technology specialists to receive a free consultation for your next event.