Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically.
Need to merge two sheets with similar data? Excel can do it.
Need to do simple math? Excel can do it.
Need to combine information in multiple cells? Excel can do it.
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. No advanced Excel knowledge required.
What is Excel?
Microsoft Excel is powerful data visualization and analysis software, which uses spreadsheets to store, organize, and track data sets with formulas and functions. Excel is used by marketers, accountants, data analysts, and other professionals. It’s part of the Microsoft Office suite of products. Alternatives include Google Sheets and Numbers.
Find more Excel alternatives here.
What is Excel used for?
Excel is used to store, analyze, and report on large amounts of data. It is often used by accounting teams for financial analysis, but can be used by any professional to manage long and unwieldy datasets. Examples of Excel applications include balance sheets, budgets, or editorial calendars.
Excel is primarily used for creating financial documents because of its strong computational powers. You’ll often find the software in accounting offices and teams because it allows accountants to automatically see sums, averages, and totals. 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. It removes the need to spend hours and hours counting cells or copying and pasting performance numbers. 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.
After you download the templates, it’s time to start using the software. Let’s cover the basics first.
If you’re just starting out with Excel, there are a few basic commands that we suggest you become familiar with. 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. (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.
- Sorting your data in alphabetical order.
Let’s explore a few of these more in-depth.
For instance, why does auto-fill matter?
If you have any basic Excel knowledge, it’s likely you already know this quick trick. But to cover our bases, allow me to show you the glory of autofill. This lets you quickly fill adjacent cells with several types of data, including values, series, and formulas.
There are multiple ways to deploy this feature, but the fill handle is among the easiest. Select the cells you want to be the source, locate the fill handle in the lower-right corner of the cell, and either drag the fill handle to cover cells you want to fill or just double click:
Similarly, sorting is an important feature you’ll want to know when organizing your data in Excel.
Sometimes you may have a list of data that has no organization whatsoever. Maybe you exported a list of your marketing contacts or blog posts. Whatever the case may be, Excel’s sort feature will help you alphabetize any list.
Click on the data in the column you want to sort. Then click on the “Data” tab in your toolbar and look for the “Sort” option on the left. If the “A” is on top of the “Z,” you can just click on that button once. If the “Z” is on top of the “A,” click on the button twice. When the “A” is on top of the “Z,” that means your list will be sorted in alphabetical order. However, when the “Z” is on top of the “A,” that means your list will be sorted in reverse alphabetical order.
Let’s explore more of the basics of Excel (along with advanced features) next.
How to Use Excel
To use Excel, you only need to input the data into the rows and columns. And then you’ll use formulas and functions to turn that data into insights.
We’re going to go over the best formulas and functions you need to know. But first, let’s take a look at the types of documents you can create using the software. That way, you have an overarching understanding of how you can use Excel in your day-to-day.
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. 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.
This is only a small sampling of the types of marketing and business documents you can create in Excel. 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.
It’s easy to get overwhelmed by the wide range of Excel formulas that you can use to make sense out of your data. 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. Example: =C5+D3.
- Subtraction: To subtract the values of two or more cells, use the – sign. Example: =C5-D3.
- Multiplication: To multiply the values of two or more cells, use the * sign. Example: =C5*D3.
- Division: To divide the values of two or more cells, use the / sign. Example: =C5/D3.
Putting all of these together, you can create a formula that adds, subtracts, multiplies, and divides all in one cell. 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. Keep in mind that you can use plain numbers in your formulas.
Excel functions automate some of the tasks you would use in a typical formula. For instance, instead of using the + sign to add up a range of cells, you’d use the SUM function. 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. To complete a sum, you would input the starting cell and the final cell with a colon in between. Here’s what that looks like: SUM(Cell1:Cell2). Example: =SUM(C5:C30).
- AVERAGE: The AVERAGE function averages out the values of a range of cells. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2). Example: =AVERAGE(C5:C30).
- IF: The IF function allows you to return values based on a logical test. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). Example: =IF(A2>B2,”Over Budget”,”OK”).
- VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s rows. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)). Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
- INDEX: The INDEX function returns a value from within a range. 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. 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. The syntax is: COUNTIF(range, criteria). Example: =COUNTIF(A2:A5,”London”).
Okay, ready to get into the nitty-gritty? Let’s get to it. (And to all the Harry Potter fans out there … you’re welcome in advance.)
- 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.
- Use the format painter.
Note: The GIFs and visuals are from a previous version of Excel. When applicable, the copy has been updated to provide instruction for users of both newer and older Excel versions.
1. Use Pivot tables to recognize and make sense of data.
Pivot tables are used to reorganize data in a spreadsheet. They won’t change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you’d like them to do.
Let’s take a look at an example. Let’s say I want to take a look at how many people are in each house at Hogwarts. 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. If you’re using the most recent version of Excel, you’d go to Insert > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.
- Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
- Column Labels: These would be your headers in the dataset.
- Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
- Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.
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. This will sum up the number of students associated with each house.
2. 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. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there’s always an easier way.
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”
In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I’m able to add an additional three blank rows into my spreadsheet quickly and easily.
3. 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. Sometimes, you only want to look at data that fit into certain criteria.
That’s where filters come in.
Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data — and from there, you can then choose which cells you want to view at once.
Let’s take a look at the example below. Add a filter by clicking the Data tab and selecting “Filter.” Clicking the arrow next to the column headers and you’ll be able to choose whether you want your data to be 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. By selecting the Gryffindor filter, the other rows disappear.
Pro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.
4. Remove duplicate data points or sets.
Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.
To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab and select “Remove Duplicates” (which is under the Tools subheader in the older version of Excel). A pop-up will appear to confirm which data you want to work with. Select “Remove Duplicates,” and you’re good to go.
You can also use this feature to remove an entire row based on a duplicate column value. 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. Your resulting list will have only unique names without any duplicates.
5. 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). It would take a lot of time to copy and paste each individual header — but what the transpose feature allows you to do is simply move your row data into columns, or the other way around.
Start by highlighting the column that you want to transpose into rows. Right-click it, and then select “Copy.” Next, select the cells on your spreadsheet where you want your first row or column to begin. Right-click on the cell, and then select “Paste Special.” A module will appear — at the bottom, you’ll see an option to transpose. Check that box and select OK. Your column will now be transferred to a row or vice-versa.
On newer versions of Excel, a drop-down will appear instead of a pop-up.
6. 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. Or perhaps you want to separate someone’s full name into a first and last name for your email marketing templates.
Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select “Text to Columns.” A module will appear with additional information.
First, you need to select either “Delimited” or “Fixed Width.”
- “Delimited” means you want to break up the column based on characters such as commas, spaces, or tabs.
- “Fixed Width” means you want to select the exact location on all the columns that you want the split to occur.
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. This could be a tab, semi-colon, comma, space, or something else. (“Something else” could be the “@” sign used in an email address, for example.) In our example, let’s choose the space. 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. When you’re done, click “Finish.”
7. 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. In the example below (10+10*10), the second and third 10 were multiplied together before adding the additional 10. However, if we made it (10+10)*10, the first and second 10 would be added together first.
8. 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). If you want to sum up a column of numbers, you can use the formula =SUM(Cell1:Cell2).
9. 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. For example, if you want to flag certain numbers that are above average or in the top 10% of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information that is important to you.
To get started, highlight the group of cells you want to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.
10. Use the IF Excel formula to automate certain Excel functions.
Sometimes, we don’t want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.
For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. 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). Let’s dig into each of these variables.
- Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor” because we want to make sure that the cell corresponding with the student says “Gryffindor.” Make sure to put Gryffindor in quotation marks here.
- Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show “10” to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
- Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show “0”. Only use quotation marks if you want the result to be text instead of a number.
Note: In the example above, I awarded 10 points to everyone in Gryffindor. 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.
The real power of the IF function comes when you string multiple IF statements
Ranges are one way to segment your data for better analysis. For example, you can categorize data into values that are less than 10, 11 to 50, or 51 to 100. Here’s how that looks in practice:
=IF(B3<11,“10 or less”,IF(B3<51,“11 to 50”,IF(B3<100,“51 to 100”)))
It can take some trial-and-error, but once you have the hang of it, IF formulas will become your new Excel best friend.
11. 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 dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.
You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you’re actually referring to a cell that’s five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not — and we can do that by 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). (Learn more on Microsoft Office’s support page here.)
12. 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. 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. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.
- Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
- Table Array: The 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. 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. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
- Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only exact value matches. 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. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.
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. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.
13. 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. Here are the main differences:
- VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, 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. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.
So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX and MATCH instead.
Let’s look at an example. 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. (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. 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. 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. 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. In the example that follows, this means the “email” column on Sheet 1, which is Column C. So: Sheet1!C:C.
- Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the “email” column on Sheet 2, which happens to also be Column C. 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.
14. 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. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.
For example, let’s say I want to count the number of times the word “Gryffindor” appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,”Gryffindor”)
In this formula, there are several variables:
- Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
- Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”
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.
15. Combine cells using &.
Databases tend to split out data to make it as exact as possible. 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. In Excel, you can combine cells with different data into one cell by using the “&” sign in your function.
The formula with variables from our example below: =A2&” “&B2
Let’s go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we’d first put our cursor in the blank cell where we want the full name to appear. Next, we’d highlight one cell that contains a first name, type in an “&” sign, and then highlight a cell with the corresponding last name.
But you’re not finished — if all you type in is =A2&B2, then there will not be a space between the person’s first name and last name. To add that necessary space, use the function =A2&” “&B2. The quotation marks around the space tell Excel to put a space in between the first and last name.
To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example.
16. 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. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.
Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.
17. 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. If you add a URL directly into Excel, it should automatically be clickable. 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. From there a box will pop up allowing you to place the hyperlink URL. 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. Add drop-down menus.
Sometimes, you’ll be using your spreadsheet to track processes or other qualitative things. 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.
From there, you’ll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.
19. Use the format painter.
As you’ve probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.
Don’t waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you’d like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.
Excel Keyboard Shortcuts
Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.
Create a New Workbook
PC: Ctrl-N | Mac: Command-N
Select Entire Row
PC: Shift-Space | Mac: Shift-Space
Select Entire Column
PC: Ctrl-Space | Mac: Control-Space
Select Rest of Column
PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up
Select Rest of Row
PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left
PC: Ctrl-K | Mac: Command-K
Open Format Cells Window
PC: Ctrl-1 | Mac: Command-1
Autosum Selected Cells
PC: Alt-= | Mac: Command-Shift-T
Other Excel Help Resources
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. 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.
Editor’s Note: This post was originally published in August 2017 but has been updated for comprehensiveness.
Intro to Amazon Non-endemic Advertising: Benefits & Examples
Amazon has rewritten the rules of advertising with its move into non-endemic retail media advertising. Advertising on Amazon has traditionally focused on brands and products directly sold on the platform. However, a new trend is emerging – the rise of non-endemic advertising on this booming marketplace. In this article, we’ll dive into the concept of non-endemic ads, their significance, and the benefits they offer to advertisers. This strategic shift is opening the floodgates for advertisers in previously overlooked industries.
While endemic brands are those with direct competitors on the platform, non-endemic advertisers bring a diverse range of services to Amazon’s vast audience. The move toward non-endemic advertising signifies Amazon’s intention to leverage its extensive data and audience segments to benefit a broader spectrum of advertisers.
Endemic vs. Non-Endemic Advertising
Let’s start by breaking down the major differences between endemic advertising and non-endemic advertising…
Endemic advertising revolves around promoting products available on the Amazon platform. With this type of promotion, advertisers use retail media data to promote products that are sold at the retailer.
In contrast, non-endemic advertising ventures beyond the confines of products sold on Amazon. It encompasses industries such as insurance, finance, and services like lawn care. If a brand is offering a product or service that doesn’t fit under one of the categories that Amazon sells, it’s considered non-endemic. Advertisers selling products and services outside of Amazon and linking directly to their own site are utilizing Amazon’s DSP and their data/audience segments to target new and relevant customers.
7 Benefits of Running Non-Endemic Ad Campaigns
Running non-endemic ad campaigns on Amazon provides a wide variety of benefits like:
Access to Amazon’s Proprietary Data: Harnessing Amazon’s robust first-party data provides advertisers with valuable insights into consumer behavior and purchasing patterns. This data-driven approach enables more targeted and effective campaigns.
Increased Brand Awareness and Revenue Streams: Non-endemic advertising allows brands to extend their reach beyond their typical audience. By leveraging Amazon’s platform and data, advertisers can build brand awareness among users who may not have been exposed to their products or services otherwise. For non-endemic brands that meet specific criteria, there’s an opportunity to serve ads directly on the Amazon platform. This can lead to exposure to the millions of users shopping on Amazon daily, potentially opening up new revenue streams for these brands.
No Minimum Spend for Non-DSP Campaigns: Non-endemic advertisers can kickstart their advertising journey on Amazon without the burden of a minimum spend requirement, ensuring accessibility for a diverse range of brands.
Amazon DSP Capabilities: Leveraging the Amazon DSP (Demand-Side Platform) enhances campaign capabilities. It enables programmatic media buys, advanced audience targeting, and access to a variety of ad formats.
Connect with Primed-to-Purchase Customers: Amazon’s extensive customer base offers a unique opportunity for non-endemic advertisers to connect with customers actively seeking relevant products or services.
Enhanced Targeting and Audience Segmentation: Utilizing Amazon’s vast dataset, advertisers can create highly specific audience segments. This enhanced targeting helps advertisers reach relevant customers, resulting in increased website traffic, lead generation, and improved conversion rates.
Brand Defense – By utilizing these data segments and inventory, some brands are able to bid for placements where their possible competitors would otherwise be. This also gives brands a chance to be present when competitor brands may be on the same page helping conquest for competitors’ customers.
How to Start Running Non-Endemic Ads on Amazon
Ready to start running non-endemic ads on Amazon? Start with these essential steps:
Familiarize Yourself with Amazon Ads and DSP: Understand the capabilities of Amazon Ads and DSP, exploring their benefits and limitations to make informed decisions.
Look Into Amazon Performance Plus: Amazon Performance Plus is the ability to model your audiences based on user behavior from the Amazon Ad Tag. The process will then find lookalike amazon shoppers with a higher propensity for conversion.
“Amazon Performance Plus has the ability to be Amazon’s top performing ad product. With the machine learning behind the audience cohorts we are seeing incremental audiences converting on D2C websites and beating CPA goals by as much as 50%.”
– Robert Avellino, VP of Retail Media Partnerships at Tinuiti
Understand Targeting Capabilities: Gain insights into the various targeting options available for Amazon ads, including behavioral, contextual, and demographic targeting.
Command Amazon’s Data: Utilize granular data to test and learn from campaign outcomes, optimizing strategies based on real-time insights for maximum effectiveness.
Work with an Agency: For those new to non-endemic advertising on Amazon, it’s essential to define clear goals and identify target audiences. Working with an agency can provide valuable guidance in navigating the nuances of non-endemic advertising. Understanding both the audience to be reached and the core audience for the brand sets the stage for a successful non-endemic advertising campaign.
Amazon’s venture into non-endemic advertising reshapes the advertising landscape, providing new opportunities for brands beyond the traditional ecommerce sphere. The blend of non-endemic campaigns with Amazon’s extensive audience and data creates a cohesive option for advertisers seeking to diversify strategies and explore new revenue streams. As this trend evolves, staying informed about the latest features and possibilities within Amazon’s non-endemic advertising ecosystem is crucial for brands looking to stay ahead in the dynamic world of digital advertising.
We’ll continue to keep you updated on all things Amazon, but if you’re looking to learn more about advertising on the platform, check out our Amazon Services page or contact us today for more information.
How Does Success of Your Business Depend on Choosing Type of Native Advertising?
The very first commercial advertisement was shown on TV in 1941. It was only 10 seconds long and had an audience of 4,000 people. However, it became a strong trigger for rapid advertising development. The second half of the 20th century is known as the golden age of advertising until the Internet came to the forefront and entirely transformed the advertising landscape. The first commercial banner appeared in the mid-90s, then it was followed by pop-ups, pay-by-placement and paid-pay-click ads. Companies also started advertising their brands and adding their business logo designs, which contributes to consumer trust and trustworthiness.
The rise of social media in the mid-2000s opened a new dimension for advertising content to be integrated. The marketers were forced to make the ads less intrusive and more organic to attract younger users. This is how native advertising was born. This approach remains a perfect medium for goods and services promotion. Let’s see why and how native ads can become a win-win strategy for your business.
What is native advertising?
When it comes to digital marketing, every marketer talks about native advertising. What is the difference between traditional and native ones? You will not miss basic ads as they are typically promotional and gimmicky, while native advertising naturally blends into the content. The primary purpose of native ads is to create content that resonates with audience expectations and encourages users to perceive it seamlessly and harmoniously.
Simply put, native advertising is a paid media ad that organically aligns with the visual and operational features of the media format in which it appears. The concept is quite straightforward: while people just look through banner ads, they genuinely engage with native ads and read them. You may find a lot of native ads on Facebook, Twitter and Instagram – they appear in the form of “in-feed” posts that engage users in search for more stories, opinions, goods and services. This unobtrusive approach turns native ads into a powerful booster for any brand.
How does native advertising benefit your business?
An average Internet user comes across around 10,000 ads a day. But even physically, it is impossible to perceive this amount of information in 24 hours. So, most of them use adblockers, nullifying all efforts of markers. Native ads successfully overcome this digital challenge thanks to their authenticity. And this is not the only advantage of native advertising. How else does your business benefit? Here are just a few major benefits that prove the value of native ads:
Better brand awareness. Native ads contribute to the brand’s visibility. They seamlessly blend into educational, emotional, and visual types of content that can easily become viral. While promotional content typically receives limited shares, users readily share valuable or entertaining content. Consequently, while you incur expenses only for the display of native ads, your audience may go the extra mile by sharing your content and organically promoting your brand or SaaS product at no additional cost.
Increased click-through rates. Native ads can generate a thrilling click-through rate (CTR) primarily because they are meticulously content-adaptable. Thus, native ads become an integral part of the user’s journey without disrupting their browsing experience. Regardless of whether your native advertising campaign is designed to build an audience or drive specific actions, compelling content will always entice users to click through.
Cost-efficient campaign performance. Native advertising proves to be cheaper compared to a traditional ad format. It mainly stems from a higher CTR. Thanks to precise targeting and less customer resistance, native ads allow to bring down cost-per-click.
Native ads are continuously evolving, enabling marketers to experiment with different formats and use them for successful multi-channel campaigns and global reach.
Types of native advertising
Any content can become native advertising as there are no strict format restrictions. For example, it can be an article rating the best fitness applications, an equipment review, or a post by an influencer on a microblog. The same refers to the channels – native ads can be placed on regular websites and social media feeds. Still, some forms tend to be most frequently used.
- In-feed ads. This type of ad appears within the content feed. You have definitely seen such posts on Facebook and Instagram or such videos on TikTok. They look like regular content but are tagged with an advertising label. The user sees these native ads when scrolling the feed on social media platforms.
- Paid search ads. These are native ads that are displayed on the top and bottom of the search engine results page. They always match user’s queries and aim to capture their attention at the moment of a particular search and generate leads and conversions. This type of ad is effective for big search platforms with substantial traffic.
- Recommendation widgets. These come in the form of either texts or images and can be found at the end of the page or on a website’s sidebar. Widgets offer related or intriguing content from either the same publisher or similar sources. This type of native ads is great for retargeting campaigns.
- Sponsored content. This is one of the most popular types of native advertising. Within this format, an advertiser sponsors the creation of an article or content that aligns with the interests and values of the platform’s audience. They can be marked as “sponsored” or “recommended” to help users differentiate them from organic content.
- Influencer Advertising. In this case, advertisers partner with popular bloggers or celebrities to gain the attention and trust of the audience. Influencers integrate a product, service, or event into their content or create custom content that matches their style and topic.
Each of these formats can bring stunning results if your native ads are relevant and provide value to users. Use a creative automation platform like Creatopy to design effective ads for your business.
How to create a workable native ad?
Consider these 5 steps for creating a successful native advertising campaign:
- Define your target audience. Users will always ignore all ads that are not relevant to them. Unwanted ads are frustrating and can even harm your brand. If you run a store for pets, make sure your ads show content that will be interesting for pet owners. Otherwise, the whole campaign will be undermined. Regular market research and data analysis will help you refine your audience and its demographics.
- Set your goals. Each advertising campaign should have a clear-cut objective. Without well-defined goals, it is a waste of money. It is a must to know what you want to achieve – introduce your brand, boost sales or increase your audience.
- Select the proper channels. Now, you need to determine how you will reach out to your customers. Consider displaying ads on social media platforms, targeting search engine result pages (SERPs), distributing paid articles, or utilizing in-ad units on different websites. You may even be able to get creative and use email or SMS in a less salesy and more “native”-feeling way—you can find samples of texts online to help give you ideas. Exploring demand side platforms (DSP) can also bring good results.
- Offer compelling content. Do not underestimate the quality of the content for your native ads. Besides being expertly written, it must ideally match the style and language of the chosen channel,whether you’re promoting professional headshots, pet products, or anything else. The main distinctive feature of native advertising is that it should fit naturally within the natural content.
- Track your campaign. After the launch of native ads, it is crucial to monitor the progress, evaluating the costs spent and results. Use tools that help you gain insights beyond standard KPIs like CTR and CPC. You should get engagement metrics, customer data, campaign data, and third-party activity data for further campaign management.
Summing up the above, it is time to embrace native advertising if you haven’t done it yet. Native ads seamlessly blend with organic content across various platforms, yielding superior engagement and conversion rates compared to traditional display ads. Marketers are allocating higher budgets to native ads because this format proves to be more and more effective – content that adds value can successfully deal with ad fatigue. Native advertising is experiencing a surge in popularity, and it is to reach its peak. So, do not miss a chance to grow your business with the power of native ads.or you can do digital marketing course from Digital Vidya.
OpenAI’s Drama Should Teach Marketers These 2 Lessons
A week or so ago, the extraordinary drama happening at OpenAI filled news feeds.
No need to get into all the saga’s details, as every publication seems to have covered it. We’re just waiting for someone to put together a video montage scored to the Game of Thrones music.
But as Sam Altman takes back the reigns of the company he helped to found, the existing board begins to disintegrate before your very eyes, and everyone agrees something spooked everybody, a question arises: Should you care?
Does OpenAI’s drama have any demonstrable implications for marketers integrating generative AI into their marketing strategies?
Watch CMI’s chief strategy advisor Robert Rose explain (and give a shoutout to Sutton’s pants rage on The Real Housewives of Beverly Hills), or keep reading his thoughts:
For those who spent last week figuring out what to put on your holiday table and missed every AI headline, here’s a brief version of what happened. OpenAI – the huge startup and creator of ChatGPT – went through dramatic events. Its board fired the mercurial CEO Sam Altman. Then, the 38-year-old entrepreneur accepted a job at Microsoft but returned to OpenAI a day later.
We won’t give a hot take on what it means for the startup world, board governance, or the tension between AI safety and Silicon Valley capitalism. Rather, we see some interesting things for marketers to put into perspective about how AI should fit into your overall content and marketing plans in the new year.
Robert highlights two takeaways from the OpenAI debacle – a drama that has yet to reach its final chapter: 1. The right structure and governance matters, and 2. Big platforms don’t become antifragile just because they’re big.
Let’s have Robert explain.
The right structure and governance matters
OpenAI’s structure may be key to the drama. OpenAI has a bizarre corporate governance framework. The board of directors controls a nonprofit called OpenAI. That nonprofit created a capped for-profit subsidiary – OpenAI GP LLC. The majority owner of that for-profit is OpenAI Global LLC, another for-profit company. The nonprofit works for the benefit of the world with a for-profit arm.
That seems like an earnest approach, given AI tech’s big and disruptive power. But it provides so many weird governance issues, including that the nonprofit board, which controls everything, has no duty to maximize profit. What could go wrong?
That’s why marketers should know more about the organizations behind the generative AI tools they use or are considering.
First, know your providers of generative AI software and services are all exploring the topics of governance and safety. Microsoft, Google, Anthropic, and others won’t have their internal debates erupt in public fireworks. Still, governance and management of safety over profits remains a big topic for them. You should be aware of how they approach those topics as you license solutions from them.
Second, recognize the productive use of generative AI is a content strategy and governance challenge, not a technology challenge. If you don’t solve the governance and cross-functional uses of the generative AI platforms you buy, you will run into big problems with its cross-functional, cross-siloed use.
Big platforms do not become antifragile just because they’re big
Nicholas Taleb wrote a wonderful book, Antifragile: Things That Gain From Disorder. It explores how an antifragile structure doesn’t just withstand a shock; it actually improves because of a disruption or shock. It doesn’t just survive a big disruptive event; it gets stronger because of it.
It’s hard to imagine a company the size and scale of OpenAI could self-correct or even disappear tomorrow. But it can and does happen. And unfortunately, too many businesses build their strategies on that rented land.
In OpenAI’s recent case, the for-profit software won the day. But make no bones about that victory; the event wasn’t good for the company. If it bounces back, it won’t be stronger because of the debacle.
With that win on the for-profit side, hundreds, if not thousands, of generative AI startups breathed an audible sigh of relief. But a few moments later, they screamed “pivot” (in their best imitation of Ross from Friends instructing Chandler and Rachel to move a couch.)
They now realize the fragility of their software because it relies on OpenAI’s existence or willingness to provide the software. Imagine what could have happened if the OpenAI board had won their fight and, in the name of safety, simply killed any paid access to the API or the ability to build business models on top of it.
The last two weeks have done nothing to clear the already muddy waters encountered by companies and their plans to integrate generative AI solutions. Going forward, though, think about the issues when acquiring new generative AI software. Ask about how the vendor’s infrastructure is housed and identify the risks involved. And, if OpenAI expands its enterprise capabilities, consider the implications. What extra features will the off-the-shelf solutions provide? Do you need them? Will OpenAI become the Microsoft Office of your AI infrastructure?
Why you should care
With the voluminous media coverage of Open AI’s drama, you likely will see pushback on generative AI. In my social feeds, many marketers say they’re tired of the corporate soap opera that is irrelevant to their work.
They are half right. What Sam said and how Ilya responded, heart emojis, and how much the Twitch guy got for three days of work are fodder for the Netflix series sure to emerge. (Robert’s money is on Michael Cera starring.)
They’re wrong about its relevance to marketing. They must be experiencing attentional bias – paying more attention to some elements of the big event and ignoring others. OpenAI’s struggle is entertaining, no doubt. You’re glued to the drama. But understanding what happened with the events directly relates to your ability to manage similar ones successfully. That’s the part you need to get right.
HANDPICKED RELATED CONTENT:
Google Discusses Fixing 404 Errors From Inbound Links
Musk regrets controversial post but won’t bow to advertiser ‘blackmail’
Google Search Console Was Down Today
SEO Salary Survey 2023 [Industry Research]
A Year Of AI Developments From OpenAI
GPT Store Set To Launch In 2024 After ‘Unexpected’ Delays
Most SEOs Believe Google’s November Core & Reviews Updates Will Complete In December
Facebook Ads Benchmarks for 2024: NEW Data + Insights for Your Industry
5 Quick Tips to Increase Referral Traffic
How to Schedule Ad Customizers for Google RSAs 
SEO7 days ago
Google Discusses Fixing 404 Errors From Inbound Links
SOCIAL4 days ago
Musk regrets controversial post but won’t bow to advertiser ‘blackmail’
SEARCHENGINES7 days ago
Google Search Console Was Down Today
SEO6 days ago
SEO Salary Survey 2023 [Industry Research]
SEO4 days ago
A Year Of AI Developments From OpenAI
SEO3 days ago
GPT Store Set To Launch In 2024 After ‘Unexpected’ Delays
SEARCHENGINES6 days ago
Most SEOs Believe Google’s November Core & Reviews Updates Will Complete In December
PPC6 days ago
Facebook Ads Benchmarks for 2024: NEW Data + Insights for Your Industry