EXCEL HOW TO USE

                      HOW TO USE EXCEL

 

If you want to sum up a column of numbers, you can use the formula =SUM(Cell1:Cell2).

9. That headache can be made even worse when you need to compare data across multiple spreadsheets. Remove duplicate data points or sets.

Larger data sets tend to have duplicate content. 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. The syntax is: COUNT IF(range, criteria). Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Your resulting list will have only unique names without any duplicates.

5. If you're working with large data sets that would require thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in Excel.
The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. 

Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

INDEX: The INDEX function returns a value from within a range. This will help you quickly see information that is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Below, we'll get into how to add things like multiple columns and rows.

Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you're filling as you move further down the document.

For a deep dive on these basics, check out our comprehensive Microsoft Excel guide.

How to Use Excel

To use Excel, you only need to input the data into the rows and columns.
We're going to go over the best formulas and functions you need to know. (Learn more on Microsoft Office's support page here.)

12. Check that box and select OK. 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. 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. It allows you to get a holistic view of a company’s financial standing.
Calendar: You can easily create a spreadsheet monthly calendar to track events or other date-sensitive information.

Here are some documents you can create specifically for marketers.

Marketing Budgets: Excel is a strong budget-keeping tool. Then, click DEVELOPER. You'll see I differentiated the MATCH formula using a different color here.

The formula: =INDEX(table array, MATCH formula)

This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))
The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. Example: =C5+D3.

Subtraction: To subtract the values of two or more cells, use the - sign. When applicable, the copy has been updated to provide instruction for users of both newer and older Excel versions.

1. you're welcome in advance.)

Excel Tips

Use Pivot tables to recognize and make sense of data.

Add more than one row or column.

Use filters to simplify your data.

Remove duplicate data points or sets.

Transpose rows into columns.

Split up text information between columns.

Use these formulas for simple calculations.

Get the average of numbers in your cells.

Use conditional formatting to make cells automatically change color based on data.

Use IF Excel formula to automate certain Excel functions.

Use dollar signs to keep one cell's formula the same regardless of where it moves.

Use the VLOOKUP function to pull data from one area of a sheet to another.

Use INDEX and MATCH formulas to pull data from horizontal columns.

Use the COUNT IF  function to make Excel count words or numbers in any range of 

cells.

Combine cells using ampersand.

Add check boxes.

Hyperlink a cell to a website.

Add drop-down menus.

Note: The GIFs and visuals are from a previous version of Excel. But first, let's take a look at the types of documents you can create using the software. Select "Remove Duplicates," and you're good to go.

Removing duplicates in Excel

You can also use this feature to remove an entire row based on a duplicate column value. Add checkboxes.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a "Called this quarter?" column and check off the cells in it when you've called the respective client.

Here's how to do it.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. I'd use the INDEX and MATCH formulas instead of VLOOKUP so I wouldn't have to switch any columns around.

So what's the formula, then? The formula is actually the MATCH formula nested inside the INDEX formula. Then, you have four options to choose from.

Report Filter: This allows you to only look at certain rows in your data set. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. That way, you have an overarching understanding of how you can use Excel in your day-to-day. Use filters to simplify your data.

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. Example: =COUNT-IF(A2:A5,"London").
Okay, ready to get into the nitty-gritty? Let's get to it. The syntax is as follows: INDEX(array, row_num, [column_num]).

In this case, for any student not in Gryffindor, we want the cell to show "0". ("Something else" could be the "@" sign used in an email address, for example.) In our example, let's choose the space. Combine cells using &

Databases tend to split out data to make it as exact as possible. Intimidating because you're not.

How to Use VLOOKUP Function in Microsoft Excel [+ Video Tutorial]

Coordinating a massive amount of data in Microsoft Excel is a time-consuming headache. Need to do simple math? Excel can do it. When you're done, click "Finish."
Excel text to column tool

7. 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 builder and drag the House column to both the Row Labels and the Values. Copy and paste the URL into this box and hit or click Enter.
If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink.

18.
Documents You Can Create in Excel

Not sure how you can actually use Excel in your team? Here is a list of documents you can create:

  Statements: You can use an Excel spreadsheet to track a company’s sales activity and financial health.

Balance Sheets: Balance sheets are among the most common types of documents you can create with Excel. Then, go to the Data tab and select "Remove Duplicates" (which is under the Tools sub header in the older version of Excel). Sometimes, you may even need to add hundreds of rows. Use INDEX and MATCH formulas to pull data from horizontal columns.

Like VLOOKUP, the INDEX and MATCH functions pull in data from another data set into one central location. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.

13. Need to merge two sheets with similar data? Excel can do it. For instance, instead of using the + sign to add up a range of cells, you’d use the SUM function. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Excel checkboxes

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. 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. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. The tab format makes it extremely easy to track your content creation efforts for custom time ranges. These are things like:
Creating a new spreadsheet from scratch.

Executing basic computations like adding, subtracting, multiplying, and dividing.
Writing and formatting column text and titles.

Using Excel's auto-fill features.

Adding or deleting single columns, rows, and spreadsheets. Pretend we want to combine first names and last names into full names in a single column. 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. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). (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. If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum.

11. Need to combine information in multiple cells? Excel can do it.
In this post, I’ll go over the best tips, tricks, and shortcuts you can use right now to take your Excel game to the next level. This could be a tab, semi-colon, comma, space, or something else. 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: This tells Excel which column the new data you want to copy to Sheet 1 is located in. Keep in mind that you can use plain numbers in your formulas.

Excel Functions

Excel functions automate some of the tasks you would use in a typical formula. It removes the need to spend hours and hours counting cells or copying and pasting performance numbers. 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, Approximate match (TRUE) or Exact match (FALSE))

The formula with variables from our example below  =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they shouldHome Software  

If you want to sum up a column of numbers, you can use the formula =SUM(Cell1:Cell2).

9. That headache can be made even worse when you need to compare data across multiple spreadsheets. Remove duplicate data points or sets.

Larger data sets tend to have duplicate content. 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. The syntax is: COUNTIF(range, criteria). Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Your resulting list will have only unique names without any duplicates.

5. If you're working with large data sets that would require thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in Excel.
The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. Example:

 =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
INDEX: The INDEX function returns a value from within a range. This will help you quickly see information that is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Below, we'll get into how to add things like multiple columns and rows.
Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you're filling as you move further down the document.
For a deep dive on these basics, check out our comprehensive Microsoft Excel guide.
How to Use Excel

To use Excel, you only need to input the data into the rows and columns.
We're going to go over the best formulas and functions you need to know. (Learn more on Microsoft Office's support page here.)

12. Check that box and select OK. 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. 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. It allows you to get a holistic view of a company’s financial standing.
Calendar: You can easily create a spreadsheet monthly calendar to track events or other date-sensitive information.

Here are some documents you can create specifically for marketers.
Marketing Budgets: Excel is a strong budget-keeping tool. Then, click DEVELOPER. You'll see I differentiated the MATCH formula using a different color here.
The formula: =INDEX(table array, MATCH formula)
This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))
The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. Example: =C5+D3.
Subtraction: To subtract the values of two or more cells, use the - sign. When applicable, the copy has been updated to provide instruction for users of both newer and older Excel versions.

1. you're welcome in advance.)
Excel Tips

Use Pivot tables to recognize and make sense of data.

Add more than one row or column.

Use filters to simplify your data.

Remove duplicate data points or sets.

Transpose rows into columns.

Split up text information between columns.

Use these formulas for simple calculations.

Get the average of numbers in your cells.

Use conditional formatting to make cells automatically change color based on data.

Use IF Excel formula to automate certain Excel functions.
Use dollar signs to keep one cell's formula the same regardless of where it moves.

Use the VLOOKUP function to pull data from one area of a sheet to another.

Use INDEX and MATCH formulas to pull data from horizontal columns.

Use the COUNTIF function to make Excel count words or numbers in any range of 

cells.

Combine cells using ampersand.

Add checkboxes.

Hyperlink a cell to a website.

Add drop-down menus.

Note: The GIFs and visuals are from a previous version of Excel. But first, let's take a 

look at the types of documents you can create using the software. Select "Remove 

Duplicates," and you're good to go.

Removing duplicates in Excel

You can also use this feature to remove an entire row based on a duplicate column value. 

Add checkboxes.

If you're using an Excel sheet to track customer data and want to oversee something that 

isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a "Called this quarter?" column and check off the cells in it when you've called the respective client.

Here's how to do it.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. I'd use the INDEX and MATCH formulas instead of VLOOKUP so I wouldn't have to switch any columns around.

So what's the formula, then? The formula is actually the MATCH formula nested inside the INDEX formula. Then, you have four options to choose from.

Report Filter: This allows you to only look at certain rows in your dataset. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. That way, you have an overarching understanding of how you can use Excel in your day-to-day. Use filters to simplify your data.
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. Example: =COUNTIF(A2:A5,"London").
Okay, ready to get into the nitty-gritty? Let's get to it. The syntax is as follows: INDEX(array, row_num, [column_num]).

MATCH: The MATCH function looks for a certain item in a range of cells and returns the position of that item. It can be used in tandem with the INDEX function. Need to merge two sheets with similar...Read more
. In this case, for any student not in Gryffindor, we want the cell to show "0". ("Something else" could be the "@" sign used in an email address, for example.) In our example, let's choose the space. Combine cells using &.
Databases tend to split out data to make it as exact as possible. Intimidating because you're not...Read more
How to Use VLOOKUP Function in Microsoft Excel [+ Video Tutorial]
Coordinating a massive amount of data in Microsoft Excel is a time-consuming headache. Need to do simple math? Excel can do it. When you're done, click "Finish."
Excel text to column tool

7. 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 builder and drag the House column to both the Row Labels and the Values. Copy and paste the URL into this box and hit or click Enter.
If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink.

18.
Documents You Can Create in Excel
Not sure how you can actually use Excel in your team? Here is a list of documents you can create:
Income Statements: You can use an Excel spreadsheet to track a company’s sales activity and financial health.
Balance Sheets: Balance sheets are among the most common types of documents you can create with Excel. Then, go to the Data tab and select "Remove Duplicates" (which is under the Tools subheader in the older version of Excel). Sometimes, you may even need to add hundreds of rows. Use INDEX and MATCH formulas to pull data from horizontal columns.
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.
13. Need to merge two sheets with similar data? Excel can do it. For instance, instead of using the + sign to add up a range of cells, you’d use the SUM function. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.
Excel checkboxes

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.
17. 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. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. The tab format makes it extremely easy to track your content creation efforts for custom time ranges. These are things like:
Creating a new spreadsheet from scratch.
Executing basic computations like adding, subtracting, multiplying, and dividing.
Writing and formatting column text and titles.

Using Excel's auto-fill features.

Adding or deleting single columns, rows, and spreadsheets. Pretend we want to combine first names and last names into full names in a single column. 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. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). (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. If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum.
11. Need to combine information in multiple cells? Excel can do it.
In this post, I’ll go over the best tips, tricks, and shortcuts you can use right now to take your Excel game to the next level. This could be a tab, semi-colon, comma, space, or something else. 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: This tells Excel which column the new data you want to copy to Sheet 1 is located in. Keep in mind that you can use plain numbers in your formulas.
Excel Functions

Excel functions automate some of the tasks you would use in a typical formula. It removes the need to spend hours and hours counting cells or copying and pasting performance numbers. 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, Approximate match (TRUE) or Exact match (FALSE))

The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this formula, there are several variables. Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they should get ten points.
The formula is: IF(logical_test, value_if_true, [value_if_false])
Example Shown Below: =IF(D2="Gryffindor","10","0")
In general terms, the formula would be IF(Logical Test, value of true, value of false). For example, instead of having a column 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. This can be frustrating if you can’t always see key data...Read more
How to Create a Pivot Table in Excel: A Step-by-Step Tutorial
The pivot table is one of Microsoft Excel's most powerful — and intimidating — functions. If you want to color code commonalities between different rows in Excel, you can do that. With the tips and tricks we shared in this post, you’ll be sure to use Excel to its fullest extent and get the most out of the software to grow your business..
Topics:
Excel
Don't forget to share this post!
Related Articles
The Quick & Easy Guide To Freezing Rows in Excel
Without freezing rows or columns in your Excel spreadsheet, everything moves when you scroll through the page, as shown in the gif below. Doing this one-by-one would be super tedious. 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. If you use TRUE, the function will pull in approximate matches.
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. You can create and track marketing budgets, as well as spend, using Excel. In the example that follows, this refers to the "email" column on Sheet 2, which happens to also be Column C. So: Sheet1!C:C.

Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. However, if we made it (10+10)*10, the first and second 10 would be added together first.

Excel simple formulas in action

8. Sometimes, Excel seems too good to be true. Excel is an excellent tool to create marketing reports. The last thing
How to Make a Chart or Graph in Excel [With Video Tutorial]

Building charts and graphs is one of the best ways to visualize data in a clear, easy-to-understand way. Check the In-Cell dropdown button, then press OK.

Other Excel Help Resources

How to Make a Chart or Graph in Excel [With Video Tutorial]

Design Tips to Create Beautiful Excel Charts and Graphs

Totally Free Microsoft Excel Templates That Make Marketing Easier

How to Learn Excel Online: Free and Paid Resources for Excel Training

Use Excel to Automate Processes in Your Team

Even if you’re not an accountant, you can still use Excel to automate tasks and processes in your team. Click here to download a free premade calculator.

This is only a small sampling of the types of marketing and business documents you can create in Excel. Let’s look at a few more functions that will help automate calculations and tasks.

SUM: The SUM function automatically adds up a range of cells or numbers. Choose the first value in your first spreadsheet. It is most often used in accounting, but can be used by virtually any professional to manage long and unwieldy datasets. In this case, we want the cell to show "10" to indicate that the student was awarded the 10 points. "House" is the second column in our range of columns (table array), so our column number is 2. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2). If you don’t want to create a document from scratch, download our marketing budget templates for free.

Marketing Reports: If you don’t use a marketing tool such as Marketing Hub, you might find yourself in need of a dashboard with all of your reports. All I have to do is enter a formula, and pretty much anything I'd ever need to do manually can be done automatically. Use conditional formatting to make cells automatically change color based on data.

Conditional formatting allows you to change a cell's color based on the information within the cell. If you’re using the most recent version of Excel, you’d go to Insert > Pivot Table. Powerful because it can help you summarize and make sense of large data sets. Download free Excel marketing reporting templates here.

Editorial Calendars: You can create editorial calendars in Excel. Example: =(C5-D3)/((A5+B6)*3).For more complex formulas, you’ll need to use parentheses around the expressions to avoid accidentally using the PEMDAS order of operations. Split up text information between 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. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. By highlighting three rows and then clicking insert, I'm able to add an additional three blank rows into my spreadsheet quickly and easily.

insert Spaces on Excel
3. 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 would be your headers in the dataset.

Row Labels: These could be your rows in the dataset. Select "OK" when you're done, and you should see your results automatically appear.

Excel conditional formatting
10. Rather than writing words into your sheet repetitively, such as "Yes", "No", "Customer Stage", "Sales Lead", or "Prospect", you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.
Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

Excel drop-down menu option
From there, you'll see a Data Validation Settings box open. 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. 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. 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, I'd choose to do an INDEX and MATCH instead.

Let's look at an example. In the example below (10+10*10), the second and third 10 were multiplied together before adding the additional 10. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)). Use the IF Excel formula to automate certain Excel functions.
Sometimes, we don't want to count the number of times a value appears. Example: =C5-D3.
Multiplication: To multiply the values of two or more cells, use the * sign. Get the average of numbers in your cells.
If you want the average of a set of numbers, you can use the formula =AVERAGE(Cell1:Cell2). No advanced Excel knowledge required.

What is Excel?

Microsoft Excel is a spreadsheet software that marketers, accountants, data analysts, and other professionals use to store, organize, and track data sets. Then, choose "Conditional Formatting" from the Home menu and select your logic from the dropdown. To add that necessary space, use the function =A2&" "&B2. You may have a list of multiple contacts in a company and only want to see the number of companies you have. Let's say we want to combine both datasets so that all the house information from mp Sheet  2 translates over to Sheet 1.

Excel VLOOKUP function
So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.
Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. 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. Only use quotation marks if you want the result to be text instead of a number.

Excel IF formula in action
Note: In the example above, I awarded 10 points to everyone in Gryffindor. 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. Excel typically has a shortcut or quick fix that speeds up the process.

You can also download Excel templates below for all of your marketing needs. Then, right-click and select "Insert."

In the example below, I want to add an additional three rows. 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).]

Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only exact value matches. Examples of Excel applications include balance sheets, budgets, editorial calendars, and data calculators.

Excel is primarily used for creating financial documents because of its strong computational powers. If you’re just getting started using Excel, you can rely on the following formulas to carry out some complex functions — without adding to the complexity of your learning path.

Equal sign: Before creating any formula, you’ll need to write an equal sign (=) in the cell where you want the result to appear.

Addition: To add the values of two or more cells, use the + sign. (And to all the Harry Potter fans out there . This is called a relative formula. 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. In our example, "A" means Column A, which contains the "Patronus" information for each person.
Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. Use Pivot tables to recognize and make sense of data.
Pivot tables are used to reorganize data in a spreadsheet. 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. Let's say I want to take a look at how many people are in each house at Hogwarts. In the example that follows, this means the "email" column on Sheet 1, which is Column C. Right-click it, and then select "Copy." Next, select the cells on your spreadsheet where you want your first row or column to begin. 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. [Note: Your range can be more than two columns. Example: =C5*D3.
Division: To divide the values of two or more cells, use the / sign. Both Row and Column labels can contain data from your columns (e.g. 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. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX and MATCH formulas in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.

Excel INDEX and MATCH functions in action
14. 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. Find more Excel alternatives here.

What is Excel used for?

Excel is used for organizing, filtering, and visualizing large amounts of data. First, highlight the column that you want to split up. Example: =AVERAGE(C5:C30).
IF: The IF function allows you to return values based on a logical test. In the example that follows, this means the first email address on the list, or cell 2 (C2).
Table Array: The table array is 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. Your column will now be transferred to a row or vice-versa.
Transpose tool in Excel
On newer versions of Excel, a drop-down will appear instead of a pop-up.
Excel transpose tool in newer versions

6. Look at the Allow options, then click Lists and select Drop-down List. We’ve created an extensive list of Excel templates you can use right now for marketing, invoicing, project management, budgeting, and more.

In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel formulas and functions you’ll need to know.
Excel Formulas

It’s easy to get overwhelmed by the wide range of Excel formulas that you can use to make sense out of your data. Add more than one row or column.

As you play around with your data, you might find you're constantly needing to add more rows and columns. 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. By selecting the Gryffindor filter, the other rows disappear.

Excel filters in actionPro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.

4. It’s part of the Microsoft Office suite of products. The syntax is: MATCH(lookup_value, lookup_array, [match_type]).
COUNTIF: The COUNTIF function returns the number of cells that meet a certain criteria or have a certain value.

FEATURED RESOURCE

Free Excel Templates

Fill out the form to download your free templates.

Open form

Hi 👋 What's your name?

First Name

John
Last Name

Smith

Hi null, what's your email address?

Email Address

john@smith.com

And your phone number?

Phone Number

111 222 3333

What is your company's name and website?

Company
HubSpot

Website

www.hubspot.com

How many employees work there?

How many employees work there?
Get Your Free Templates

After you download the templates, it’s time to start using the software. Right-click on 

the cell, and then select "Paste Special." A module will appear — at the bottom, you'll 

see an option to transpose. 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. Let’s cover the basics first.

Excel Basics

If you're just starting out with Excel, there are a few basic commands that we suggest 

you become familiar with. All I have to do is enter a formula, and pretty much anything 

I'd ever need to do manually can be done automatically. Example: =SUM(C5:C30).

AVERAGE: The AVERAGE function averages out the values of a range of cells. In our 

example, this would be the column that "House" is located in. 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 turning the formula into 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). Use formulas for 

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.

You can also use parentheses to ensure certain calculations are done first. Add drop-

down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative 

things. In our example, the criteria is "Gryffindor."

Simply typing in the COUNTIF formula in any cell and pressing "Enter" will show me 

how many times the word "Gryffindor" appears in the dataset.

Excel COUNTIF function

15. 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, simply drag the corner of that first cell downward as 

shown in the example.
Excel combination of cells

16. 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. You’ll often find 

the software in accounting offices and teams because it allows accountants to 

automatically see sums, averages, and totals. From there a box will pop up allowing you 

to place the hyperlink URL. Here are the main differences:

VLOOKUP is a much simpler formula. Excel will then show you a preview of what 

your new columns will look like.

When you're happy with the preview, press "Next." This page will allow you to select 

Advanced Formats if you choose to. 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. Example: 

=IF(A2>B2,"Over Budget","OK").

VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s 

rows. And then you'll use formulas and functions to turn that data into insights. 

Alternatives include Google Sheets and Numbers. With Excel, they can easily make 

sense of their business’ data.

While Excel is primarily known as an accounting tool, professionals in any field can use 

its features and formulas — especially marketers — because it can be used for tracking 

any type of data. Use dollar signs to keep one cell's formula the same regardless of 

where it moves.

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn't 

representing an American dollars 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. Download a free editorial content calendar template here.
Traffic and Leads Calculator: Because of its strong computational powers, Excel is an 

excellent tool to create all sorts of calculators — including one for tracking leads and 

traffic. This will sum up the number of students associated with each house.Excel pivot 

table creation

2. Let's dig into each of these variables.

Logical_Test: The logical test is the "IF" part of the statement. If you add a URL directly 

into Excel, it should automatically be clickable. 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. 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. 

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. 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, if you have to hyperlink words, such as a page title or 

the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. 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. 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. Here’s what that looks 

like: SUM(Cell1:Cell2). Example: =C5/D3.

Putting all of these together, you can create a formula that adds, subtracts, multiplies, 

and divides all in one cell. 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 award ten points to everyone who belongs 

in the Gryffindor house. A pop-up will appear to confirm which data you want to work 

with. To do this, we'd first put our cursor in the blank cell w

here we want the full name to appear. To complete a sum, you would input the starting 

cell and the final cell with a colon in between. Only use quotation marks if you want the 

result to be text instead of a number. Use the COUNTIF function to make Excel count 

words or numbers in any range of cells.

Instead of manually counting how often a certain value or number appears, let Excel do the work for you. 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). Use the VLOOKUP function to pull data from one area of a sheet to another.

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. Transpose rows into columns.

When you have 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). (Check out this 

guide for making better charts to learn more.)However, it's no surprise that...Read more

How to Use Excel Like a Pro: 18 Easy Excel Tips, Tricks, & Shortcuts

Sometimes, Excel seems too good to be true