24 June 2013

Excel VLOOKUP Formula Demystified

In previous posts of this blog we talked about Google AdWords metrics and how to use them in order to improve our results.  Now we will learn how to use VLOOKUP formula and Conditional Formatting  (Excel) to compare our performance month over month. Let's dig in!

VLOOKUP formula and Conditional Formatting.
VLOOKUP formula and Conditional Formatting.

Excel is one of the most useful tools we have to analyze our data. VLOOKUP formula allows us to compare data from two different date ranges (i.e.: two quarters), whilst Conditional Formatting allows us to improve the readability of our charts (i.e.: highlighting percentages).

In this post we will learn how to create both our formula and our Conditional Formatting rule step by step.

1) Download Data
First of all, we have to extract our data from Google AdWords and /or Google Analytics.

2) Create an Excel worksheet
Once downloaded our data (for example, April-June and January-March stats ) we have to:
  • Open our Excel spreadsheets;
  • Copy the "January-March" spreadsheet (the oldest quarter) and paste it into a new tab of "April-June" spreadsheet;
  • Rename the tabs (i.e: "Apr-Jun" and "Jan-Mar");
  • Use "Tab Color" to add a little customization (see image below). 

How to customize the color of  Excel Tabs.
How to customize the color of  Excel Tabs. 

3) Insert New Column
Let's say we want to compare the Bounce Rates of "April-June" and "January-March": On the right of Bounce Rate column, in "April-June" spreadsheet - "Apr-Jun" tab, we have to insert a new column ("Bounce Rate Jan-Mar", see image below).

Insert a new column in "April May" tab: "Bounce Rate Jan-Mar".
Insert a new column in "April May" tab: "Bounce Rate Jan-Mar".

4) VLOOKUP Formula
Now, we will use VLOOKUP Formula to put the Bounce Rates of January-March ("Jan-Mar" tab) into our new column
As we can see in the image below, our VLOOKUP formula has 5 pieces:
  1. VLOOKUP: name of the formula;
  2. A2:A11:  Rows 2-11 of the column A (the names of campaigns we want to compare);
  3. "Jan-Mar"!A2:F11: the set of data from A2 to F11 in the "Jan-Mar" tab;
  4. 6: The number of columns from the first column ("Campaign") to the column of our interest ("Bounce Rate");
  5. FALSE: With this logical value we tell Excel to find the exact match for the Bounce Rates located in the sixth column of the "Jan-Mar" tab). If one is not found, the error value #N/A is returned.

VLOOKUP step by step.
VLOOKUP described step by step.

5) Conditional Formatting
Now let's put icing on our Excel cake with Conditional Formatting!
Let's say we want to highlight with red the cells of column G that have a Bounce Rate higher than those on column F: 
  • Select the cells G2-G11; 
  • In Excel - Format - Conditional Formatting, click on "+" 
  • Choose "Style: Classic" - "Use a formula to determine which cells to format";
  • Enter the formula "=G2>F2" and specify the desired formatting (see image below).

Conditional Formatting step by step.
Conditional Formatting step by step.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...