How Do I Count Distinct Records in an Excel Pivot Table

Document Sample

```					  aaceInternational Alaska Section   June 2008

Excel Tips and Tricks
for Project Controls

Sean Robbins PMP, PSP
NANA/Colt Engineering, LLC
Outline

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

Learning Methods and
References
• 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

Books
• Want ones that will explain the functions, how to
actually apply and combine them, as well as serve as
reference
• 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

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

Overview
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)
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
functions.
• 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
once

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
dumps
• 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
section

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
• 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
each
You can’t edit within pivot table    Make your edits on a static copy
fields

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
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

…Uh, what was he/she trying to
do here?
• One of Excel’s greatest strengths is that is allows just
• 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?

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

Document The Reasons Behind
Complex Calculations
• Particularly is equations were derived as part of a multi-
disciplinary team (such as between cost control and
procurement)
• 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)

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 107 posted: 11/14/2010 language: English pages: 32
Description: How Do I Count Distinct Records in an Excel Pivot Table document sample