Let Excel do the Math: Easy tricks to clean and analyze data in Excel
This article is rated as:
One of the most common things I see when I work data in excel that others have compiled or analyzed is the under-use of Excel’s computational powers. In part, this has to do with setting up your data correctly to all Excel to do the work.
Our Data Entry Tip Sheet or article on using survey platform data can help you set up your data to maximize use. But if you don’t know how to harness Excel’s power, you’ll spend more time than necessary counting and sifting through data, like I did when I started.
It’s hard to know where or how to get started or even what to google to set you on the right path. There are plenty of tutorials and how-to articles online, so the focus today is to introduce you to some tools you can use for cleaning and analyzing your data, to give you a place to start!
Cleaning Your Data
Once you have your data formatted, we have some simple tricks to help clean your data so you can sit back and let excel do the work. Also check out our Data Cleaning Toolbox.
Format the cells. Ensure that date cells are formatted as dates etc.
Convert the data to a table. This allows you to spot check column value and look for missing or inconsistently entered data
Look for outliers or unexpected values in numerical data. Sort the data by specific columns, to look for minimums and maximums or values outside of what’s expected.
Look for repeated and misspelled values in text data. Look for values that don’t make sense or duplicate or misspelled text entry data.
Filter each column to look for values that don’t belong.
Separate multiple entries in one cell into their own cells. Separating the entries by a common feature, like a comma; like this.
Check for duplicates. Highlight the entire dataset and use “Remove Duplicates” or conditional formatting.
Now that your data is clean, it’s time to analyze it! So where to start?
Analyzing Your Data
While this article won’t go into detail about how to use each of these formulas, we’ll get you started with our favourite formulas for conducting simple descriptive statistics and let you google how to apply them.
COUNTIF
SUMIF
AVERAGE, AVERAGEIF, MEDIAN, MAX, MIN
XLOOKUP for basic data matching (or HLOOKUP and VLOOKUP in older versions of Excel)
Between our Data Entry Tip Sheet and these cleaning and analysis tools, I hope you are starting to feel confident that, yes, YOU can analyze your data in excel.
As you get more familiar with the types of data you might be analyzing in Excel, you’ll start to develop your own favourite tricks and formulas to help you analyze your data more efficiently.
To learn more about applying evaluation in practice, check out more of our articles, or connect with us over on Twitter (@EvalAcademy) or LinkedIn.