I am thinking of making a tutorial about Microsoft
Excel PIVOT tables and
MACRO together... but I am hesitant to post it here coz I only know the basics.
But since this will help other people (somehow, i supposed =D ) in creating their summary reports much easier, I decided to publish it this time.... ;)
It makes sense after all...
So.....
To begin, let me define each of its definition with the help of Mr. Google.
A PIVOT table is a handy tool for summarizing data, featured in many spreadsheet programs, such as Microsoft's popular Excel package. Pivot tables can sort and count data in a spreadsheet, then display the summarized data in a form specified by the user. They can provide summarized data as raw counts, totals, or averages.
Read more: http://www.ehow.com/facts_5006904_definition-pivot-table.html#ixzz1U2hsfM5g
Creating a MACRO using Excel's record macro option is a quick and easy way for users to eliminate those repetitive tasks, such as adding or removing columns and rows, that regularly occur while building a spreadsheet.
Read more: http://spreadsheets.about.com/od/m/g/macro_def.htm
Let's Start Now:
1. Before anything else, we should display the Developer tab to write macros.
Click the Microsoft Office Button Button image, and then click Excel Options.
Click Popular, and then select the Show Developer tab in the Ribbon check box.
2. Make your summary report in simplest form. In my example, I'd want to show how much is the Total Sales per Region.
3. Record a MACRO by ticking the "Record Macro" button under Developer Tab.
4. A pop-up window will open. Fill up the "Macro Name", "Shortcut keys", and "Description" field. Click the "Okay" button.
Macro is now recording all the steps performed and translates them into macro code. It records every keystroke and mouse clicks.
Let's start using the "Pivot table".
5. Click the "Pivot Table" under Insert Tab. In "Table/Range" field, shade the created summary report from the first row to the last row ( 9R x 4C ), or you can just shade "column A, B, C, D".
6. Tick the "New Sheet" radio button.
7. From sheet 1, a new Pivot table field has moved into another sheet. It contains table field list and PivotTable layout.
8. We need to select items from the Pivot Table field list which should appear in the Pivot Table. This is a very important step as this will decide on how the final data be viewed. Order of selection is very important, we need to add fields to the respective areas.
9. Since I'd want to show how much is the total sales per region, I dragged the "Region" in Report Filter list, "SalesRep" in Row labels field list and both "# of orders","Total Sales" in Values field list.
10. Everything is done. We can now tick the "Stop Recording" button under Developer Tab.
11. For the last part of this tutorial, just click the shortcut key assigned for the macro.
Below is the final output.
We can now use the shortcut key (CTRL-SHIFT-S) everytime we need to update some fields in our report (eg. adding/deleting regions or order of sales).
In just a click, a ready made report will be created. ;)
PS. sample spreadsheet shared on this blog was just a revision from the web.
♥
