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