Excel

https://align.me/files/B2B%20marketing%20wiki%20incomplete%20page_0.png This wiki is incomplete. Please help to improve it by adding relevant content.

Why would a B2B Marketer need Excel?

Marketers rely on Excel for many reasons, including but not limited to:

  • List management
  • Equations
  • Funnel data (storing, tracking and analysing)
  • Graphing
  • Preparing timelines and schedules

Excel can add a lot of value to a Marketers life, but you have to know how to use it. Once you get the hang of shortcuts and tricks, it can be easy and efficient to use, and make your work life a lot easier! Below are some tips and it would be great to extend this list further.

Deduping

To dedupe contacts with Excel :

  1. Insert a new column next to the email column
  2. Sort the email column A - Z
  3. Type in the new column
    1. =if(b2=b3, "dupe""") ENTER
    2. NB: edit cell numbers where required

This will list "dupe" in the new column when a duplicate appears

How to open Excel on two screens

To open Excel on two screens :

  1. Open the first file you would like for screen 1
  2. Hit the start button
  3. Open Excel separately

These two files will be split into two workbooks, and you can move them between screen 1 and 2 separately.

How to save groups of workbooks

To save "groups" of workbooks with Excel :

  1. Select the "File" tab on the ribbon
  2. Save "Workspace"

Filling a series

This refers to filling in a sequence of ordered entries in adjacent cells, like days of the week, or months of the year.

To fill a series with Excel :

  1. Fill in the chosen start of the series i.e. January in the first cell.
  2. Click on the first cell in the series
  3. Drag from the fill handle across as many columns as required.

The fill handle is brought up by hovering the mouse over the bottom right hand corner of the cell, and comes up as a thin, black cross.

Filling dates

To fill in dates with Excel :

  1. Fill in the beginning date in cell e.g. B3.
  2. Drag the fill handle all the way along to the end required cell.
  3. Click the "Smart Tag" that comes up and and select "Fill Weekdays" or "Fill months" etc.

Absolute vs. Relative referencing

Formulas are entered into a worksheet using cell references rather than actual values which means there's a time advantage when the formula is to be duplicated. Each time a formula is entered using cell referencing a shape is created.

Relative referencing = the cell address changes relative to the current location. To create a relative formula:

  1. Click in the cell in which you want the formula
  2. Enter the formula and ensure that the formula contains references to other cells.
  3. Click on the cell again and drag the fill handle from the bottom right hand corner of the cell along to the bottom right hand corner of the cell where you want to formula to stop. This will copy the formula shape into each chosen cell.

Absolute referencing = the cell reference remains the same even when it is copied around the worksheet. To make a cell reference absolute :

  1. Press F4 to insert $ before the row and column reference.

To make a "row' reference absolute, press F4 twice to insert $ before the row reference only. To make the column reference absolute, press F4 three times to insert $ before the column reference only.

Logical functions

Logical functions provide decision-making tools for information on your spreadsheet. They allow you to perform a calculation then test that result against a required figure or value.

  • The IF function is the key function used for decision-making and the information displayed depends on the outcome of the condition test. The resulting text will appear in the cell where the formula containing the IF function is.

=IF(condition, true, false) For example, the format =IF(B2>400, “High”, “Low”) could be used.

  1. B2>400 is the condition being tested. This means ‘Is the value is cell B2 greater than 400?’
  2. High is the text displayed if B2 is greater than 400.
  3. Low is the text displayed if B2 is less than or equal to 400.


  • IFERROR(calculation, error_value) The IFERROR function is used to trap errors that may occur as the result of a calculation and then display alternative text or values.
  • TRUE and FALSE

TRUE or FALSE are logical values. As they are values, they do not need quotation marks around them. The result of a logical test is either true or false and Excel allows you to enter these values in cells or test them for functions.

  • The AND function is used to compare more than one condition. It returns true only is all of the conditions are met. If all the conditions are true, AND returns the value “true”. If any of the conditions are false, the function will return the value “false”.

=AND(condition1, condition2,…) For example, =AND (B2>400, C2 <300) where B2>400 is the first condition being tested and C2< 300 is the second condition being tested. The result “true” will be returned if the value in cell B2 is greater than 400 and the value is cell C2 is less than 300. Otherwise, it will be false.

  • The OR function is used to compare the results of more than one condition test. The value “true” will be returned if any of the condition tests value true. “False” will only be returned if all of the condition tests return false. OR tests the specified logical conditions or cell references.

=OR(condition 1, condition2,)

For example, =OR(B2>400, C2<300) where B2<400 is the first condition being tested, and C2<300 is the second. The result “true” will be returned if either the value if cell B2 is greater than 400 OR the value in cell C2 is less than 300. The result will be “false” only if neither of the conditions are met. ]

  • NOT

The NOT function is for excluding the values you do not want. If the condition test returns the value “true” then NOT function will return the value “false”.

Adding and deleting columns and rows

To add a column or row :

  1. Select a cell
  2. Click Insert in the "Columns" or "Rows" group on the home tab

To delete a column or row :

  1. Select a column or row
  2. Hit delete

Hiding and showing columns and rows

To hide a column or row from view. This is effective if you want to hide cells with small detail values and only show total values, or protect sensitive date such as payrolls.

To hide a row or column :

  1. Select the row or column headers.
  2. Click the Format tab and select "Hide and Unhide"
  3. Select "Hide Columns" or "Hide Rows".

"Quick tips" Hide a row or column by changing the row height or column width to zero, or by right clicking the selected column and clicking Hide.

To show the rows or columns again, simply :

  1. Select the the range next to and including the hidden cells.
  2. Click on Format and select "Hide and Unhide"
  3. Select "Unhide Column" or "Unhide Row"

Splitting windows

Dividing the screen into separate working areas, so you can scroll to any part of the worksheet in any of the panes. This is useful if you want to modify numbers yet see the end result at the same time. This can be done horizontally or vertically, or both. To split a window with Excel :

  1. Click on the cell where you want the split.
  2. In the "View" tab, select the "Window" tab and click "Split". This creates a four pane split.

For a two pane split:

  1. Select the column or row where you want the split, and select "Split" in the Window tab.

To remove any split, go to the Window tab and select "Remove Split".


Finding and replacing last and first cells

To find specific information in a worksheet, and replacing values. To find :

  1. Click on "Find and Select" and select "Find"
  2. Enter what information you would like to search for i.e the surname Smith or a specific cell reference.
  3. Click "Find Next" to search for more occurrences of your search.

You can search the entire workbook, or just the active sheet by clicking in options in the "Find and Replace" box and selecting workbook or sheet.

To replace :

  1. Bring up the "Find and Select" box again, and select "Replace"
  2. Type in what you want the replacement number or word to be.
  3. "Replace all".

To "Find" the last cell, in the "Find and Select" box, "Go to Special"

  1. Click on "Last cell".

Sorting

With Excel, you can sort data alphabetically, numerically or chronologically. To sort alphabetically:

  1. Click in the column you want to sort.
  2. In "Filter" in the "Edit" tab, chose either "Sort A to Z" or "Sort Z to A"
  3. To sort more than one column, find "Sort" in the "Edit" tab and chose "custom sort". You can then adjust how you sort, for example firstly by a Column A: A-Z, then Column B: Z-A etc.
  4. You can also sort rows by going to the "Custom Sort" bar and clicking Sort Left to Right.
  5. You can also sort numbers by clicking "Sort Largest to Smallest" or the other way around.

Filtering

Use filtering to compare lists of records against specific criteria and hide the records that do not match up. Do this to find records, or create a subset of data that you can then edit, format, cope and manipulate without affecting the other records. To filter with Excel :

  1. Click on your heading in one of the columns.
  2. Click on "Data" then "Filter"
  3. An arrow should come up beside each group name, e.g name, year, company. Click on the arrow to display the options.
  4. Select the option you want to filter, e.g 1995 in the year column. All the records with this number will come up and the rest will be hidden.
  5. Remove this filter by clicking on the arrow and clicking "clear filter" or "show all".

You can also create custom filters by clicking "custom filter" and choosing e.g. "contains" Smith which will just bring up those records that include the name Smith.

If you print this list, excel will hide the other records and just show the filtered version.

Charting

You can modify the default chart layout produced by Excel by adjusting the chart title, choosing the chart type, (Column, bar, pie etc.) add text boxes, renaming axis labels and repositioning the legend. To create a new chart  :

  1. Select the range of cells you want to show in chart form.
  2. Click on "Insert" and "Column:
  3. Chose the desired chart type which best displays your information.

Create a separate chart sheet by :

  1. Clicking on "Chart' and then "Move Chart" to "New Sheet".