An Excel spreadsheet full of data on ticket sales, budget, and registration information can be an incredibly helpful resource as you plan your event.
By using Excel for your event planning, you’ll discover it’s more than a simple event checklist. It’s a versatile application with plenty of tools to help with event management. We’ll examine the five most relevant functions, explain what they do, and show you how to use them. Find tips on incorporating your Eventbrite Sales and Attendee Summary reports, too, and don’t forget to explore these free templates for planning, organising, and hosting your event.
How do event planners use Excel?
Creating an event management spreadsheet in Excel helps track and manage critical data related to your event. Excel lets you easily build and manage your budget, track ticket sales, and automatically combine data from different spreadsheets. Discover these key functions for event planning in Excel.
1. VLOOKUP – search for specific data
What is it?
VLOOKUP lets you search for specific information in your worksheet by looking at a value in one column and then finding its corresponding value on the same row in another column. It’s convenient if you have a large amount of data coming from different sources.
How can it help you?
This function helps you quickly look up details about a particular order or track attendance with information like ticket type, date ordered, or the number of tickets ordered.
How do you do it?
Choose any blank cell on your spreadsheet and enter:
=VLOOKUP(
Now you need to tell Excel what to look for. Let’s say we’re looking to match a ticket sales order number (known as the unique identifier) with the customer’s email address, and the sales order number is 10350. The formula would look like this:
=VLOOKUP(10350
Important note: If you’re searching for a text value (or a code that contains both letters and numerals), you must put it in quote marks like this:
=VLOOKUP(“poster”
Next, tell Excel where to look for the data. Specify the cell range (the look-up data must be in the first column, so rearrange the columns if it’s not). If your data starts at A2 and spans to C70, you would enter A2:C70.
The formula now looks like this:
=VLOOKUP(10350,A2:C70
Specify the column where the data you’re looking for is located (in this case, the email address). Don’t do this by entering the column number; you must physically count the columns. So if it’s column C, it’s the third across, and you would enter 3 like this:
=VLOOKUP(10350,A2:C70,3
Finally, tell Excel if you’re looking for exact matches or approximate matches. Enter FALSE for exact matches only or TRUE for approximate matches. Then add closing parentheses. Your complete formula should look like this:
=VLOOKUP(10350,A2:C70,3,FALSE)
Now press enter, and it should return the information you’re looking for – no need to search through reams of data!
2. Pivot tables – group data to find trends
What is it?
The Pivot Table is one of Excel’s most powerful features. It lets you group data together in different ways and allows you to compare and see trends. This feature is handy for extracting meaning from large, complex data sets.
For example, if you wanted to find out the demographic of attendees who purchased premium tickets, you would create a pivot table using ticket type and age, ticket type and gender, or ticket type and income bracket (assuming you had collected this data at registration) from which to build a complete profile. This function makes your Excel ticket tracker even more helpful.
How can it help you?
Getting into the details of your sales and attendee data is invaluable for guiding your future sales and marketing activity. Taking the example above, if you found that the premium tickets were primarily purchased by females aged 30-40 who live in New York City, you could target this demographic more deliberately next time.
The more data you have, the greater the intelligence you can gather. Make use of Eventbrite’s Custom Questions feature when setting up your registration Excel template for your event. This feature uses an Excel template to easily store and organise your event data. You can even use pivot tables across data from multiple events – use Event Name as your unique identifier and then break the data down. See how much gross revenue you made from each event, your demographic splits, or which social media channels led attendees to your event.
How do you do it?
Click inside any single cell in your spreadsheet (not a blank one), go to the TABLES tab, and insert a table. Now select Summarise with Pivot Tables from the menu bar across the top of the page. Click OK on the pop-up menu, and Excel will create a blank pivot table.
In the Pivot Table Builder, you’ll see a box headed Field name. You’ll see all the names of the columns in your worksheet listed here. To tell Excel how to organise the pivot table by field, drag and drop one into the Row Labels section below. Continuing with the example above, this would be Ticket Type.
Now we need to tell Excel what data we want to compare. In this example, you’d select Age and drag it into the Values area. The pivot table will be immediately populated, and you’ll see the number of each ticket type sold to the various age groups.
3. Consolidate – combine data from multiple worksheets
What is it?
The Consolidate feature allows you to merge and summarise data from multiple worksheets into a master worksheet. It’s a great tool for event management in Excel, allowing you to combine data when several users work with different versions of the same file. For example, you may have used an event registration Excel template, but you need to draw a collection of data rather than scrolling through individual registrants.
How can it help you?
Suppose you have different departments or teams keeping separate budgets, but using the same Excel event budget template. The Consolidate feature will let you work out the total event spend and carry out other calculations.
How do you do it?
There are two ways to consolidate data: by position or by category. Since consolidating by category is similar to using a pivot table, let’s focus on consolidation by position. For this to work, the data in your spreadsheets must be arranged in the same order and use the same labels. Ideally, the different spreadsheets would have different data sets as well. You can even set it up so it updates automatically when the source files are updated.
Search Microsoft for detailed step-by-step instructions on consolidating by position in Excel.
Top tip: Check out this event budget template to help you keep track of your budget.
4. Conditional Formatting – highlight data that meets criteria
What is it?
Another Excel for event planning function is Conditional Formatting, which lets you format cells according to their value. Highlight cells with a specific color, apply a certain color to text, or have it appear in bold type when the cell’s value meets the format condition. For example, if the value of the cell is greater than 100, make it stand out by having the cell turn green and the text go bold.
If the cell’s value does not meet the format condition, the cell’s default formatting is used. A cell can have up to three format conditions, each with its own format options. This lets you have different formats depending on the value of the cell. For example, if the value is greater than 200, you can display the text in red; if the value is between 100 and 200, display the text in green.
How can it help you?
Excel’s Conditional Formatting feature is useful for event management, as it highlights important items in a sea of data. Easily see expenditure over a certain threshold on your event budget, identify attendees with an allergy in your catering plans, or illustrate bookings made within a certain period.
How do you do it?
Select the cell range you wish to format. On the HOME tab, click Conditional Formatting > Highlight Cells Rules. You’ll have options that allow you to format cells containing a value that’s greater than, less than, between, and equal to X. Try formatting cells that contain specified text or a date that falls within a specific range.
Once you make your selection, a pop-up will appear where you can enter the relevant value and choose how you want to format the cell. Excel provides a number of formatting suggestions. Or you can format it another way by selecting Custom format. Once you press OK the formatting will be in place.
Top tip: Clear formatting by selecting Clear rules and choosing either Entire sheet or Selected cells.
5. Correlation – learn how variables are correlated
What is it?
The Correlation feature in your event-planning Excel sheet tells you how strongly two variables are related to each other. It helps you understand and explore the linear relationships between two or more sets of numbers. For example, find out how the attendee satisfaction score was affected by the number of sessions attended.
Two variables can be positively correlated (more of one means more of another) or negatively correlated (more of one means less of another).
How can it help you?
Use the Correlation feature in your Excel attendance tracker to measure how various factors may have affected one another. There are countless other factors to choose from. In conjunction with measuring the impact of session attendance on overall satisfaction, look at the ticket price paid, which day of the event they attended, age, or even room temperature – as long as you have the numeric data, you can measure it. This type of insight (which you might not find expressly specified on feedback forms) can help you make meaningful changes so that your events can consistently meet and exceed audience expectations.
How do you do it?
First, ensure the data you wish to analyse – numeric data – are located next to each other in two columns. Next, go to Insert and select Function. Type CORREL into the search box. Double-click on CORREL when it appears in the menu.
Click in the box labeled Array 1 and then highlight the relevant cells on your worksheet. In our example, this would be the column containing the attendee satisfaction scores. Next, click the Array 2 box and select the second data set. In our example, Array 1 would be the column containing the attendee satisfaction scores, and Array 2 would be sessions attended.
Excel will generate a table containing figures. Look for the figure with a decimal point – this is the correlation between the two sets of data. In statistics, it’s generally accepted that the following scale can be used to estimate the effect size:
+/= .5 Large
+/= .3 Medium
+/= .1 Small
Let’s say the result of our test was .59 (a positive number). We would conclude that attending more sessions left attendees more satisfied. If the result was -.59, we would conclude the opposite – attending more sessions made them less satisfied.
Important note: If you get an error from your Excel Correl function, this is likely to be one of the following:
- #N/A – occurs if the supplied arrays are of different lengths
- #DIV/0! – occurs if either of the supplied arrays is empty or if the standard deviation of their values equals zero
Simple expert tricks for your event planning spreadsheet
If you’re looking for more ways to make use of your event planning spreadsheet, give these expert Excel tricks a try.
1. Forecasting
The Forecasting function in your event planning Excel sheet can give you insight into trends and can even predict future trends such as ticket sales. For example, comparing ticket sales from a previous event to ticket sales from your current event can create a forecast that can help you anticipate future sales. This function can help you compare historical, time-based data.
How do you do it?
Select two data sets that correspond to each other. For example, you might select two columns indicating your previous two years of ticket sales. On the DATA tab in the Forecast group, select the Forecast Sheet icon. You can choose between a line chart or a column chart in the Create Forecast Window. Then, select a date for the forecast to end, and click create.
2. CONCATENATE
The CONCATENATE function lets you combine two or more text strings into one string. This can be useful if your attendees’ names are split into a first-name column and last-name column. The CONCATENATE function allows you to merge the names into one cell. Use this function for longer strings of text as well.
How do you do it?
In the cell where you want the string of text to appear, type in the formula as follows:
=CONCATENTATE(.
Then, enter the cells you want to include after the open parenthesis. If you want to add a space between text, include quotation marks in the string. A sample formula looks like this:
=CONCATENATE(A2,” “,B2)
3. Color coding
Color coding your event management spreadsheet can help you manage large amounts of data. Assigning colors to certain categories of data can help you read the spreadsheet more easily and find the information you need quickly. For example, you might want to color-code ticket types.
How do you do it?
Use Conditional Formatting to color code your event management spreadsheet in Excel. Select Format and then Conditional Formatting. Decide how you want to color code the data. You can choose specific colors for ranges, exact numbers or words, and more. As an example, you can select equal to and type in the number, word, or phrase that you want to color code. Then, choose the color and press OK.
4. Goal Seek
Event management in Excel can help you set goals for your event. Use the Goal Seek function to determine how you can hit your sales goal. For example, use Goal Seek to calculate how many tickets you have to sell to meet your financial goal.
How do you do it?
Select Tools and then Goal Seek. Complete the dialog box that pops up. Enter three pieces of data: set cell, to value, and by changing cell. Using the ticket sales example, the set cell will indicate your ticket price. The To Value cell will be your sales goal. The By Changing cell is the reference for the input cell you want to adjust (so, in this case, your current ticket sales). Goal Seek will determine how many tickets you need to sell to meet your sales goal.
Use Excel to your advantage when planning your next event
By mastering simple formulas, use Excel to provide valuable insight into your sales and attendee reports and other event data sets – and to make your event-planning spreadsheet in Excel more useful than ever before. Pair this spreadsheet with Eventbrite, an all-in-one management system that helps you sell tickets, engage your community, and confidently plan an event. Learn more about how to use Eventbrite’s event management software to get started.
