As much as we hate to admit it, spreadsheets are still the number one way to work with complex data. That being said, there are a few tricks that make working in Excel (the most popular spreadsheet tool in the world) a little bit more manageable, especially when it comes time to dump that spreadsheet into a nice, organized software system that will handle all that big data for you.
Say you have text in multiple cells that you need to merge to one cell. Meeting planners for example might need to converge certain fields before uploading their spreadsheets to their event management platform.
To do this, you’d use the concatenate function, explained in more detail here.
Simply create a new row or column in which you’d like to merge the text, type =CONCATENATE into the new cell, then select the cells you want to merge in parentheses directly following the function, like so: =CONCATENATE(A1, A2). Hit enter and voila! there you go.
Sometimes you need to split text in a single columns to separate columns. Marketers encounter this will first and last names in mailing lists. Meeting planners encounter it when working with attendee registration lists.
The Office Support page has a great tutorial on how to do this.
The best way is to define a delimiter (or boundary between bits of data). The most common delimiters are commas, colons, or even spaces. To do this, find the ‘Text to Column’ option in the ‘Data’ toolbar, define your settings, and choose which cells you want to split. Easy as that.
Getting the exact date format just right in your spreadsheet can be tricky. At times data is preloaded with various timestamps, others the format is simply not correct, and sometimes you just need to calculate the end time of something like your meeting’s sessions. That’s where Excel’s Date & Time functions come in handy.
Excel Easy breaks down the basics of how to use Excel’s Date & Time functions perfectly, while Five Minute Lessons gets into the more complex matter of formatting dates and times with the DATEVALUE and TEXT functions.
There’s a lot you can do here (and a lot of different scenarios), so I’ll let the tutorials speak for themselves.
Things can also get tricky if you need the time to display in standard civilian format but it’s defaulted to military (or vice versa). Luckily Excel has a very simply way to fix this.
Simply select the cells you want to fix, right click and select ‘Format Cells,’ go to the ‘Time’ option under the ‘Number’ tab, and choose the format that’s right for you.
ExcellsFun has a great step by step guide on how to do this over on YouTube.
I actually figured out how to do this one while I was building CadmiumCD’s editorial calendar (and trust me, if you’re a marketer or event planner, you need to have some sort of flexible calendar tool… things like CoSchedule’s scheduled posts and Conference Harvester’s drag and drop session scheduler are great if you need something as robust as a spreadsheet without the hassle of building one). It’s perfect for predefining information you don’t want to have to type over and over again — like product type, education track, or room number.
There’s a great visual guide over at Online Tech Tips about how to create a dropdown list using Excel’s validate feature, but it’s very easy to do.
Simply select the cells you want to create a downdown for, click the ‘Data’ tab and select ‘Data Validation,’ go to ‘Setting,’ select ‘Allow: List,’ then under ‘Source’ enter the text you want to show up in your dropdown with each item seperated by a comma. There you go! You’ve got your very own dropdown list.
This is another one I figured out while building CadmiumCD’s editorial calendar. It’s super helpful for any tasks you might need to complete and have a visual representation of to stay organized. Meeting planners, for example, might collect a lot of information from speakers and exhibitors and need to check off who has submitted the correct items (this is actually where we got our inspiration for Conference Harvester’s automated task completion).
To create a check box in Excel, go to the ‘Developer’ tab and choose ‘Insert Check Box,’ then simply select where you want to place the object.
PCWorld breaks it down step-by-step in this article.
When I first started using Excel, and especially when I was working with big lists of names and addresses, this was one of my number one frustrations. I just couldn’t figure out how to make zip codes that begin with ‘0’ keep the zero. Everytime I’d hit enter to move to a new cell, Excel would drop the zeroes and cut the number down to 3 or 4 numbers.
That’s until I realized that if you enter an apostrophe before any number that begins with 0, Excel would retain it. This is because Excel was primarily built for working with numbers and doesn’t recognize numbers beginning with 0 as having any value.
Still having trouble making it work? Check out this tutorial from the University of Texas.
Excel has A LOT of functionality. These are some of the most common frustrations we’ve heard from talking with meeting planners over the years and working in Excel ourselves, but maybe there’s a secret or lesser known function or feature you know of. Please don’t hold back! We’d love to hear about it in the comments.