|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.
In Google Analytics, in the upper right corner, below Admin-Help, we can select our date range.
|Google Analytics, select your date range.|
In Google AdWords we can use "Segment" to view our performance data (see image below). Then, we have to click on the "Download report" button.
|Google AdWords: use Segment to view performance data.|
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 them into a new tab of "April-June" spreadsheet;
- Rename the tabs with the names of the quarters ("Apr-Jun" and "Jan-Mar");
- Use "Tab Color" to add a little customization (see image below).
|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".|
4) Use VLOOKUP formula
Now, we want to put the bounce rates of January-March ("Jan-Mar" tab) into our new column; here we will use the VLOOKUP formula.
As we can see in the image below, our VLOOKUP formula has 5 pieces:
- VLOOKUP: name of the formula;
- A2:A11: Rows 2-11 in the column A (the names of campaigns we want to compare);
- "Jan-Mar"!A2:F11: the set of data from A2 to F11 in the "Jan-Mar" tab;
- 6: The number of columns from the first column ("Campaign") to the column of our interest ("Bounce Rate");
- 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.|
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.|