Filters
When you’re looking at large data sets, you usually don’t need to look at every row at the same time. Sometimes, you only want to look at data that fit into certain criteria. That’s where filters come in.
Filters allow you to pare down data to only see certain rows at one time. In Excel, you can add a filter to each column in your data. From there, you can choose which cells you want to view.
To add a filter, click the Data tab and select “Filter.” Next, click the arrow next to the column headers. This lets you choose whether you want to organize your data in ascending or descending order, as well as which rows you want to show.
Let’s take a look at the Harry Potter example below. Say you only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.
Pro tip: Start with a filtered view in your original spreadsheet. Then, copy and paste the values to another spreadsheet before you start analyzing.
Sort
Sometimes you’ll have a disorganized list of data. This is typical when you’re exporting lists, like marketing contacts or blog posts. Excel’s sort feature can help you alphabetize any list.
Click on the data in the column you want to sort. Then click on the “Data” tab in your toolbar and look for the “Sort” option on the left.
- If the “A” is on top of the “Z,” you can just click on that button once. Choosing A-Z means the list will sort in alphabetical order.
- If the “Z” is on top of the “A,” click the button twice. Z-A selection means the list will sort in reverse alphabetical order.
Remove Duplicates
Large datasets tend to have duplicate content. For example, you may have a list of different company contacts, but you only want to see the number of companies you have. In situations like this, removing duplicates comes in handy.
To remove duplicates, highlight the row or column where you noticed duplicate data. Then, go to the Data tab, and select “Remove Duplicates” (under Tools). A pop-up will appear so that you can confirm which data you want to keep. Select “Remove Duplicates,” and you’re good to go.
If you want to see an example, this post offers step-by-step instructions for removing duplicates.
You can also use this feature to remove an entire row based on a duplicate column value. So, say you have three rows of information and you only need to see one, you can select the whole dataset and then remove duplicates. The resulting list will have only unique data without any duplicates.
Paste Special
It’s often helpful to change the items in a row of data into a column (or vice versa). It would take a lot of time to copy and paste each individual header.
Not to mention, you may easily fall into one of the biggest, most unfortunate Excel traps — human error. Read here to check out some of the most common Microsoft Excel errors.
Instead of making one of these errors, let Excel do the work for you. Take a look at this example:
To use this function, highlight the column or row you want to transpose. Then, right-click and select “Copy.”
Next, select the cells where you want the first row or column to begin. Right-click on the cell, and then select “Paste Special.”
When the module appears, choose the option to transpose.
Paste Special is a super useful function. In the module, you can also choose between copying formulas, values, formats, or even column widths. This is especially helpful when it comes to copying the results of your pivot table into a chart.
Text to Columns
What if you want to split out information that’s in one cell into two different cells? For example, maybe you want to pull out someone’s company name through their email address. Or you want to separate someone’s full name into a first and last name for your email marketing templates.
Thanks to Microsoft Excel, both are possible. First, highlight the column where you want to split up. Next, go to the Data tab and select “Text to Columns.” A module will appear with more information. First, you need to select either “Delimited” or “Fixed Width.”
- Delimited means you want to break up the column based on characters such as commas, spaces, or tabs.
- Fixed Width means you want to select the exact location in all the columns where you want the split to occur.
Select “Delimited” to separate the full name into first name and last name.
Then, it’s time to choose the delimiters. This could be a tab, semicolon, comma, space, or something else. (For example, “something else” could be the “@” sign used in an email address.) Let’s choose the space for this example. Excel will then show you a preview of what your new columns will look like.
When you’re happy with the preview, press “Next.” This page will allow you to select Advanced Formats if you choose to. When you’re done, click “Finish.”
Format Painter
Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a spreadsheet, you know it can get a bit tedious.
Don’t waste time repeating the same formatting commands over and over again. Use the format painter to copy formatting from one area of the worksheet to another.
To do this, choose the cell you’d like to replicate. Then, select the format painter option (paintbrush icon) from the top toolbar. When you release the mouse, your cell should show the new format.
