HomeHome SearchSearch MenuMenu Our productsOur products

How to create an excel pivot table to manage all your sales data in less than five minutes

by , 12 April 2013
Selwyn is the sales manager at Elco Manufacturers. He realises his sales staff create new sales items on the accounting system when they can't quickly find the item to invoice. This has led to duplications of inventory items in the system. As you can imagine this makes stock count a nightmare.


Selwyn is the sales manager at Elco Manufacturers. He realises his sales staff create new sales items on the accounting system when they can't quickly find the item to invoice. This has led to duplications of inventory items in the system. As you can imagine this makes stock count a nightmare.
 
He can't even integrate the stock module with the general ledger because of the mess. But, there was one thing he could do to fix the problem. He could use pivot tables. These helped him prepare a report to count the number of times each of the inventory items appear in the list. He could also filter the report to exclude items where the inventory item count is one. But, in this case he only wants to focus on the duplicated items.
 
If you've been faced with the same situation I'm sure you'll agree that a pivot table would be your saving grace. Here's how you can use a pivot table, in less than five minutes, to manage your sales data summarisation and analysis.
 
+++Do you know why Gail doesn't spend R10 000 a month on bookkeeping fees?+++

Gail is a GP who's just opened her own private practice. Because she's trying to save money on admin costs, she is doing her books herself. She thinks: 'How hard can it be?'

The answer: 'It's very hard unless you've got the right tools at your fingertips!' The Practical Accountancy Loose Leaf is just one of those tools.

With everything from checklists about how to prepare for an audit, to detailed explanations about financial statements, the Practical Accountancy Loose Leaf is your one-stop accountancy resource!

 +++

How to create an excel pivot table to manage all your sales data in less than five minutes

Step 1: Choose your raw data. Select the data you want to put into your pivot table. Say you want to analyse sales for a certain period. You'll use the raw sales data as input data.

Step 2: Click anywhere on the raw sales data. Click 'insert menu' on the top of your screen.

Step 3: Click 'file' and then 'pivot table'.
 
pivot table image 1
 

+++Limited offer+++

Get professional tax advice from our top tax consultants for just R0.89 an hour


+++

Step 4: In the 'create pivot table' dialog pop-up box, Excel will automatically select the whole data source. Put your new pivot table on a new worksheet and use this sheet as your report page. Then click 'ok'.
 

pivot table image 2



Step 5: In the 'pivot table field list' select the fields to add to your report and drag and drop into the areas where you want them displayed
Well done, you've created a pivot table!


pivot table image 3


For examples and more detail on how to create pivot tables, turn to page 003 of Excel: Pivot tables chapter E 02 in the Practical Accountancy Loose leaf. Don't own a copy? Click here to order now.



Until next time,
Philip Rosenberg

P.S :  'How is the profit or loss determined on an asset that is sold after say 4 years e.g.: motor vehicle purchased 2008 for 350 000 and sold in 2012 for 285 000?' For answers to this accounting question, and other urgent accounting and tax questions, check out our Accounting and Tax club website. It's free!

Vote article

How to create an excel pivot table to manage all your sales data in less than five minutes
Rating:
Note: 5 of 1 vote


Related articles




Related articles



Related Products