Excel makes complex numerical tasks easy. All you need are a couple of formulas, clicks, and keystrokes. And voilà – you’re done. Sounds way too good to be true, right?
Mastering core Excel functions can be of great help, especially if number-crunching is a normal part of your workday. If you’re looking to start using Excel like a pro, you’ve come to the right place.
Excel is incredibly useful for keeping data sets organized. It’s packed with formulas that helps you solve countless numerical problems, manipulate data, and simplify complex computations. Think of it as a calculator on steroids.
If you’re still spending tons of hours manually moving data between cells, rows, and columns in Excel, it needs to stop right now. You need to read on to learn the most essential Excel shortcuts and make your Excel game as efficient as possible. (Thank us later.)
Don’t worry if you’re still new to Excel – everyone learns it in different ways. But before you dive in, we recommend you brush up on these basics first.
When you feel confident in doing these things, scroll down to get started.
Back to the table of contents ⇑
Pivot tables can be used for reorganizing data within a spreadsheet. They do not change your data but can compare different information within your spreadsheet and sum up values depending on what you want them to do.
Let go through an example together. Say I would like to look at the number of people there are in each GetMyInvoices department. You may think I don’t have a lot of data, however, this comes in handy for longer data sets.
To make a Pivot Table, go to Insert > Pivot Table. The Pivot Table will be automatically populated by Excel. However, you can also change the order of the data if you want to. There are four different options for you to pick from.
I wanted to count how many employees are in each department, so I go to my Pivot table and then drag my Department column over to both the Rows and Values. That will add up the number of employees who are associated with each of the departments.
Back to the table of contents ⇑
As you are working with your data, you may find that you constantly have to add more columns and rows. At times, you might need to add as many as hundreds of rows. It would be really tedious if you need to do this one at a time. Fortunately, Excel offers an easier way to do this.
To add more than one column or row in a spreadsheet, simply highlight the identical number of columns and rows that you would like to add. Next, right-click and choose “Insert.”
For example, if you want to add five extra rows, highlight five rows and click on Insert to add five extra blank rows into your spreadsheet easily and quickly.
Back to the table of contents ⇑
When you are reviewing very large sets of data, usually don’t need to look at every single row all at the same time. You sometimes only want to review data that fit certain criteria. This is where filters can be useful.
Filters make it possible to pare your data down so you look at just certain rows at one point in time. You can add a filter in Excel to each column that is part of your data. You then can select which cells you want to view at the same time.
Let’s look at an example. Click on the Data tab and then choose “Filter” to add a filter. If you click the arrow that is next to the column headers you can choose whether you would like your data to be organized in either descending, or ascending order, or also the specific rows that you would like to display.
In my example, I just want to see the accounting employees. When I choose the Accounting filter, it causes the other rows to disappear so I can focus on just the accounting department.
Pro Tip: When there is a Filter on, copy and paste your values into the spreadsheet to do additional analysis within a separate spreadsheet.
Back to the table of contents ⇑
There is a tendency for there to be duplicate content in larger data sets. You might have a list of multiple company contacts and just what to see how many companies you have. In these situations, it can be very useful to remove the duplicates.
To remove duplications, highlight the column or row that you would like to remove duplicates from. Next, go to your Data tab, and under Tools choose “Remove Duplicates.” A pop-up will be displayed to confirm the data that you would like to work with. Choose “Remove Duplicates” and you will be all set.
This feature can also be used to remove a whole row based on the duplicate column value. For example, if there are three rows that have John Smith information but you just need to look at one, you can choose the entire dataset and then duplicates can be removed based on email. The resulting list will just have unique names that do not contain any duplicates.
Back to the table of contents ⇑
When your spreadsheet has low rows of data, you may want to transform the items from one of the rows into columns (or a column into rows). It would be very time-consuming to copy and paste each of them into an individual header. However, the transpose feature lets you do this by just moving your row data into a column or vice versa.
Begin by highlighting the column you would like to transposed into rows. Then right-click on it, and choose “Copy.” Then choose the cells on the spreadsheet where you would like the first column or row to start. Right-click on that cell, and then choose “Paste Special”. This will cause a module to appear and you will see an option on the bottom to transpose. Check the box and then choose OK. That will transfer your column to a row, or the other way around.
Back to the table of contents ⇑
Do you want to be able to split out the information contained in one cell into two separate cells? For example, perhaps you would like to pull out a company name via their email address. Or maybe you want to separate the full name of someone to use in email marketing templates into a first name and last name.
Both are possible, thanks to Excel. First, highlight whatever column you would like to split. Then navigate to the Data tab and choose “Text to Columns.” Additional information will be provided in a module.
First, you need to choose either “Fixed Width” or “Delimited.”
Let’s choose “Delimited” for our example, so the full name can be separated into first and last names.
Now it is time to select the Delimiters. It can be a space, comma, semi-colon, tab, or something else (for example, something else can be the “@” sign that an email address uses). We will choose the space for our example. Excel then displays a preview of what your new columns are going to look like.
After checking the preview to make sure you are happy with it, press “Next. On this page, you can choose Advanced Format if you want. Click “Finish” when you are done.
Back to the table of contents ⇑
Along with performing some fairly complex calculations, Excel also can help you with basic arithmetic like dividing, multiplying, subtracting, or adding your data.
In our example, we will calculate the total number of working hours of all employees in accounting department.
Pro Tip: Parentheses can also be used to make sure certain calculations are performed first. In the example (10+10*10), the second and third 10’s are multiplied together before the other 10 is added. However, with (10+10)*10, the first two 10s are first added together.
Back to the table of contents ⇑
The formula =AVERAGE(Cell Range) can be used if you would like the average for a certain set of numbers. The formula =SUM(Cell Range) can be used to add up a column of numbers.
Back to the table of contents ⇑
You can use conditional formatting to change the color of a cell based on the information contained inside the cell. For example, if you would like certain numbers to be flagged that are in the top 10% or above average in your spreadsheet’s data, it is possible to do that. If you would like to color-code certain commonalities that different roles have in Excel, which can be done as well. That will help you see important information quickly.
To start the process, highlight a group of cells that you would like to use conditional formatting with. Next, on the Home menu select “Conditional Formatting” and from the dropdown choose your logic. (It is also possible to create a rule of your own if you would like something different). Then a window will pop up and prompt you to supply more information on your formatting rule. After you are done, choose “OK” and your results will appear automatically.
In our example, we want to mark in yellow all employees in all departments who had 40 working hours.
Back to the table of contents ⇑
There are times when you might not want to count the number of times that a certain value appears. You may want to input different information into a cell instead when there is a corresponding cell that contains the information.
For example, say I would like to award ten points to everybody who is in accounting department. Rather than manually typing a 10 next to each employee’s name from accounting department, I can instead use the IF THEN formula to state that an employee should be given ten points if they are in accounting department.
Here is the IF formula: IF(logical_test, value_if_true, value of false)
Our example formula is: =IF(E2=”Accounting “,”YES”,”NO”).
This means that if the employee has 40 working hours, “YES“ will be displayed automatically. Otherwise, if the employee has less than 40 working hours, “NO” will be displayed automatically.
Generally speaking, the formula is IF(Logical_Test, Value_if_True, Value_if_False). Now let’s take a closer look at each of the variables.
Back to the table of contents ⇑
Have you ever seen an Excel formula with a dollar sign in it? It is not representing the American dollar when the dollar sign is used in a formula. Instead, it ensures that the exact row and column are held the same even when the same formula is copied in adjacent rows.
A cell reference – when, for example, you refer to cell A4 from cell C4 – is by default relative. In this case, you are referring to a cell five columns to the left (C – A) and also in the same row (4). That is referred to as a relative formula. When a relative formula is copied from one cell to a different cell, it adjusts the values that are in the formula that is based on where it is moved. However, at times, you may want these values to remain the same, whether they are moved or not or where they are moved to. That can be done by making an absolute formula in the cell.
To change the relative formula (=A4+C4) into an absolute formula, then we proceed to the column and row values using dollar signs, like (=$A$4+$C$4).
Back to the table of contents ⇑
Do you ever have two data sets on two different spreads that you would like to combine into one spreadsheet?
For example, you could have a list of employee’s names and their email addresses in a spreadsheet, with a list of the same employees’ email addresses with their department names in another spreadsheet – but you would like to have the department names, email addresses, and people’s names to appear in one spreadsheet.
This situation happens very often, and the best solution is VLOOKUP function. However, before using the formula, make sure you have one column at least that is identical in both spreadsheets. Review your data to ensure that the column of data that you are using to combine, that the information is identical, including having no extra spaces.
Here is the formula: VLOOKUP(lookup value, table array, column number, [range lookup])
From our example: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
There are several different variables in this formula, If you would like to combine information from Sheet 1 and Sheet 2 to Sheet 1, the following is true.
In our example, Sheet 1 and 2 have lists that describe different information on the same people. Their email addresses are the common thread. Say we would like to combine both of these data sets so that all Department information on Sheet 2 is translated to Sheet 1.
When this formula is typed =VLOOKUP(C2,Sheet2!A:B,2,FALSE), it brings all of the Department data over to Sheet 1.
Note that VLOOKUP only pulls values in from our second sheet that are right of the column that contains the identical data. That can result in some limitations. That is why some people prefer using MATCH and INDEX functions instead.
Back to the table of contents ⇑
MATCH and INDEX, like VLOOKUP, functions pull data in from another dataset to one central location. The following are the major differences.
So, if you would like to combine information from Sheet 1 and Sheet 2 to Sheet1, but if Sheet 1 and 2 do not have the same column values, then in order to perform a VLOOKUP, the columns would need to be switched around. If that case, you might want to perform an INDEX MATCH instead.
Here is an example. Say Sheet 1 has a list of employees’ names as well as their GetMyInvoices’s email addresses, while Sheet 2 contains a list of the employees’ email addresses along with the employee’s supervisor name. The information that is on both sheets is the column that contains the email addresses. However, on each sheet, the email addresses are in different columns. I would not need to switch columns around if I use the INDEX MATCH formula instead of using VLOOKUP.
So, what is the formula? The INDEX MATCH formula is the MATCH formula that is nested inside of the INDEX formula.
Here is the formula: =INDEX(table array, MATCH formula)
INDEX MATCH: =INDEX(table array, MATCH (lookup_value, lookup_array))
From our example, here is our formula that includes the variables: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))
The following are the variables that are used:
– Table Array: This is the column range located on Sheet 2 that contain the new data that you are wanting to take Sheet 1. “A” refers to Column A in our example, and contains each employee’s supervisor name.
– Lookup Value: This refers to the column located in Sheet that has identical values that are in both spreadsheets. In our example, it is the “Email” column or Column C on Sheet 1 Which is: Sheet1!C:C.
– Lookup Array: This column is on Sheet 2 and contains the identical values that are both in spreadsheets. It refers to the email column in Sheet 2 in our example, which is Column C as well: Sheet2!C:C.
After your variables are all right, type the INDEX MATCH formula into the upper-most cell of the blank “Supervisor” column located on Sheet 1, here you would like the combined information to be located.
Back to the table of contents ⇑
Instead of having to manually count how frequently a certain number or value appears, you can let Excel do the work instead. The COUNTIF function can be used so that Excel counts the number of times that a number or word appears in a range of cells.
From our example, say we could like to count how many times the word “Accounting” is used in the data set.
Here is the formula: =COUNTIF(range, criteria)
From our example, here is the formula with variables: =COUNTIF(B:B,”Accounting”)
– Range: This is the range that we want to be covered by the formula. In our case, since we only have one column that we are focusing on, we will use “B:B” to indicate that both columns are B. If I was looking at column A and column B, then “A:B.” would be used.
– Criteria: This is whatever piece of text or number that you would like Excel to count. Quotation marks should only be used if you want your result to be in text form rather than a number. The criterion in our example is “Accounting.”
Just type in the COUNTIF formula into any cell and then press “Enter” to show the number of times “Accounting ” appears in our data set.
Back to the table of contents ⇑
Databases have a tendency to split the data out to make it as precise as possible. So, instead of data showing an individual’s full name, the database may have data as first name and last name appearing in separate columns. Or it could have an individual’s location with separate columns for the city, state, and zip code. You can combine cells in Excel with different data into a single cell by using the “&” as your function.
From our example, the formula with variables is: =A2&” “&B2
Using an example, let’s go through this formula. Let’s say we would like to combine the first and last names into one column as full names. To that, place the cursors in the blank cell where you would like the full name to be. Then, highlight one cell containing the first name, and then type an “&” sign, and highlight the cell that has the corresponding last name.
If all you do is type =A2&B2, there will be no space in between the individual’s first name and their last name. Use the function =A2&” “&B2 to add the necessary space. The quotation marks tell Excel to place a space in between the first name and the last name.
To achieve this for multiple rows, just Copy/Paste first cell to other cells.
Back to the table of contents ⇑
If you are tracking customer data using an Excel spreadsheet and would like to oversee something that is not quantifiable, checkboxes can be inserted in a column.
For example, if an Excel sheet is being used to manage sales prospects and you want to track whether or not you called them during the past quarter, you can have a column “Called this quarter” and then check the cells off after you have called each client.
Here is how to add checkboxes in Excel:
Highlight the cell in your spreadsheet where you want to add checkboxes. Then, click on DEVELOPER. Under FORM CONTROLS, click on the checkbox.
After the box has appeared in the cell, copy the box and then highlight the other cells that you want to have the checkbox, and then paste it.
Back to the table of contents ⇑
If you are using an Excel spreadsheet to track website metrics or social medial, it can be used to include a reference column that has links for each row that is racking. A URL can be directly added into Excel and it should be clickable automatically. However, if you need to hyperlink words, like a headline or page title that you are tracking, here is how to do it.
Highlight the words that you would like to hyperlink. Next, press Shift K. A box will then pop up and allow you to add the hyperlink URL. Add the URL to the box and then click Enter.
If for some reason the key shortcut is not working, you can do it manually as well, by highlighting the cell and then clicking on Insert > Hyperlink
Back to the table of contents ⇑
You will sometimes use a spreadsheet to track qualitative things like processes. Instead of repetitively writing words in your spreadsheet, like “Prospect,” “Sales Lead,” “Customer Stage,” “Yes,” or “No, dropdown menus can be used instead to quickly mark descriptive characteristics about contacts or whatever else you are tracking.
You can add drop-downs to cells by doing the following.
Highlight the cells where you would like to have drop-downs. Then go to the top Navigation and click on the Data menu and press Validation.
A Data Validation Settings box will then open. From the Allow options, click on Lists and then choose Drop-down List. Finally, check the button labeled In-Cell dropdown and then click OK.
Now that you’ve learned 18 essential Excel tips and tricks, feel good about upping your Excel game the next time you deal with a complex worksheet. If you feel like you need to refresh your skillset every now and then, just come back to this article and review a couple of things that you need to move forward.
Excel doesn’t need to intimidate you – learn how to take advantage of its powerful features, and soon enough you’ll appreciate its sheer capabilities. This guide is designed to help you become a more confident, efficient, and productive Excel power user.
See the benefits of automated invoice management:
Better overview. Less accounting work. More time for your ideas.
Everyone liked the new work adaptation- work from home (WFH) – when the whole world...
21.07.2021
Setting a budget for 2022 is possibly one of the most exciting tasks that finance...
29.10.2021
The functions performed by finance professionals can quickly be overwhelming for them, regardless of the...
04.02.2022
