Should Writers Use Excel – Part 3

For any of you who are just tuning in to Techie Tuesday here at More Cowbell, we’re doing a series on some of the features that are exciting for writers (and everybody else) in Microsoft Excel.

There’s a certain amount of baseline work that we did to ensure that everyone was on the same page, with at least a moderate level of skill and comfort.

If you need to catch up, here is a breakdown of the Excel posts so far:

In Part 1 of this series, we talked about entering data in Excel and saving your file, moving data around and getting around a spreadsheet.

 Part 2 discussed formulas and functions.

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.)

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 view 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 tidbits for the next Techie Tuesday. Y’all will have to let me know!

See you later this week for some More Cowbell fun!
Jenny