Photo by Hillary Ehlen
Data is only as useful as your ability to analyze it. This month, we’re going to cover some Excel basics to help you crunch those numbers more effectively. Keep in mind: This is just a basic primer to get your creative juices flowing. Like most programs these days, Google and YouTube will be your best friends as you learn something new. I’ll be using the Windows version of Excel here, but all of these features are available on the macOS version of Excel unless otherwise noted.
Filtering and Tables
If you need only basic sorting, you can click on any cell with data and hit the Sort & Filter button. From there, you can either sort the data from the Sort & Filter button or click the filter option. This will add clickable arrows to the top row of your document, which allows you to sort individual columns. This works best if your data already has headers.
For additional formatting options, you can always create a Table. With Tables, you can add visual features such as banded rows and columns, which make the table easier to read, as well as total rows at the bottom of the table.
Tech Tip #1
In addition to adding up all of the numbers in the column, total rows can display more than 10 different functions such as count, average and standard deviation.
Text to Column and Consolidate
Text to Column and Consolidate are really helpful for manipulating lists and extracting data.
Text to Column will split the text in one cell into multiple cells (e.g., a cell that contains John Doe can be split in to two cells: one with “John” and another with “Doe”). Consolidate is the opposite. It combines the text of two different cells into one.
Common use-cases are to take a list of combined first and last names and split it into two cells, so you can use people’s first names in a mail merge. Another common scenario is to combine separate first and last names into one cell to create a user name. The possibilities are endless, though.
Tech Tip #2
Text to Columns can split text after a fixed number of characters (Fixed Width) or at a specific character (Delimited). This character is usually a tab, space, or comma, but any character can be used. If needed, you can use multiple de-limiters.
Conditional Formatting literally highlights the data you’re looking for. You can highlight every number above, below, in between or equal to a specific number. You can also highlight cells with text that contains something you are looking for, as well as dates. The option I use most often is to highlight duplicates.
You can also visualize your data with Data Bars and Color Scales. These buttons add a bar graph or change the color of the cells in a column according to the data in the cells. Let’s say you have a column of test scores; you can use Color Scales to color the best test score green and the worst test score red and the other scores some shade in between. Data Bars does the same thing but with a bar graph.
Tech Tip #3
In addition to highlighting duplicates, you can also delete duplicates with the Remove Duplicates button. Just highlight the cells you want to remove duplicates from and hit the button.
Because Excel is used in many different ways, it’s important to understand cell data types. Cells can be formatted to display data in 11 pre-configured categories: General, Number, Currency, Accounting, Data, Time, Percentage, Fraction, Scientific, Text and Special. If none of them are right you, you can create your own custom option.
Tech Tip #4
Formulas are far and away the most powerful feature of Excel and underpin almost all of the features described in this article. They are not easy to describe in an article, though, so I’m just going to recommend you search “INDEX MATCH” or “VLOOKUP” on YouTube to get started.