Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically. Need to merge two sheets with similar data? Excel can do it. Need to do simple math? Excel can do it. Need to combine information in multiple cells? Excel can do it.
If you encounter a situation where you need to manually update your data, you’re probably missing out on a formula that can do it for you. Before spending hours and hours counting cells or coping and pasting data, look for a quick fix on Excel — you’ll likely find one.
In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel tricks to get you started with how to use Excel.
General Excel Help
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.
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 Clumn 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.
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.”
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 organizing in ascending or descending order, as well as which specific rows you want to show.
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.
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 vise 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.
7) SIMPLE CALCULATIONS
In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.
– To add, use the + sign.
– To subtract, use the – sign.
– To multiply, use the * sign.
– To divide, use the / sign.
8) CONDITIONAL FORMATTING FORMULA
Conditional formatting allows you to change a cell’s color based on the information within the cell. For example, if you want to flag certain numbers that are above average or in the top 10% of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information the is important to you.
To get started, highlight the group of cells you want to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.
9) IF STATEMENT
Sometimes, we don’t want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.
The formula: IF(logical_test, value_if_true, value of false)
In general terms, the formula would be IF(Logical Test, value of true, value of false). Let’s dig into each of these variables.
– Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor” because we want to make sure that the cell corresponding with the student says “Gryffindor.” Make sure to put Gryffindor in quotation marks here.
– Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show “10″ to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
– Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show “0″ to show 0 points. Only use quotation marks if you want the result to be text instead of a number.
10) DOLLAR SIGNS
Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn’t representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.
You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you’re actually referring to a cell that’s five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not — and we can do that by making the formula in the cell into what’s called an absolute formula.
To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs, like this: (=$A$5+$C$5).
11) VLOOKUP FUNCTION
Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?
For example, you might have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.
I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.
The formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.
– Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
– Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
– Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
– Range Lookup: Use FALSE to ensure you pull in only exact value matches.
12) INDEX MATCH
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:
– VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, then using the INDEX MATCH function will significantly decrease load time in Excel.
– INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.
So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX MATCH instead.
13) COUNTIF FUNCTION
Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.
For example, let’s say I want to count the number of times the word “Gryffindor” appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,”Gryffindor”)
In this formula, there are several variables:
– Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
– Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”
14) COMBINE CELLS USING “&”
Databases tend to split out data to make it as exact as possible. For example, instead of having a data that shows a person’s full name, a database might have the data as a first name and then a last name in separate columns. Or, it may have a person’s location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell by using the “&” sign in your function.
The formula with variables from our example below: =A2&” “&B2
Let’s go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we’d first put our cursor in the blank cell where we want the full name to appear. Next, we’d highlight one cell that contains a first name, type in an “&” sign, and then highlight a cell with the corresponding last name.
But you’re not finished — if all you type in is =A2&B2, then there will not be a space between the person’s first name and last name. To add that necessary space, use the function =A2&” “&B2.The quotation marks around the space tell Excel to put a space in between the first and last name.
To make this true for multiple rows, simple drag the corner of that first cell downward.