March 11th, 2013

Writerly Uses for Excel – Part 3

by Jenny Hansen

Earlier this year, we started talking about Microsoft Excel and all the ways writers could use it without hyperventilating.

In case you missed the other two posts, I’ve linked them below:

Part 1 – Excel Shortcuts and Helpful Tricks
Part 2 – Doing Formulas without Freaking Out

Laura Drake also did a post last November about Keeping Your Novel Organized In Excel. (It was excellent!)

We did all that baseline work to ensure that everyone was on the same page, with at least a moderate level of skill and comfort. I’m hoping you’ve been at least opening your Excel and giving it a periodic keystroke.

Today’s Part 3 is going to cover some more time-saving tricks like Quick Formulas, Data tools like Filters and Subtotals, and Saving a Workspace.

Get ready to rock some Excel!

Quick Formulas

Since I’m starting to explore some freelance writing options, my sample file details a list of all my (fake) freelance work for 2011. This is a file that I definitely need to have so I threw a quick one together (see below).

When you have a list like this, sometimes you want to have numerical information on the fly. Perhaps you need to know how much you made from your February assignments, but you don’t have time to create a formula or function.

By highlighting the amounts to the right of the February dates in the example above, you can get this information from the Status Bar (located at the bottom of your screen) .

In the later versions of Excel, a right click brings up the Customize Status Bar shortcut menu, allowing you to turn on extra Functions or see your data at a glance. Select some cells with numbers in your own practice spreadsheet…now point to the Sum down in your status bar and right click. Fun, huh?

Earlier versions of Excel were not quite as detailed as what you see below:

Mac Note: The way to get the Windows “right-click” features in Excel on the Mac is to hold down the Option button on your keyboard while you click your single mouse button.

Data Tools

Take another look at the spreadsheet list above. There are a few important things to note about this file:

  1. This list has column headers like Customer #, Service performed, Due Date, etc.
  2. This list is filtered, which means I can see only a certain State or Service performed by clicking the drop down arrow and applying the Filter for a column.

Here is the Data Ribbon, which has all the Filter, Sort and Subtotal types of buttons so you can try this magnificence out.

Subtotaling is another cool data feature in Excel.

Note: You must Sort a list by the column that you wish to Subtotal. For example, if I want to see a total dollar amount for Blogging or Editing, I would sort by Column B before I applied the Subtotal Feature.  See below for a list that has had Subtotals applied. (When you want to remove the Subtotals, click the icon on the Data Ribbon again and hit the “Remove All” button.)

For you, this would be magic with your e-book sales. If you sorted by book title, you could see how much each book made. 🙂

Excel Workspaces

Most of us work with more than one Excel file at the same time. For example we might need to see two sheets in the same workbook, or we might need to open a file that contains our royalty statements from Amazon, another from our traditional publisher, and a third file where we keep a conglomeration of ALL our book sales.

If you’re like me and you only get little pockets of time to work in, one file might take you four or five computer sessions to finish. I love Excel’s Workspace feature because it lets me open all my workbooks exactly where I left off the last time I closed Excel.

Yes, really. I can have three files open on my screen and not only save the files, but I can save the View of these files. This viewing arrangement is called a Workspace.

The Workspace button is located on the right side of the View Ribbon and looks like this:

To save a Workspace file:

  1. Arrange your files as you like (use the View buttons – also located on the View Ribbon, shown below).
  2. Before quitting your Excel session, select Files>Save Workspace (this is located in the File àSave As… dialog box in earlier versions of the program and on the View ribbon in later versions of Excel).
  3. Specify a file name and choose OK (it’ll have an .xlw extension)
  4. To pick up where you left off in the last session, reopen the workspace file: Use File>Open and choose the workspace file.

Note: A workspace file contains only configuration information, not the actual workbooks and worksheets. Therefore, you can’t simply copy the workspace file — you’ll need the workbook files, too.

So, there you have it…some quick and easy tools to keep lists in Excel and to work with multiple files.

Are you on Excel Information Overload or should I plan some more Excel posts for the future? Y’all will have to let me know!

What are your “Writerly Uses” for Excel? Do have any questions for me?

About Jenny Hansen

Jenny fills her nights with humor: writing memoir, women’s fiction, chick lit, short stories (and chasing after her toddler Baby Girl). By day, she provides training and social media marketing for an accounting firm. After 20 years as a corporate software trainer, she’s digging this sit down and write thing.

When she’s not at her blog, More Cowbell, Jenny can be found on Twitter at JennyHansenCA and here at Writers In The Storm. Jenny also writes the Risky Baby Business posts at More Cowbell, a series that focuses on babies, new parents and high-risk pregnancy.

No comments yet to Writerly Uses for Excel – Part 3

  • LOVE Excel! Big shock, I know! This is awesome stuff, Jenny – thanks!

  • Reblogged this on Linda Joyce Contemplates and commented:
    I’m a “C” level user of excel and I believe these posts will help me greatly. I use an excel spreadsheet to track my plotting, character details, and chapter notes. This may take me to a whole new level of organization.

    If you put this to use- leave me a note and share. I’ll take all the suggestions I can.

    Smiles,

    Linda Joyce

  • I use excel for everything. One year, the Pro-group at RWA issued a challenge to have 50,000 words from April until the Pro- retreat at Nationals (Orlando). I’d already been tracking myself at 300 words per day, so I just added another couple of calculations to incorporate the new challenge. Incidentally, that was the year I completed 3 books—- all due to the challenges and my organizational skills utilizing Excel!
    Excellent post.

  • I find it amazing that all these programs can do so much more than I ever thought possible. There’s not enough time in the day or night to learn the potential of Excel, Word, PhotoShop, etc.
    😐

  • This continues to be an excellent series on Excel! Well done 🙂

  • Where was I all day yesterday, Jenny? Off to complete the mundane chores that can strangle our resolve. Not to worry. I am back and happily absorbing … if not the exact science of Excel … then its application to what I do in another sense. It’s the age old battle of my right and left brain … my Virgo and Libra … they argue with me over obsessive organizing or being a air head. Which do I choose?

    Thanks for another example how an accounting software can be a writer’s best friend. Maybe not this writer … who still does battle with columns and lists instead of chunks of notes. I’ll work it out I am sure and saving this amazing series will give both sides of my brain more “food for thought.” Thanks 🙂

    • There’s nothing worse than mundane chores, huh? Well, we’re delighted you visited with us today and I can’t wait for you to see tomorrow’s post. (I kind of squeed over it 🙂 )

  • […] Hansen: Writerly Uses for Excel (Part 3). Jenny is a master of technology – here you’ll find best practices for utilizing […]