Oniva Event Blog

Helpful information for event managers.
Discover exciting articles about the implementation of unforgettable events.

Excel cheat sheet for guest lists

When processing guest lists, event managers are often confronted with a mountain of data that needs to be processed. Processing guest lists in Excel is often time-consuming, nerve-wracking and error-prone at the same time. In this article we explain the best cheats for guest lists in Excel that help to reduce the effort to a minimum.

Anna Fredholm

December 2, 2022

•

5 min

Format data as a table

To format an Excel file as a table, all data must be selected and then transformed into a table via 'Insert > Table'. Tables allow filtering and analyzing data in columns.

Split data into multiple columns

In some cases, multiple data are displayed in one column. An example of this is .CSV (Comma-Separated Values) files. A .CSV file can be converted into an Excel format, where the data is split into multiple columns via 'Data > Data Tools > Text to Columns'. In the subsequent Text-to-Columns Wizard, the file type 'Delimited' must be chosen, and then the delimiter, for example, 'Comma' for .CSV files. After completion, the data will be split into multiple columns.

‍Identify or clean duplicates

Duplicates in an Excel file can either be highlighted with color or automatically removed.

To highlight duplicates, the desired column can be selected and then, via 'Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values', a color can be chosen to mark duplicate values.

To remove duplicates, the desired column can be selected and then, via 'Data > Data Tools > Remove Duplicates', all duplicates can be removed.

‍Find and replace data

Sometimes, data need to be adjusted throughout an entire file. For example, all guests of an account manager may need to be reassigned to another manager, or the language of contacts may need to be formatted in a certain way for further processing in another tool. Excel's 'Find and Replace' function can be used for this purpose.

Via 'Home > Editing > Find & Select > Replace' or the shortcut 'Control + F', the dialog box can be opened. In it, either all can be replaced at once 'Replace All', or the data can be adjusted individually 'Replace' or 'Find Next'.

Display count

To quickly determine the count of selected data fields, the 'Count' can be used at the bottom right. This way, all guests who, for example, have registered for a certain program item can be selected, and then the number can be read at the bottom.

Merge data through VLOOKUP

VLOOKUP is a function in Excel used to map datasets together. Through VLOOKUP, for example, two tables with information about the same guest can be merged.

Example: If these two tables exist and the account ID should be taken over for the guests in the first table where available, the VLOOKUP can be searched and selected via 'Insert Function'.