Pivot Tables and Charts for Health User Manual v.1
Contents
1. PivotTables amp Charts for Health Data Inputs Regional Reported Age Grou Population HIW 1 Data_Source Year Region District Gender Tip jall ages Prevalenc 2 ANC 2008 Dar Es Salaam l Female 15 24 4 25 3 ANC 2008 Dodoma a Female 15 24 2 45 4 ANC 2008 Kagera g Female 15 24 3 6 5 ANC 2008 Kilimanjaro Female 15 34 3 15 eh NC 2008 Mbeya l Female 15 24 11 2 7 ANC 2008 Mtwara Female 15 24 4 35 amp ANC 2008 Kigoma Female 15 24 1 05 HIV Prevalence among 15 49 year olds Tanzania 1 DataSource THMIS Source THMIS 2007 8 a a Cer umio T a 15 49 7 Data Sowce Ya 5 Row Labels gt Reported HIV Prevalence Max of 3 of HIV Positive Individuals yon 6 Arusha 27 20 683 Regon 7 Dar Es Salaam WA 232 308 dat Gende Pivot Charts Ragone apulaton jal 3925 P Reported HIV Prevalesce a ever esd W ever tested and recevet sesi ti S esos m kant mo 17 UMI j Manyara 23 14 407 15 Mara as 105 382 wa TENEN Y meta gt Cokam abel 16 Mbeya 3 190 444 17 Morogoro Ja 487 pan fon 2 Fi 18 Mtwara 44 4 87 19 Mwanza 20 Pwani iA 9 573 21 Rukwa 5 7 55 9 22 Ruvuma TA G93 23 Shinyanga 4 an A Sonda 2545 ow kabati I van Regen Yo Reported lv Prevalence 9 Was of aof iIi Poten Y EL 1d 1 2 1 3 1 3 1 1 32 1 3 3 1 3 4 1 3 1 1 3 2 Table of Contents INTFOCUCTION ccccccecceccsceccecescesceccecestescecceces2. S F kad Merge A Cente 9 lt 0 00 Conditiona Format Painter B TU s SE ER bad Merge an a men amana as Clipboard Fi Font F Alignment Number Styl Chart 2 M ke 1 ja 2 Sum of Results 3 Row Labels T Sum of Results TE E g 7 MN rovi ETI Number of positive Malaria cases by province 5 Prov2 91 120 6 Grand Total 202 100 Fi 80 8 ma 60 a E Total 10 ii 1 20 12 0 13 Prov1 Prov2 14 Province W 15 4 1A Add a legend 1 Highlight your graph 2 Under the Layout ribbon select Legend 3 Choose the orientation of your Legend PivotChart Tools Layout Format Analyze da Aa aj l aj A an Chart Title x P aa a By Format Selection Picture Shapes Text Chart Axis Data Data Axes Gridlines Plot Chart Chart 3 D Trendlir g Reset to Match Style Mi Box Title Titles Labels Table Mi Area Wall Floor Rotation Current Selection Insert None Background Chart 2 lt gt Fe ra Turn off Legend A B c Show Legend at Right ji H l J K a lanl Show Legend and align i E men Sum of Results m a a Show Legend at Top 3 RowLabels Sum of Results N b lm Show Legend and top kk Don 4 Provi 111 NUM De _ Show Legend at Left 5 Prov2 31 120 laa Show Legend and align 6 Grand Total 202 100 left F F i Show Legend at Bottom 80 Show Legend and align 8 en
3. and Total 768 1 3 2 Filters PivotTables allow for certain elements to be included and others to be excluded using the filter function We can filter out exclude elements two ways 1 Click the filter drop down arrow next to the contents to be filtered in the PivotTable Report Uncheck the boxes next to the contents you wish to exclude from the table Version Malaria 1 0 m Ja Results A 1 Results AN y LAY YO blank OK Cancel 2 Click the filter drop down arrow next to the contents to be filtered in the PivotTable Field List Uncheck the boxes next to the contents you wish to exclude from the table PivotTable Field List v X el Choose fields to add to report EB PatientID Month Province District Health_facility ai lid Diagnostic vV Results Z Species F Origin Positvity Rate Label Filters Value Filters Drag fields between areas below Search P VY Report Filter H Legend Fields Select All Ho ai C blank f Axis Fields Ca 2 Values Province Count of Res v Defer Layout Update cancer moun j 3 Select OK Be sure to review the table to ensure that all the proper filters were utilized and that data make reasonable sense 1 3 3 Value Field Settings By default Excel summarizes values using either the sum function sum of values within a certain indicator or the count function counting the values or classification
4. bottom 9 Overlay Legend at Right E Total 10 40 agl Show Legend at right of s the chart without resizing 11 20 Overlay Legend at Left 12 0 n Show Legend at left of the chart without resizing i Prov2 14 More Legend Options Province wf 15 1 Right click one of the chart axes 2 Select Format Axis Version Malaria 1 0 3 Select preferred formatting options Delete Reset to Match Styte Fort Change Chart Type Select Data Add Minor Gridlines Format Major Gridiines n Format Axis Number Fil Axis Options Minimum Auto Maximum Auto Major unit Auto Minor unit Auto Line Color Line Style Shadow C Logarithmic scale Display units 3D Format t None Major tick mark type Outside Minor tick mark type Axis labels Horizontal axis crosses Automatic Axis value Maximum axis value O Exed Fixed O Fixed Fixed C Values in reverse order v None v Next to Axis w Change element color 1 Right click an element you wish to change e g line bar data point 2 Select Format Data Series 3 Select preferred formatting options 4 Select Fill 5 Select Solid fill 6 Select color Format Data Series Series Options Fill w No fill Solid fill Gradient fill Border Color Version Malaria 1 0 ENN Border Styles Shadow 3 D Format Sort and Filter Expand Co
5. he PivotTable Report is in the center and the PivotTable field list is on the right The fields in the PivotTable Field List are as follows Fields to add to report Columns in the input dataset and any Note If the PivotTable Field List other calculated indicators dialog box is hidden you can unhide it in two ways Report Filter Filters out data you do not want to include in your 1 Select Options in the table e g exclude males to show only data for females a PivotTable Tools Ribbon 2 Field List in Show Hide Group Column Labels Data grouped in the columns of your table Within the PivotTable right click and select Show Field Row Labels Data grouped in the rows of your table Values The contents of your table To create your PivotTable drag indicators from the upper window of the PivotTable Field List to the lower windows Version Malaria 1 O B PivotTable Field List T x Choose fields to add to report m PatientID IP Month Province District Health fadlity Sex Age Diagnostic Results Species Origin Positvity Rate Drag fields between areas below Report Filter fed Legend Fields Sq Axis Fields Ca E Values Province Count of Res Y E Defer Layout Update Update Your PivotTable Report will begin to develop A3 v fx A B 1 2 3 Row Labels Count of Results 4 Provi 384 5 Prov2 384 6 blank 7 Gr
6. lated Field J K L M M Solve Order if List Formulas reate Set Based on Row Items reate Set Based on Column Items Manage Sets 3 Enter a name for your new indicator for Name 4 Enter a formula using the same format as in Excel Select items from the lower Items window to define your formula 5 Click Add soo ya Insert Calculated Item in Species ma x Name Proportion falciparum aa Delete Formula falciparum falciparum vivax Fields Province District Health facility Sex Age Diagnostic Results Species va Items falciparum Proportion F vivax blank Note use the Insert Item button to quickly insert items when you type a formula To delete a calculated item select the item and click Delete under Add 7 Click OK Version Malaria 1 0 w 1 3 1 Calculated Field A calculated field uses the values from another field e g create a formula using other indicators To insert a calculated field execute the following steps 1 Click any cell inside the PivotTable Report 2 The PivotTable Tools contextual tab activates On the Options tab Select Fields Items amp Sets 3 Select Calculated Item Pivotlable Tools Options Design 2 T Ga la fa E afresh Change Data Clear Select Move Summarize Show Pivotchart OLAP What T Source 7 ki PivotTable Values By Values As 7 Tools Analys Data Actions Calculati
7. llapse gt Reset to Match Style Change Series Chart Type Select Data al amp amp Male Preva ee Add Data Labels Add Trendline Picture or texture fill Automatic C Invert if negative Theme Colors E Seen 3 Transpa Standard Colors More Color ys Yellow F 1 5 Finalize PivotTables and Charts After you have created a table or chart that adeguately visualizes your data paste your final table into another Microsoft Application Word PowerPoint or Excel to save 1 Highlight the table or chart Copy the element Open a new Microsoft Word document PowerPoint presentation or Excel worksheet or workbook 4 Paste the chart or table without linking data Depending on the version of Microsoft Office you have either Paste Normal or Select Use Destination Theme amp Embed Workshop under Paste Options c D E F G H W J K L Sum of Results amp Cut a l Ga Copy Number of positive Malaria cases by prov i paste options 120 gt 111 100 4 91 Paste Special 80 Insert 60 4 Delete 40 4 Clear Contents 20 4 Filter 0 Sort Provi Prov2 B Insert Comment Province W Format Cells Version Malaria 1 0 C Pick From Drop down List Define Name 2 Hyperlink Calibri 11 AY a BIZZ A 9 lt 0 00 00 0 A a Use Destination Theme amp Embed Wo
8. on n J K L M N Solve Order af List Formulas ased on Row Items Column Items gased an L 3 Enter a name for your new indicator for Name 4 Enter a formula using the same format as in Excel 5 Click Add Insert Calculated Field Name Positivity Rate Formula sitive Number tested Number positive Fields Sex Number tested Number positive e Number falciparum Number vivax Number imported Number local Proportion positive m Insert Field OK Close Version Malaria 1 0 Eo Note use the Insert Field button to guickly insert fields when you type a formula To delete a calculated field select the field and click Delete under Add 6 Click OK 7 Drag this new indicator to the Values area in your PivotTable Field List to see the results 1 3 2 Format PivotTables Cell contents Edit PivotTable Report appearance similar to other Excel elements 1 Right click a cell within the PivotTable 2 Select Format Cells and adjust the appearance of the contents 1 cl JA Foo Humber Format Refresh Sort Format Cells a F Remove Reporte Number Alignment Font Border Fal Protection Summarize Values S tegory General Sample Show Values As 094878337 Currency Z Show Details N Decimal places Time 1000 Separator value Field Settin Percentage Oue va Fraction Negative numbers PivotTable Option Scien
9. ounts of tabular data A PivotTable functions like a cross tabulation table to l Calculate totals averages medians and counts based on source data li Classify data based on other fields in the source data and display in a table iii Auto generate charts To demonstrate how to use this tool we will follow an example using mock Malaria surveillance data It is important to remember that although the manual is organized in a step by step format the use of PivotTables is an iterative process As new information becomes available previous steps may need to be revisited Software Requirements To produce visual outputs users will need software and basic operational knowledge of Excel This manual will refer to Excel 2007 but all Microsoft Office versions have this capability Materials used in this manual were adapted from http www excel easy com Special thanks to the UCSF Global Health Group and Asia Pacific Malaria Elimination Network for contributing knowledge and content to this manual Version Malaria 1 0 ES Version Malaria 1 0 a 1 2 Data Inputs An Excel dataset consists of rows individual records displayed horizontally columns groups or indicators displayed vertically and data items contents within rows and columns Data to be used in PivotTables must first be input into a Microsoft Excel spreadsheet using the following format Columns Descriptive characteristics indicators Row 1 Column Headers R
10. ows 2 Data items The matching contents within a column must be identical 08 7 fa A B D li f G H J K PatientiD Month Province District Health facility Sex Age DiagnosticResults Species Origin 2 1 1 Provi a HF1 Male 27 Microscop 0 2 1 Provi a HF1 Male 29 Microscop 0 4 3 1 Provi a HF1 Female 55 RDT 0 5 4 1 Provi a HF1 Male 39 RDT 0 6 5 1 Provi a HF1 Male 17 Microscop 0 7 6 1 Provi a HF1 Male 34 RDT 0 8 7 1 Provi a HF1 Male 10 Microscop 0 8 1 Provi a 0 HF1 Female 46 RDT 1 3 Insert PivotTable To insert a PivotTable execute the following steps 1 Click any single cell inside the data set 2 On the Insert tab click PivotTable Insert Page Layout Formulas Data Review View Developer PivatTable Table Picture a Shapes SmartArt Screenshot Column Line F Bar Area Scatte T Art kai kai T ka T kai kai F Tables Illustrations Charts The following dialog box appears Excel automatically selects the data for you The default location for a new PivotTable is New Worksheet 3 Click OK Version Malaria 1 0 E Create PivotTable Choose the data that you want to analyze Q Select a table or range Table Range SurvData A K ou Use an external data source Connection name Choose where you want the PivotTable report to be placed New Worksheet Existing Worksheet Location Choose whether you want to analyze multiple tables E Add this data to the Data Model 1 3 1 PivotTable Field List T
11. rkbook H iii iii Calibri 11 A A BIUEY A S Bullets gt Numbering gt Styles Hyperlink Look Up Synonyms Translate Additional Actions t 2
12. rwotihart Tou File Home Insert Page Layout Formulas Data Review View Developer Design Layout Form W Z Es TTT ele Save As Switch Select Type Data Chart Layouts Chart Styles ype Template Row Column Data 3 Select a new chart type Version Malaria 1 0 Ea Si af UB AA as ad WAA al a ab ad a a oC all KY Scatter Stock Doughnut Bubble Radar TF 4 Click OK Add a Title 1 Highlight your graph 2 Under the Layout ribbon select Chart Title 3 Choose the orientation of your title Above Centered None TERP Oor went ayout Fo E So vi i wr Z y fi Picture Shapes Text ar Axis Legend Data Data Axes Gridlines Plot Chart Chart 3 D Titi e Titles ad Labels Table w Area Wall Floor Rotation Axes Background SRI W TRIS RNR ee i r a at A ti ee Do not display a chart Title Centered Overlay Title Overlay centered Title on chart without resizing chart pi i e E aM zA n a m es ya MERONEN More Title ee 4 Double click in the title text box Version Malaria 1 0 EPA 5 Select the Home ribbon to change the text font color style or size SS Home Insert Page Layout Formulas Data Review View Design Layout Format Analyze PI dot Calibri Body 18 AN Fwe 2 Wrap Text General Mi E Past m oz o 00 C ai I F aste w vi EE
13. s within a certain indicator To change a value summary or calculation execute the following steps 1 Double click within the header column in the Pivot Report 2 Click on Value Field Settings Version Malaria 1 O ENN Copy Ea Format Cells Number Format 3 Refresh Sort d K Remove Sum of Sales Summarize Values By Show Values As b tE Show Details Pivotlable Options Hide Field List Value Field Settings Source Name Results Custom Name Sum of Results Summarize Values By Show Values As Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum A Count Average Max Min Product 4 Click OK 1 3 4 Calculated Item A calculated item uses the values from other items within the same field or indicator To insert a calculated item execute the following steps 1 Drag the field you wish to calculate item s from into the Row Column window 2 On the Options tab Select Fields Items amp Sets Version Malaria 1 0 EN 3 Select Calculated Item Pivotlable Tools Options Design HE lite like DA a Jls aa HET 2 B lig lz Ya EI afresh Change Data Clear Select Move Summarize Show PivotChart OLAF What Source by ba PivotTable Values By Values As Tools Analys Data Actions Calculation Calcu
14. tesceccetesteseeccetestessences 3 Software Reguirements wanananwannwananananwanawananuaanunaazananuaanwananananuaanuaaazananaaanuaaazawaa alice UL SAA ai n a e n an AA AAA AAA AA AAA AA 5 Insert PivotTable wwmananananananananananananan 5 OE tO SE VV ai AA Wa ENTEL Ia vValue gi AA eee nee a n ab ae EE CACC NEC AAA AA Cac DSF VG IG II Format PivotTables ai ab eer eee ee eee eee ore C ele a k AA AA WA AA AA ee a ne ene eee OUTTA KAA Wa 1 4 1 4 1 Insert Pivot Chart ccccceccccccscscecccscececcscececescsceteaescetenenceseneneecs 13 Format Pivot Charts c cccccsceccccececsccccececscseececececseeececseseeeesecseseeetetstseseaenesseseaeaenecseseenenecas CASE UA PE s vo seat kan pain Gol ka kt os a e gon ka ese a a ons pan n ee atan A a a ai ae Aa AA RA ANA A NA FE A A RE er MN RE YA RW A EE PE A er REN A AGO A VE SO M AA FONN AL ANE WA n n a AA kk a a a e e a ek e a tesa ie e kn n e ae AA e re ake eka CHAY EIA AA rl ad fat tal ti e a a e kt n a a E rt n e s a oi n e sa ai ian 1 5 Version Malaria 1 O Et Finalize PivotTables and Charts wwanananananananan 17 1 1 Introduction This manual offers a systematic approach to using PivotTables for health surveillance and M amp E An Excel PivotTable is an interactive worksheet that provides a powerful tool for summarizing large am
15. tific Text 1234 0 E Show Field List Special 1234 0 Custom 1234 0 Number is used for general display of numbers Currency and Accounting offer specialized formatting for monetary value C e Column header 1 To change the header of a column click on the header cell 2 Select the text box at the top of the worksheet and type your preferred header Version Malaria 1 0 ET HOME INSERT PAGELAYOUT FORMULAS DATA v X v fr CountofResults Row Labels Count of Results sum of Positvity Rate falciparum 392 DIV O Proportion F 0 510416667 DIV O vivax 376 DIV NI 1 4 Insert Pivot Chart To insert a pivot chart simply insert a chart 1 Click any cell inside the PivotTable Report 2 On the Insert tab select a chart type such as Column PivotTable Tools Insert Review View Options Design Home Data wl Sa Fi w l ME a za E a Pee MXre M ki PivotTable Table Picture Clip Shapes SmartArt Screenshot Column Line Pie Bar NA Scatter Other Line Art w w Ki a Ki Ki Charts Tables Illustrations Charts F 5p A3 vi f Row Labels Column B Cc Insert a column chart G H 1 4 1 Format Pivot Charts Change chart type You can change to a different type of pivot chart at any time 1 Select the chart 2 The PivotChart tools contextual tab activates On the Design tab click Change Chart Type X W A jF pivot chart Compatibility Mode Microsoft Excel P
Download Pdf Manuals
Related Search