Working on several spreadsheets at once can be difficult, especially if you only have one screen, you could end up working on the completely wrong sheet, wasting valuable time. Using Alt + Tab will allow you to easily shift between different files freely. This function is also applicable to other files or programs open on your Windows PC.
You might how to add a new row or column, but if you are looking to add more than one, rather than repeating the action multiple times, do it all at once. The best way is to drag and select the number of rows or columns (X is two or more) if you want to add X rows or columns above or left. Right-click the highlighted rows or columns and choose Insert from the drop-down menu. New rows will be inserted above the row or to the left of the column you first selected.
When you’re looking at large data sets, chances are you don’t need, or want to be looking at every single row at the same time – too confusing for the brain. Sometimes, you only want to look at data that fit into certain criteria. That’s where filters come in.
Filters allow you to reduce your data allowing you 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’d like to see.
Add a filter by clicking the Data tab and selecting “Filter”, click the arrow next to the column headers and you’ll be able to choose whether you want your data to be organised in ascending or descending order, as well as which specific rows you want to show.
If I only want to see the employees in Sales, by selecting the Sales filter, the other rows disappear. Easy!
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 from. Then, go to the Data tab, and select “Remove Duplicates”. A pop-up will appear to ask if you want to “Expand the selection” or “Continue with the current selection” for the purpose of this example, you want to stick to the data you have selected. Select “Remove Duplicates” and “OK” then BAM, no more duplicates.
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. Luckily, both options are possible and pretty easy to get to grips with.
Let’s take a look at how to separate someone’s full name into first and last time, this is something I have to do a lot of when creating marketing materials. 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.”
For this example, we should select “Delimited” so we can divide the full name into first name and last name.
Next, we need to choose our Delimiters. This could be a range of things from tabs to semi-colon’s or a space, (this could be also be the “@” sign used in an email address.) In this example, it makes sense to choose the space! Then, press “Next” (this page will allow you to select Advanced Formats if you choose to). When you’re done, click “Finish.” Boom, you’ve just saved yourself some serious time!
Let’s flip that like a pancake and 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 say we now want to combine first names and last names into full names in a single column. To do this, select the blank cell you want the full name to appear in. Then, we need to highlight one cell that contains a first name, type in an “&” sign, and then highlight a cell with the corresponding lastname.
But hold on, you’re not finished there. 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 gap in between the first and last name.
To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example, or double click the cell.
Excel is somewhat known for its complex calculating abilities, but before you move on to the difficult stuff make sure you master the basics first! Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.
You can also create averages for a set of numbers, using the formula =AVERAGE(Cell Range). If you want to sum up a column of numbers, you can use the formula =SUM(Cell Range).
Colour coding always make data easier to understand, which is in a sense, what Conditional formatting is. It allows you to change a cell’s colour based on the information within the cell. For example, if you want to note certain numbers that are above average or in the top 25% of the data in your spreadsheet, you can do that. If you want to colour code commonalities between different rows in Excel, you can. This allows you to see the information that is important to you, faster.
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. Note how 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. Ta-da!
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.
For example, in the situation below, I want to add three “good feedback” points to everyone in the Sales team. Instead of manually typing in 3’s next to each Sale’s person’s name (which could take forever), I can use the IF THEN Excel formula to say that if the employee is in Sales, then they should get three points.
The formula: IF(logical_test, value_if_true, value of false)
Example Shown Below: =IF(D2=”Sales”,”3″,”0″)
In general terms, the formula would be IF(Logical Test, value of true, value of false). Let’s check out what each of these variables means.
With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells, relieving you of tedious manual work that could take more time than you have.
For example, let’s say I want to count the number of times the word “Sales” appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,”Sales”)
In this formula, there are several variables:
Simply typing in the COUNTIF formula in any cell and pressing “Enter” will show me how many times the word “Sales” appears in the dataset.
This one’s a biggie and something we’ve probably all come across! This is for when you have two sets of data on two different spreadsheets and 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.
Read carefully because it may take you a few attempts to get this one…
The formula we’re using is: =VLOOKUP(lookup value, table array, column number, [range lookup])
My example formula is: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this formula, there are several variables. The following is true when you want to combine the information in Sheet 1 and Sheet 2, onto Sheet 1.
In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. What if we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1? If you apply the logic you learnt above, you can do just that!