4W WESCOORD Guideline
Contents
1. click Filter click the arrow on the heading for the relevant column Choose your Sort order or what items to display in the click boxes filtering For Excel 2003 Select column headings and choose Data Filter Autofilter b If you want to sort on more than one level please choose Home tab under Editing Sort amp Filter click Custom Sort Sort 2 Ts xs X yy Add Level X Delete Level H Copy Level t Options My data has headers Column Sort On Order Sortby Organisation Values Atoz x Then by Location v Values AtoZ X Home Insert Page Layout Formulas Data Review View Acrobat Options Design 9 xi Calibri 11 E General Fi Conditional Formatting Insert AF ria Z Create a Pivot table dynamic table d Ga Bz wu yA al a 9 43 Format as Table iiio a anfi are wee OS ea ia a a Select the relevant data including B E F G PivotTable Field List CO u m n h ea d i ngs 1 Status all 2 Sector ail Choose fields to add toreport EIT b Go to the nsert tab and choose Pivot 3 v Organisation 4 Sum of Case load Column Labels v v seor ta b le ow Labels z na anila Rizal Benguet Grand Total iLocation re k a 4000 2500 oon gy 7 MSF 500 400 3000 3900 Excel 2003 Go to Data pivot table and pivot 8 UNICEF 699 1100 1000 799 35982. record and only change parts of it for your needs Often you have more than one activity in one area or the opposite the same activity in multiple areas Then just add the changes to the record I e activity and number of beneficiaries Page 4 WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE i Select the record you want to copy if you want to copy more than one record press the left mouse button and drag to the row you want to copy Once selected press the right mouse button then click copy or use Ctrl c ii Go to the row or column you want to paste the data then press the right mouse button then click paste or use Ctrl v b Add additional data after last record Don t add additional rows i The table will allow you add row anywhere but as a precaution don t do this Additional rows will not be set up with the formulas needed to perform automatic functions and drop down menus Use the next available row instead If you need to see data in specific order use Excel s filter and sort functions instead See below c The drop downs stops working i It is possible to delete or change the matrix so it will stop working as intended The formulas can be retrieved and replaced Contact us and we will help you 5 Efficient use of Excel for analysis and reporting and consolidation for IMM 1 Sort and Filter your table a With the help of the Automatic filter if not activated go to Home tab under Editing Sort amp Filter
3. Grand Total 5199 4000 6000 999 16198 charts follow wizard 11 H Drag fields between areas below 13 W Report Filter Column Labels Status Location 15 16 17 FF Row Labels Values Organisation SumofCasel 19 20 21 I gt gt Sheet Data Control_ Sheet3_ 3 J Defer Layout Update WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE c Place the Pivot in another sheet d Click Ok e Depending of what you want to show This example will show number of beneficiaries in a location by organization which can be filtered by sector and status i Drag Status and Sector to Report filter ii Location to Column labels iii Organisation to Row labels iv Case load to Values make sure it says Sum of otherwise click the small arrow and choose Sum of instead of Count of Filter the Pivot table f Use the Filter arrows at Status and Sector and choose if you only want to see on going projects or the Water sector Updating data in your table g Change the data or add records make sure it is within the table under the sheet you are editing h Go back to your Pivot right click on the Pivot table and choose Refresh Your changes will be reflected 3 Creating a Pivot chart a Under the Tab Options PivotTable Tools only accessible when the Pivot is selected choose Pivot chart b Choose type of chart c The chart will show the number of beneficiaries per orga
4. WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE AW Guideline for WESCOORD WASH KENYA DROUGHT RESPONSE Basic User Manual 16 Sept 2011 This user manual is designed to provide brief description and instruction on how to use the 4W reporting tool Additional steps were included to facilitate reporting and analysis of information The instructions are for Excel 2007 For any question please contact Cecilia and Charles at wescoord gmail com and call us for any help Cecilia 0705 253264 and Charles 0722 383998 Deadline for 4W reporting in august September 26 COB The 4W tool is available on the WESCOORD WASH sector website for download Please fill in and submit to wescoord gmail com INSTRUCTIONS The response to be reported would be any activities within the WASH sector undertaken in the drought affected areas defined in sector response strategy and started later than 1 January 2011 The DATA Window Enter Data Here Please fill in the matrix using the predefined lists available Note Using the drop downs when available will ensure consistency of data for analysis WESCOORD WASH Drought Response in KENYA 4W ETTA as required information is READ BASIC Data Entry INSTRUCTIONS the name of organization providing This sheet has been configured to accept data from predefined dependent lists using drop down menu Please take extra care while entering PROVINCE DISTRICT DIVISION with the help of drop down men the assistan
5. activities to accurate location Please use ONLY decimal degree format Fo 36 323653 Ongoing m conversion to decimal degrees INPUT IN DECIMAL DEGREES ONLY http www directionsmag com site latlong converter The column will accept Latitude in decimal degrees ONLY e g 2 564 in the range gt covering Kenya from 5 to 6 degrees see Other possibilities to get accurate location http www directionsmag com site latlon g converter for convertor tool to convert from degrees minutes seconds to decimal match can be done with pre existing datasets degrees With the accurate name of school and or health facility a Locate location on Google map and extract coordinates See description under the tab in matrix Get coordinates off Google maps Page 2 WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE Status Activity Activity Description Additional activity IMPORTANT from Drop Down What Choose the type of activities from drop down list Proceed to choose the appropriate description of the activities The activity need to be set before activity description The preset list of activities is Act Type Water Sanitation Hygiene NFIs Coordination Water Borehole NEW Solid Waste Hygiene Messages Distribution Coordination Sanitation Borehole REHAB CLTS Hygiene Promoters Trained Other Assessment Hygiene HWTS Vector Control Cholera Response Reporting NFIs O amp M Training Household Latrines Distribution of Aquatabs Monitoring Sh
6. allow well with Handpump REHAB Other Page 3 WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE Any changes to the list of activities should be run through the Wescoord Or use the comment field at end of table WHEN To track the response and be able to produce maps and make appropriate analysis of the data following fields are available Start and Finish data of project Status required field Completed On going Suspended Planned funded TO WHOM All numbers reported by individuals not families The beneficiaries reporting is the most crucial part as most of the indicators are on number of people received services Three groups are available e Number of individuals targeted e Number of individuals actually received assistance If not specified number of beneficiaries following standardized numbers might be used for analysis when appropriate e Water trucking 3 5 l p d e One shallow well serving 400 people e One rural borehole serving 5 000 people one urban borehole serving 3 500 people e One hand pump serving 250 people Disaggregated data Breakdown of the beneficiaries is possible and the fields are available e Gender and age Women men boys up to 5 and 6 18 and girls up to 5 and 6 18 OTHER DATA Contact details and reporting date Shortcuts and problems encountered when using the Cluster IM Tool a The Copy Paste command To enhance the efficiency please make yourself familiar with the copy paste Copy a
7. ces and who is funding it DEPENDENT on the other fields Also the ACTIVITY DESCRIPTION is dependent on the selected ACTIVI As default DISAGREGGATED DATA is hidden column S AB and the cost of the response how Please send your inputs to Cecilia and Charles at wescoord gmail com and call us for any help Cecili DEADLINE 29 AUGUST 2011 muc h WHO HOW MUCH Use the drop down list or provide the Indes Mo do not fill in EXAMPLE UNICEF ACTED 1 200 000 T a S yO Funded by Implementing Partner Project Cost KSh Province name of the organization in the field from drop down or type in agency if not on list Page 1 WESCOORD WASH KENYA DROUGHT RESPONSE 4W GUIDELINE Location Site Extra Latitude Information 2 456350 Province District Division Locations Site Longitude Rarieda Primary Moles School ee ace down list ONLY am T Dd ie an T J oo Where Use the populated drop down lists for entering the geographic areas down to location The drop down lists are dependent on previous choice so fill in order from left Location is free text please provide as detailed info as possible f ex name of schools health facilities or number in a location If available fill in accurate coordinates GPS location in the format decimal degrees Accurate project location with GPS Latitmde Longitude Provision of GPS coordinates in decimal degrees will greatly help to tie the project
8. nization and location Further filtering can be done in the PivotChart Filter Pane F G H l J K L M PivotChart Filter Pane x Active Fields on the B PivotChart 4000 Report Filter 3500 Status Ly Sector 7 uet 20d 3000 2500 E Laguna i Axis Fields Categories 79 2000 4 E Manila Organisation Ly get i E Rizal 1500 Legend Fields Series Location l ade a S Ss E Benguet 1000 500 Values OE a a i P Sum of Case load ACF MSF UNICEF Page 6
Download Pdf Manuals
Related Search