Using Microsoft Excel to Track Squidoo Lenses

Excel uses tabbed worksheets which allow you to create a single workbook containing dozens of related worksheets. My Squidoo Master List workbook has worksheets for:

• Master List of Squidoo Lenses


• Login Information


• Affiliate Information


• Ideas

My first worksheet, the Master List of Squidoo Lenses, contains information such as lens name, lens URL, lens description, number of groups, incoming links, whether it's been submitted to search engines, last Squidcast date, last update, and whether the lens has potential for becoming part of a series. I use conditional formatting to highlight areas that need work which allows me to quickly prioritize my projects.

My next tabbed worksheet contains the login information for Squidoo and related accounts. It's difficult to remember which username and password is associated with which site. Having this information readily available is a timesaver. if you're not comfortable storing usernames and passwords on your computer (such as if you share your computer with someone), you can hide and/or protect the worksheet.

The Affliate Info worksheet is useful for storing information about the products that you represent. For example, I recently discovered AllPosters.com, as many other Squidoo users have, along with a couple of other affiliations that look promising.




Buy at AllPosters.com

This worksheet contains details such as: product, company, URL, username and password, and affiliate ID. You can add additional information to suit your needs such as earnings information, referral codes, and so on. The beauty of using spreadsheets is that you can make them whatever you want them to be.

Finally, I have a spreadsheet for my Squidoo lens ideas. You can set this spreadsheet to automatically insert your lenses from the Master list of lenses and jot down additional ideas in a second column or simply freely post your ideas as they come to you.

Another idea for a worksheet is keyword research. If you research keywords using keyword tools like Google Adwords, having a place to store them ensures that you won't forget which ones were hot. You can also link from one worksheet or workbook to the next. For example, if you have a Keyword field on your Master List, you can link each lens to its related record of keywords stored in a separate worksheet or workbook.

The Master List of Squidoo Lenses worksheet is the most detailed while the other worksheets are fairly straight forward. Here's a look at the categories I have created for my own Squidoo tracking worksheet. Feel free to use these categories when creating your own Excel spreadsheet or add your own based on your personal preferences. If you've come up with a terrific idea, please share your ideas below.

The Lens Description column isn't necessarily for your information. Rather, it's for copying and pasting the descriptions of your lenses into the various bookmarking sites and blogs. Type up your description once and then copy and paste as needed. This will save you a ton of time if you promote the lens in multiple places.

I have several columns such as Photos, Discovery, Tags, and Groups that I consider "Squidoo To-Dos." These are areas that I know I need to check or work on. In addition, I have columns for linking, lensrolling, stumbling upon, and Squidcasting. Create columns for the categories that you know that you need to work on at some point and enter the current status for each lens.

Try to standardize your entries so that you can then use conditional formatting to highlight areas that need work. For example, for Photos, I enter the following values: yes, no, and check. With "yes" meaning that the lens has photos, "no" meaning it doesn't, and "check" meaning that I need to check it out and see. I've set up Excel to highlight "check" with yellow and "no" with red. I can see at a glance which lenses need photos.

You can create drop down menu lists to force yourself to enter certain values if it helps. Doing so ensures consistency. To do this, first create a list of entries for the drop down list.Then, use the Data Validation tool. In Excel 2007, this is found on the Data tab. Under the Settings tab, click on Allow and choose List. Enter the location of the list of approved entries.

Finally, I have columns where I enter the number of incoming links. You can use formulas to calculate this depending on how you set up the worksheet. For example, I have columns for Stumble, Tagfoot, My Blog, Groups, and so on. When I link the lens somewhere, I place a "1" in the appropriate column. Then the column for Incoming Links automatically calculates the total number of incoming links.

Imagine if your Squidoo lens tracking worksheet could tell you - at a glance - exactly which lenses need additional work. It can! Using Excel's conditional formatting is one such way to do this. For example, let's say that you want to submiteach lens to at least five groups. However, let's also say that you don't always have the free time to find five well-matched groups in one sitting.

Set up a column in your Squidoo spreadsheet for the number of groups the lens has been submitted to and enter a value representing the number of groups you've submitted the lens to. Update this value each time you submit the lens to a new group. While this provides you with the information that you want, adding conditional formatting makes the values pop out. For example, you can set up the formatting so that any cell with a value less than 5 displays with red highlighting and any value above five displays with green highlighting. Now, you can quickly see that any Squidoo lens with red highlighting in the Groups column needs to be submitted to more groups.

In Excel 2007, first select the column that you want to work with and then go to the Home tab. Find the Styles area and click on the Conditional Formatting icon. Click New Rule. Click "Format only cells that contain" and then enter the values in the cell fields below. For example, enter between 0 and 4 in the values indicated if you are using the example discussed earlier. Now, click the Format button and choose the formatting style that you want to apply. In this example, you would click the Fill tab and choose the red color.

As you're in the Conditional Formatting area, play around with the various options. Quite a few preset formatting rules are available and they're fun to use!