While numerous analytics and business intelligence packages now dot the landscape, for many people Excel remains their weapon of choice when it comes to data analysis. Working in Excel means more than just reading through a spreadsheet. Excel is a powerful system in its own right, and every analyst should know the following 5 tricks that will allow them to get the most out of their work.
1. Data Cleaning in Excel
One of the most important jobs when looking at a spreadsheet is data cleaning. Fortunately, there are several built-in solutions for data cleaning in Excel.
Before you can get any work done, you’ll want to make sure the cells are properly formatted. To accomplish this, you can use the conversion tools that are available when you right-click on any cell. This gives you a slew of options, but the two big ones are:
- Number stored as text
- Convert to number
Respectively, those two allow you to either treat a number as text or to make sure a cell with numbers in it can be read as a number. Especially if you’ve imported something in scientific notation, this can simplify a typically painstaking task.
2. PivotTable and Unpivot
When it comes to data analysis Excel issues, the simplest methods are often the best ones. Few are quite as good as using PivotTable and Unpivot, operations that are two sides of the same coin.
Suppose you have a table of data that needs to be condensed into a simpler second table. For example, you might want to tally all of your social media visitors by region. With a column labeled “region” in the original context, PivotTable will create a second table that condenses the work. If you need to accomplish the reverse, simply use Unpivot.
3. INDEX and MATCH
Finding a specific entry can be a genuine pain, especially if you’re dealing with rows and columns that are inconsistent. INDEX and MATCH are the tools that allow you to specify relationships between cells as a way to track down which ones you want. For example =INDEX(B6:O6, 3) will give you the entry in cell D6. It might not seem like a big deal when you first hear about it, but INDEX can massively reduce headaches when dealing with tables that are constantly changing.
MATCH is much easier to understand. If you need to find, for example, the February entry in a set of columns, =MATCH(“Feb”, B6:Z6, 0) will hunt down the right match from the range of cells provided.
4. SUMIF and COUNTIF
The SUM and COUNT tools are among the first weapons analysts tend to learn when using Excel. You can take them to the next level by using the IF versions. For example, SUMIF will only sum the values if the condition included is present. This allows you to tally up entries only if they meet specific criteria that exist. It’s also possible to go one level higher and use the SUMIFS syntax to set more criteria and look at multiple ranges.
5. VLOOKUP
Hunting through giant spreadsheets can cross a line at which it ceases to be humanly possible. A lot of people end up either using massive chains of entries, or they might even break out VBA to do the job programmatically.
This is where VLOOKUP enters the game because programmatic solutions are rarely necessary for Excel. It’s a fairly straightforward tool that operates as =VLOOKUP(target, table, index). Whatever you want to look up is the target, and the table is what you mean to search. The index informs Excel which column will be searched, with the first column starting at one.
