How To Create an Excel Spreadsheet to Manage Your Articles (or Anything)

Microsoft Excel is a spreadsheet application. It is excellent for financial and accounting uses because it has many built-in mathematical functions aimed at just that. It can also be used as a simple database – a list of related items that you want to keep track of.

I’m going to start with a pet peeve though. I do not really approve of using Excel as a database if you also have (or can afford to buy) Access. I’m not talking to home users or people on a budget, but I have been appalled at the many offices I’ve worked in (sometimes as a temp) and found they were trying to use a huge spreadsheet instead of a real database program. Either they are cheap (many certainly are) or they are ignorant, but that’s just bad business. Take the time to move your data into Access and start using the right tool for the right job. Okay, I’m done with that little rant. Moving on.

For those of us who do not have Access or any other database software, and whose need is for a small, simple database, Excel works fine. I’m going to give you some pointers on how to set up a spreadsheet to be used as a simple database, and I’ll use the example of a database of your eHow or Bukisa articles (because I just set up one of my own).

New Workbook – Launch Excel and begin a new workbook. You will probably have 4 worksheets in this blank workbook. We’ll just work with the first sheet. I actually set up my first sheet with my eHow articles and my second sheet with my Bukisa articles. You can certainly use as many sheets as you want.

Leave a few rows for any comments or titles you might like on the worksheet.

TITLE Line – on row 3 or 4, you want to create a line of titles for your mini-database. These would be field names in a real database. These are the titles I used in my spreadsheet:

• cell: A4 – TYPE
• cell: B4 – ARTICLE
• cell: C4 – DATE
• cell: D4 – EARNED
• cell: E4 – VIEWS
• cell: F4 – SUBMITTED?

ARTICLE - First we will populate the cells under ARTICLE. Have the spreadsheet open and not maximized on one side of your desktop. Open your browser and sign in to Bukisa and go to your content page. Your articles are sorted by descending date (the first article you ever wrote is at the bottom). Your newest article is on top.

Place your cursor over the title of your article and right click. Look through the menu that pops up and find “Copy Link Location” (if you use Firefox) or “Copy Shortcut” (in Internet Explorer). Click on that menu item and the URL for your article will go into your buffer.

Switch to your Excel spreadsheet, click into the first cell below the word ARTICLE (B5, if you followed my directions above) and press CTRL V to paste the url into your spreadsheet. You can either do this for each article URL and then populate the next field, or fill in all fields for each article. Up to you.

TYPE – now that you know which article you’re working on, you can click in cell A5 and describe the type of article it is. You don’t have to do this if all your articles are recipes (although you might then say the type of meal or type of food). I have a number of different types of articles. The secret here is to classify the type broadly enough that you don’t have a different type for each article, yet narrowly enough that it is useful. I used “Spiritual” and “Crafts” and “Computer” and “Health” which are good categories for most of mine. You can always tweak this later.

DATE – Enter the date you wrote the article in cell C5. If you don’t like how it looks (I usually don’t) then you can change the appearance. Click at the top of the spreadsheet on the Column Heading that says C (with no number). This highlights the entire column C. Alt F E (menu, format, cells) and click on Date. Choose the formatting you like – I prefer mm/dd which is third on the list.

EARNINGS – This is what it’s all about, isn’t it? The whole reason you want to make this spreadsheet. You’ll probably want to update this at least once a week, or daily if you’ve got time on your hands. Refer to your article library on Bukisa and type in the amount in your spreadsheet cell D5. You may want to change the formatting of this column of cells to Currency.

VIEWS – Again referring to the number of views from your Article library in Bukisa, enter the number of views this article has received so far into cell E5.

SUBMITTED? – I don’t submit every article I write to the search engines, although no doubt I should. When I do submit them, it’s hard to remember which ones I’ve done or not, so I write YES in this column when I’ve submitted it. I just leave it blank otherwise.

That’s the whole database. Enter this information for each article you’ve published.

Here’s how to use the database:

NAME the database: Place your cursor in cell A4 (where you have the field name TYPE). Hold down your shift key and keep it depressed (poor thing). Find the LAST article and click in the cell in column F in that last row (F20? F50?) This will highlight your entire database. Click on the menu Insert, Name, Define (Alt, I, N, D) and type in a name for your list (do not accept the default ‘Type’). Let’s say you call it “Bukisa.”

SORTING – you can sort your new database on any of the columns you want. Sorting in Excel is TRICKY though, so be very careful when you do this. Highlight your entire database, as in the last step, or (easier to do, harder to explain) click on the little down arrow in the box that is over the column A heading and choose ‘Bukisa’ (or whatever you named the database). It’s all highlighted! Now to sort:

Using the menu click Data, Sort (Alt D S) which will bring up a little pop-up window. You can sort on any of the fields you created, in either direction, and can even sort on more than one at a time. Let’s say you want to sort on the earnings of each type. First make sure “My List Has” Header Row is bulleted near the bottom of the pop up. Then where it says “Sort by” choose “Type” (ascending or descending, it will be alphabetical up or down). In “Then by” choose “Earnings” (again, ascending or descending depending on whether you want the big money or the bombs to display first). Then click OKAY. All sorted.

AUTOFILTER – This is pretty cool, and the only reason I will use Excel for a mini-database. Click the menu Data, Filter, Autofilter (Alt D, F, F) and your field names will now have little down arrows next to them. You can use these arrows to select certain articles based on what’s in there. Let’s try Type: click on the little down arrow on the right side of Type in cell A4. You’ll see a list of the different types you named your articles. Choose one. AHA! Only the articles of that type are visible now. Notice that the little down arrow has turned blue. That’s your clue as to which field has the filter on. Cool, huh?

To turn off the filter, choose “All” when you press down the blue arrow.

To add a new article, place your cursor in A5 and insert a row. By adding the new article to the top of your list, it will continue to be included in your database named Bukisa. (If you add at the bottom, you’ll have to rename the database to include it.)

• You can play around with this a lot, and it will help you analyze the type of articles that are bringing you income, and you can focus on writing more of that type. Or you can focus your attention on getting your bombs to bring in money.
• A database, even a very small one, makes data into information. What you do with information is up to you.

NOTE: When sorting in Excel you must be VERY careful. If you sort a column without selecting the entire database you will lose the connection between the fields of data, making a grand mess. Ctrl Z to undo when this happens to you (it happens to us all).

To see a sample view of my database go to Manage Articles in Excel