Use this link to share with your colleagues:
Recommended Excel Tools, Formulas, and Tricks: https://help.pm.leapevent.tech/a/829758
If you're preparing data for import, or for a mass update, you're probably working in Excel. Really knowing your way around can speed your data-cleansing process up exponentially, allowing you to comb your spreadsheets with more precision. Below, we've compiled some of our favorite time-savers.
Because a new version of Excel is always just around the corner, this article won't always provide step-by-step instructions - though we will provide some screenshots and videos to give you a frame of reference. For an in-depth walkthrough on any topic, and for your specific version of Excel, we recommend using your favorite search engine, i.e. "Text function Excel 2008".
The Basics
Start by watching DIY Data Part 1 - Becoming an Excel Ninja: The Basics.
In this video, we cover:
- Anatomy of Excel
- Essential keyboard shortcuts (PDF below)
- Formatting spreadsheets
- Page view options
- Common shortcuts
- Filtering
- Formulas
Tools
Conditional Formatting > Highlight Cells
This tool can help you to identify duplicate records or values in a column or row of your document.
Filter
This tool is useful for looking at an overview of the contents of a column. You can also use this tool to sort and find spaces or commas in a field, or to find characters that don't belong in email fields, such as !, #, , commas, spaces or semicolons.
Text to Columns
This tools is incredibly useful for separating full names into multiple columns by using a common delimiter, such as a space or comma.
Formulas
Concatenate
This function is essential for combining first and last names into one Account Name, Address Name or Salutation.
- Syntax
- =(value1)&(value2)
- Example
- =A2&" "&B2
Proper
This function will update a cell's contents to the Proper case. This will be helpful for records that were entered inconsistently in all caps or all lowerÂcase in your old system.
- Syntax
- =PROPER(value)
- Example
- =PROPER(A2)
Text
This function converts a numeric value to text and lets you specify the display formatting by using special format strings.
- Syntax
- =TEXT(value,format)
- Example
- =TEXT(A2,"dddd,mmmm d, yyyy at h:mm am/pm")
- The value of cell A2 will be converted into a date format, such as "Saturday, August 8, 2015 at 7:00 PM."
VLookup
One of the more complex tips we'll go over here, VLOOKUP is also one of the most useful, allowing you to use unique identifiers from one spreadsheet to populate values in another spreadsheet. For a more in-depth walkthrough, check out the video or PDF below.
- Syntax
- =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- Example
- =VLOOKUP(B2,'Mailing List category Key'!A:B,2,FALSE)
Tricks
Fill Blank Cells in a Table
Tired of copy-pasting formulas? This trick shows you how to use CTRL + ENTER to fill all highlighted, or all blank cells with a formula.
Check out "How to Fill Blank Cells in a Table" in our DIY Data extras section.
Fix Last Names with the "Mc" Prefix
When cleaning up a long list of patrons for import, you may find that names like "MCDOUGAL" provide a unique challenge - using the "Proper" formula above results in "Mcdougal", rather than "McDougal". This trick will circumvent that issue with a set of nested formulas.
Watch "Excel Magic! Using a Nested Formula to Fix Last Names with the "Mc" Prefix" in our DIY Data extras section.
What's next?
If you're interested in putting these skills to good use, and becoming more self-reliant for your data projects, check out our entire "DIY Data" webinar series to get started!