How To Add Up Your E-Book Sales In A Snap

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.

Example:

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:

  1. Sort the list on the field for which you want subtotals inserted.
  2. Click the Subtotal button in the Outline group on the Data tab.

    The Subtotal dialog box appears.

    Use the Subtotal dialog box to specify the options for the subtotals. In this case, we want to add the Royalties up by Country, since that’s what we sorted for.

  3. Select the field for which the subtotals are to be calculated in the At Each Change In drop-down list.
  4. Specify the type of totals you want to insert in the “Use Function” drop-down list (ex: Sum, Average, Count).
  5. Select the check boxes for the field(s) you want to total in the Add Subtotal To list box.
  6. 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!

Jenny

p.s. I’m FINALLY on Pinterest ~ please visit me at https://pinterest.com/jennyhansenllc/.

About Jenny Hansen

Avid seeker of "more"...More words, more creativity, More Cowbell! An extrovert who's terribly fond of silliness. Founding blogger at Writers In The Storm (http://writersinthestormblog.com). Write on!
This entry was posted in ePublishing, Excel for Writers, Techie Tuesday and tagged , , , , . Bookmark the permalink.

40 Responses to How To Add Up Your E-Book Sales In A Snap

  1. zkullis says:

    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

    Like

    • Jenny Hansen says:

      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!

      Like

      • zkullis says:

        Good morning

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

        Like

  2. Laura Drake says:

    Well shown, Jenny!

    Like

  3. 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!🙂

    Like

  4. K.B. Owen says:

    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!🙂

    Like

  5. Julie Glover says:

    Shut the front door! I had no idea this function existed in Excel. Thanks, Jenny.

    Like

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

    Like

  7. amyshojai says:

    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…

    Like

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

    Like

  9. Fabio Bueno says:

    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🙂

    Like

    • Jenny Hansen says:

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

      Like

  10. Jenny, This-is-awesome!!! Off to try it!!

    Like

  11. angelapeart says:

    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.

    Like

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

    Like

  13. susielindau says:

    I can’t wait until I have something to count! Thanks for the info!

    Like

  14. Thank you so much! This is awesome information.

    Like

  15. Kim Mullican says:

    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!

    Like

  16. Pingback: The End is Near (and we deserve it) . . . Skydive Naked for Rhinos « Bayard & Holmes

  17. Pingback: Writing Blog Treasures 10~20 | Gene Lempp ~ Writer

  18. Pingback: Monday Mentions: Neuters, Bats & Crazy Writers « Amy Shojai's Blog

  19. Pingback: No Wasted Ink Writer’s Links « No Wasted Ink

  20. Pingback: Fun Facebook Tricks and a Halloween Treat | Jenny Hansen's Blog

  21. Pingback: Hubby’s Corner: What’s crack-a-lackling? – Natalie Hartford

  22. Pingback: Link Feast For Writers, vol. 26 | Reetta Raitanen's Blog

Comments are closed.