Top 10 Excel Formulas and Shortcuts for Digital Marketing

Excel Formulas User Guide for Digital Marketing

Data is one of the main elements of Digital Marketing. Data analytics is required to keep track of your conversions and impressions. Despite the number of software and applications that are available to keep track of your data, one Microsoft application still remains the preferred option to a lot of experienced Digital Marketers – MS Excel. Learning excel formulas for Digital Marketing will help you plan your marketing activities more precisely.

Microsoft Excel provides a lot of opportunities for Digital Marketers. It allows them to analyze, evaluate, record, and display their data in a structured form.

A simple Excel Tutorial can guide you to plan your marketing activities for the highest productivity and performance. With Excel, you can connect data from multiple sources and derive analytics that most applications cannot.

Marketers who have gone through Excel Training hold an edge when it comes to organizing, analyzing, and obtaining data. Most of the tools and features available on Excel allow you to maintain track of your marketing efforts.

Here’s a guide to, How Excel can enhance the life of a Digital Marketer;

How to use Formulas in Excel?

If you know the formulas in excel, you are on the right track. You can calculate anything and get the correct results to set your digital marketing strategy.
If you want to find the total of your data.
Step 1: Go to B18
Step2: Use = Sign (with SUM)
Step 3: Select the boxes you want to do the total
For Example Select B2 to B17
Step 4: Press Enter

Use the steps for other formulas too.

Excel Formulas for Digital Marketing:

Excel is a powerful tool and very helpful for all the Digital Marketing Analyst. As digital marketers you know, a lot of your time goes into running the campaign, checking the budget, analyzing the data, forecasting, daily reports etc. Which is like half of your life depends on Excel formulas.

Let’s discuss some of the excel formulas for Digital Marketing that will turn you into an Excel pro!

1. 1. VLOOKUP in Excel

   

   VLOOKUP formula: VLOOKUP(lookup value, table array, column number, [range lookup]).

   that VLOOKUP formula is useful for large datasets or two sets of data and wants to combine in a single spreadsheet. The function helps you to find out particular information.

   
   LOOKUP Value: Select the value you are looking for. Next enter the range of cells within which our information can be found.

   Table array: The specific table or cell where the value that you looking for may be found.

   Column Number: The column in the table or cell where you may find the value.

   LOOKUP Range: A choice to find the exact match(FALSE) or an approximate match (True).

2. 2. IFERROR in Excel

   

   IFERROR Formula =IFERROR(value, value_if_error)

   This formula helps to identify the errors. like eliminates the #DIV/0!
   Value: The particular value that has to be checked for errors.
   Value_if_error: The value to return if a mistake is detected.

3. 3. Average in Excel

   

   To calculate the average in excel, Excel adds the numbers together and divides by the total number of numbers.

   Average Formula=AVERAGE(number1, [number2],..)

4. 4. Percentage in Excel

   

   Calculating percentage is very important for every digital marketer, whether it’s bounce rate, interest rate, % entry, %exist, etc.
   For example, if you had 20 pens and you gave 10 pens to your brothers, how much did you give? By performing a simple calculation =10/20*100 you get the answer =50%
   But excel makes it easy, you don’t have to calculate click your HOME Tab- Select the value you want to convert- select percentage.

5. 5. Date in Excel

   

   Date Formula=DATE(year, month, day)

   Date formula will help you find the month, year, and day within a sec and save your time.

6. 6. Trim in Excel

   

   Trim Formula =TRIM(text)

   The trim formula in Excel is used to remove extra spaces from the text.
   For example: In Roopa Kumari there was space, but after using the trim formula space is gone.

7. 7. Weekday in Excel

   

   Weekday Formula= =TEXT(Date,”ddd”)

   The TEXT function returns a number representing the day of the week, given a date value. The “ddd” function is a custom date format to return the weekday of any date. For example, TEXT(“01/10/2018″,”ddd”) will return “mon” because 01/10/2018 falls on Monday.

8. 8. Sum for Excel

   

   If you want to do the sum of values either row or column, Excel will do your mathematical calculation.

   

   1. When you do Autosum, Excel will automatically calculate the formula and do the sum.
   2. Next is to enter the sum formula and select the cell for calculation and your sum is done.

9. 9. Len in Excel

   

   LEN formula helps to count characters in numbers. It is useful only when anyone wants to count how many characters are there in some text.

10. 10. Word Count for Excel

    Word Count is used to counting characters in numbers.

    

Word Count Formula=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)

According to the above picture enter the formula with Shift +ctrl+enter, you will get the results.

If you have to calculate range-wise data.

Enter the formula =SUM(IF(LEN(TRIM(A2:A3))=0,0,LEN(TRIM(A2:A3))-LEN(SUBSTITUTE(A2:A3,” “,””))+1)) and press the Shift + Ctrl + Enter keys to get the results.

Quick MS-Excel Shortcuts:

Microsoft Excel is one of the best reporting tools that can be used by any sector. When it comes to digital marketing we have Google analytics and some other third-party tools to work on, but for reporting excel plays a major role.

When you are running out of time and your boss crunching you to make work quickly, at that moment you will ask God to send a savior. Here are the time-saving shortcuts for you to complete your work faster than usual. There are hundreds of shortcuts to make your work easy and fast in Excel.

Let’s see some of them here:

You might have an idea about following shortcut keys as

• CUT (Ctrl+X)
• COPY (Ctrl+C)
• PASTE (Ctrl+V)
• UNDO (Ctrl+Z)
• PRINT (Ctrl+P)
• BOLD (Ctrl+B)
• SAVE (Ctrl+S)
• NEW WORKBOOK (Ctrl+N)
• Move to cell A1 (Ctrl+Home)

Here we are going to show you a few more useful Excel shortcuts.

1. 1- To Select entire data with one click in a sheetPress Ctrl+A by placing the cursor on any cell which has data to select the entire data of the sheetYou can see the above image, only it selected the cells which have data.
2. 2- Fill beneath the cell with the same data of the above cellPress Ctrl+D to get the data of the above cell                                                 

   Select the data cell which you want to paste on multiple cells. Press shift + Down Arrow keys to select multiple cells and press Ctrl+D.

   Follow the above procedure to paste data on the right cells. Use Ctrl+R  and SHIFT+Right Arrow.

3. 3- Go to a certain cell

   You can go to any of the particular cells in the sheet by using the shortcut key.

   

   Press the shortcut key Ctrl+G a pop-up box will open, enter the cell number to navigate to a particular cell of the worksheet.

4. 4- Find and replace the dataYes, you have a shortcut key in excel to find and replace the data.Here you can see on the above picture by pressing Ctrl+H a pop-up box will display, there give the input what to FIND and REPLACE with. Check the below image to see the result.

   The data which has the name of SEO had replaced with PPC.

5. 5- Insert or Delete Rows and columns.Sometimes we may miss entering the data in a given order form. We can use a short key to insert a new row in between the rows to not miss the given particular order. Select a row and press Ctrl++(Plus Sign).To delete an entire row, select the row and press Ctrl+-(Minus Sign).               If we want to insert only a cell, we have to use the same procedure by selecting a particular cell.

                                              

   Here you will get options to shift the cells right or down.

6. 6- Hide Rows and ColumnsData in the worksheet might be huge, we think of hiding some data.Ctrl+9 to hide rows of the sheet                                   Ctrl+Shift+(  to unhide the rows                        Ctrl+0 to hide columns                                                   Ctrl+Shift+) to unhide columns

                                          

   So above shortcuts will help you to hide and unhide the rows and columns.

7. 7- Group or Ungroup Rows and Columns.It is a great way to group the column or rows, this shortcut will help easily to hid are unhide the data.Shift+Alt+Right Arrow is the shortcut to hide the grouped columns or rows.Shift+Alt+Left Arrow is the shortcut to unhide the grouped columns or rows.                 

   On the above image, you can see rows are grouped and hidden. Look at the Plus button circled by the red mark; by clicking on that button we can see the grouped and hidden data.

8. 8- Filter the selected cells.A filter option is necessary for every digital marketing report. Save your time by using a shortcut key.No need to go to the Data menu and select the filter option. We have a shortcut key to assign a filter option to selected cells. Press  Ctrl+Shift+L buttons to keep filter.                         On the above image, you can see the data filter option.
9. 9- To Give Hyperlink to the selected cell dataUse the shortcut key to HYPERLINK selected data cells with shortcut key Ctrl+K.By pressing above mentioned shortcut a pop-up menu will display, there we have to give the URL.
10. 10- Format numbers into the currencyIf you have raw numerical data that you want to change into currency; the solution is simple. Just select the cells which you wish to format and press Ctrl+Shift+$.                                                   There are few other shortcuts to insert and date in a cell.To insert current data Ctrl+; (semicolon).

    To insert current time Ctrl+Shift+;(semicolon).

    To insert both together Ctrl+; space and Ctrl+Shift+;

A solution for errors in Excel:

There are a few errors that occur in MS Excel. Sometimes it is difficult to solve these types of errors. Here in this article, I have explained how to solve their problems.

Types of errors:-

• 1. ###### error
  Problem: The column is not wide enough to represent all the characters in a cell.

Solution: Extend the width of the column.

• 2. # Div/0! error
  Problem: Excel demonstrates this error if a number is divided either by zero (0) or an empty cell.

Solution: Change the divider to a value that is not equivalent to 0.

• 3. #Name? error
  Problem: This text in a formula is not identified by Excel. This is effected by misspelling the function names. For example, =su(A1: A7) will result in the #Name? error.

Fix: Correct the error by entering =sum(A1: A7).

• 4. #Value! error
  Problem: This error will be displayed if the formula involves cells that include various data types. = B1(55) + B2(45) +B3(data) will appear in the #Value! error message.

Solution: Substitute B3(data) with a numeric value and the error will be fixed.

• 5. #REF! error
  Problem: The fault will be displayed when a cell reference is not correct. Removing cells that were referred by other formulas will create this error.

Solution: Refer the cells to the right ranges and the error will be corrected.

• 6. #NUM! error
  Problem: The formula or function includes wrong numeric values.

Solution: Using $, % symbols with the number can occur in this error, so avoid practicing these.

• 7. #NULL error
  Problem: Excel displays this error when you define an intersection of two areas that do not intersect (cross). The intersection operator is a space character that departs references in a formula. =Sum(A1: A2 C3: C5) returns the #NULL error because the two ranges do not intersect.

Solution: =SUM(A1:F1 B1:B10) will return the correct reference.

I hope you have learned a few shortcuts. Next time while using excel use this given shortcuts to finish up the work quickly.

Conclusion

MS Excel is very useful in the Digital Marketing field & plays a very important role in Digital Marketing. It allows you to analyze, evaluate, record, and display the data in a structured form. The use of MS Excel in Digital Marketing will help you analyze your data and master your skill in Digital Marketing.