15 Excel Tricks to Manage Your Event

Excel can seem complicated and overwhelming with its grids, formulas, number orientation and analytical approach. It can also go inexplicably wrong at times seemingly without realizing what you have even done! Don’t let this put you off though because Excel is an essential tool for managing events. If you only know some basic formulas you could be missing out on some sneaky hints and tools that can make event planning and management much easier. Using Excel to analyse data, create reports or charts as well as keeping databases tidy can help you to streamline your event management processes.

With forecasting, easy data comparison and searchable databases, Excel can offer so much more than most people imagine and it is a lot simpler to learn than it looks. From some basics to advanced tricks, here are some new ways to manage your event data that will benefit you by keeping you organized and professional and easily giving you the information that you need.

1. Multi-Sheets

Let’s start with the basics. Event planners need to store and access a lot of data quickly. An easy way to do this is by using multiple sheets, or tabs, within the same document. This is where multi-sheets comes in, in the bottom left hand corner of your current sheet there is a plus symbol which when clicked on creates a new sheet within the same document and can help collate all of your data in the same place.

You can easily rename each sheet by double clicking on the sheet number or right clicking the sheet tab and selecting “Rename”. Using multiple sheets can be used for different sets of data and keeping things neat and organized, for example a sheet for attendee information, key contact information for event suppliers, event budget and sales figures/projections for the event.

2. Autocorrect

One of the main mistakes that many event professionals make is that they will either use shorthand or make spelling errors in an Excel spreadsheet that Autocorrect in word processing normally takes care of for you. This can come across as unprofessional and can lead to misunderstandings and time wasted.

Use this function if you prefer to write in shorthand so that it automatically changes the word for you. In order to adjust these settings you need to go to:

File > options > proofing > AutoCorrect

A dialogue box will give you the option to create shorthand and correction options as well as other options such as capitalizing the first letter of days or sentences as well as other text formatting options.

3. Conditional Color Coding

You can opt to set “rules” in Excel that will highlight certain cells according to the ranges that you set. For example you might want attendees younger than 40 in blue and 41+ in Yellow to give an at a glance overview of the demographics of your event.

You can set this according to your whole sheet by selecting the range of cells you want the highlighting to apply to and selecting:

Home > Conditional Formatting > Highlight Cells Rules

And then choosing the ranges which include some options including less than, greater than or between ranges as well as more specific options.

4. CONCATENATE

To string multiple cells of information together without merging cells this is the perfect function. This is great for combining figures or names and addresses together when managing an event, suppliers or attendee details.

You may have accidentally used it before because using an ampersand because it is one of the key components. It is really simple to do by, selecting the cell you want the data to combine to and using this formula:

=CONCATENATE(cell1&cell2&cell3)

Alternatively you can also remove the “concatenate” if the cell has no previous formulas or data inside.

5. Adding Multiple Columns/Rows

Remember when you forgot to add a row or column in and you realised you could right-click and insert a new one? This is really helpful if you are trying to stay in alphabetical or numerical order and can help to slot in new data with existing ones. But you can also do this multiple times at once to add a block of rows or columns to accommodate new chunks of data. It would take a while to add all of the relative rows and columns one by one so instead, select the amount of columns/rows you want, making sure they are above/below where the rows/columns are going to be added. Right click and select “insert” which will prompt a dialogue box that can help you specifically select the action that you want.

6. Forecasting

This is beneficial for long term or recurring events and projects that you want to analyse over time. It uses your historical data inputs to create forecasts that can project error margins, sale potential or seasonal changes in your data. This is particularly useful if you are trying to determine when in the year you want to have a new event or whether to change the venue or price of an existing one and can provide key insights into the future.

Do this by selecting the information that you want to include: e.g. yearly ticket sales or attendees over months or quarters and then select:

Data > forecast > forecast sheet

Next you can collate this into a line or bar chart as well as selecting how far you want the projection to go (although keep in mind the further in the future you go, the less accurate the data will be.)

7. Goal Seek

This is particularly useful during marketing phases to help make projections and decide how you can reach your event goals. For example, you can use the formula to see how many tickets to the event you need to sell to cover certain entertainment or venue costs as well as how much food/drink to buy according to set pricing etc. It is very good at quick targets that can help when going into a meeting and being able to present the figures with minimal effort.

The way to do this is to select:

Data > Data Tools > What-If Analysis > Goal Seek > Set Cell

At this point you can now determine what cell has the formula you want in it. Then select the “To Value” option which is where you put the goal value in and then select “by changing cell” which you should select the input that can be changed to meet your goal.

8. Power View

Particularly useful for presenting large amounts of data and creating reports, power view collates everything for you in an interactive way that can also be transferred or exported into PowerPoint. This works by using the information and relationships between your data that you have created in your sheets and then the Power View model identifies this and collates it into an easier way to report it. For example you could identify the relationship between events attendees, sales and seasons as well as products and inventory.

Do this by selecting:

Insert > Reports and selecting your criteria

Each Power View will usually create a new correlating sheet but you can have many of these per document depending on what you are reporting on.

9. Transposing Rows to Columns

A common problem when sharing Excel files is that some like to work in columns and others in rows which can make it difficult for some managers to interpret the data if they aren’t used to the layout. By transposing rows to columns or vice versa you can also move data around to make it fit nicely on the sheet or just to make it more neat and organized.

In order to do this you should select and copy the row/column you want to transpose and then right click on the cell you want to paste to. This will give you the option to “Paste Special” and you should select “Transpose”.

10. Easy Capitalization

If you have forgotten to capitalize names or need all text in rows or columns to be in the same format for collating such as all upper or lower case then the capitalization function is perfect for fixing these errors all at once to save you time and effort. This is particularly useful for combining invitation lists for larger events to make sure names are given the proper capitalization or for changing the case of countries that are all upper case.

Easily do this by selecting a cell to start and typing in =UPPER (for all upper case), =LOWER (for all lower case) and =PROPER (for capitalizing the first character of the word) and selecting cells the way you normally would by using colons or commas for the cells you choose.

11. Hiding Data

If you are aiming to quickly share data with another party then you may need to hide secure or sensitive information (event income information for example!) which normally means you would have to create a copy spreadsheet and remove the sensitive information before sending or printing. However this is not the case, you can in fact hide entire rows or columns of information at once (or just specific cells).

You may have hidden data before using the hide function on the right click select which works wonderfully for documents that are printed however it is easier to uncover and is more commonly well known. This hack is a great workaround which is useful for those who are a bit more tech savvy or if you are sharing via email or online. Select the data you want hidden and use this process:

Font>format cells>number tab>custom>type;;;>ok which will hide the data and only allow it to be previewed in the blank function area above.

12. Charting

Images can be easier to understand and digest than columns of data and this is particularly useful when influencing others, for example during fundraising. Potential customers and clients will react more to your services and be attracted to your event by figures and graphics than cold, hard numbers.

Create charts or graphs by highlighting the data that you want, including the headings, and selecting “insert>Chart>Chart type” which will take you to the recommended options. Here you can select bar charts, pie charts or other forms of graphs and representations that helps to display data against each other and illustrates it better.

13. Split Text

Have you ever decided that you want to change the data in the cells from when you first compiled your spreadsheet? We’ve all done it, when you have put the first and last name together in the same column and then later realised it would be better if they were separated for marketing purposes. In another instance you may be trying to separate info such as telephone numbers and addresses.

This can easily be done using the split text option which can be performed over a large amount of cells at a time which is efficient and effective. Do this by highlighting the data you want and then select either delimited (which allows you to divide the column according characters e.g. spaces, tabs or commas) or fixed width (allowing you to pinpoint exactly where in the column the split should happen). In the case of separating names, select delimited and chose the space character (you could also opt for an “@” sign to separate email addresses) and finish.

Your process should look something like this:

Select data> and go to “Data>Text to columns>delimited>space>finish”.

14. Pivot Tables

For those who are formula-phobes and can’t quite wrap their head around it, pivot tables are an excellent way to report and analyse data without including formulas. For example, if you want to summarize lists of the amount of attendees, ticket prices or even your current expenditure it is an excellent way to analyse them.

Also, one of the changes Excel has made is the update to additional Pivot Charts which is a combination of traditional Excel charting and including the Pivot Table which provides a great aesthetic way to represent data as well as being able to use filters to organize it more efficiently.

To use Pivot Charts or tables, select:

Insert > Pivot Chart/ Pivot Table

You can then use the right-hand pop up to edit the fields that you are using by dragging to the size that you want in the normal sheet.

15. Duplicate Removal

Event management is very fluid and this means circumstances change, leads refresh and you are constantly changing and readjusting databases or spreadsheets. For larger amounts of information you may find that you have a lot of repeated numbers, addresses, supplier names or even attendees that have been to your events more than once.

This can cause unnecessary work and throw off analysis and graph results giving false figures (especially in projections and forecasting). In order to ensure you have all of your data correct and you haven’t ended up with several sets of the same information, simply select:

Data>Tools> Remove Duplicates

This will prompt you to confirm it is checking the right data and once you confirm it will clean things up for you. Be wary of names that could be the same but are different people or suppliers and make sure you clearly indicate who is who when filling out your spreadsheet.

In Conclusion

As you can see there are an array of uses for Excel that should be able to help manage events as well as maintaining and analyzing useful information during the planning phases. Using these different functions gets the most out of Excel and should be able to streamline your functions to save you time and energy to focus on the events themselves.