Drag and Drop Template - Excel

Document Sample
Drag and Drop Template - Excel Powered By Docstoc
					                                Excel Pivot Table
    Sheet Index:                           description
    readme - outline & notes               outline & notes including cautions & workarounds

    preparatory - customize toolbar        make buttons easier to use
    wizard steps 1-3                       pivot table wizard
    drag & drop on layout                  pivot table wizard layout - 2 dimensional
    pivot table w drag & drop              interactive drag & drop
    1 click sort descending                1 click descending sort of pm2.5 & rog emissions
    % of column                            new field showing % of column
    show pages                             show pages - 3 dimensional
    drilldown                              drilldown to raw data detail

    2nd pivot table                        pivot table of ncfoursum2003 & ncfoursum2004 data
    field list - add nox pm                another method to change layout using field list
    calculated item w 1 row field          advanced feature
    2nd calculated item                    advanced feature
    format & desc pm & drilldown           drilldown of row total to get raw data detail for 2 years

    ASAP utilities                         very useful add-in

    help resources                         list of online & offline resources for pivot table help




e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
sheet = Sheet Index                                                                                    1 of 18
Excel Pivot Table - a Powerful Analytical Tool
                                                                       "Use a PivotTable report when you want to make large, complex sets of data more comprehensible and easier to understand
When & why would I use a pivot table?                                  at a glance. PivotTable reports are especially useful when you have a large amount of data to sum and you want to compare
                                                                       several facts about each figure. Because PivotTable reports enable you to drag fields into different positions in the layout, you
                                                                       can change the view of the data to see more detail or calculate different summaries."

Pivot Table Advantages                                                 Help
 powerful                                                                Microsoft help
 summary statistics at a glance                                          google pivot table
 easy manipulation with drag & drop                                      encourage practice & experimentation "One cannot learn anything so well as by experiencing it oneself." (Albert Einstein)
 zero formula writing on basic pivot table                               undo available



Preparatory Exercise - Customize Toolbar                               show pivot table toolbar & add icons (sort ascending, sort descending, auto filter)



Start with Raw Data in a List
 header row - each field has a name
 contiguous - no blank rows or columns within list
 no subtotals or grand totals

                                                                       1st Example
Basic Pivot Table                                                       select * from ncems2004 where dis='NSI' and facid>0

 pivot table icon                                                      active cell should be within data (ie in list)
 pivot table wizard
   step 1 - select data & report
   step 2 - select range                                               automatic if data is surrounded by blank rows & columns
   step 3 - select location. Layout button & options button
     layout - drag & drop fields into page, column, row, & data area   row & column fields limited by available memory. Workaround - concatenate
        double click fields in data area                               change name. summarize by sum, count… options: show data as normal, % of column…
        double click fields in row & column areas                      subtotal options
     options - column & row grand totals
 dynamic rearrangement of fields in pivot table
                                                                       works only with 1 row item (eg facid only, not dis ab co facid)
 1 click descending or ascending sort
                                                                        workaround - click descending sort icon each time row item changes
 % of column
 show pages
 drilldown




        e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
        sheet = readme - outline & notes                                                                                                                                                         2 of 18
Excel Pivot Table - a Powerful Analytical Tool

                                                       2nd Example
                                                         select * from ncfoursum2004 where dis='FR'
Advanced Pivot Table Across 2 Years                      select * from ncfoursum2003 where dis='FR'
 with Calculated Items                                   combine results & add column preceding data with manual input of database year
                                                       better than QA tool because spreadsheet format (vs pdf) & includes all sources (vs inner join),
                                                        but still be aware of inconsistent fname or facid between years


                                                       works only with 1 row item (eg fname only, not dis ab co facid fname)
                                                        workaround - concatenate
 calculated item                                       cannot have tog, %tog, & calculated item
                                                        workaround - copy & paste values, then create own formulas
                                                        do not drag page field down to row area if using calculated item (will expand rows)



Very Useful Add In
                                                       see my favorites. Www.asap-utilities.com for free download.
ASAP utilities
                                                       No undo. Great for concatenation. New version every 8 months



Other Pivot Table Features, Time Permitting (no screenshots)
 rearrange column order
 suppress subtotals
 group - hide detail - show detail - ungroup
 external data source                                  from .csv or .xls or .mdb
 reverse pivot                                         useful when need to create vertical list
 ?? Format, top 10, pivot chart


Using Filters on Pivot Table Data (no screenshots)     manual workaround
 copy, paste values, paste format                      to the right of the pivot table (for easier drill down) or on another sheet
 fill with previous nonblank                           ASAP utilities is best
 auto filter icon
 create extra columns for more calculations or flags




         e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
         sheet = readme - outline & notes                                                                                                                3 of 18
Show pivot table toolbar                                                       Expand pivot table toolbar
 click view                                                                     click arrow at end of toolbar
 toolbars                                                                       add or remove buttons
 click pivot table                                                              pivot table
                                                                                click to show all except reset




Customize toolbar
 click tools
 click customize
 in command tab, click data
 drag sort ascending , sort descending, & auto filter to pivot table toolbar
 click close
                                                                               Resulting toolbar




            e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
            sheet = preparatory - customize toolbar                                                              4 of 18
in sheetname=1 - nsi ncems2004
with active cell in list, click pivot table icon
click next




click next




click layout




   e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
   sheet = wizard steps 1-3                        5 of 18
drag & drop field names on pivot table template - page, column, row, data




click ok when done

click finish




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = drag & drop on layout                                             6 of 18
Dynamic end product
  format data fields (2 decimals in ems ) & shorten column width




Experiment with drag & drop




       e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
       sheet = pivot table w drag & drop                           7 of 18
with active cell in data area of pollutant 88101 (pm2.5), click sort descending icon




with active cell in data area of pollutant 16113 (rog), click sort descending icon




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = 1 click sort descending                                              8 of 18
click pivot table icon
click layout
drag ems field to data area
double click sum of ems2
change name to %
click options                                (Aside: note summarize by options including count)
click show data as % of column
click ok
click ok
click finish




drag data under pol
resulting pivot table




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = % of column                                                                             9 of 18
with active cell in data, click show pages icon
click co
click ok




notice 3 new sheets created labeled with county id




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = show pages                                 10 of 18
in sheetname=29
with active cell in data area of pollutant 11101 (pm), click sort descending icon
double click cell B10 (value=17.03) to drilldown to raw data detail

notice 1 new sheet created containing detail from ncems2004 can see dev, proid, & ems
check ems total




      e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
      sheet = drilldown                                                                 11 of 18
in sheetname= 2 - fr ncfoursum2004
with active cell in list, click pivot table icon
click next
click next
click layout
drag & drop field names on pivot table template - page, column, row, data




click ok
click finish

resulting pivot table




format columns B, C, & D with 2 decimals




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = 2nd pivot table                                                   12 of 18
with active cell in data area, click show field list icon
scroll down pop-up
click noxt
click data area under drop-down menu
click add to
click pmt
click add to
close field list screen




Click & hold data. Drag data under dbyr. Release




1 click ascending or descending sorts available


  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = field list - add nox pm                           13 of 18
with active cell C8, click down arrrow next to pivot table on left side of toolbar
formulas
click calculated item




type diff in name box
clear zero in formula box
click ncfoursum2004 in items box
click insert item
type minus sign in formula box
click ncfoursum2003 in items box
click insert item
click ok




resulting table




   e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
   sheet = calculated item w 1 row field                                             14 of 18
with active cell D8, click down arrrow next to pivot table on left side of toolbar
formulas
click calculated item




type diff/ 2003 ems in name box
clear zero in formula box
click diff in items box
click insert item
type divide sign in formula box
click ncfoursum2003 in items box
click insert item
click ok




resulting table




   e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
   sheet = 2nd calculated item                                                       15 of 18
move the cursor so that it turns into a down arrow in upper part of cell E8
click to highlight
right click
click format cells
click percentage
click ok

with active cell in data area of rogt diff, click sort descending icon
sutter power plant has largest rog difference between years

scroll right to see nox
with active cell in data area of noxt diff, click sort descending icon
yuba city cogeneration has largest nox difference between years

scroll right to see pm
with active cell in data area of pmt diff, click sort descending icon
western aggs. inc has largest pm difference between years
scroll right & make P9 the active cell
double click cell P9 (value=69.84) to drilldown to detail of both years

scroll right to see pmt ems by device and proid
(actual sum is 48.64.          69.84 is sum of 2003, 2004, diff, & diff/2003)
useful for comparison (missing/deleted device or proid, diff process rate, diff ems)




   e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
   sheet = format & desc pm & drilldown                                                16 of 18
ASAP Utilities




www.asap-utilities.com




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = ASAP utilities                     17 of 18
Microsoft Office Online
PivotTable reports 101
Assistance > Office XP > Excel 2002 > PivotTable Reports and Charts           http://office.microsoft.com/en-us/assistance/HA010346321033.aspx

Training > Excel > PivotTable I: What's so great about PivotTable reports?    http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033
reference card for above                                                      http://office.microsoft.com/training/Training.aspx?AssetID=RP010140321033&CTT=6&Origin=RC010136191033

Training > Excel > PivotTable II: Swing into action with PivotTable reports   http://office.microsoft.com/training/training.aspx?AssetID=RC010286901033
reference card for above                                                      http://office.microsoft.com/training/Training.aspx?AssetID=RP010287081033&CTT=6&Origin=RC010286901033

Training > Excel > PivotTable III: Show off your PivotTable skills            http://office.microsoft.com/training/training.aspx?AssetID=RC010381561033
reference card for above                                                      http://office.microsoft.com/training/Training.aspx?AssetID=RP010381551033&CTT=6&Origin=RC010381561033


Excel Offline Help                                                            More Online Information
                                                                               http://www.microsoft.com/smallbusiness/resources/technology/business_software/how_to_use_pivottables_exc
                                                                               els_data_tool_du_jour.mspx
                                                                               http://www.mrexcel.com/archive/Pivot/
                                                                               http://www.exceltip.com/exceltips.php?view=category&ID=32
                                                                               http://www.contextures.com/tiptech.html




  e5f20fab-bcd3-4b09-98b4-7660ed87b895.xls
  sheet = help resources                                                                                                                                                18 of 18

				
DOCUMENT INFO
Description: Drag and Drop Template document sample