Mastering Pivot Tables to Summarize Data and Find Investigative Leads

​

Karrie Kehoe

ICIJ - kkehoe@icij.org

​

Company Name

Date

This is your space for legal copy. State whether the information is confidential and how it's intended to be used.

1

WHY PIVOT TABLES?

• ANALYSIS
• CLEANING DATA
• UNIQUE LISTS
• ADVANCED FILTERING
• CAN HELP RESTRUCTURE DATA
• VERIFICATION OF RESULTS

• UK CAMPAIGN FINANCE SPENDING

https://www.electoralcommission.org.uk/political-registration-and-regulation/financial-reporting/campaign-spending-candidates

​

GET DATA

3

Company Name

Date

Full Name

Title

Full Name

Title

Full Name

Title

Full Name

Title

Write a brief bio of this team member. Mention their background, area of expertise, and what their responsibilities will be in this project.

Write a brief bio of this team member. Mention their background, area of expertise, and what their responsibilities will be in this project.

Write a brief bio of this team member. Mention their background, area of expertise, and what their responsibilities will be in this project.

Write a brief bio of this team member. Mention their background, area of expertise, and what their responsibilities will be in this project.

4

http://bit.ly/3LSut3G

5

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

LIST OUT YOUR QUESTIONS

1. WHICH CANDIDATE GOT THE MOST MONEY?
2. WHICH PARTY GOT THE MOST MONEY?
3. WHICH INDEPENDENT CANDIDATE GOT THE MOST MONEY?

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

1. WHICH CANDIDATE GOT THE MOST MONEY?

​

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

=CONCATENATE(A2," ",B2)

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

2. WHICH PARTY GOT THE MOST MONEY?

​

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

=TRIM(H2)

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

SET UP YOUR WORKSPACE

• MAKE A COPY OF YOUR DATA
• KEEP A DATA DIARY OR A README FOR FACT CHECKING AFTERWARDS
• ATTEND TO YOUR FORMATTING ISSUES (FREEZE ROWS)
• CHECK THAT YOU HAVE WHAT YOU EXPECT TO HAVE
• LOOK FOR DIRTY DATA

3. WHICH INDEPENDENT CANDIDATE GOT THE MOST MONEY?

​

LET’S SUMMARIZE OUR FINDINGS

LET’S SUMMARIZE OUR FINDINGS

Thank you!

​

Please get in touch if you have any questions - kkehoe@icij.org