Connect with us

MARKETING

15 Excel Formulas, Keyboard Shortcuts & Tricks That’ll Save You Lots of Time

Published

on

For most marketers, trying to organize and analyze spreadsheets in Microsoft Excel can feel like walking into a brick wall repeatedly if you’e unfamiliar with Excel formulas. You’re manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, “There has to be a better way to do this.”

Truth be told, there is — you just don’t know it yet.

Excel can be tricky that way. On the one hand, it’s an exceptionally powerful tool for reporting and analyzing marketing data. It can even help you visualize data with charts and pivot tables. On the other, without the proper training, it’s easy to feel like it’s working against you. For starters, there are more than a dozen critical formulas Excel can automatically run for you so you’re not combing through hundreds of cells with a calculator on your desk.

What are excel formulas?

Excel formulas help you identify relationships between values in the cells of your spreadsheet, perform mathematical calculations using those values, and return the resulting value in the cell of your choice. Formulas you can automatically perform include sum, subtraction, percentage, division, average, and even dates/times.

Advertisement

We’ll go over all of these, and many more, in this blog post.

How to Insert Formulas in Excel

You might wonder what the “Formulas” tab on the top navigation toolbar in Excel means. In more recent versions of Excel, this horizontal menu — shown below — allows you to find and insert Excel formulas into specific cells of your spreadsheet. 

formulas-toolbar-excel

The more you use various formulas in Excel, the easier it’ll be to remember them and perform them manually. Nonetheless, the suite of icons above is a handy catalog of formulas you can browse and refer back to as you hone your spreadsheet skills.

Excel formulas are also called “functions.” To insert one into your spreadsheet, highlight a cell in which you want to run a formula, then click the far-left icon, “Insert Function,” to browse popular formulas and what they do. That browsing window will look like this:

insert-function-menuWant a more sorted browsing experience? Use any of the icons we’ve highlighted (inside the long red rectangle in the first screenshot above) to find formulas related to a variety of common subjects — such as finance, logic, and more. Once you’ve found the formula that suits your needs, click “Insert Function,” as shown in the window above.

Now, let’s do a deeper dive into some of the most crucial Excel formulas and how to perform each one in typical situations.

Advertisement

To help you use Excel more effectively (and save a ton of time), we’ve compiled a list of essential formulas, keyboard shortcuts, and other small tricks and functions you should know.

NOTE: The following formulas apply to the latest version of Excel. If you’re using a slightly older version of Excel, the location of each feature mentioned below might be slightly different.

1. SUM  

All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you’d like Excel to perform. 

The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you’d like to add together using the format, =SUM(value 1, value 2, etc).

The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet. 

Advertisement
  • To find the SUM of 30 and 80, for example, type the following formula into a cell of your spreadsheet: =SUM(30, 80). Press “Enter,” and the cell will produce the total of both numbers: 110. 
  • To find the SUM of the values in cells B2 and B11, for example, type the following formula into a cell of your spreadsheet: =SUM(B2, B11). Press “Enter,” and the cell will produce the total of the numbers currently filled in cells B2 and B11. If there are no numbers in either cell, the formula will return 0. 

Keep in mind you can also find the total value of a list of numbers in Excel. To find the SUM of the values in cells B2 through B11, type the following formula into a cell of your spreadsheet: =SUM(B2:B11). Note the colon between both cells, rather than a comma. See how this might look in an Excel spreadsheet for a content marketer, below:

sum-formula-excel

2. IF

The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell “if” something else in your spreadsheet is true or false. For example, =IF(D2=”Gryffindor”,”10″,”0″) would award 10 points to cell D2 if that cell contained the word “Gryffindor.”

There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.

We’ll go back to Sprung’s example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10’s next to each Gryffindor student’s name, we’ll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.

  • The formula: IF(logical_test, value_if_true, value_if_false)
    • Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor.” Make sure your Logical_Test value is in quotation marks.
    • Value_if_True: If the value is true — that is, if the student lives in Gryffindor — this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.
    • Value_if_False: If the value is false — and the student does not live in Gryffindor — we want the cell to show “0,” for 0 points.
  • Formula in below example: =IF(D2=”Gryffindor”,”10″,”0″)

Excel formulas and keyboard shortcuts: IF THEN

3. Percentage

To perform the percentage formula in Excel, enter the cells you’re finding a percentage for in the format, =A1/B1. To convert the resulting decimal value to a percentage, highlight the cell, click the Home tab, and select “Percentage” from the numbers dropdown.

There isn’t an Excel “formula” for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you’re not stuck calculating and reentering the numbers yourself.

Advertisement

The basic setting to convert a cell’s value into a percentage is under Excel’s Home tab. Select this tab, highlight the cell(s) you’d like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say “General” at first). Then, select “Percentage” from the list of options that appears. This will convert the value of each cell you’ve highlighted into a percentage. See this feature below.

percentage-formula-in-excel

Keep in mind if you’re using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells’ format to “Percentage” from the Home tab — as shown above.

4. Subtraction

To perform the subtraction formula in Excel, enter the cells you’re subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you’re subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4.

Like percentages, subtracting doesn’t have its own formula in Excel either, but that doesn’t mean it can’t be done. You can subtract any values (or those values inside cells) two different ways.

Subtraction formula in Excel

  • Using the =SUM formula. To subtract multiple values from one another, enter the cells you’d like to subtract in the format =SUM(A1, -B1), with a negative sign (denoted with a hyphen) before the cell whose value you’re subtracting. Press enter to return the difference between both cells included in the parentheses. See how this looks in the screenshot above.
  • Using the format, =A1-B1. To subtract multiple values from one another, simply type an equals sign followed by your first value or cell, a hyphen, and the value or cell you’re subtracting. Press Enter to return the difference between both values.

5. Multiplication

To perform the multiplication formula in Excel, enter the cells you’re multiplying in the format, =A1*B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1*B1 would return a value of 60.

Advertisement

You might think multiplying values in Excel has its own formula or uses the “x” character to denote multiplication between multiple values. Actually, it’s as easy as an asterisk — *.

multiplication-formula-in-excel

To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1*B1*C1 … etc. The asterisk will effectively multiply each value included in the formula.

Press Enter to return your desired product. See how this looks in the screenshot above.

6. Division

To perform the division formula in Excel, enter the cells you’re dividing in the format, =A1/B1. This formula uses a forward slash, “/,” to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5.

Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, “=,” and follow it up with the two (or more) values you’d like to divide with a forward slash, “/,” in between. The result should be in the following format: =B2/A2, as shown in the screenshot below.

Advertisement

excel-division-formula

Hit Enter, and your desired quotient should appear in the cell you initially highlighted.

7. DATE

The Excel DATE formula is denoted =DATE(year, month, day). This formula will return a date that corresponds to the values entered in the parentheses — even values referred from other cells. For example, if A1 was 2018, B1 was 7, and C1 was 11, =DATE(A1,B1,C1) would return 7/11/2018.

Creating dates in the cells of an Excel spreadsheet can be a fickle task every now and then. Luckily, there’s a handy formula to make formatting your dates easy. There are two ways to use this formula:

  • Create dates from a series of cell values. To do this, highlight an empty cell, enter “=DATE,” and in parentheses, enter the cells whose values create your desired date — starting with the year, then the month number, then the day. The final format should look like this: =DATE(year, month, day). See how this looks in the screenshot below.
  • Automatically set today’s date. To do this, highlight an empty cell and enter the following string of text: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())). Pressing enter will return the current date you’re working in your Excel spreadsheet.

excel date formula

In either usage of Excel’s date formula, your returned date should be in the form of “mm/dd/yy” — unless your Excel program is formatted differently.

Advertisement

8. Array

An array formula in Excel surrounds a simple formula in brace characters using the format, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}. By pressing ctrl+shift+center, this will calculate and return value from multiple ranges, rather than just individual cells added to or multiplied by one another.

Calculating the sum, product, or quotient of individual cells is easy — just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells?

Numerical arrays are a useful way to perform more than one formula at the same time in a single cell so you can see one final sum, difference, product, or quotient. If you’re looking to find total sales revenue from several sold units, for example, the array formula in Excel is perfect for you. Here’s how you’d do it:

  1. To start using the array formula, type “=SUM,” and in parentheses, enter the first of two (or three, or four) ranges of cells you’d like to multiply together. Here’s what your progress might look like: =SUM(C2:C5
  2. Next, add an asterisk after the last cell of the first range you included in your formula. This stands for multiplication. Following this asterisk, enter your second range of cells. You’ll be multiplying this second range of cells by the first. Your progress in this formula should now look like this: =SUM(C2:C5*D2:D5)
  3. Ready to press Enter? Not so fast … Because this formula is so complicated, Excel reserves a different keyboard command for arrays. Once you’ve closed the parentheses on your array formula, press Ctrl+Shift+Enter. This will recognize your formula as an array, wrapping your formula in brace characters and successfully returning your product of both ranges combined.

Excel array formula

In revenue calculations, this can cut down on your time and effort significantly. See the final formula in the screenshot above.

9. COUNT

The COUNT formula in Excel is denoted =COUNT(Start Cell:End Cell). This formula will return a value that is equal to the number of entries found within your desired range of cells. For example, if there are eight cells with entered values between A1 and A10, =COUNT(A1:A10) will return a value of 8.

Advertisement

The COUNT formula in Excel is particularly useful for large spreadsheets, wherein you want to see how many cells contain actual entries. Don’t be fooled: This formula won’t do any math on the values of the cells themselves. This formula is simply to find out how many cells in a selected range are occupied with something.

Using the formula in bold above, you can easily run a count of active cells in your spreadsheet. The result will look a little something like this:

count-formula-in-excel

10. AVERAGE

To perform the average formula in Excel, enter the values, cells, or range of cells of which you’re calculating the average in the format, =AVERAGE(number1, number2, etc.) or =AVERAGE(Start Value:End Value). This will calculate the average of all the values or range of cells included in the parentheses.

Finding the average of a range of cells in Excel keeps you from having to find individual sums and then performing a separate division equation on your total. Using =AVERAGE as your initial text entry, you can let Excel do all the work for you.

For reference, the average of a group of numbers is equal to the sum of those numbers, divided by the number of items in that group.

Advertisement

11. SUMIF

The SUMIF formula in Excel is denoted =SUMIF(range, criteria, [sum range]). This will return the sum of the values within a desired range of cells that all meet one criterion. For example, =SUMIF(C3:C12,”>70,000″) would return the sum of values between cells C3 and C12 from only the cells that are greater than 70,000.

Let’s say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees’ salaries — but only if they fall above a particular amount. Doing that manually sounds a bit time-consuming, to say the least.

With the SUMIF function, it doesn’t have to be — you can easily add up the sum of cells that meet certain criteria, like in the salary example above.

  • The formula: =SUMIF(range, criteria, [sum_range])
    • Range: The range that is being tested using your criteria.
    • Criteria: The criteria that determine which cells in Criteria_range1 will be added together
    • [Sum_range]: An optional range of cells you’re going to add up in addition to the first Range entered. This field may be omitted.

In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3:C12,”>70,000″).

SUMIF formula in Excel

12. TRIM

The TRIM formula in Excel is denoted =TRIM(text). This formula will remove any spaces entered before and after the text entered in the cell. For example, if A2 includes the name ” Steve Peterson” with unwanted spaces before the first name, =TRIM(A2) would return “Steve Peterson” with no spaces in a new cell.

Email and file sharing are wonderful tools in today’s workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.

Advertisement

Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).

  • The formula: =TRIM(text).
    • Text: The text or cell from which you want to remove spaces.

Here’s an example of how we used the TRIM function to remove extra spaces before a list of names. To do so, we entered =TRIM(“A2”) into the Formula Bar, and replicated this for each name below it in a new column next to the column with unwanted spaces.

trim-formula-in-excel

Below are some other Excel formulas you might find useful as your data management needs grow.

13. LEFT, MID, and RIGHT

Let’s say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.

LEFT

  • Purpose: Used to extract the first X numbers or characters in a cell.
  • The formula: =LEFT(text, number_of_characters)
    • Text: The string that you wish to extract from.
    • Number_of_characters: The number of characters that you wish to extract starting from the left-most character.

In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.

LEFT formula in Excel

MID

  • Purpose: Used to extract characters or numbers in the middle based on position.
  • The formula: =MID(text, start_position, number_of_characters)
    • Text: The string that you wish to extract from.
    • Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
    • Number_of_characters: The number of characters that you wish to extract.

In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.

MID formula in Excel

RIGHT

  • Purpose: Used to extract the last X numbers or characters in a cell.
  • The formula: =RIGHT(text, number_of_characters)
    • Text: The string that you wish to extract from.
    • Number_of_characters: The number of characters that you want to extract starting from the right-most character.

For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.

RIGHT formula in EXCEL

14. VLOOKUP

This one is an oldie, but a goodie — and it’s a bit more in depth than some of the other formulas we’ve listed here. But it’s especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung — whose “How to Use Excel” tutorial is a must-read for anyone who wants to learn — uses a list of names, email addresses, and companies as an example. If you 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 — that’s where VLOOKUP comes in.

Advertisement

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. 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, [range lookup])
    • Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung’s example that follows, this means the first email address on the list, or cell 2 (C2).
    • Table Array: 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: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the “House” column, the second one in our table array, making it column number 2.
    • Range Lookup: Use FALSE to ensure you pull in only exact value matches.
  • The formula with variables from Sprung’s example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, 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. Here’s how that would work:

Excel formulas and shortcuts: VLOOKUP

15. RANDOMIZE

There’s a great article that likens Excel’s RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card — 52 in a deck — is a row. “To shuffle the deck,” writes Steve McDonnell, “you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field.”

In marketing, you might use this feature when you want to assign a random number to a list of contacts — like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”

  • The formula: RAND()
    • Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” — without the quotation marks — starting with the top contact’s row.
    • RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.
    • bottom: The lowest number in the range.
    • top: The highest number in the range,For the example below: RANDBETWEEN(bottom,top)
    • Formula in below example: =RANDBETWEEN(1,10)

 

excel formulas and keyboard shortcuts example: Randomize
Helpful stuff, right? Now for the icing on the cake: Once you’ve mastered the Excel formula you need, you’ll want to replicate it for other cells without rewriting the formula. And luckily, there’s an Excel function for that, too. Check it out below.

Sometimes, you might want to run the same formula across an entire row or column of your spreadsheet. Let’s say, for example, you have a list of numbers in columns A and B of a spreadsheet and want to enter individual totals of each row into column C.

Obviously, it would be too tedious to adjust the values of the formula for each cell so you’re finding the total of each row’s respective numbers. Luckily, Excel allows you to automatically complete the column; all you have to do is enter the formula in the first row. Check out the following steps: 

Advertisement
  1. Type your formula into an empty cell and press “Enter” to run the formula.SUM formula entered in column C of Excel spreadsheet to find the sum of cells B2 and C2.
  2. Hover your cursor over the bottom-right corner of the cell containing the formula. You’ll see a small, bold “+” symbol appear.
  3. While you can double-click this symbol to automatically fill the entire column with your formula, you can also click and drag your cursor down manually to fill only a specific length of the column.insert-formula-in-excel-for-entire-columnOnce you’ve reached the last cell in the column you’d like to enter your formula, release your mouse to copy the formula. Then, simply check each new value to ensure it corresponds to the correct cells.

Excel Keyboard Shortcuts

1. Quickly select rows, columns, or the whole spreadsheet.

Perhaps you’re crunched for time. I mean, who isn’t? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.

Quickly select rows, columns, or the whole Excel spreadsheet

Just want to select everything in a particular column or row? That’s just as easy with these shortcuts:

For Mac:

  • Select Column = Command + Shift + Down/Up
  • Select Row = Command + Shift + Right/Left

For PC:

  • Select Column = Control + Shift + Down/Up
  • Select Row = Control + Shift + Right/Left

This shortcut is especially helpful when you’re working with larger data sets, but only need to select a specific piece of it.

1658936298 619 15 Excel Formulas Keyboard Shortcuts Tricks Thatll Save You

2. Quickly open, close, or create a workbook.

Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.

Quickly open, close, or create an Excel workbook

For Mac:

  • Open = Command + O
  • Close = Command + W
  • Create New = Command + N

For PC:

  • Open = Control + O
  • Close = Control + F4
  • Create New = Control + N

3. Format numbers into currency.

Have raw data that you want to turn into currency? Whether it be salary figures, marketing budgets, or ticket sales for an event, the solution is simple. Just highlight the cells you wish to reformat, and select Control + Shift + $.

Format numbers into currency in Excel

The numbers will automatically translate into dollar amounts — complete with dollar signs, commas, and decimal points.

1658936299 589 15 Excel Formulas Keyboard Shortcuts Tricks Thatll Save You

Note: This shortcut also works with percentages. If you want to label a column of numerical values as “percent” figures, replace “$” with “%”.

4. Insert current date and time into a cell.

Whether you’re logging social media posts, or keeping track of tasks you’re checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information.

Then, depending on what you want to insert, do one of the following:

Advertisement
  • Insert current date = Control + ; (semi-colon)
  • Insert current time = Control + Shift + ; (semi-colon)
  • Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).

Insert a current date and time into a cell in Excel

Other Excel Tricks

1. Customize the color of your tabs.

If you’ve got a ton of different sheets in one workbook — which happens to the best of us — make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month’s marketing reports with red, and this month’s with orange.

Simply right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.

Customize the color of your tabs in Excel

2. Add a comment to a cell.

When you want to make a note or add a comment to a specific cell within a worksheet, simply right-click the cell you want to comment on, then click Insert Comment. Type your comment into the text box, and click outside the comment box to save it.

Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.

Add a comment to a cell in Excel

3. Copy and duplicate formatting.

If you’ve ever spent some time formatting a sheet to your liking, you probably agree that it’s not exactly the most enjoyable activity. In fact, it’s pretty tedious.

For that reason, it’s likely that you don’t want to repeat the process next time — nor do you have to. Thanks to Excel’s Format Painter, you can easily copy the formatting from one area of a worksheet to another.

Advertisement

Select what you’d like to replicate, then select the Format Painter option — the paintbrush icon — from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:

Excel formulas and keyboard shortcuts example: Copy and Duplicate Formatting in Excel

4. Identify duplicate values.

In many instances, duplicate values — like duplicate content when managing SEO — can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it.

Whatever the situation may be, it’s easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values

Identify duplicate values in Excel

Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.

1658936300 260 15 Excel Formulas Keyboard Shortcuts Tricks Thatll Save You

In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow.

Excel Shortcuts Save You Time

In marketing, the use of Excel is pretty inevitable — but with these tricks, it doesn’t have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they’ll become second nature.

Advertisement

Editor’s note: This post was originally published in January 2019 and has been updated for comprehensiveness.

excel marketing templates

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address

MARKETING

The Current State of Google’s Search Generative Experience [What It Means for SEO in 2024]

Published

on

person typing on laptop with

Search


By Tinuiti Team

SEO enthusiasts, known for naming algorithm updates after animals and embracing melodrama, find themselves in a landscape where the “adapt or die” mantra prevails. So when Google announced the launch of its Search Generative Experience (SGE) in May of 2023 at Google/IO, you can imagine the reaction was immense.

Although SGE has the potential to be a truly transformative force in the landscape, we’re still waiting for SGE to move out of the Google Labs Sandbox and integrate into standard search results. 

Advertisement

Curious about our current take on SGE and its potential impact on SEO in the future? Read on for more.

Decoding Google’s Defensive Move

In response to potential threats from competitors like ChatGPT, Bing, TikTok, Reddit, and Amazon, Google introduced SGE as a defensive maneuver. However, its initial beta release raised questions about its readiness and global deployment.

ChatGPT provided an existential threat that had the potential to eat into Google’s market share. When Bing started incorporating it into its search results, it was one of the most significant wins for Bing in a decade. In combination with threats from TikTok, Reddit, and Amazon, we see a more fractured search landscape less dominated by Google. Upon its launch, the expectation was that Google would push its SGE solution globally, impact most queries, and massively shake up organic search results and strategies to improve organic visibility.

Now, industry leaders are starting to question if Google is better off leaving SGE in the testing ground in Google labs. According to Google’s recent update, it appears that SGE will remain an opt-in experience in Google Labs (for at least the short term). If SGE was released, there could be a fundamental reset in understanding SEO. Everything from organic traffic to optimization tactics to tracking tools would need adjustments for the new experience. Therefore, the prospect of SGE staying in Google Labs is comforting if not entirely reliable. 

The ever-present option is that Google can change its mind at any point and push SGE out broadly as part of its standard search experience. For this reason, we see value in learning from our observations with SGE and continuing to stay on top of the experience.

SGE User Experience and Operational Challenges

If you’ve signed up for search labs and have been experimenting with SGE for a while, you know firsthand there are various issues that Google should address before rolling it out broadly to the public.

Advertisement

At a high level, these issues fall into two broad categories including user experience issues and operational issues.

Below are some significant issues we’ve come across, with Google making notable progress in addressing certain ones, while others still require improvement:

  • Load time – Too many AI-generated answers take longer to load than a user is willing to wait. Google recommends less than a 3-second load time to meet expectations. They’ll need to figure out how to consistently return results quickly if they want to see a higher adoption rate.
  • Layout – The SGE layout is massive. We believe any major rollout will be more streamlined to make it a less intrusive experience for users and allow more visibility for ads, and if we’re lucky, organic results. Unfortunately, there is still a decent chance that organic results will move below the fold, especially on mobile devices. Recently, Google has incorporated more results where users are prompted to generate the AI result if they’d like to see it. The hope is Google makes this the default in the event of a broad rollout where users can generate an AI result if they want one instead of assuming that’s what a user would like to see. 
  • Redundancy – The AI result duplicates features from the map pack and quick answer results. 
  • Attribution – Due to user feedback, Google includes sources on several of their AI-powered overviews where you can see relevant web pages if there is an arrow next to the result. Currently, the best way to appear as one of these relevant pages is to be one of the top-ranked results, which is convenient from an optimization standpoint. Changes to how attribution and sourcing are handled could heavily impact organic strategies. 

On the operational side, Google also faces significant hurdles to making SGE a viable product for its traditional search product. The biggest obstacle appears to be making the cost associated with the technology worth the business outcomes it provides. If this was a necessary investment to maintain market share, Google might be willing to eat the cost, but if their current position is relatively stable, Google doesn’t have much of an incentive to take on the additional cost burden of heavily leveraging generative AI while also presumably taking a hit to their ad revenue. Especially since slow user adoption doesn’t indicate this is something users are demanding at the moment.

While the current experience of SGE is including ads above the generative results now, the earliest iterations didn’t heavily feature sponsored ads. While they are now included, the current SGE layout would still significantly disrupt the ad experience we’re used to. During the Google I/O announcement, they made a statement to reassure advertisers they would be mindful of maintaining a distinct ad experience in search.  

“In this new generative experience, Search ads will continue to appear in dedicated ad slots throughout the page. And we’ll continue to uphold our commitment to ads transparency and making sure ads are distinguishable from organic search results” – Elizabeth Reid, VP, Search at Google

Google is trying to thread a delicate needle here of staying on the cutting edge with their search features, while trying not to upset their advertisers and needlessly hinder their own revenue stream. Roger Montti details more of the operational issues in a recent article digging into the surprising reasons SGE is stuck in Google Labs.

He lists three big problems that need to be solved before SGE will be integrated into the foreground of search:

Advertisement
  1. Large Language Models being inadequate as an information retrieval system
  2. The inefficiency and cost of transformer architecture
  3. Hallucinating (providing inaccurate answers)

Until SGE provides more user value and checks more boxes on the business sense side, the traditional search experience is here to stay. Unfortunately, we don’t know when or if Google will ever feel confident they’ve addressed all of these concerns, so we’ll need to stay prepared for change.

Experts Chime in on Search Generative Experience

Our team has been actively engaging with SGE, here’s a closer look at their thoughts and opinions on the experience so far:

“With SGE still in its early stages, I’ve noticed consistent changes in how the generative results are produced and weaved naturally into the SERPs. Because of this, I feel it is imperative to stay on top of these on-going changes to ensure we can continue to educate our clients on what to expect when SGE is officially incorporated into our everyday lives. Although an official launch date is currently unknown, I believe proactively testing various prompt types and recording our learnings is important to prepare our clients for this next evolution of Google search.”

– Jon Pagano, SEO Sr. Specialist at Tinuiti

“It’s been exciting to watch SGE grow through different variations over the last year, but like other AI solutions its potential still outweighs its functionality and usefulness. What’s interesting to see is that SGE doesn’t just cite its sources of information, but also provides an enhanced preview of each webpage referenced. This presents a unique organic opportunity where previously untouchable top 10 rankings are far more accessible to the average website. Time will tell what the top ranking factors for SGE are, but verifiable content with strong E-E-A-T signals will be imperative.”

–Kate Fischer, SEO Specialist at Tinuiti

“Traditionally, AI tools were very good at analytical tasks. With the rise of ChatGPT, users can have long-form, multi-question conversations not yet available in search results. When, not if, released, Google’s Generative Experience will transform how we view AI and search. Because there are so many unknowns, some of the most impactful ways we prepare our clients are to discover and develop SEO strategies that AI tools can’t directly disrupt, like mid to low funnel content.”

– Brandon Miller, SEO Specialist at Tinuiti

“SGE is going to make a huge impact on the ecommerce industry by changing the way users interact with the search results. Improved shopping experience will allow users to compare products, price match, and read reviews in order to make it quicker and easier for a user to find the best deals and purchase. Although this leads to more competitive results, it also improves organic visibility and expands our product reach. It is more important than ever to ensure all elements of a page are uniquely and specifically optimized for search. With the SGE updates expected to continue to impact search results, the best way to stay ahead is by focusing on strong user focused content and detailed product page optimizations.” 

– Kellie Daley, SEO Sr. Specialist at Tinuiti

Navigating the Clash of Trends

One of the most interesting aspects of the generative AI trend in search is that it appears to be in direct opposition to other recent trends.

Advertisement

One of the ways Google has historically evaluated the efficacy of its search ranking systems is through the manual review of quality raters. In their quality rater guidelines, raters were instructed to review for things like expertise, authority, and trustworthiness (EAT) in results to determine if Google results are providing users the information they deserve. 

In 2022, Google updated their search guidelines to include another ‘e’ in the form of experience (EEAT). In their words, Google wanted to better assess if the content a user was consuming was created by someone with, “a degree of experience, such as with actual use of a product, having actually visited a place or communicating what a person has experienced. There are some situations where really what you value most is content produced by someone who has firsthand, life experience on the topic at hand.” 

Generative AI results, while cutting-edge technology and wildly impressive in some cases, stand in direct opposition to the principles of E-E-A-T. That’s not to say that there’s no room for both in search, but Google will have to determine what it thinks users value more between these competing trends. The slow adoption of SGE could be an indication that a preference for human experience, expertise, authority, and trust is winning round one in this fight. 

Along these lines, Google is also diversifying its search results to cater to the format in which users get their information. This takes the form of their Perspectives Filter. Also announced at Google I/O 2023, the perspectives filter incorporates more video, image, and discussion board posts from places like TikTok, YouTube, Reddit, and Quora. Once again, this trend shows the emphasis and value searchers place on experience and perspective. Users value individual experience over the impersonal conveyance of information. AI will never have these two things, even if it can provide a convincing imitation.

The current iteration of SGE seems to go too far in dismissing these trends in favor of generative AI. It’s an interesting challenge Google faces. If they don’t determine the prevailing trend correctly, veering too far in one direction can push more market share to ChatGPT or platforms like YouTube and TikTok.

Final Thoughts

The range of outcomes remains broad and fascinating for SGE. We can see this developing in different ways, and prognostication offers little value, but it’s invaluable to know the potential outcomes and prepare for as many of them as possible.

Advertisement

It’s critical that you or your search agency be interacting and experimenting with SGE because:

  • The format and results will most likely continue to see significant changes
  • This space moves quickly and it’s easy to fall behind
  • Google may fix all of the issues with SGE and decide to push it live, changing the landscape of search overnight
  • SGE experiments could inform other AI elements incorporated into the search experience

Ultimately, optimizing for the specific SGE experience we see now is less important because we know it will inevitably continue changing. We see more value in recognizing the trends and problems Google is trying to solve with this technology. With how quickly this space moves, any specifics mentioned in this article could be outdated in a week. That’s why focusing on intention and process is important at this stage of the game.

By understanding the future needs and wants SGE is attempting to address, we can help you future-proof your search strategies as much as possible. To some extent we’re always at the whims of the algorithm, but by maintaining a user-centric approach, you can make your customers happy, regardless of how they find you.

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

How to create editorial guidelines that are useful + template

Published

on

How to create editorial guidelines that are useful + template

Before diving in to all things editorial guidelines, a quick introduction. I head up the content team here at Optimizely. I’m responsible for developing our content strategy and ensuring this aligns to our key business goals.

Here I’ll take you through the process we used to create new editorial guidelines; things that worked well and tackle some of the challenges that come with any good multi – stakeholder project, share some examples and leave you with a template you can use to set your own content standards.

What are editorial guidelines?

Editorial guidelines are a set of standards for any/all content contributors, etc. etc. This most often includes guidance on brand, tone of voice, grammar and style, your core content principles and the types of content you want to produce.

Editorial guidelines are a core component of any good content strategy and can help marketers achieve the following in their content creation process:

Advertisement
  • Consistency: All content produced, regardless of who is creating it, maintains a consistent tone of voice and style, helping strengthen brand image and making it easier for your audience to recognize your company’s content  
  • Quality Control: Serves as a ‘North Star’ for content quality, drawing a line in the sand to communicate the standard of content we want to produce 
  • Boosts SEO efforts: Ensures content creation aligns with SEO efforts, improving company visibility and increasing traffic 
  • Efficiency: With clear guidelines in place, content creators – external and internal – can work more efficiently as they have a clear understanding of what is expected of them 

Examples of editorial guidelines

There are some great examples of editorial guidelines out there to help you get started.

Here are a few I used: 

1. Editorial Values and Standards, the BBC

 

Ah, the Beeb. This really helped me channel my inner journalist and learn from the folks that built the foundation for free quality journalism. 

How to create editorial guidelines, Pepperland Marketing

pepperlandmarketingblogpostoneditorialguidelines

After taking a more big picture view I recognized needed more focused guidance on the step by step of creating editorial guidelines.

I really liked the content the good folks at Pepperland Marketing have created, including a free template – thanks guys! – and in part what inspired me to create our own free template as a way of sharing learnings and helping others quickstart the process of creating their own guidelines.

Advertisement

 

3. Writing guidelines for the role of AI in your newsroom?… Nieman Lab

NiemanLabsguidanceonroleofaiinyournewsroom

As well as provide guidance on content quality and the content creation process, I wanted to tackle the thorny topic of AI in our editorial guidelines. Specifically, to give content creators a steer on ‘fair’ use of AI when creating content, to ensure creators get to benefit from the amazing power of these tools, but also that content is not created 100% by AI and help them understand why we feel that contravenes our core content principles of content quality. 

So, to learn more I devoured this fascinating article, sourcing guidance from major media outlets around the world. I know things change very quickly when it comes to AI, but I highly encourage reading this and taking inspiration from how these media outlets are tackling this topic. 

Learn more: The Marketer’s Guide to AI-generated content

Why did we decide to create editorial guidelines?

1. Aligning content creators to a clear vision and process

Optimizely as a business has undergone a huge transformation over the last 3 years, going through rapid acquisition and all the joys and frustrations that can bring. As a content team, we quickly recognized the need to create a set of clear and engaging guidelines that helps content creators understand how and where they can contribute, and gave a clear process to follow when submitting a content idea for consideration. 

2. Reinvigorated approach to brand and content 

As a brand Optimizely is also going through a brand evolution – moving from a more formal, considered tone of voice to one that’s much more approachable, down to earth and not afraid to use humor, different in content and execution. 

Advertisement

See, our latest CMS campaign creative:

Mock ups of Optimizely CMS campaign creative

It’s pretty out there in terms of creative and messaging. It’s an ad campaign that’s designed to capture attention yes, but also – to demonstrate our abilities as a marketing team to create this type of campaign that is normally reserved for other more quote unquote creative industries. 

We wanted to give guidance to fellow content creators outside the team on how they can also create content that embraces this evolved tone of voice, while at the same time ensuring content adheres to our brand guidelines.

3. Streamline content creation process

Like many global enterprises we have many different content creators, working across different time zones and locations. Documenting a set of guidelines and making them easily available helps content creators quickly understand our content goals, the types of content we want to create and why. It would free up content team time spent with individual contributors reviewing and editing submissions, and would ensure creation and optimization aligns to broader content & business goals.

It was also clear that we needed to document a process for submitting content ideas, so we made sure to include this in the guidelines themselves to make it easy and accessible for all contributors. 

4. 2023 retrospective priority 

As a content team we regularly review our content strategy and processes to ensure we’re operating as efficiently as possible.

Advertisement

In our last retrospective. I asked my team ‘what was the one thing I could do as a manager to help them be more impactful in their role?’

Editorial guidelines was the number 1 item on their list. 

So off we went… 

What we did

  • Defined a discrete scope of work for the first version of the editorial guidelines, focusing on the Blog and Resources section of the website. This is where the content team spends most of its time and so has most involvement in the content creation process. Also where the most challenging bottlenecks have been in the past
  • Research. Reviewed what was out there, got my hands on a few free templates and assembled a framework to create a first version for inputs and feedback 
  • Asked content community – I put a few questions out to my network on LinkedIn on the topic of content guidelines and content strategy, seeking to get input and guidance from smart marketers.  

linkedinpostoneditorialguidelines

Combining two of my great passions in life – content strategy and Arrested Development – in one LinkedIn post (Feb 2024)

  • Invited feedback: Over the course of a few weekswe invited collaborators to comment in a shared doc as a way of taking iterative feedback, getting ideas for the next scope of work, and also – bringing people on the journey of creating the guidelines. Look at all those reviewers! Doing this within our Content Marketing Platform (CMP) ensured that all that feedback was captured in one place, and that we could manage the process clearly, step by step:

Optimizelycmpscreenshotofeditorialguidelines

Look at all those collaborators! Thanks guys! And all of those beautiful ticks, so satisfying. So glad I could crop out the total outstanding tasks for this screen grab too (Source – Optimizely CMP) 

  • Updated content workflow: Now we have clear, documented guidance in place, we’ve included this as a step – the first step – in the workflow used for blog post creation: 

Optimizely CMP screenshot of editorial guideline review

Source: Optimizely CMP

Advertisement

Results

It’s early days but we’re already seeing more engagement with the content creation process, especially amongst the teams involved in building the guidelines (which was part of the rationale in the first place :))

Screenshot of teams message editorial guidelines

Source: My Teams chat 

It’s inspired teams to think differently about the types of content we want to produce going forwards – for the blog and beyond.

I’d also say it’s boosted team morale and collaboration, helping different teams work together on shared goals to produce better quality work.

What’s next?

We’re busy planning wider communication of the editorial guidelines beyond marketing. We’ve kept the original draft and regularly share this with existing and potential collaborators for ongoing commentary, ideas and feedback.

Creating guidelines has also sparked discussion about the types of briefs and templates we want and need to create in CMP to support creating different assets. Finding the right balance between creative approach and using templates to scale content production is key. 

Advertisement

We’ll review these guidelines on a quarterly basis and evolve as needed, adding new formats and channels as we go.

Key takeaways

  • Editorial guidelines are a useful way to guide content creators as part of your overall content strategy
  • Taking the time to do research upfront can help accelerate seemingly complex projects. Don’t be afraid to ask your community for inputs and advice as you create
  • Keep the scope small at first rather than trying to align everything all at once. Test and learn as you go
  • Work with stakeholders to build guidelines from the ground up to ensure you create a framework that is useful, relevant and used

And lastly, here’s that free template we created to help you build or evolve your own editorial guidelines!

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Effective Communication in Business as a Crisis Management Strategy

Published

on

Effective Communication in Business as a Crisis Management Strategy

Everyday business life is full of challenges. These include data breaches, product recalls, market downturns and public relations conflicts that can erupt at any moment. Such situations pose a significant threat to a company’s financial health, brand image, or even its further existence. However, only 49% of businesses in the US have a crisis communications plan. It is a big mistake, as such a strategy can build trust, minimize damage, and even strengthen the company after it survives the crisis. Let’s discover how communication can transform your crisis and weather the chaos.

The ruining impact of the crisis on business

A crisis can ruin a company. Naturally, it brings losses. But the actual consequences are far worse than lost profits. It is about people behind the business – they feel the weight of uncertainty and fear. Employees start worrying about their jobs, customers might lose faith in the brand they once trusted, and investors could start looking elsewhere. It can affect the brand image and everything you build from the branding, business logo, social media can be ruined. Even after the crisis recovery, the company’s reputation can suffer, and costly efforts might be needed to rebuild trust and regain momentum. So, any sign of a coming crisis should be immediately addressed. Communication is one of the crisis management strategies that can exacerbate the situation.  

The power of effective communication

Even a short-term crisis may have irreversible consequences – a damaged reputation, high employee turnover, and loss of investors. Communication becomes a tool that can efficiently navigate many crisis-caused challenges:

  • Improved trust. Crisis is a synonym for uncertainty. Leaders may communicate trust within the company when the situation gets out of control. Employees feel valued when they get clear responses. The same applies to the customers – they also appreciate transparency and are more likely to continue cooperation when they understand what’s happening. In these times, documenting these moments through event photographers can visually reinforce the company’s messages and enhance trust by showing real, transparent actions.
  • Reputation protection. Crises immediately spiral into gossip and PR nightmares. However, effective communication allows you to proactively address concerns and disseminate true information through the right channels. It minimizes speculation and negative media coverage.
  • Saved business relationships. A crisis can cause unbelievable damage to relationships with employees, customers, and investors. Transparent communication shows the company’s efforts to find solutions and keeps stakeholders informed and engaged, preventing misunderstandings and painful outcomes.
  • Faster recovery. With the help of communication, the company is more likely to receive support and cooperation. This collaborative approach allows you to focus on solutions and resume normal operations as quickly as possible.

It is impossible to predict when a crisis will come. So, a crisis management strategy mitigates potential problems long before they arise.

Tips on crafting an effective crisis communication plan.

To effectively deal with unforeseen critical situations in business, you must have a clear-cut communication action plan. This involves things like messages, FAQs, media posts, and awareness of everyone in the company. This approach saves precious time when the crisis actually hits. It allows you to focus on solving the problem instead of intensifying uncertainty and panic. Here is a step-by-step guide.  

Identify your crisis scenarios.

Being caught off guard is the worst thing. So, do not let it happen. Conduct a risk assessment to pinpoint potential crises specific to your business niche. Consider both internal and external factors that could disrupt normal operations or damage the online reputation of your company. Study industry-specific issues, past incidents, and current trends. How will you communicate in each situation? Knowing your risks helps you prepare targeted communication strategies in advance. Of course, it is impossible to create a perfectly polished strategy, but at least you will build a strong foundation for it.

Advertisement

Form a crisis response team.

The next step is assembling a core team. It will manage communication during a crisis and should include top executives like the CEO, CFO, and CMO, and representatives from key departments like public relations and marketing. Select a confident spokesperson who will be the face of your company during the crisis. Define roles and responsibilities for each team member and establish communication channels they will work with, such as email, telephone, and live chat. Remember, everyone in your crisis response team must be media-savvy and know how to deliver difficult messages to the stakeholders.

Prepare communication templates.

When a crisis hits, things happen fast. That means communication needs to be quick, too. That’s why it is wise to have ready-to-go messages prepared for different types of crises your company may face. These messages can be adjusted to a particular situation when needed and shared on the company’s social media, website, and other platforms right away. These templates should include frequently asked questions and outline the company’s general responses. Make sure to approve these messages with your legal team for accuracy and compliance.

Establish communication protocols.

A crisis is always chaotic, so clear communication protocols are a must-have. Define trigger points – specific events that would launch the crisis communication plan. Establish a clear hierarchy for messages to avoid conflicting information. Determine the most suitable forms and channels, like press releases or social media, to reach different audiences. Here is an example of how you can structure a communication protocol:

  • Immediate alert. A company crisis response team is notified about a problem.  
  • Internal briefing.  The crisis team discusses the situation and decides on the next steps.  
  • External communication. A spokesperson reaches the media, customers, and suppliers.
  • Social media updates. A trained social media team outlines the situation to the company audience and monitors these channels for misinformation or negative comments.
  • Stakeholder notification. The crisis team reaches out to customers and partners to inform them of the incident and its risks. They also provide details on the company’s response efforts and measures.
  • Ongoing updates. Regular updates guarantee transparency and trust and let stakeholders see the crisis development and its recovery.

Practice and improve.

Do not wait for the real crisis to test your plan. Conduct regular crisis communication drills to allow your team to use theoretical protocols in practice. Simulate different crisis scenarios and see how your people respond to these. It will immediately demonstrate the strong and weak points of your strategy. Remember, your crisis communication plan is not a static document. New technologies and evolving media platforms necessitate regular adjustments. So, you must continuously review and update it to reflect changes in your business and industry.

Wrapping up

The ability to handle communication well during tough times gives companies a chance to really connect with the people who matter most—stakeholders. And that connection is a foundation for long-term success. Trust is key, and it grows when companies speak honestly, openly, and clearly. When customers and investors trust the company, they are more likely to stay with it and even support it. So, when a crisis hits, smart communication not only helps overcome it but also allows you to do it with minimal losses to your reputation and profits.

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

Trending

Follow by Email
RSS