Spreadsheets for writers - first of two

(This is a repeat of the post I made today on All Things Writing, so if you follow that one, you can skip this. I'll forgive you.)

A writer friend mentioned today that she had never used spreadsheets and was having fun discovering that tool. I thought to myself, How does one live without spreadsheets?

If you use them all the time and love (or not love) them, you can skip the rest of this. But if you don't use them and would like to explore, I'll try to give a Spreadsheet 101 class here, starting with the barest basics and moving to creating a timesheet.

Spreadsheets have columns and rows, obviously. But they have much than that. When you open a new one, the rows are numbered down the left side starting with 1. The columns are lettered across the top starting with A. The numbers go on for a long, long time. I've never reached the maximum. The letters go to Z, then start over with AA, AB, etc. Next is BA, BB, if you need that many columns. I've gotten into the AA/AB part often, but have never needed the Bs.

I'll tell you another way I use spreadsheets for my writing next time, but, for today, the first way is as a timesheet. Since I'm a retired (OK, I couldn't find any more contract jobs, but I call it retired) programmer, I was used to working from home and keeping track of my time. Since I'm doing writing full time and taking tax deductions for my expenses, it only makes sense to keep track of my time, if only to prove to the IRS (should they get curious) that I'm serious about being a writer and AM working pretty much full time at this.

I use the first row of my timesheet for a label, so if I print it out, I'll know what the heck it is. The next row I use for column headers and I used: DATE, START, END, HOURS, TOTAL, TASK, MILES, DAY OF WK.

The date is obvious, right? What's great about an Excel spreadsheet is, you can put the first date in, say space A4 (a little window below your toolbars tells you what space you're in), then, where you want the next date, just type =A4+1.

I type the time of day I start a project and the time I end it in B4 and C4. Then, in the HOURS column, I type =c4-b4. Voila! It figures out for me how much time I spent on that task.

If I drove, say to pick up office supplies or do business related banking, I put my miles in that column so everything will be in one place.

I like to know what day of the week it is, especially when I'm going back trying to find a reference to something a month or two ago. So I put the day of the week in the next column. If the month starts on Thursday, I can put =5 there.

Need to backtrack a moment here and talk about formatting the numbers. You can have the dates displayed however you want them. If you're on the HOME tab of your toolbar, there's a place for Number about midway across the top. On the first column, it will know you've typed a date and the window will display *Date*. But you can click the little arrow on the bottom right corner and change the way the date looks. Same way with the times you've typed in. On the DAY OF WK column, choose Custom for Category and ddd or dddd to display the name of the day. This enables you to type =5 and have Thursday displayed. Magic! Then you can type =h4+1 for the next day and Friday will pop up.

You can insert rows using the Cells block on the toolbar. If you insert rows a lot, like I do, you can click that little thing that almost looks like a down arrow, to the right of the very top small toolbar, and you can click More Commands and, under Customize, you can add Insert Sheet Row, Insert Sheet Columns to this little toolbar so you can do this with one click.

You can also hover your mouse over the line between one of the letters at the top, left click and drag to make the column wider, or narrower. If you want a lot of text in one box, but not in all of them, you should click Wrap Text to enlarge just that box and display everything you've typed into it.

I use that TOTAL column to add my hours for the day. If I type =SUM(d4:d16), it will add the hours that have been calculated from the start and stop times I've typed in, over in the E column.

The fun part is, if you set up one day, you can copy it and paste below. Put your cursor on the first cell you want to copy, press shift and, keeping it pressed, arrow to the right and then down until the cells you want to duplicate are outlined. Then press Control-C, move your cursor where you want that first cell to be copied, and press Control-V. In fact, if you want to repeat a sample day over and over you can do Control-V again and again without going back and copying again. Here's where you can change 7/1/2010 to 7/2/2010 by adding one, then copy and paste the cell displaying 7/2/2010 to the next 7/1/2010 and it'll turn into 7/1/2010. You may have to tell it exactly which cell to add 1 to if it gets out of whack. Then you can do the DAY OF WKs that way, too.

When you get to the bottom, you'll want to count the hours you've worked that month, and the miles you've driven. Put your cursor where you want the total to appear, in the TOTAL column at the bottom, and press AutoSum at the top right. It might sum only one or part of the numbers, and you'll have to put your cursor in the white box where the =SUM formula appears and type in the rest. For example, if it just says =SUM(E45), you can change it to =SUM(e4:e45). It will change your lower case to caps, which it seems to prefer.

I have another nifty use for spreadsheets, plotting, but I'll save that for another day. This is long enough! Is this at all clear??