How Do I Count Distinct Records in an Excel Pivot Table

Document Sample
How Do I Count Distinct Records in an Excel Pivot Table Powered By Docstoc
					  aaceInternational Alaska Section   June 2008

Excel Tips and Tricks
 for Project Controls

         Sean Robbins PMP, PSP
        NANA/Colt Engineering, LLC

1.   Learning Excel and finding reference materials
2.   Helpful project controls formulas
3.   Pivot tables
4.   Project controls pivot table examples
5.   Spreadsheet documentation
1. Learning Excel and finding reference materials

  Learning Methods and
 • Courses, seminars, presentations, books, videos,
   online, peers
 • Broad versus detailed
 • Present Excel’s capabilities in an organized manner
 • Applying the capabilities to actual situations
 • Leave you with an indexed ―instruction manual‖, to look
   up things like syntax and limitations
1. Learning Excel and finding reference materials

   • Want ones that will explain the functions, how to
     actually apply and combine them, as well as serve as
   • My favorites
      – ―Formulas and Functions with Microsoft Excel
        2003‖, by Paul McFedries
      – ―Pivot Table Data Crunching‖ by Bill Jelen and
        Michael Alexander
1. Learning Excel and finding reference materials

   • There’s a surprising amount of Excel training
     information and tips out there
   • University .edu often good for basic function/module
   • Excel consultants often have forums where
     practitioners share solutions to complex problems. and are among
     the better ones
   • Microsoft has online help, and some short videos. OK
     for things like formula syntax
2. Helpful project controls formulas

  A.       Zero suppression
  B.       Error suppression
  C.       Formatting numbers as $K and $MM
  D.       Breaking out parts of coded activity numbers or
           WBS into their components
  E.       Primavera data
        A.    Stripping ―h‖s or ―d‖s out of durations and/or units
        B.    Removing actualize ―A‖s from dates
2. Helpful project controls formulas

 Zero Suppression
• Clean up reports to only show actual (<>0) values
• Apply to entire worksheet: Tools > Options > View >
  uncheck ―zero values‖
• Conditional formula =if(old_formula=0,‖‖,old_formula)
  (where old_formula is the original formula in that cell)
• Custom cell formatting: Find one that works, and
  append with ;#;. Then use format painter to apply this
  rule down the rest of the column or row.
2. Helpful project controls formulas

Error Suppression
• #DIV/0, #N/A, #VALUE, #REF, etc.
• Clutter up report, and can’t be summed
• Pinpoint the problem versus general approach
   – Pinpoint for DIV/O =IF(B3=0,‖‖,A3/B3)
   – Broad =IF(ISERROR(old_formula),‖‖,old_formula)
2. Helpful project controls formulas

$K or $MM Format
• Useful in graphs or high-level reports
• Custom format
    – $K: $#,###.##,K
    – $MM: $#,###.##,,‖MM‖
       • $ - start with a dollar sign
       • #,### - show thousands commas
       • .## - the number of decimal places you want to
         show (optional)
       • , round to nearest thousand, vs. ,, round to
         nearest million
       • ―MM‖-has to be in quotes, otherwise the format
         thinks you are talking about months.
2. Helpful project controls formulas

Parts of a String
• 123.ABC.45.6789 in cell A3
• With letters and/or multiple decimal points, Excel
  considers this to be TEXT, so you have to use text
• To retrieve the 123 = left (A3,3)
• To retrieve the ABC =mid(A3,5,3)
   – Where are starting at the beginning of the 5th
     character, and taking following 3
• To retrieve the 6789 = right(A3,4)
• Even though these appear to be numbers, Excel still
  considers them to be text at this point.
2. Helpful project controls formulas

Letters in Primavera Durations /
Units – Substitute Option
• Schedulers—Edit > User Preferences > Time Units tab
  > uncheck ―Show Unit Label‖ for units format and
  durations format before exporting to Excel!
• =Value(Substitute(A3, ‖h‖,‖‖))
   – Take the value in cell A3, and substitute nothing
     (note, not a space) for every occurrence of h (note-as
     a piece of text, needs to be in quotes)
   – Value: Coverts something previously stored as text
     into a number, so you can now use it in calculations.
• Limitation-this formula needs to be tweaked if you
  switch units between hours and days, for example
2. Helpful project controls formulas

Letters in Primavera Durations /
Units – Left/Length Option
• The end result is the left few characters of the cell, but
  we don’t necessarily know how many characters
• =value(LEFT(A3,len(A3)-1))
   – We start by determining the length (LEN) of cell A3.
     Once we have that, we take the left-most one less
     than the total length (to trim off the h) characters,
     and then determine the value of that
• This formula will work as-is regardless of unit
  descriptor (h versus d, for example), as long as the unit
  designator is a single character
2. Helpful project controls formulas

Trimming Actual ―A‖s off
Primavera Dates
• Date string lengths might be different (depending on
  whether leading zeroes are specified or not), and not
  all dates are actualized
• =value(if(RIGHT(A3,1)=―A‖,LEFT(A3,len(A3)-2),A3))
   – If A3 ends in an A, give me all but the last two
      characters (for the space and the A), otherwise just
      give me A3,
   – and then determine the value of whichever of those
      two options gets selected
3. Pivot Tables

Pivot Table Intro
•   Ideal for:
    – Large data sets (up to 65,531 lines of data)
    – Ad-hoc queries, where you’re not really sure how you
      want to present the data. Pivot tables are flexible
    – Periodic reports. You set up one pivot table, which
      you can then can re-use it again and again just by
      pasting in the new data, and hitting recalculate!
    – Ranking, grouping, filtering, and sorting data
    – Reformatting or ―prettying up‖ data
    – Where you need to be able to drill down see the
      underlying data
3. Pivot Tables

Two Sheet Solution
• Using a ―one sheet solution‖, you would be doing things
  like moving and hiding columns, filtering data, and
  applying formatting one line at a time. These steps are
  not required when using pivot tables
• In pivot tables, you typically have one worksheet for your
  raw data, and then another one for your finished
  product—the pivot table report or graph
• Since raw data is hidden from the end-user of the
  finished product, you can add columns of conditional
  data to automate your sort results, for example
3. Pivot Tables

Two Sheet Solution
• One data sheet can feed many reports. Thus, you can
  have one pivot table report preset for each filtering or
  arrangement of the data, so you only have to recalculate

         Raw Data                    Raw Data

     Finished Report
                       Finished Report 1   Finished Report 2
      (pivot report)
                         (pivot report)       (pivot report)
3. Pivot Tables

Pivot Table Raw Data
Requirements—What you DON’T
Have to Worry About
• Sort order
• Column order
• Number format (for example, if some records display $,
  and some don’t)
• Deleting irrelevant data (you can get the pivot table to
  ignore or filter it out)
3. Pivot Tables

Raw Data Requirements
• Records (data points) in rows, categories in columns
• Each column name must be unique, and single-cell.
• If you are re-using a pivot table, column order and
  names you are using must be consistent between data
• When setting up, data set must be contiguous (no empty
  columns or rows between portions of data)
• Flat instead of hierarchical data—meaning that rows
  must contain all relevant data
3. Pivot Tables

Hierarchical Data Example
• Excel will interpret the second line as representing:
   – For project N/A, employee number N/A, last name
     N/A, first name N/A, worked 3 hours on 17 Jun
            Project   Empl Num   Last      First   Date      Hours
           Proj A        12345 Doe      John        16-Jun           5
                                                    17-Jun           3
                                                    18-Jun           6
                                                    20-Jun           8
                         45678 Smith    Ann         17-Jun           2
                                                    18-Jun           7
                                                    19-Jun           5
           Proj B        12345 Doe      John        16-Jun           2
                                                    17-Jun           4
                                                    19-Jun           7
3. Pivot Tables

Flattening Hierarchical Data
• Out of Primavera, create unique pivot-table-feeding
  layouts, with consistent column order. Leave the data
  ungrouped, and instead have a column for the factor(s)
  you were planning on grouping it by. Filtering is optional
• For pre-formatted hierarchical data, such as out of a cost
  system, have calculated columns in the raw data tab
  which read out of the raw data pasted to their left and
  duplicate them in a flat manner
• You only have to flatten the hierarchical columns you
  plan to use in your chart or table
• Give this calculated column unique name, which you can
  keep straight from the hierarchical one. For example,
  preface with Z
3. Pivot Tables

Flattening Hierarchical Data

                      A       B        C        D         E         F        G       H         I
            1     Zproj   Zlast   Zfirst     Project   Empl Num   Last      First   Date     Hours
            2                              Proj A         12345 Doe      John       16-Jun           5
            3                                                                       17-Jun           3
            4                                                                       18-Jun           6
            5                                                                       20-Jun           8
            6                                             45678 Smith    Ann        17-Jun           2
            7                                                                       18-Jun           7
            8                                                                       19-Jun           5
            9                              Proj B         12345 Doe      John       16-Jun           2
           10                                                                       17-Jun           4
           11                                                                       19-Jun           7
3. Pivot Tables

Flattening Formula
• Take hierarchical data in column D, which we are going
  to flatten in column A
• Formula for A2: =if(isblank(D2),A1,D2) --and
  paste this down column A
   – What this formula does in column A is repeat the
      cell above unless we have a (new) value in the
      corresponding cell in column D, at which point if
      takes the D value instead
• Give column A a distinct name from column D
• Make as many ―flattening columns‖ as required to
  generate your reports and/or graphs
• Tip—shade these calculated columns, so that you don’t
  erase or paste over them
3. Pivot Tables

Flattened Hierarchical Data
•    Data from the green columns (D, F, and G) was used to
     populate the pink columns (A-C). In our pivot table, we
     will use the pink columns in place of the green ones.
•    We are not going to use column E, so we can ignore it
     (no need to either delete or flatten it).
•    Data in columns H and I is already flat

                       A       B        C        D         E         F        G       H         I
            1     Zproj    Zlast   Zfirst     Project   Empl Num   Last      First   Date     Hours
            2     Proj A   Doe     John     Proj A         12345 Doe      John       16-Jun           5
            3     Proj A   Doe     John                                              17-Jun           3
            4     Proj A   Doe     John                                              18-Jun           6
            5     Proj A   Doe     John                                              20-Jun           8
            6     Proj A   Smith   Ann                     45678 Smith    Ann        17-Jun           2
            7     Proj A   Smith   Ann                                               18-Jun           7
            8     Proj A   Smith   Ann                                               19-Jun           5
            9     Proj B   Doe     John     Proj B         12345 Doe      John       16-Jun           2
           10     Proj B   Doe     John                                              17-Jun           4
           11     Proj B   Doe     John                                              19-Jun           7
3. Pivot Tables

Running the Pivot Table Wizard
• Data > PivotTable and PivotChart menu
• Step 1: Accept the defaults and click NEXT
• Step 2: Edit the range as required. If you plan on
  reusing this table with future data, set the end row
  number high; although you can edit the range at a later
  date. Then click NEXT.
• Step 3: Where to put the new pivot table. Normally,
  the default (in a new worksheet) is fine.
3. Pivot Tables

Running the Pivot Table Wizard
                                                            Drag and
           Fields you want to filter with (optional)
                                                            drop fields
                                                            into the right
                  Field(s) you want to display as columns

   Row fields                      Data
3. Pivot Tables

Pivot Table Wizard Menu Bar
• If you’re going to be using pivot tables frequently, I
  highly recommend adding the pivot table group to your
  to menu
• Right-click in top menu bar > check Pivot Table
3. Pivot Tables

Pivot Table Annoyances
Problem                              Workaround

Pivot tables are large (often into   Give end users printout or static
the 10’s of MB)                      copies (using paste special)
Data fields often default to         Only have to reset once
―Count‖, rather than ―Sum‖
Row groups default to subtotaling    Only have to turn off once for
You can’t edit within pivot table    Make your edits on a static copy

Percentages from data tab don’t      Calculate percentages from pivot
sum properly                         fields
4. Pivot Table Examples

Pivot Table Examples
• Weekly progress reporting tool, using pivot tables
   – Timesheet summary for past week
   – Activity status from Primavera
• Primavera manpower-loading pivot table graph
4. Pivot Table Examples

Weekly Progress Reporting

          Cost system timecard                       Primavera
               data dump                          assignments data

        Paste into pivot table,                  Paste into pivot table
         which reformats and
        splits out by discipline
     (Made static when distributed)
                                          Macros to make static, customize
                                            layout, and add calculations

           Assemble discipline-specific pairings of these, and distribute
5. Spreadsheet Documentation

…Uh, what was he/she trying to
do here?
• One of Excel’s greatest strengths is that is allows just
  about anybody to create a spreadsheet
• However, one of it’s greatest weaknesses is that not
  only can anybody create one, but there are countless
  ways to get to the same result
• Although we all work in the same specialty, and many
  in the same industry, few of us would welcome the
  opportunity to ―inherit‖ one of our peer’s spreadsheets
• What can we do to break the cycle of confusion and
  wasted effort?
5. Spreadsheet Documentation

Document How to Use Your
• For your commonly re-used (periodic) spreadsheets
• Simply adding a tab to the spreadsheet is a simple way
  to do this
• List the steps required for the update
• Once you have that step inventory, look for
  opportunities to simplify
• A realistic goal is the ―paste and recalculate‖ simplicity
  you get with well-set-up pivot tables
5. Spreadsheet Documentation

Document The Reasons Behind
Complex Calculations
• Particularly is equations were derived as part of a multi-
  disciplinary team (such as between cost control and
• Overall philosophy of the approach, and organization of
  the data (where the data comes from, and what it feeds,
  for example)
• Objectives of the equation (or groups of equations,
  such as a column), as well as reasons for deviations
  (such as non-obvious IF statements)

Shared By:
Description: How Do I Count Distinct Records in an Excel Pivot Table document sample