The Beginner’s Guide to Excel

Tuesday, 3. November 2020 | 0 Comments

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.

What Can I Use Excel For?

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.)

Getting Started

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.

  • Create a new spreadsheet completely from scratch.
  • Execute basic computations within a spreadsheet, such as multiplying, dividing, adding, and subtracting in a spreadsheet.
  • Write and format column titles and text.
  • Use the auto-fill features offered by Excel.
  • Add or delete spreadsheets, rows, and single columns. Below we will be discussing how to add things such as multiple rows and columns.
  • Keep row and column titles visible as you are scrolling through a spreadsheet. That way, you know what data is being filled in as you continue to move down the document.

When you feel confident in doing these things, scroll down to get started.

18 Essential Excel Power Tips and Tricks

  1. Pivot Tables can be used to make sense of and recognize data.
  2. Add together multiple columns or rows.
  3. Simplify your data by using filters.
  4. Remove duplicate data sets or points.
  5. Rows can be transposed into columns.
  6. Text can be split up between columns.
  7. Formulas can be used for basic calculations.
  8. Obtain the average of the numbers in the cells.
  9. Conditional formatting can be used to make cells change color automatically based on the data.
  10. The IF THEN Excel formula can be used for automating certain Excel functions.
  11. Dollar signs can be used to keep a cell’s formula the same no matter where it moves to.
  12. The VLOOKUP function can be used to pull data from one part of a spreadsheet to another part.
  13. INDEX MATCH formulas can be used for pulling data out of horizontal columns.
  14. The COUNT IF function can be used to make Excel count numbers or words in a range of cells.
  15. Use the & sign to combine cells.
  16. Add checkboxes to a spreadsheet
  17. Hyperlink a cell with a website
  18. Add drop-down menus

1. Pivot Tables Can Be Used to Make Sense of and Recognize Data

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.

  1. Report Filter: This option allows you to look at just certain rows that are part of your data set. For instance, in order to create a filter based on department, instead of choosing all employees, could choose to just include the accounting employees.
  2. Column Labels: They can be the headers of your dataset.
  3. Row Labels: They can be the rows in your dataset. Both Column and Row labels can contain data from the columns (e.g. you can drag the label First Name to either the Column or Row label – it depends on how you want to view the data.)
  4. Value: You can use this section to look at data in a different way. Instead of simply pulling any numeric value in, you can count numbers, use min, max, average, count, sum, or manipulate the data in a couple of other ways. By default, when a file is dragged to Value, it does a count automatically.

Excel Pivot Tables

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.

2. Add Together Multiple Columns or Rows

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.

Excel Add Multiple Columns Or Rows

3. Simplify Your Data by Using Filters

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.

Excel Using Filters

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. 

4. Remove Duplicate Data Sets or Points

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.

Excel Remove Duplicates

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.

5. Rows Can Be Transposed into Columns

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.

Excel Rows Into Columns

6. Text Can Be Split Up Between Columns

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.”

  • “Fixed Width” means you would like to choose the exact location where all of the columns that you would like to split will occur.
  • “Delimited” means you would like to break the column up based on characters like tabs, spaces, or commas.

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.

Excel Split Text To Columns

Excel Formulas

7. Formulas Can Be Used for Performing Basic Calculations

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.

  • Use the + sign, to add.
  • Use the – sign, to subtract.
  • Use the * sign, to multiply
  • Use the / sign, to divide

In our example, we will calculate the total number of working hours of all employees in accounting department.

Excel Performing Basic Calculations

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.

8. Obtain the Average of the Numbers Contained In the Cells

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.

9. Conditional Formatting Can Be Used to Make Cells Change Color Automatically Based On the Data

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.

Excel Conditional Formatting

10. The IF THEN Excel Formula Can Be Used for Automating Certain Excel Functions

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.

Excel If Formula

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.

  • Logical_Test: The “IF” portion of the statement is the logical test. The logic in our example is D2=”Accounting” since we want to ensure that the cell that corresponds with the employee contains “Accounting.” Be sure that Accounting is placed in quotation marks.
  • Value_if_True: When the value is true, this is what we want to show in the cell. In our example, we want “YES” to show in the cell to indicate that the employee has 40 working hours. Quotation marks should only be used when you want the result to be in text form rather than a number.
  • Value_if_False: When the value is false, this what we want to be shown in the cell. For all employees, we want “NO” to be shown in the cell to represent less than 40 working hours. Quotation marks should only be used if you want the result to be in text form rather than a number.

11. Dollar Signs Can Be Used to Keep a Cell’s Formula the Same No Matter Where It Moves To

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).

Excel Functions

12. The VLOOKUP Function Can Be Used to Pull Data from One Part of a Spreadsheet to another Part

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)

Excel VLOOKUP Function

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.

  • Lookup Value: This refers to the identical value that is in both spreadsheets. Select the first value in the first spreadsheet. In our example, that is the first email address in our list, or cell 2 (C2).
  • Table Array: This refers to the column range located on Sheet 2 that you will be pulling data from, which includes the column of data that is identical to the lookup value (email addresses, in our example) in Sheet 1 and the column of data that you want to copy to Sheet 1. This is “Sheet2!A:B.” in our example, which is Column A in Sheet 2. That is the column from Sheet 2 where our data is identical to the lookup value (email) that is in Sheet 1. “B” refers to Column B, which contains information that is available only in Sheet 2 that you would like to translate over to Sheet 1. 
  • Column Number: This is the table array (range of columns you have indicated). It tells Excel which column your new data is located that you would like to copy to Sheet 1. In our example, that would be the column where “Department” is located. In our range of columns (or table array) “Department” is located in the second columns, so 2 is the column number. [Note: The range can cover more than just two columns. For instance, if Sheet 2 has three columns – Department, Phone Number, and Email – you still might want to take Department to Sheet 1, and VLOOKUP can still be used. The “2” just needs to be changed to a “3” to pull the value into the third column: The result is =VLOOKUP(C2:Sheet2!A:C,3,FALSE) for example.
  • Range Lookup: FALSE should be used to make sure you only pull in exact value matches.

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.

13. INDEX MATCH Formulas Can Be Used for Pulling Data Out of Horizontal Columns

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.

  • The VLOOKUP formula is much simpler. If you have large data sets that you are working with that would necessitate thousands of lookups, you can significantly reduce your Excel load time by using the INDEX MATCH function.
  • An INDEX MATCH formula works from right to left. VLOOKUP formulas, by contrast, work only as a left-to-right lookup. So, if you need to perform a lookup with a lookup column that is to the right of your results column, then you would need to rearrange the columns to perform a VLOOKUP. That can lead to errors or be very tedious when you have large datasets.

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)))

Excel Index Match

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.

14. The COUNTIF Function Can Be Used to Make Excel Count Numbers or Words in a Range Of Cells

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.

Excel COUNTIF Function

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.

15. Use the & Sign to Combine Cells

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.

Excel Combine Cells

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.

16. Add Checkboxes to a Spreadsheet

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.

Final Thoughts

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.

Try GetMyInvoices
now for free for 14 days

See the benefits of automated invoice management:
Better overview. Less accounting work. More time for your ideas.

Test for free

Weitere interessante
Beiträge

Collect invoices automatically from emails

Waiting and checking for invoices from various business partners is a time-consuming procedure. The lifecycle...

13.09.2021

Pandemic impacts on VDI – Virtual and desktop infrastructure

We might never have imagined such a rush for remote working capabilities as seen over...

17.06.2021

How To Avoid These 10 Common Financial Forecasting Mistakes

Financial forecasting plays a fundamental role in positioning an organization for sustainability and growth. However,...

25.11.2021