For all those who have little to zero experience using Excel, here are a few pointers to tidy up your spreadsheets!
Whether you're working with the Event Details Report or the Transaction Report, these tricks will help make sorting your data a piece of cake.
column: each column is identified as a letter along the top of the page and runs vertically down the spreadsheet. Columns help identify the location of a cell.
row: each row is identified as a number on the left side of the page and runs horizontally across the spreadsheet. Rows help identify the location of a cell.
cell: not a biology term in this case, it is a little box formed by the intersection of a row and column that holds a piece of information on your spreadsheet
filter: a glorious little trick to help you do just that, filter your spreadsheet to view only the information you want to see.
count: nix ticks on paper, a count in Excel will do all your tallying just by using a filter and highlighting cells. Need to know how many chocolate milks were ordered? The guide below will show you how to do just that in Excel super fast!
Whoa, My Columns Are Too Narrow
When you first look at your spreadsheet you may see cells that look like this:
That is because columns are too narrow. But we can easily remedy this by wrapping text, and expanding column widths.
This little maneuver will allow data in the cell to 'wrap' and fit the column width, making it fully visible to you. Note, when you change the column width, data wrapping adjusts automatically.
Follow these three quick steps to wrap text in your spreadsheet!
Step 1 - Highlighting all cells in grey will allow you to change the width of all your columns at once. See image below for instructions.
Step 2 - Option A: Double clicking between the the column that’s too narrow and it’s neighbouring cell as shown below (in this case column D and column E), will adjust every columns width to fit the data in each cell.
Option B: By clicking, holding, and dragging to the right or left, all column widths will be uniform.
Mastering the Excel Filter
Filtering will allow you to hide the information you don't want visible on your spreadsheet. For example, you may want to see only those contacts that have not paid yet or only those contacts who answered 'yes' they can volunteer.
Filtering does not delete any information, it only hides it temporarily.
TIP: A tally of selectable ticket options won't appear in your Ticket Type Report. This is where the invention of the Excel filter becomes very handy.
Eg. of a ticket with a selectable option:
We are hosting a fundraising BBQ. Each entry ticket comes with the option of a hot-dog or hamburger.
Pre-steps: You've opened your Event Details Report in Excel, adjusted column widths, and now you want to add a filter to see how many hot-dogs have been ordered.
Follow these first two steps:
Next, find the column(s) you're interested in filtering. Note that you can filter as many columns as you would like to narrow down the data further. Follow these next two steps and you're done! You're on your way to mastering the Excel filter.
Once you've filtered to the desired field(s) you may want a tally of how many hot dogs were ordered. Here is a quick trick that will instantly tally the number of cells containing the data you filtered.
Step 1: Highlight the cells containing the data you are concerned with. In this case 'hot dogs'.
Step 2: Find Count at the bottom of the spreadsheet as shown below. Now if your list is 300 participants long, this quick move will be a big time saver!
Much like filtering cells, hiding columns enables you to work with only the data that matters without deleting important data and brings it back into play when you need it.
Step 1: Highlight the column(s) you would like to hide. In the example below, we want to hide column E and column F.
TIP: To highlight columns as done in the example below, click and hold the mouse button on column letter E, and drag mouse to column letter F.
Step 2: Right click inside the grey shaded area and a menu will appear.
Step 3: Select 'Hide'. Boom. Columns are hidden.
Last but not least, use the quick search shortcut. Need to quickly find a person or a ticket? Press Ctrl and the letter 'F' simultaneously. The menu below will pop up so you can enter the item name you are in search for.