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:
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!
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.
Take another look at the spreadsheet list above. There are a few important things to note about this file:
- This list has column headers like Customer #, Service performed, Due Date, etc.
- 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. 🙂
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:
- Arrange your files as you like (use the View buttons – also located on the View Ribbon, shown below).
- 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).
- Specify a file name and choose OK (it'll have an .xlw extension)
- 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.