by Jenny Hansen
Should writers (or anyone else) use Microsoft Excel? In a word, YES!
Laura Drake did a post last week about how she keeps track of her plot in Excel and I started hearing rumbling from the Spreadsheet-Phobics in the WITS crowd.
What sort of tasks might one do in this spooky financial-y program we call Excel? Royalty statements, tax expense summaries, submission tracking, Agent/Editor querying… All of these are pretty important, and Excel makes them easier, believe it or not.
However, before you do any of that, you need to know how to save your document and move around a spreadsheet. This was originally a single blog post, but when I got to 1o pages, I realized this topic needed several posts, so this is Part 1 of at least a 3-part series.
Today: We’ll familiarize ourselves with some Excel shortcuts. More can be found here.
Part 2: Strap in for some useful formulas and functions.
Part 3: More time-saving tricks like Quick Formulas, Data tools like Filters and Subtotals, and Saving a Workspace.
Note: For those of you already familiar with the program, I encourage you to at least skim through this post so you can learn all the quick tips you don’t know. 🙂
Saving your Document - the most important thing
Moving Around Your Spreadsheet
Excel spreadsheets are made up of columns and rows. Where the columns and rows meet is called a Cell. These cells actually have names so that you can reference them in calculations or Formulas (covered tomorrow).
Columns and rows are listed in an orderly fashion:
While you can absolutely use the arrow keys on your keyboard, the best way to move around your spreadsheet is by doing this:
There are people reading this right now who are hissing at me for making things “too hard” with this Shift key business. Simmer down, all you hissers...I’d like everyone to do a little test:
See that A1 in the box up there? That's called your Name Box. You can click on it and type to move too. Go on...type in D10 and hit Enter...you know you want to!
So, now that we can all sail around our spreadsheets and get back to cell A1, let’s look around a little more.
What if you want to "play with" your royalty statements?
Whether your statements come from a traditional publisher or from Amazon, you want to know how to manipulate your data in Excel.
Copy and paste works slightly different in Excel.
OMG, I need separate months!
What about if you want to keep your monthly records separate, but you want to have the entire year or quarter together? I’ve seen people save a file for each month, making me shake my head in pity. If this is you, listen up...
I am giving you the Golden Gift of Tabs.
Yep, the way to keep separate data in the same file is to use what are called Sheet Tabs. They’re at the bottom of the screen on the left (see below).
Here’s some fun Sheet Facts:
OK, that should be enough to get you started if you’re new to the program. If you don’t have Excel on your computer, go to Google Spreadsheets – Google Docs will basically work the same and will allow you to practice.
I hope you'll come back next week for more Writerly Uses For Excel. We're going to dig in to the really fun stuff. In case you’re new here, I’m a software trainer by day so you can ask me questions in the comments. I don't mind. 🙂
Are you using Excel? If so, what do you use it for and what is giving you trouble? If you aren’t using Excel, why not and is there something you’d like to learn?
See you next week for Part 2!
Note: Need MORE writing magic for the day? Jenny posted 10 Tips To Help You Finish Your Novel and/or ‘Win’ NaNoWriMo at More Cowbell today!
About Jenny Hansen
Jenny fills her nights with humor: writing memoir, women’s fiction, chick lit, short stories (and chasing after the newly walking Baby Girl). By day, she provides training and social media marketing for an accounting firm. After 15 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.
Copyright © 2023 Writers In The Storm - All Rights Reserved
I love Excel. I'm a dork, right? Oh, well, Give me data and my brain instantly starts thinking about ways to manipulated it, sort it, chart it. My writing has certainly given me loads of data to track, i.e. word count, by day, by month, by scene, by project. I keep a tab for all my character details, another tab for tracking progress in self-editing, a tab of hashtags I like to use, etc. I may know Excel well, but there's always something to learn, it's a powerful program. Thanks, Jenny.
You're a dork in great company then, Sidney because Laura and I love it too. 🙂 Fabulous uses...I can't wait for Laura to see this comment...
I am completely techophobic and need even the most rudimentary of programs explained in simple terms. Thank you!
Diana, you will love this series then. You'll want to open up the program and follow along as you read. 🙂
Thanks for doing this, there a lot of people out there who are afraid of Excel and this is very helpful info for them. I ADORE Excel.
Many years ago, I self taught myself on many microsoft programs like Word, for instance. But I just couldn't seem to understand Excel. I went online and looked up tutorials but they lost me right away.
Later, when I had to learn Excel for work, my bosses sent to to an Excel class and I discovered my problem had all been in terminology. I had been looking to make a spreadsheet. I had tried pulling up "spreadsheet" in the help section of Excel - guess what - it wasn't there! Explanations in the tutorials referred to creating a "workbook" - I didn't want a workbook, I just wanted a simple spreadsheet.
Now, I know some of you are saying "duh", but when you're starting something new, terminology is key. Apparently Microsoft didn't want to call a spreadsheet a spreadsheet anymore so they didn't refer to it at all. If they had said something like "in older programs, you would create a spreadsheet but because Excel is more dynamic and can be used for things other than financial/mathematical equations, we are now referring to a single page as a worksheet and multiple pages as a workbook" then it would have all fallen into place and made sense.
In the class I attended I discovered I wasn't the only one who'd been flummoxed by terminology in this fabulous new world I was embarking on. After one hour of the class, I was delighted with Excel. Within a week after taking the class, I was learning all the ins and outs of creating dynamic charts and forumules to create amazing workbooks that were all connected and brought information from many different workbooks together to compare data.
I came to love Excel for its versatility. If I want to make a table, I much prefer to use Excel rather than Word. You can format it SO much easier. You can color different rows and columns, change the sizes, wordwrap information and really make it POP quickly and easily.
Thanks for this great info, I know it will be helpful to a LOT of people out there!!
Susan, what a great comment! You're using Excel like a champ and I'm so glad you took the time to take a class. I'm a huge believer that a little training goes an extremely long way. 🙂
Thanks Jen. You know how phobic I am about these things. 🙂
I do know. Not to worry - there's several parts to this series and we'll have you using Excel like a pro before it's all done.
Oh my goodness! I've been using the basic tools of Excel for years in the day job and, of course, brought Excel's magic into my writing life. It's usefulness is never-ending. Learning these shortcuts will make my life so much easier. Thanks for sharing. This is AWESOME, and I cCan't wait to see what's next in Part 2 & 3 & 4 & 5 & . . .
Thanks, Jaye! I agree - useful, useful, useful. 🙂
Hi, Jenny. Thanks for the command shortcuts. I've used excel for years but have always used the arrow keys to move around. The control + shortcuts will surely make it quicker. There is a romance author, Beverly Brandt, who uses excel and has a page about it on her site.she uses different columns to record the purpose of the scene, what happens, what stage of the plot (set up, inciting incident etc.) There's a link here http://www.beverlybrandt.com/spreadsheet.htm Best wishes.
That's fantastic, Richard! I'm delighted to have given you some useful tips. 🙂
I'll go check Beverly out - it sounds a bit like what Laura gets up to with her worksheet. Genius stuff. 🙂 I use Scrivener.
I use EXCEL to keep track of my daily word count. I'm on a writing loop of 100 words per day for 100 days. I finished one year and now I'm on year 2. I keep track of my bills on a second sheet as well. I have a column for the cumulative days, the year 2 day i.e. day 242 year 2, the date, the day of the week, the word count, and like for NaNo and other challenges the daily required word count.
I created a column where I simply have the word count again minus the daily requirement say 1667 for NaNo and I simply subtract it. I cheated I made the required word count a negative. If I'm under, the number is negative over it is positive. I also keep track of miles and costs of RWA meetings and other business costs.
C.K. How wonderful that you're getting to use the tool that way. I'll confess that in NaNoWriMo, I get totally excited when I verify word count and watch it pile up in the graph.
I use Excel to keep track of all my editing work, with each client in a separate sheet. I have it set up to calculate my payment (I get paid per word), as well as a couple of columns that will break down my rates on an hourly basis. I also use it to keep track of submissions, although I tend to forget to enter them... ooops. I've progressed from having to ask my best friend, who is an Excel wizard, to set up spreadsheets for me to being able to do it on my own. Go me? LOL Anyway, I do find it to be a useful tool. My latest project is using it to keep track of promotional activities around a book launch. We'll see how that one goes...
Katriena, as someone who is just starting freelance work, that sounds like an excellent idea. Hmmmm...
Thanks for this informative post, Jenny. I'm keeping it and will be back for the next installment.
That's fantastic, Lyn! We love to see you around here. 🙂
Okay, Jenny ... now that I have picked myself off the floor ... I'll admit I am "excel" challenged. I add this to the growing number of challenges I face daily as a hapless writer who started out only wanting to tell you a story. Got a minute?
Okay ... I'll come back for more because I am a neurotic basket case who loves to torture herself with challenges. BUT ... I am having a vision problem. I can't quite get the vision of plotting on excel or of one day needing to use the spreadsheet for plotting my career. What the heck is an algorithm anyway?
Florence, I don't really know what an algorithm is either! Fae was a math teacher for 30 years...maybe she knows. I love Excel because it does the math for me. But, in a pinch, I call Fae. 🙂
An algorithm is just a "math recipe" to do something-a list of instructions (okay, in mathspeak, usually) to accomplish a task. Computer code to do something is sometimes referred to as an algorithm as well. An equation can be classed as an algorithm when the equation refers to a "real life" problem. Nothing to worry about.
Shoot, I don't even know what she just SAID! In spite of being a bean-counter for 30 years, I suck at math.
Sounds like a macro or a formula to me, but I'm with Laura that I glazed out pretty soon after the word "algorithm" showed up the second time. *dons dunce cap*
Excel ends up the tool that nearly everyone uses, and most don't use it to work on accounting. It just lends itself to so many uses, doesn't it?
It truly does, John! Plus, I work with accountants so I've been teaching them accountant-y things like Pivot Tables and VLookup functions. Good times...
Oh, BOY! I have a friend who works for Microsoft, and she's been trying to decipher pivot tables herself. She's one of the sales-y folks, so even though I thought she knew every thing there is to know about them, guess I was wrong...
I adore Pivot Tables. There's just so much you can do with them. I think it's way more than most writers want to know though...
Great advice! I use excel all the time and I still managed to pick up a couple of short cuts I didn't know here! Looking forward to the next parts.
That's completely groovy, Ken. I'm so glad you learned new tricks. 🙂
I'll try to top the number of useful shortcuts in next week's post...
[...] http://writersinthestorm.wordpress.com/ [...]
[...] Writerly Uses for Microsoft Excel by Jenny Hansen. [...]
[...] Writerly Uses For Microsoft Excel – Part 1 [...]
[...] In Part 1 of this series, we talked about using Microsoft Excel efficiently. You learned some fun finesse tools for moving around the program and laying out data. [...]
I absolutely LOVE excel!!! I use it for my budget & bills, or for anything requiring charts, graphs, & math. But I never ONCE thought to bring it into my writing corner. How ingenious! I use "Save As" quite often myself, but wasn't aware of the F12 shortcut. Great, great tip! As "savvy" as I am in excel, I still learned a couple other new shortcuts here as well, but F12 is my absolute FAV because I know I'll use it most often. I am loving this series already! Thanks for putting this together!!! 🙂
How cute are you, Andi?! I'm so happy to give you shortcuts. May you save often and well, my friend. Part 2 is already up (link above) and Part 3 is coming soon. 🙂
[...] 1 – Excel Shortcuts and Helpful Tricks Part 2 – Doing Formulas without Freaking [...]
[…] Writerly Uses for Microsoft Excel – Part 1 by Jenny Hansen Other Round-Ups […]