Office Excel Tips to Try
1) Pivot Tables
Pivot Tables are used to reorganize data in a spreadsheet. They won’t change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you’d like them to do.
Let’s take a look at an example. Let’s say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don’t have too much data, but for longer data sets, this will come in handy.
To create the Pivot Table, I go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.
- Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
- Column Labels: These could be your headers in the dataset.
- Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
- Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default,when you drag a field to Value, it always does a count.
Since I want to count the number of students in each house, I’ll go to the Pivot Table and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.
2) Add More Than One New Row or Column
As you play around with your data, you might find you’re constantly needing to add more rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there’s always an easier way.
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”
In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I’m able to add an additional three blank rows into my spreadsheet quickly and easily.
When you’re looking at very large data sets, you don’t usually need to be looking at every single 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 your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data — and from there, you can then choose which cells you want to view at once.
Let’s take a look at the example below. Add a filter by clicking the Data tab and selecting “Filter.” Clicking the arrow next to the column headers and you’ll be able to choose whether you want your data to be organized in ascending or descending order, as well as which specific rows you want to show.
In my Harry Potter example, let’s say I only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.
Pro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.
4) Remove Duplicates
Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.
To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab, and select “Remove Duplicates” (under Tools). A pop-up will appear to confirm which data you want to work with. Select “Remove Duplicates,” and you’re good to go.
You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows with Harry Potter’s information and you only need to see one, then you can select the whole dataset and then remove duplicates based on email. Your resulting list will have only unique names without any duplicates.
When you have low rows of data in your spreadsheet, you might decide you actually want to transform the items in one of those rows into columns (or vice versa). It would take a lot of time to copy and paste each individual header — but what the transpose feature allows you to do is simply move your row data into columns, or the other way around.
Start by highlighting the column that you want to transpose into rows. Right-click it, and then select “Copy.” Next, select the cells on your spreadsheet where you want your first row or column to begin. Right-click on the cell, and then select “Paste Special.” A module will appear — at the bottom, you’ll see an option to transpose. Check that box and select OK. Your column will now be transferred to a row or vice-versa.
6) 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 perhaps you want to separate someone’s full name into a first and last name for your email marketing templates.
Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select “Text to Columns.” A module will appear with additional 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 on all the columns that you want the split to occur.
In the example case below, let’s select “Delimited” so we can separate the full name into first name and last name.
Then, it’s time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or something else. (“Something else” could be the “@” sign used in an email address, for example.) In our example, let’s choose the space. Excel will then show you a preview of what your new columns will look like.