Welcome to Techie Tuesday here at More Cowbell! This is the day each week when I unleash my inner geek and we talk about some groovy piece of technology or a technical point of writing.
Today’s post is for my indie author pals.
I was talking to one of my friends that gets huge Excel downloads from Barnes & Noble, Amazon, Smashwords, etc. and she has no idea how to make it all add up easily and quickly.
I won’t even tell you how she was doing it, but I showed her to use the Subtotal feature in Excel — trust me, it’s easier.
Note: I’m using an Amazon list as an example in this post, but this works with any Excel list. Also, a CSV file can be opened in Excel.
First, what are Subtotals in Excel?
You can use Excel 2010’s Subtotals feature to add up data easily in a sorted list.**
**A list in Excel is a bunch of cells together with a heading at the top of each column of data.
It is important to first sort the list on the field for which you want the subtotals. If you don’t do this, the feature will not work. I’ve put a YouTube video at the bottom of this post, in case you are a visual learner and need to see this feature in action.
The file you receive from Amazon will look something like this when you start:
Note: The formula to calculate the total royalties is:
=(List Price*(Units Sold-Units Refunded))*Transaction Type
To make Excel see your data as a list, you just delete the three rows between the headers (Title, ASIN, etc) and the data, which starts with Book A above.
If you need to note what you sold by country, add a country column and put in US or UK or Asia. Just get rid of all the blank rows and total rows (see example below).
Note: I believe all the e-book distributors pay us in the currency of our native country, so you shouldn’t have to do the exchange rate conversions yourself.
You see how the header row and the books are right next to each other now? This is when you begin sorting, and then subtotaling.
The list above could only be subtotaled by Country. If you tried to do it by book Title, it would be a disaster because they’re not sorted together.
Now that you get how the data must look, here’s the steps to Subtotal:
- Sort the list on the field for which you want subtotals inserted.
- Click the Subtotal button in the Outline group on the Data tab.
The Subtotal dialog box appears.
- Select the field for which the subtotals are to be calculated in the At Each Change In drop-down list.
- Specify the type of totals you want to insert in the “Use Function” drop-down list (ex: Sum, Average, Count).
- Select the check boxes for the field(s) you want to total in the Add Subtotal To list box.
- Click OK. Excel adds the subtotals to the worksheet.
When you use the Subtotals command, Excel outlines the data at the same time that it adds the rows with each country’s royalty totals and the grand total. This means that you can collapse the data list down to just its departmental subtotal rows or even just the grand total row simply by collapsing the outline down to the second or first level.
Here’s what that means – this is collapsed at level 2:
In a large list (i.e. if you have a ton of books selling in multiple countries), you may want Excel to insert page breaks every time data changes in the field on which the list is being subtotaled (in our example, this is country).
To do this, you simply select the Page Break between Groups check box in the Subtotal dialog box before you click OK to subtotal the list.
Here’s a fine little YouTube clip using Subtotal with movies, which is close enough to books that I thought it would work well for y’all. This should help my visual learners get the hang of things.
Have you ever heard of the Subtotal feature before? Do you have more questions about how to use it? Are there other Excel issues you’d like me to cover in a future Techie Tuesday post? Enquiring minds LOVE to know these things here at More Cowbell!
p.s. I’m FINALLY on Pinterest ~ please visit me at https://pinterest.com/jennyhansenllc/.
Good techie info Jenny!
My sales info is still small enough that I can use the first two rows of an abacus to figure out the sales. 😉 IYKWIM
That’s OK, you’ll use Subtotals to add up law enforcement “stuff.” Then someday soon, you’ll add up sales. 🙂
Are you on Pinterest? I just added a link to a new account. Come find me!
Now that would need some serious numbers! I will have some more sales to add up once I get a few more books under my belt.
Nope, I’m not onPinterest yet. I should set up an account.
Yesterday’s blog was seriously funny. I have a Bearded Clam shirt on the way. 😀
Thank you! And I already responded to you over there. 🙂 We need pictures, dude.
Well shown, Jenny!
Hahaha! I can’t even make a header!
Tech sets up all my Excel spreadsheets at the beginning of each semester, and helps me tweak the formula as I go.
One day, when I have a book. I will show him this post! 🙂
That’s hilarious, Renee! But don’t you want to kind of know how to do this stuff yourself??
Jenny, I’m bookmarking this for when I’m more awake, LOL. Columns and numbers are best WITH caffeine. But thanks so much for the tech guru goodness! 🙂
You’re so funny…I’ll bet you’d have rocked it, even in the AM. 🙂
Shut the front door! I had no idea this function existed in Excel. Thanks, Jenny.
Julie, you are in for some serious Excel Happy Time. (IYKWIM) 🙂
The subtotal feature and I are good friends. Because I’m self-employed, I have multiple clients and income sources, and had to come up with some sort of a workable system to keep track of it all. I still got something valuable out of this post though. I wouldn’t have thought to have a “Country” column in my list.
Very cool! Yep, if it matters to determine WHERE you got the business from, Subtotals to the country will make you very happy. 🙂
What KB Owen said…info I need but not yet fully caffeinated to appreciate it. Usually I just take off my socks so I can count to twenty…
LOL, that’ll change soon as you sell, sell, SELL!! You could also use this for Kurves-related items. 🙂
I love excel, Jenny. As a former accountant, I use the program for almost everything, including outlining my books. Great post. I’ll be sure to pass this on.
Thanks, Sheila…and I’m glad. 🙂
The Excel nut in me appreciates this post!
Like Sheila, I use Excel for outlining, creating timelines and characters profiles…
Very RT-able post, Jenny 🙂
LOL…I’m so impressed that you guys use it for your plotting and timelines! Laura Drake is looking for something to help with timelines, so be sure to tag her if you have something you can pass on. 🙂
Jenny, This-is-awesome!!! Off to try it!!
Thanks, Diane! Let me know if you need help. 🙂
I love this, Jenny! I keep so many things organized in Excel spreadsheets that if, for some weird reason, the program disappear from the face of the earth, I would be LOST!
Great advice – neatly organized, step-by-step.
Sounds like you and Fabio are a great mix. 🙂 I’m so impressed with all your organized types. 🙂
This is totally awesome, now I need to figure out how to make it work in Numbers. I miss Excel…
Thanks for the groovy breakdown.
I’ll be you could Google it to get this same feature for Numbers, Tameri…
I can’t wait until I have something to count! Thanks for the info!
Sweet Susie! You will most assuredly have something to count very, very soon. 🙂
Thank you so much! This is awesome information.
Thanks tons, Pauline!
Combining reports for Kindle, Nook etc are a pain. This will make it easier too. I’ve been using Excel for years and never touched this feature. Thank you!
You’re very welcome, Kim. What I recommend for this is that you add a column for Vendor so you can Subtotal that way too. 🙂
Pingback: The End is Near (and we deserve it) . . . Skydive Naked for Rhinos « Bayard & Holmes
Pingback: Writing Blog Treasures 10~20 | Gene Lempp ~ Writer
Pingback: Monday Mentions: Neuters, Bats & Crazy Writers « Amy Shojai's Blog
Pingback: No Wasted Ink Writer’s Links « No Wasted Ink
Pingback: Fun Facebook Tricks and a Halloween Treat | Jenny Hansen's Blog
Pingback: Hubby’s Corner: What’s crack-a-lackling? – Natalie Hartford
Pingback: Link Feast For Writers, vol. 26 | Reetta Raitanen's Blog