Using Excel's Pivot Tables

Sales Info Export feature (unrestricted usage in PRO version) allows you to save your sales data into CSV files, which you can then open with Microsoft Excel, or any other analytical/reporting tool. This article, while not even trying to replace excellent Microsoft tutorial, will give you a brief idea what you can do with your own data if you dare to take a few extra steps into the unknown..
Export your sales data, hit Save All button (with the way outdated 3.5" floppy disk on it:-), select the following options:

Hit OK. If you have MS Excel installed, most likely it will be Excel that will open your file.

Make sure your selection is somewhere within your data, and only one cell is selected.

In the main menu, select "Data" and then "Pivot Table and Pivot Chart Report" (the actual wording may vary depending on your OS, your Office version and the selected language:-):

You will see the following dialog (step 1 of 3):

Click Next.

You will see another dialog (Step 2 of 3), your Range may vary:

Click Next.

Final dialog (Step 3 of 3) looks like this:

Click Finish.

Congratulations! Most of the hard part is over, but we're not totally there yet.

Your courage so far is rewarded with a geeky looking empty sheet:

What you need to do is to drag and drop three items (you can use more than that or less than that, but let's just try this one first).

Drag Profit to Data area, Date to Rows area, and Status to Columns area, and hit the Chart button (green arrow):

Lo and behold! You'll see something like this:

Not bad for a two minutes work! All your previous orders paid, just a few recent ones are not.

But wait - this is just a beginning!

Go back to Sheet1, drag Date back to the fields roster, and drag Product on its place (Rows area). Go again to Chart1 - you'll see that it automatically changed to something like this:

OK, this is close to what you'd expect - 4x6 and 5x7 are the obvious leaders, with 8x10 being in a third place. Everything else is hardly worth the efforts, but hey - it's not YOUR efforts, it's good Smugmug elves who do all this background work with the shopping cart. etc. You just get the cash:-)

Interesting enough - you actually don't have to go back and forth between Chart1 and Sheet1. As far as your fields roster is present on the chart, too, you can do all the dragging and dropping right here! Let's try it!

Drag Status field (on the right hand part of the screen) back to the fields roster, move Product field from the bottom part on Status' former place on the right, then select Order# from the roster and drop it to the bottom part. You'll get something like this:

Now, the question - what happens if you drop more than just three fields?

Well,  I'm not gonna tell you! Try it! Remember - it's YOUR data, and nobody can tell you what you can and cannot do with it. And since the chart is "live", all you experiments will produce immediate results. In a matter of minutes (and that's just because you're still learning, otherwise it's a few second deal) you'll be able to find out what your bestseller is, how much profit did you make on that dreary rainy Tuesday, and much more. Before you know it, you will realize that you can add extra formula columns to your grid - and this is where the REAL fun starts...


Happy Pivoting!


Copyright © 2005 Star*Explorer, contact: