際際滷

際際滷Share a Scribd company logo
Pivottables in Excel for a CAR
oriented approach
Ann.Deraedt@howest.be 20 Januar 2014
Computer-Assisted-Reporting
Workshop is based on the concept article Data
Journalism or Computer Assisted Reporting by
Elena Egawhary and Cynthia O-Murchu
http://www.tcij.org/resources/handbooks/datajournalism
Only support for page 7- 27

NEW MEDIA AND COMMUNICATION TECHNOLOGY

2
Workshop topics
CAR oriented approach
1. Calculation with formulas and operators
2. Use Excel tables to manage information
3. A pivot table: an interactive way to quickly summarize large
amounts of data
4. Visualizing with Excel charts
5. Import data from external data sources

6. Using macros to speed up your work from rows to columns
7. Distribute the contents of a cell to adjacent columns
8. Understand data at a glance with conditional formatting
9. Not: BI database sources only RDBM sources

All sources can be found on http://office.microsoft.com
NEW MEDIA AND COMMUNICATION TECHNOLOGY

3
1. Calculation with formulas and
operators
- Celreferences:
http://spreadsheets.about.com/od/excelformulas/ss/formula_begin_3.htm

- Operator: + - / * : influenced empty fields

http://office.microsoft.com/en-us/excel-help/calculation-operatorsand-precedence-HP010078886.aspx?CTT=1

- video: introduction to formulas in Excel 2010

http://office.microsoft.com/en-us/videos/video-introduction-toformulas-in-excel-2010-VA102502537.aspx?CTT=1

- Video: more complex formulas in Excel 2013

http://office.microsoft.com/en-us/excel-help/video-more-complexformulas-in-excel-2013-VA103990859.aspx?CTT=1

NEW MEDIA AND COMMUNICATION TECHNOLOGY

4
2. Use Excel tables to manage
information
Demo: Organize your data by using an Excel table

http://office.microsoft.com/en-us/excel-help/demo-organize-your-databy-using-an-excel-table-HA010237617.aspx?CTT=1

Training:

http://office.microsoft.com/en-us/excel-help/use-exceltables-to-manage-information-RZ102252956.aspx?CTT=1

- Format data
- Sort & filter
- Add totals
- Use formulas
NEW MEDIA AND COMMUNICATION TECHNOLOGY

5
3. Pivot table: interactive way to
quickly summarize large amounts
Article and training on

http://office.microsoft.com/enus/results.aspx?qu=pivottable&ex=1&origin=EC079000070

- Row, Column, Value, Report Filter
- Different calculations in value fields

- Refresh
- Layout

NEW MEDIA AND COMMUNICATION TECHNOLOGY

6
4. Visualizing with Excel charts
Articles, demo and training on
http://office.microsoft.com/en-

us/results.aspx?qu=excel+charts&ex=1&origin=EC079000070

- Series: values, names and legend

- Different types
- Save as chart template
- Pivotgraphics

NEW MEDIA AND COMMUNICATION TECHNOLOGY

7
5. Import data from external data
sources
Article:

http://www.office.microsoft.com/en-us/excel-help/importdata-from-external-data-sources-HA104003952.aspx

Data from the Belgian government:
http://www4dar.vlaanderen.be/sites/svr/Cijfers/Pages/Excel.aspx

NEW MEDIA AND COMMUNICATION TECHNOLOGY

8
6. Using macros to speed up your
work from rows to columns
Training:

http://office.microsoft.com/en-us/excel-help/save-timeby-creating-and-running-macros-in-excel-2010RZ102337714.aspx?CTT=1

- Use relative macros in current map
- Add a shortcut key to speed up execution

NEW MEDIA AND COMMUNICATION TECHNOLOGY

9
7. Distribute the contents of a cell to
adjacent columns
Articles: split text to different cells:
- http://office.microsoft.com/en-us/excel-help/de-inhoud-van-een-celover-aangrenzende-kolommen-verdelen-HP001216399.aspx
- http://office.microsoft.com/en-us/excel-help/tekst-splitsen-en-oververschillende-cellen-verdelen-HA102809804.aspx

NEW MEDIA AND COMMUNICATION TECHNOLOGY

10
8. Understand data at a glance with
conditional formatting
Training understand:

http://office.microsoft.com/en-us/macexcel-help/training-understand-data-at-a-glance-with-conditionalformatting-RZ103796396.aspx?CTT=1

Training use conditional formatting:

http://office.microsoft.com/en-us/excel-help/use-conditional-

formatting-RZ104078009.aspx?CTT=1

Find duplicate values fast:
http://office.microsoft.com/en-us/excel-help/course-summary-useconditional-formatting-HA104104022.aspx?CTT=1

NEW MEDIA AND COMMUNICATION TECHNOLOGY

11
9. Not: BI database only RDBM
sources
Business Intelligence or Business Analytic data are
structured on FACT & DIMENSIONS
(http://www.cio.com/article/40296/Business_Intelligence_Definition_and_Solutions )
Most sources are structured OLTP databases
(OnLine Transaction Processing), mainly in the 3NF
rule of the CODD Normalization

NEW MEDIA AND COMMUNICATION TECHNOLOGY

12
Ad

Recommended

excel
excel
Jocelyn
Presentatie every picture_ic14
Presentatie every picture_ic14
EveryPicture
Deel 2: Infographic ik als timemanager simulatie excel document
Deel 2: Infographic ik als timemanager simulatie excel document
Ann Deraedt
SharePoint - Make Informed Decisions
SharePoint - Make Informed Decisions
David J Rosenthal
Excelling with Microsoft Excel
Excelling with Microsoft Excel
Sirui Wang
Advance Data Analysis & Database
Advance Data Analysis & Database
Ahmed Yasir Khan
excel syllabus .pdf
excel syllabus .pdf
amansharmapaprola199
Oracle application express ppt
Oracle application express ppt
Abhinaw Kumar
Introduction-to-Power-Pivot-in-Excel.pptx
Introduction-to-Power-Pivot-in-Excel.pptx
Anupama Kate
Advanced excel brochure
Advanced excel brochure
Zabeel Institute
Oracle application express
Oracle application express
Abhinaw Kumar
What is Advanced Excel and what are some best practices for designing and cre...
What is Advanced Excel and what are some best practices for designing and cre...
Technogeeks
4 Ways to Merge IBM i Data with Microsoft Excel
4 Ways to Merge IBM i Data with Microsoft Excel
HelpSystems
Information and technology Basics computer skills required to learn and to un...
Information and technology Basics computer skills required to learn and to un...
VasundharaRajput6
Introto excel
Introto excel
guest1035f97
Smart view instructions_2_7_14
Smart view instructions_2_7_14
Sabyasachi Srimany
Office 365 Tip: Analyze Data Effectively
Office 365 Tip: Analyze Data Effectively
Microsoft India
How can advanced Excel skills benefit professionals in finance and accounting...
How can advanced Excel skills benefit professionals in finance and accounting...
kzayra69
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Ahmed Yasir Khan
Introduction to Pivot Tables
Introduction to Pivot Tables
Kevin Jay Kaufman Ortiz
L.E-WK3_ETECH_3.docx
L.E-WK3_ETECH_3.docx
giogonzaga
Microsoft Excel Dashboards and Their Features.pdf
Microsoft Excel Dashboards and Their Features.pdf
Nitin
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
ASIS&T
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Ahmed Yasir Khan
Excel - Group 1.pptx
Excel - Group 1.pptx
AlistairDatu1
Power bi slide share pdf it is a very important
Power bi slide share pdf it is a very important
Satyabratarath5
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
Knowledge Management Associates, LLC
What is a spreadsheet
What is a spreadsheet
Kelly Scallion
ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
jutaydeonne
YSPH VMOC Special Report - Measles Outbreak Southwest US 6-14-2025.pptx
YSPH VMOC Special Report - Measles Outbreak Southwest US 6-14-2025.pptx
Yale School of Public Health - The Virtual Medical Operations Center (VMOC)

More Related Content

Similar to Pivottables through car approach (20)

Introduction-to-Power-Pivot-in-Excel.pptx
Introduction-to-Power-Pivot-in-Excel.pptx
Anupama Kate
Advanced excel brochure
Advanced excel brochure
Zabeel Institute
Oracle application express
Oracle application express
Abhinaw Kumar
What is Advanced Excel and what are some best practices for designing and cre...
What is Advanced Excel and what are some best practices for designing and cre...
Technogeeks
4 Ways to Merge IBM i Data with Microsoft Excel
4 Ways to Merge IBM i Data with Microsoft Excel
HelpSystems
Information and technology Basics computer skills required to learn and to un...
Information and technology Basics computer skills required to learn and to un...
VasundharaRajput6
Introto excel
Introto excel
guest1035f97
Smart view instructions_2_7_14
Smart view instructions_2_7_14
Sabyasachi Srimany
Office 365 Tip: Analyze Data Effectively
Office 365 Tip: Analyze Data Effectively
Microsoft India
How can advanced Excel skills benefit professionals in finance and accounting...
How can advanced Excel skills benefit professionals in finance and accounting...
kzayra69
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Ahmed Yasir Khan
Introduction to Pivot Tables
Introduction to Pivot Tables
Kevin Jay Kaufman Ortiz
L.E-WK3_ETECH_3.docx
L.E-WK3_ETECH_3.docx
giogonzaga
Microsoft Excel Dashboards and Their Features.pdf
Microsoft Excel Dashboards and Their Features.pdf
Nitin
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
ASIS&T
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Ahmed Yasir Khan
Excel - Group 1.pptx
Excel - Group 1.pptx
AlistairDatu1
Power bi slide share pdf it is a very important
Power bi slide share pdf it is a very important
Satyabratarath5
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
Knowledge Management Associates, LLC
What is a spreadsheet
What is a spreadsheet
Kelly Scallion
Introduction-to-Power-Pivot-in-Excel.pptx
Introduction-to-Power-Pivot-in-Excel.pptx
Anupama Kate
Advanced excel brochure
Advanced excel brochure
Zabeel Institute
Oracle application express
Oracle application express
Abhinaw Kumar
What is Advanced Excel and what are some best practices for designing and cre...
What is Advanced Excel and what are some best practices for designing and cre...
Technogeeks
4 Ways to Merge IBM i Data with Microsoft Excel
4 Ways to Merge IBM i Data with Microsoft Excel
HelpSystems
Information and technology Basics computer skills required to learn and to un...
Information and technology Basics computer skills required to learn and to un...
VasundharaRajput6
Smart view instructions_2_7_14
Smart view instructions_2_7_14
Sabyasachi Srimany
Office 365 Tip: Analyze Data Effectively
Office 365 Tip: Analyze Data Effectively
Microsoft India
How can advanced Excel skills benefit professionals in finance and accounting...
How can advanced Excel skills benefit professionals in finance and accounting...
kzayra69
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Power BI & Advanced Business Intelligence Tools Excel 2013 / 2016 By Spark Tr...
Ahmed Yasir Khan
L.E-WK3_ETECH_3.docx
L.E-WK3_ETECH_3.docx
giogonzaga
Microsoft Excel Dashboards and Their Features.pdf
Microsoft Excel Dashboards and Their Features.pdf
Nitin
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
RDAP 15 Excel Archival Tool: Automating the Spreadsheet Conversion Process
ASIS&T
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Advanced Excel 2013 2016 Tips and Tricks by Spark Training
Ahmed Yasir Khan
Excel - Group 1.pptx
Excel - Group 1.pptx
AlistairDatu1
Power bi slide share pdf it is a very important
Power bi slide share pdf it is a very important
Satyabratarath5
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
FCSPUG - SharePoint Business Intelligence and Data Visualization - See Beyond...
Knowledge Management Associates, LLC
What is a spreadsheet
What is a spreadsheet
Kelly Scallion

Recently uploaded (20)

ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
jutaydeonne
YSPH VMOC Special Report - Measles Outbreak Southwest US 6-14-2025.pptx
YSPH VMOC Special Report - Measles Outbreak Southwest US 6-14-2025.pptx
Yale School of Public Health - The Virtual Medical Operations Center (VMOC)
Publishing Your Memoir with Brooke Warner
Publishing Your Memoir with Brooke Warner
Brooke Warner
How to Customize Quotation Layouts in Odoo 18
How to Customize Quotation Layouts in Odoo 18
Celine George
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
SHERAZ AHMAD LONE
LDMMIA Yoga S10 Free Workshop Grad Level
LDMMIA Yoga S10 Free Workshop Grad Level
LDM & Mia eStudios
Tanja Vujicic - PISA for Schools contact Info
Tanja Vujicic - PISA for Schools contact Info
EduSkills OECD
Code Profiling in Odoo 18 - Odoo 18 際際滷s
Code Profiling in Odoo 18 - Odoo 18 際際滷s
Celine George
K12 Tableau User Group virtual event June 18, 2025
K12 Tableau User Group virtual event June 18, 2025
dogden2
Hurricane Helene Application Documents Checklists
Hurricane Helene Application Documents Checklists
Mebane Rash
English 3 Quarter 1_LEwithLAS_Week 1.pdf
English 3 Quarter 1_LEwithLAS_Week 1.pdf
DeAsisAlyanajaneH
IIT KGP Quiz Week 2024 Sports Quiz (Prelims + Finals)
IIT KGP Quiz Week 2024 Sports Quiz (Prelims + Finals)
IIT Kharagpur Quiz Club
Values Education 10 Quarter 1 Module .pptx
Values Education 10 Quarter 1 Module .pptx
JBPafin
OBSESSIVE COMPULSIVE DISORDER.pptx IN 5TH SEMESTER B.SC NURSING, 2ND YEAR GNM...
OBSESSIVE COMPULSIVE DISORDER.pptx IN 5TH SEMESTER B.SC NURSING, 2ND YEAR GNM...
parmarjuli1412
VCE Literature Section A Exam Response Guide
VCE Literature Section A Exam Response Guide
jpinnuck
ENGLISH-5 Q1 Lesson 1.pptx - Story Elements
ENGLISH-5 Q1 Lesson 1.pptx - Story Elements
Mayvel Nadal
Birnagar High School Platinum Jubilee Quiz.pptx
Birnagar High School Platinum Jubilee Quiz.pptx
Sourav Kr Podder
Assisting Individuals and Families to Promote and Maintain Health Unit 7 | ...
Assisting Individuals and Families to Promote and Maintain Health Unit 7 | ...
RAKESH SAJJAN
Chalukyas of Gujrat, Solanki Dynasty NEP.pptx
Chalukyas of Gujrat, Solanki Dynasty NEP.pptx
Dr. Ravi Shankar Arya Mahila P. G. College, Banaras Hindu University, Varanasi, India.
Nutrition Assessment and Nutrition Education Unit 4 | B.Sc Nursing 5th Seme...
Nutrition Assessment and Nutrition Education Unit 4 | B.Sc Nursing 5th Seme...
RAKESH SAJJAN
ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
ENGLISH_Q1_W1 PowerPoint grade 3 quarter 1 week 1
jutaydeonne
Publishing Your Memoir with Brooke Warner
Publishing Your Memoir with Brooke Warner
Brooke Warner
How to Customize Quotation Layouts in Odoo 18
How to Customize Quotation Layouts in Odoo 18
Celine George
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
SHERAZ AHMAD LONE
LDMMIA Yoga S10 Free Workshop Grad Level
LDMMIA Yoga S10 Free Workshop Grad Level
LDM & Mia eStudios
Tanja Vujicic - PISA for Schools contact Info
Tanja Vujicic - PISA for Schools contact Info
EduSkills OECD
Code Profiling in Odoo 18 - Odoo 18 際際滷s
Code Profiling in Odoo 18 - Odoo 18 際際滷s
Celine George
K12 Tableau User Group virtual event June 18, 2025
K12 Tableau User Group virtual event June 18, 2025
dogden2
Hurricane Helene Application Documents Checklists
Hurricane Helene Application Documents Checklists
Mebane Rash
English 3 Quarter 1_LEwithLAS_Week 1.pdf
English 3 Quarter 1_LEwithLAS_Week 1.pdf
DeAsisAlyanajaneH
IIT KGP Quiz Week 2024 Sports Quiz (Prelims + Finals)
IIT KGP Quiz Week 2024 Sports Quiz (Prelims + Finals)
IIT Kharagpur Quiz Club
Values Education 10 Quarter 1 Module .pptx
Values Education 10 Quarter 1 Module .pptx
JBPafin
OBSESSIVE COMPULSIVE DISORDER.pptx IN 5TH SEMESTER B.SC NURSING, 2ND YEAR GNM...
OBSESSIVE COMPULSIVE DISORDER.pptx IN 5TH SEMESTER B.SC NURSING, 2ND YEAR GNM...
parmarjuli1412
VCE Literature Section A Exam Response Guide
VCE Literature Section A Exam Response Guide
jpinnuck
ENGLISH-5 Q1 Lesson 1.pptx - Story Elements
ENGLISH-5 Q1 Lesson 1.pptx - Story Elements
Mayvel Nadal
Birnagar High School Platinum Jubilee Quiz.pptx
Birnagar High School Platinum Jubilee Quiz.pptx
Sourav Kr Podder
Assisting Individuals and Families to Promote and Maintain Health Unit 7 | ...
Assisting Individuals and Families to Promote and Maintain Health Unit 7 | ...
RAKESH SAJJAN
Nutrition Assessment and Nutrition Education Unit 4 | B.Sc Nursing 5th Seme...
Nutrition Assessment and Nutrition Education Unit 4 | B.Sc Nursing 5th Seme...
RAKESH SAJJAN
Ad

Pivottables through car approach

  • 1. Pivottables in Excel for a CAR oriented approach Ann.Deraedt@howest.be 20 Januar 2014
  • 2. Computer-Assisted-Reporting Workshop is based on the concept article Data Journalism or Computer Assisted Reporting by Elena Egawhary and Cynthia O-Murchu http://www.tcij.org/resources/handbooks/datajournalism Only support for page 7- 27 NEW MEDIA AND COMMUNICATION TECHNOLOGY 2
  • 3. Workshop topics CAR oriented approach 1. Calculation with formulas and operators 2. Use Excel tables to manage information 3. A pivot table: an interactive way to quickly summarize large amounts of data 4. Visualizing with Excel charts 5. Import data from external data sources 6. Using macros to speed up your work from rows to columns 7. Distribute the contents of a cell to adjacent columns 8. Understand data at a glance with conditional formatting 9. Not: BI database sources only RDBM sources All sources can be found on http://office.microsoft.com NEW MEDIA AND COMMUNICATION TECHNOLOGY 3
  • 4. 1. Calculation with formulas and operators - Celreferences: http://spreadsheets.about.com/od/excelformulas/ss/formula_begin_3.htm - Operator: + - / * : influenced empty fields http://office.microsoft.com/en-us/excel-help/calculation-operatorsand-precedence-HP010078886.aspx?CTT=1 - video: introduction to formulas in Excel 2010 http://office.microsoft.com/en-us/videos/video-introduction-toformulas-in-excel-2010-VA102502537.aspx?CTT=1 - Video: more complex formulas in Excel 2013 http://office.microsoft.com/en-us/excel-help/video-more-complexformulas-in-excel-2013-VA103990859.aspx?CTT=1 NEW MEDIA AND COMMUNICATION TECHNOLOGY 4
  • 5. 2. Use Excel tables to manage information Demo: Organize your data by using an Excel table http://office.microsoft.com/en-us/excel-help/demo-organize-your-databy-using-an-excel-table-HA010237617.aspx?CTT=1 Training: http://office.microsoft.com/en-us/excel-help/use-exceltables-to-manage-information-RZ102252956.aspx?CTT=1 - Format data - Sort & filter - Add totals - Use formulas NEW MEDIA AND COMMUNICATION TECHNOLOGY 5
  • 6. 3. Pivot table: interactive way to quickly summarize large amounts Article and training on http://office.microsoft.com/enus/results.aspx?qu=pivottable&ex=1&origin=EC079000070 - Row, Column, Value, Report Filter - Different calculations in value fields - Refresh - Layout NEW MEDIA AND COMMUNICATION TECHNOLOGY 6
  • 7. 4. Visualizing with Excel charts Articles, demo and training on http://office.microsoft.com/en- us/results.aspx?qu=excel+charts&ex=1&origin=EC079000070 - Series: values, names and legend - Different types - Save as chart template - Pivotgraphics NEW MEDIA AND COMMUNICATION TECHNOLOGY 7
  • 8. 5. Import data from external data sources Article: http://www.office.microsoft.com/en-us/excel-help/importdata-from-external-data-sources-HA104003952.aspx Data from the Belgian government: http://www4dar.vlaanderen.be/sites/svr/Cijfers/Pages/Excel.aspx NEW MEDIA AND COMMUNICATION TECHNOLOGY 8
  • 9. 6. Using macros to speed up your work from rows to columns Training: http://office.microsoft.com/en-us/excel-help/save-timeby-creating-and-running-macros-in-excel-2010RZ102337714.aspx?CTT=1 - Use relative macros in current map - Add a shortcut key to speed up execution NEW MEDIA AND COMMUNICATION TECHNOLOGY 9
  • 10. 7. Distribute the contents of a cell to adjacent columns Articles: split text to different cells: - http://office.microsoft.com/en-us/excel-help/de-inhoud-van-een-celover-aangrenzende-kolommen-verdelen-HP001216399.aspx - http://office.microsoft.com/en-us/excel-help/tekst-splitsen-en-oververschillende-cellen-verdelen-HA102809804.aspx NEW MEDIA AND COMMUNICATION TECHNOLOGY 10
  • 11. 8. Understand data at a glance with conditional formatting Training understand: http://office.microsoft.com/en-us/macexcel-help/training-understand-data-at-a-glance-with-conditionalformatting-RZ103796396.aspx?CTT=1 Training use conditional formatting: http://office.microsoft.com/en-us/excel-help/use-conditional- formatting-RZ104078009.aspx?CTT=1 Find duplicate values fast: http://office.microsoft.com/en-us/excel-help/course-summary-useconditional-formatting-HA104104022.aspx?CTT=1 NEW MEDIA AND COMMUNICATION TECHNOLOGY 11
  • 12. 9. Not: BI database only RDBM sources Business Intelligence or Business Analytic data are structured on FACT & DIMENSIONS (http://www.cio.com/article/40296/Business_Intelligence_Definition_and_Solutions ) Most sources are structured OLTP databases (OnLine Transaction Processing), mainly in the 3NF rule of the CODD Normalization NEW MEDIA AND COMMUNICATION TECHNOLOGY 12