Navigating 20Books Vegas Videos: A Tracking Template

Loading

Welcome to Las Vegas sign
Photo by Zalman Grossbaum on Unsplash

This article may contain affiliate links, meaning we’ll earn a small commission if you purchase products through these links.

20Books Vegas Writers Conference

The 20BooksTo50k(R) Facebook Group is one of the most widely known groups for self-publishing authors. Each year, they host one of the largest author conferences in the U.S. known as 20Books Vegas. It’s a week of sessions on writing craft, marketing, writing tools, and business information. These sessions are recorded, then uploaded to YouTube for public consumption. And the sheer number of sessions can be incredibly overwhelming.

The 20Books team provides a spreadsheet to help track all the sessions and your progress in getting through them. As helpful as it is, it’s not enough to keep this author from getting confused about where I am and what I’ve already viewed.

So, as in most things in my life, I found my own way to organize and track which videos I wanted to watch, which ones I might be interested in if I have time, and which ones I wasn’t interested in at this point in my career. I thought it might be useful to other authors, so I’m sharing my template with everyone.

Lancy’s Template

First, let’s look at the 20Books spreadsheet. The link is: https://docs.google.com/spreadsheets/d/1LOkJsLkfSoy4M4JEdDU_7W7SnEYEGLe-/edit.

It has three sets of columns contains the session name and a convenient link to the YouTube video. The first set of columns from the 2023 conference and the other columns to the right contain various videos from previous conferences. (I don’t understand the meanings behind the “End Card Video” and “Info Card Video” headings, but they include the conference year in the titles of the sessions in these columns. Another note is there are duplicate listings of sessions in these last two, which I’m assuming is because some sessions fall into multiple categories.) Additionally, the sessions are listed by category, which we can use to help with our planning.

screenshot of 20Books Vegas Checklist for Success
20Books Vegas 2023 – Checklist for Success

This is great source material and I love how they include the session links for easy access, but it’s difficult for me to parse this in a way that makes sense. So I’m going to bring this data over into Excel, where I can work with it better.

Here’s what we’ll end up with when we’re done:

Excel spreadsheet 20Books Vegas session list
Screenshot by Lancy McCall

How It Works

I organize my spreadsheet by how I might want to view the data. In this case, I’ll want to filter by the general topic (category), the conference year, and my interest level in the session. (If I’m selling wide, I probably don’t need to see sessions on Kindle Unlimited or maybe I’m not ready for advanced marketing topics yet.)

In my spreadsheet, the information from the 20Books Google sheet is contained in columns D and E. I’ve done some cleanup work on the list:

  • Extracted the year from the named of previous years’ sessions.
  • Removed the “Vegas 20XX – ” from the session names.
  • Eliminated duplicate entries from the list.

I’ve sorted the information by Category first, then by Session Name. (You can change the sort to match your needs.)

There are filters on each column so you can view by interest level, year, category, or even the session name. (It’s unlikely you’ll ever filter by the link, but Excel gives us that option.)

Using Filters

Setting up filters on columns helps cut through the noise of too many options and allows us zero in on those things important to us right now.

There are two ways to use the filters at the top of the columns: basic and advanced.

The basic filter lets you select from all the options that exist in the column. If you click on the dropdown arrow for the Year column, you’ll see a checkbox list of all the years in this list and you can choose which ones to include.

The advanced filter lets you get more precise. For example, the way I found all sessions related to Amazon Ads was using the advanced filter on the Session Name column. Here’s how I did it:

  • Click the dropdown arrow in the Session Name column.
  • In the Filter section, change the dropdown from Choose One to Contains and type “Amazon Ads” into the field next to it.
  • This will find all session names that contain “Amazon Ads” and filter out everything else.
screenshot of filtered spreadsheet
Screenshot by Lancy McCall

(No offense to Bryan Cohen, but I’ve taken his course twice, so I’m skipping this video.)

Note: To clear the advanced filter, click the Select All box in the filter window.

You can do multiple filters at once. For example, you might want to set a filter on the Category, then set a second filter to exclude older videos. (We all know how fast things change in marketing!)

Now that you understand how to use the filters, you can limit what you’re looking at, then label each session based on your learning priorities in the Interest Level column.

Interest Level

Normally, for columns where I want consistent entries, I’ll use Data Validation (where you can only enter defined values from a dropdown list). However, because this is a template for others, I’ve left it open since everyone has their own idea of what needs to go in this column.

The options I’m using are: Yes, No, Maybe, Future and Done. Maybe you’d prefer a priority ranking system like P1, P2, P3, etc. You can use whatever verbiage makes sense to you. Just be consistent, so the filter serves your needs. 

Conditional Formatting

If you use the options I used above, you’ll have the benefit of conditional formatting. (You can change this to fit your options. See the Advanced section below to see how this works.)

Below is a screenshot where I’ve labeled some sessions in my planning spreadsheet. I’m not doing anything with Kickstarter or Press Releases at the moment, so I’ve marked those as No. But I have been dabbling in Amazon Ads, so anything containing “Amazon Ads,” I’ve marked with Maybe.

Excel spreadsheet with conditional formatting
Screenshot by Lancy McCall

Once you get the Interest Level added for the videos, you can filter on the Interest Level column. This lets you eliminate any videos you’re not interested in. Or you do the opposite and filter for specific videos you want to watch and work your way through them from there.

Be sure to mark them as Done when you have finished.

Setting It Up – Advanced Excel Instructions

In this section, I’ll describe the steps I took to organize the data and the advanced Excel functions and features I used. (You can skip this section if you don’t care about the details and just want the template!)

Set Up Columns

First, figure out what you want to filter by and set up a column for each filter item. I set up Interest Level, Year, Category, Session Name, and Link. 

(While setting up column headings isn’t advanced, I’m mentioning this now because it will be useful in the following directions describing where we’re putting the data.)

Copy the Session Names and YouTube links from the 20Books spreadsheet.

While the 20Books spreadsheet has six columns of session names and links, we’re going to copy them into two columns in our Template spreadsheet. (All sessions are in column D and all links are in column E.)

Add the Categories

There’s probably a fancy formula I could have used to get the categories into place in the Template column C, but it was just faster to manually copy and paste them. 

Find the category in the 20Books spreadsheet column B and copy it next to the sessions that apply. Remember, the 20Books column B category applies to the sessions in columns B, F, and J.

Remove Duplicates

Because the learning sessions can fall into multiple categories, 20Books has listed them more than once. Let’s get rid of the duplicate entries before we process the data further. (One of my favorite features in Excel is that it lets you painlessly highlight duplicate values in a range.)

Tip: I found a few session names worded differently, but the YouTube links were identical. We’ll use the links to identify duplicates entries.

Highlight Duplicate Values
  • Highlight the Link column.
  • On the Ribbon Bar, select the Home tab.
  • Look for the Conditional Formatting dropdown.
  • Click the dropdown, then Highlight Cells Rules > Duplicate Values…
Excel menu for highlighting duplicate values
Screenshot by Lancy McCall
  • Click OK to accept the default highlighting.
Remove the duplicate values.
  • Review the highlighted links and delete the entire row of the duplicate entry.
  • Mostly this means deciding which category you want that session to appear in. If it’s listed under Writing Craft, I don’t need the one under Panel. 
  • Be careful! Some appear three times!
  • Once you remove all duplicate entries, the highlights will disappear.

Extract the year from the session title into 

There are two steps to filling the Year column: get the year, then paste the values. We’ll use a formula to extract the year, but then we must change the formula to text because our next step is to update the session’s name.

Extract the Dates:
  • In the Year column of the first session, enter the following formula, where D4 is the cell of the first Session Name: =mid(d4,find(“20”,d4),4)
    • This formula extracts four characters beginning with the date. If there is no “20” in the name, it will return an error. (All the 2023 sessions should return an error.)
  • In the Year column, enter “2023” for all the 2023 sessions.
  • Double-check any lines displaying an error that are not a 2023 session.
Paste the Values:
  • Highlight the entire Year column.
  • Copy the values. (Cmd-C or Ctrl-C)
  • With the column still highlighted, Paste Special the Values:
    • Navigate to Edit > Paste Special… from the menu. (You can also hit Cmd-Opt-V on the Mac.)
    • Click the Values option in the first column.
    • Click OK.

Now we can change the session names and it won’t affect the data in our Year column.

Change the Session Names (Remove Vegas YYYY)

Next, we’re going to get rid of the “Vegas 20YY – ” text in the Session Names. 

Warning: You must do this step AFTER you’ve pasted the values in the Year column.

  • Highlight the Session Name column.
  • Hit Ctrl-H to bring up the Find and Replace window.
Excel Find and Replace window
Screenshot by Lancy McCall
  • In the Find what: field, enter “Vegas 2022 – ” (note there is a trailing space).
  • In the Replace with: field, leave it empty. (I usually click in it and hit my backspace key just to make sure it truly is empty.)
  • Click Replace All.
  • Repeat for the years 2019 and 2021, changing the value in the Find box as needed. (There was no conference in 2020.)

Set up Conditional Formatting for Interest Levels

This section describes how to set up the conditional formatting for the Interest Level options from scratch. If you’re just interested in modifying the existing ones, see the next section.

We’re going to set up Conditional Formatting based on the values in our Interest Level column.

First, determine what options you want to use. I used Yes, No, Maybe, Future, and Done. You may prefer a priority ranking system like P1, P2, P3, etc.

If you’re using different Interest Level options or you don’t like the color scheme, you may change the existing conditional formatting rules in the Template. Here’s how:

Modify an Existing Rule:
  • Navigate from the main menu to Format > Conditional Formatting… OR click on the Conditional Formatting drown from the Home tab on the Ribbon Bar and select Manage rules…
  • At the very top, change the dropdown to This Worksheet. (This will show all the rules and not just those relevant to where your cursor is.)
Excel Conditional Formatting Manage Rules window
Screenshot by Lancy McCall
  • Highlight the rule you want to change and click the Edit Rule button in the lower left.
Excel Edit formatting Rule window
Screenshot by Lancy McCall
  • Change the value within the quotes to match your green option. Make sure you ONLY change the value within the quotes.
  • To change the color, click the Format with: dropdown.
    • Click Custom Format… to change the font, color, etc. for this option. (Fill is the tab where you set the background color.)
  • Click OK to save the font, then OK to save the rule changes.
  • Modify the next rules as needed and click OK in the Manage Rules window when you finish your updates.
Add a New Rule:
  • In the Conditional Formatting > Manage Rules… window, highlight one of the existing rules and click the Duplicate Rule button in the bottom left.
  • Highlight the new rule created and click the Edit Rule button.
  • Change as needed following the instructions above.
  • Click OK on each window to save changes.

Wrap Up and Download Link

That’s how I set up my tracking spreadsheet in Excel. I have a blank copy for you to use. Click the link below to download the blank Excel template. 

Warning: Clicking the link below will open the spreadsheet in Google Sheets, but you won’t get the benefit of the conditional formatting features without Excel.

To download, click on the link below. Then select File > Download > Microsoft Excel from the menu.

https://docs.google.com/spreadsheets/d/1L1cpvLwoewziXFFiay4VIzKteJbDW7ya/edit

Let me know if you like it or if you have another way of tracking your learning goals.


Happy Writing!


NOTE: We ask for your email address in the comment section to prevent spammers. Your email will not be displayed or shared, nor are you subscribing to any list. Thank you!

Leave a Reply