Excel is a powerful tool bloggers can use to save time formating the HTML content of top list posts (you know, the popular top 10 type of posts). Chances are, if you are making a top list, Excel (or a similar spreadsheet application) will be used to collate the list. Using basic Excel formulas it’s quick to turn the list into a chunk of HTML code that can be simply cut & pasted into Wordpress. And with the spreadsheet ready it just takes 10 minutes of research time to build a top list.
For this example I’m going to use the spreadsheet used to build the 31 Breathtaking Planet & Space Tutorials For Photoshop post on DesignReviver.
Step 1 – Collate the List
Collate the list, including screenshots or images as required. In the space tutorial we have the title, description, link URL and image data fields:
Step 2 – Work Out What Format is Required
Work out which type of HTML formatting you are going to use, in my example I’m using normal paragraph formatting plus <h5> for the titles giving the following HTML output for one entry:
<h5><a href='http://dinyctis.deviantart.com/art/Planet-Tutorial-3131869' target='_blank'>Planet with Solar Glow</a></h5>
<a href='http://dinyctis.deviantart.com/art/Planet-Tutorial-3131869' target='_blank'><img src='http://designreviver.com/wp-content/uploads/2008/09/planet1.jpg' /></a>
<br>Beginning with a simple rock for texture this design created a new world with clarity and great detail.
Step 3 – Use Excel to Format the List
Use Excel to code the HTML from the data fields using formulas. Use “&” symbol to add strings of text together to make links:
="<a href='"&B3&"'>"&A3&"</a>"
And continue to construct the image code in the same way:
=E3&"<img src='http://designreviver.com/wp-content/uploads/2008/09/"&D3&"' /></a><br>"
Then use the “if” statement to check if there is an image and output the correct code:
=IF(D3="","",G3)
Carry on to complete the rest of the HTML in the same way and use the final column to add all the code from the first entry together. At this stage make sure you haven’t made any errors by testing the HTML by copying the output cell at the end. If you are unsure of this step download the example spreadsheet and look at the formulas in columns E to K.
Step 4 – Copy The Formulas Throughout
Copy all the formulas down from all formula columns from the top line throughout the list so each line with data now has formulas.
Step 5 – Copy & Paste Into Wordpress
Copy the final column and paste this directly into the correct part of your post, make sure the HTML tab is selected in the Wordpress editor.
Save the post, preview and check out the results. Normally a bit of editing back and forward to ensures you have the result you want.
Changing the List Type
Once you’ve got the hang of using the “if” statements and adding text strings together with “&” you can build custom spreadsheets for different post types. For example in my 122 Marketing Tips post I produced a bullet pointed list with many sections using this spreadsheet.
Downloads
Space_Image_Top_List_Post_Example.xls
Bulletpoint_Top_List_Example.xls
Why save time and use the spreadsheets above to make your top lists.
Happy listing!
{ 1 comment… read it below or add one }
This is also a handy tutorial for string manipulation in Excel. It can be quite cryptic, so good job with your clear explanations!