An Excel spreadsheet full of numbers can be… well, rather intimidating. Where on earth do you start?

But Microsoft Excel has a number of features (simple once you get to know them) that can help you gain really useful insights from what is otherwise a jumble of data.

We’re going to run you through five of them, first explaining what they actually do and then showing you step by step how to do it. We’ll also explain why they’re each relevant to you, as event professionals, and how it can help you make short work of your Eventbrite Sales and Attendee Summary reports.

So let’s get started with these five need-to-know Excel tips…

Related: How to create and export Sales Summary reports

1) VLOOKUP

What is it?

VLOOKUP lets you search for specific information in your worksheet. It can find a specific value and then display a corresponding value in the same row i.e. you might search for a product number in order to see an item’s price.

It’s similar to looking up a person’s name in a telephone book to get a telephone number. VLOOKUP looks at a value in one column, and finds its corresponding value on the same row in another column. It’s especially handy if you have a large amount of data, and coming from different sources.

How can it help you?

If you wish to look up any information about a particular order or attendee, such as ticket type, date ordered or number of tickets ordered, this function let’s you do it in a jiffy.

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 19970. The formula would look like this: =VLOOKUP(19970

Important note – if you’re searching for a text value (or a code which contains both letters and numerals), it’s necessary to put it in quote marks i.e. =VLOOKUP(“poster”

Next you need to tell Excel where to look for the data, by specifying the cell range (the look-up data must be in the first column, so you’ll need to rearrange the columns if it’s not). So if your data starts at A2 and spans to C70 you would enter A2:C70.

The formula now looks like this: =VLOOKUP(19970,A2:C70

You also need to specify in which column the associated data you wish to find is located (in this case the email address). However, you don’t do this by entering the column number; you need to physically count what number column it is. So if it’s column C, it’s the third across and you would enter ‘3’, like this:

=VLOOKUP(19970,A2:C70,3

Finally, tell Excel if you’re looking for exact matches or approximate matches by entering either FALSE or TRUE (FALSE will return exact matches only) and adding a closed bracket. Your complete formula should look like this:

=VLOOKUP(19970,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!

Related: How to edit and export attendee summary reports

2) Pivot Tables

What is it?

Pivot tables are one of Excel’s most powerful features, allowing you to extract meaning from large, complex data sets. Essentially, it lets you group data together in different ways in order to make comparisons and see trends.

For example, if you wanted to find out the demographic of attendees who purchased premium tickets, you could 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) in order to build a complete profile.

How can it help you?

Being able to get into the nitty gritty 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 being purchased by females aged 30-40 living in London then you could more deliberately target this demographic next time round.

The more data you have, the greater the intelligence you can glean, so be sure to make use of Eventbrite’s Custom Questions feature when setting up registration for your event. You can even use pivot tables across data from multiple events, simply use ‘Event Name’ as your unique identifier and then break the data down. See how much gross revenue you made from each event, your male/female split or which social media channels led attendees to your event.

How do you do it?

Click inside any single cell in your dataset (not a blank one), go to the ‘Tables’ tab and insert a table. Now select ‘Summarize with PivotTables’ 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 PivotTable Builder you will see a box headed ‘Field name’. Here you will see listed all the names of the columns in your worksheet.  In order to tell Excel what to organise the pivot table by, 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 we want to compare that data with, so in this example you’d select ‘Age’ and drag it into the ‘Values’ area. The pivot table will be immediately populated and you will be able to see how many of each ticket type were sold to which age group.

3) Consolidate

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 combining data when several users work with different instances of the same file.

How can it help you?

If you have different departments or teams keeping separate budgets but using the same template, you can work out total event spend and carry out other calculations using the consolidate feature.

How do you do it?

There are two ways to consolidate data: by position or by category, but since consolidating by category is similar to using a pivot table, we’ll focus on consolidation by position.

This is for when the data in the worksheets to be consolidated is arranged in the same order and uses the same labels – ideal for different data sets created from the same template. You can even have it set up so it updates automatically when the source files are updated.

For detailed step-by-step instructions of how to set up consolidate by position in Excel see this Microsoft document.

4) Conditional Formatting

What is it?

Conditional formatting in Excel enables you to format cells according to their value. You can highlight cells with a certain colour, apply a certain colour to text or have it appear bold when the value of the cell meets the format condition, for example if the value of the cell is greater than 100.

If the value of the cell 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 formats. This allows you to have different formats depending on the value of the cell.  For example, if the value was greater than 200, you can display the text in red, but if the value is between 100 and 200, display the text in green.

How can it help you?

The conditional formatting feature is really useful for highlighting important items in a sea of data. Easily see expenditure over a certain threshold on your event budget, mark up 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 using your mouse. On the ‘Home’ tab, click ‘Conditional Formatting’, ‘Highlight Cells Rules’. You have a number of options that enable you to format cells with a value that’s greater than x, less than x, between x and x, equal to x (i.e. contains the same value as the one specified), contains specified text, a date which falls in a specified range or duplicate values)

Once you have made 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, but if you want to format it another way select ‘Custom format’. Once you press ‘OK’ the formatting will be in place.

You can clear formatting by selecting ‘Clear rules’ and choosing either ‘Entire sheet’ or ‘Selected cells’.

5) Correlation

What is it?

The correlation feature in Excel tells you how strongly two variables are related to each other. It helps you to understand and explore the linear relationships between two or more sets of numbers. For example, if you wanted to find out how attendee satisfaction score was affected by the number of sessions attended, you could do it using the correlation feature.

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?

As well as being able to look at the impact of session attendance on satisfaction, there are myriad other factors, such as ticket price paid, which day of the event they attended, age of attendee, or even room temperature – as long as it’s numeric, you can measure it! This type of insight (that you might not find expressly specified on feedback forms) can help you make meaningful changes to your events.

How do you do it?

First you need to make sure that the data you wish to analyse is located next to each other in two columns (it must be numeric data). 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 labelled ‘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 in ‘Array 2’ and select the second data set (in our example, 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 is generally accepted that the following scale can be used to estimate the effect size:

+/= .5 Large

+/= .3 Medium

+/= .1 Small

So 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 – that 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 are empty or if the standard deviation of their values equals zero.

Conclusion

By mastering a few simple formulas, Excel can take your sales and attendee reports, and other event data sets and provide you with valuable additional intelligence. It might take a little bit of practise to become familiar and comfortable with its functions, but once you unleash your inner geek there will be no stopping you!