How To Create A Scrolling Table In Excel [VIDEO]

Scrolling table in Excel
Does this table ever end?

In today’s video I demonstrate how to create a scrolling table in Excel using the scrollbar option in your format controls. This is a great way to allow more data in a small space, such as a dashboard. You can see how this will look in the animated gif below:

Scrolling table from Google Analytics data in Excel

I use the INDEX function to add this interactivity. If you want to better understand the INDEX function, I did a comprehensive video covering three marketing uses for the INDEX function. (You’ll also find out why I always use the INDEX function over the OFFSET function.)

You can also see how I used a scrollbar to create a scrolling chart.

Video

Download File

If you’d like to download the file to follow along, you can get it here. (It’s a Dropbox link, so if your company blocks Dropbox, you may have to wait until you get home.)

Learn More

If you want to learn how to create compelling dashboards like this or this, check out my Annielytics Dashboard Course.

 

How To Use The SUMIF And SUMIFS Functions To Build Dynamic Dashboards [VIDEO]

Function tag teaming at its finest
We have some serious tag teaming going on with this data

In today’s #FunctionFriday post we’re going to look at how the SUMIF and SUMIFS functions can be used to build out interactive dashboards like the ones below I just added to my dashboard course for subscribers to apply to their data.

Excel dashboard for analytics data
Click for larger image

 

Excel dashboards for Google Analytics
Click for larger image

Note: If you want to reanimate the images, just refresh the page with a hard refresh. On a PC use Ctrl-F5 and Command-Shift-R if you’re on a Mac. (I hate animated gifs that automate ad infinitum.)

Excel File Download

I don’t share the whole dashboard, but I share a portion, so you can see how I used both the SUMIF and SUMIFS functions in a single formula to make the drop-down at the top of the dashboard update the entire dashboard.

Here is what the formula looks like that you’ll be learning to rock. Call it a formula BOGO deal. :) Example of SUMIFS dashboard in Excel Members of the Annielytics Dashboard Course will have full access to the dashboard, as well as directions for how to update it with your analytics data. Or you could buy it for $795 and have my team update it with your data and colors/branding. It would require a subscription to Analytics Canvas, which you can get for $49/mo for one user. Having tried most of the API tools for Google Analytics, I found Analytics Canvas to have, by far, the most flexibility, especially if you’ve screwed up your campaign tagging. (It allows you to rewrite this data.)

You can download the Excel file to follow along.

Video

Warning: I have a habit of saying formula when I mean function. Formulas are made up of individual functions, such as the SUMIF and SUMIFS functions.

Learn How To Create Your Own

Want to learn how to create interactive dashboards using the Google Analytics API? Check out the dashboard course and get $200 off just for purchasing from this blog post. (The discounted price should show up in the cart. If it doesn’t, let us know here.)

The INDEX Function: Excel’s More Stable Alternative To OFFSET [VIDEO]

The index vs the offset function in Excel

Back in March I did a post on three marketing uses for the OFFSET function. But, truth be told, I avoid the OFFSET function like the plague. The reason is that it’s very unstable and can bring large Excel files to their knees.

Why? Every time you modify the file the OFFSET function takes that as a cue to go ahead and run again.

It’s not alone. There are a few more functions that are volatile in this way, namely:  

  • NOW()
  • TODAY()
  • CELL()
  • INDIRECT()
  • ROWS()
  • COLUMNS()

REMIX!

So what I decided to do was take all of the marketing uses I demonstrated in that video and show the INDEX alternative.

Demo File

If you’d like to see how these look out in the wild, you can download the Excel file I used.

Excel’s INDEX MATCH Alternative To VLOOKUP For Marketers [VIDEO]

An Excel function as flexible as this contortionist
Now that’s flexible

Last week’s #FunctionFriday video tutorial covered VLOOKUP tips and techniques for marketers. This week’s video explores the INDEX MATCH alternative to VLOOKUP.

Why would VLOOKUP need an understudy? Because VLOOKUPs require that the lookup column be the far-left column in your lookup data set (aka the table_array argument). The INDEX-MATCH dream team doesn’t have that requirement, making it more flexible.

Video

Comparing VLOOKUP and MATCH

I make a comment in the video about how the MATCH function is nearly identical to the VLOOKUP function and wanted to expand on that here.

Here’s the syntax for VLOOKUP:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

And here’s the syntax for MATCH:

MATCH(lookup_value, lookup_array, [match_type])

For marketing purposes, they’re practically fraternal twins. But there are a couple key differences:

  • With VLOOKUP, you’re working with a table, so you need the col_index_num argument. With MATCH you’re just feeding it one column or row.
  • Much like the INDEX function, VLOOKUP returns the value in the cell that meets the lookup requirements; MATCH returns the relative position of the cell where it finds a match to your lookup value.

Note: Since marketers are most commonly working with exact match for both of these functions and you can use 0 in lieu of FALSE in a VLOOKUP, the range_lookup and match_type arguments accomplish identical objectives: telling Excel to only look for exact matches.

Excel Download

You can download the file I used in the video tutorial here.

VLOOKUP Tips And Tricks For Marketers [VIDEO]

Excel lookups for marketers

The VLOOKUP function in Excel is one of the most essential functions marketers need to master. It’s a staple in my tool set because it allows you to join any number of data sets, as long as they have one column in common. Sadly, as is the case with most really useful Excel techniques, it can be quite intimidating for newbies. I hope to remove some of that intimidation with this video.

Syntax

The syntax for the VLOOKUP function is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Video Overview

In this tutorial I take a custom landing page report from Google Analytics (which you can access here) and marry it to a data set I got from a Screaming Frog crawl. I cover these techniques:

  • Concatenating hostname and landing pages to match the format of URLs in Screaming Frog exports
  • Alternating between absolute and relative references for formulas
  • Building flat table custom reports in Google Analytics (learn more with this post)
  • Working with named ranges in Excel
  • Concatenating on the fly inside a VLOOKUP
  • Making VLOOKUPs dynamic
  • Error handling for VLOOKUPs

Video

Excel Download

You can download the Excel file here.

Couple More For The Road

Partial Matches

One more cool technique you can jam with VLOOKUPs (but didn’t work with my data set) is to use wildcard characters to look for partial matches in text. (Learn more about wildcard characters from the Microsoft site.) This transforms the matching functionality to a makeshift CONTAINS function. (I’ve requested that the Excel teams at Microsoft add a CONTAINS function.

Trims

Some marketing data exports can be pretty messy, but if they contain things like extra spaces before or after your lookup text or table array, your VLOOKUPs will break. If this happens to you, you can wrap either your lookup_value or table_array in a TRIM function to remove all extraneous spaces.

Your Turn

Do you have some wild and crazy things like you do with VLOOKUPs? If so, please share with the rest of the class in the comments!