Pre-arrival CPIE Coordination and IM Core Skills E-Learning Modules, Part B

The e-learning component of the CPIE Coordination and IM Core Skills Training is composed of two parts: Part A and Part B. You are viewing Part B. In Part B, we have four additional modules with accompanying assignments to be completed and submitted.  Each module contains a practical, IM-based assignment. You must complete Part B, at the latest, by 13 June 2016. If you would like feedback on your exercises, please send your work by 8 June 2016. The purpose of the pre-arrival e-learning modules is to prepare you for the 5-day simulation.

 To qualify for the in-person portion of this course, you must complete and submit all assignments in both Part A and Part B.

If you are already comfortable with the IM components of these modules, you do not have to do the walkthrough or videos. Rather, you can directly go to the exercises.

Module 1 - Getting to know Excel

Basic structure and set up of Excel

Assignment: Complete the Module 1 Exercise

Please note: This module offers several lessons, walkthroughs, videos, and challenges which will help you learn the skillset required to complete the exercise at the end of the lesson.  If you feel you have already mastered the skills described in a particular lesson within this module, feel free to skip it

1.1 Getting Started

Lesson 1.1.A Navigate Excel

  • Components of a workbook
  • The difference between a worksheet and a workbook
  • Where the formula bar is located
  • What is a row, a column and a cell
  • Where the ribbon, quick bar and tabs are located

    1.1.A. Walkthrough
    1.1.A. Video (3:30)

     

     

Lesson 1.1.B Explore/customise your Excel Environment

  • Know how to customize your ribbon and quick access toolbar
  • Learn how to access basic tools and commands
  • Learn about the backstage view and options for saving, opening a file and sharing your documents

    1.1.B Walkthrough
    1.1.B Video (3:42)

     

     

Lesson 1.1.C Create and Open Workbooks

1.2 Cell Basics

Lesson 1.2.A: How to select cells, insert content, delete cell content; cut, copy and paste cells; drag and drop cells; fill cells using the fill handle.

1.2.A Walkthrough
1.2.A. Video (4:20)
 

Lesson 1.2.B: How to 'paste special; and 'paste values'

1.2.B Walkthrough
1.2.B Video (4:20)
 

 

Lesson 1.2.C How to paste a Word table into Excel

1.2.C Walkthrough
1.2.C Video
 

Try completing 1.2 Challenge to try out your new skills

1.3 Modifying columns, rows and cells

How to change row height and column width, insert and delete rows and columns, wrap text in a cell, and merge cells.

1.3 Walkthrough
1.3 Video 1 (3.49)



1.3 Video 2 (1.32)



Try completing 1.3 Challenge to try out your new skills

1.4 Freeze panes & 1.5 formatting cells

How to freeze rows and columns in your worksheet

Lesson 1.4.A Freeze the first row and the first column of your worksheet.

1.4 Walkthrough
1.4.A Video (Part 1, 1:23)

Lesson 1.4.B Freeze more than just the first column and first row of your worksheet

1.4.B Video (Part 2, 4:00)

Lesson 1.5

How to change the color and style of text and cells, align text, and apply special formatting to numbers and dates.

1.5 Walkthrough
1.5 Video (3:50)


Try completing 1.5 Challenge to try out your new skills

1.6 Sorting data

How to sort data to better view and organize the contents of your spreadsheet. Sorting is a common task that allows you to change or customize the order of your spreadsheet data.

1.6 Walkthrough
1.6 Video (4:08)
 


Try completing 1.7 Challenge to try out your new skills

1.8 Formatting tables

How to format your data as a table in Excel. Here are ten reasons why it is a good idea to format your data as a table.

Try completing 1.8 Challenge to try out your new skills

1.8 Walkthrough
1.9 Video

1.9 Saving

How to use the Save and Save As commands, save as an Excel 97-2003 compatible workbook, and save as a PDF. 

Try completing 1.9 Challenge to try out your new skills

1.9 Walkthrough
1.9 Video (3.21)



 

Module 1: Exercise

You have been provided with a very, ugly participant list in Word for this training: (ParticipantList_OLD_New_v6_Final_Draft.docx.docx). Using the skills learned in the various lessons of Module 1, follow the steps below to create a beautiful participant database in Excel.

Module 1 Exercise Video: watch this video if you would like to receive step-by-step guidance on how to complete the following exercise. 

  1. Open the provided participant list ParticipantList_OLD_New_v6_Final_Draft.docx.docx as well as a new Excel file
  2. Copy the contents from the Word document and paste the text values only into your new Excel file
  3. Adjust all column widths appropriately so that you can easily read all data in the cells (Hint: this does NOT have to be done one column at a time)
  4. Format your data as a table (Caution: make sure you have the correct Header Row)
  5. From the ‘Design’ tab, change the Table Style to your preferred style and color
  6. Add a column with the title ‘Sex’ and another column with the title ‘Skype’
  7. Rearrange all columns to the following order: Name, Sex, Country, Org, Role, Email, Skype (Hint: Make sure to watch 1.3 Video 2 from Lesson 1.3 for the easiest way to move columns containing data)
  8. Freeze the panes so that you can see the first column (Column A or ‘Name’) when you scroll to the right as well as the header row when you scroll down
  9. Apply a 2-level ‘Custom sort’ so that your data is sorted alphabetically first by ‘Country’ and then by ‘Name’
  10. Filter the data so that you are only viewing participants from UNICEF
  11. Save your file using an appropriate file name. It is always a good idea to add an inverted date at the end; for example: “FileName_2016-04-21”
  12. Send this document to Sara Lim ([email protected]).

Have you emailed your exercise to Sara Lim?

  • Yes I have!
  • Not yet

Module 2 - Preparing your Excel file

Preparing your Excel files

Assignment: Complete the Module 2 Exercise outlined at the end of this document.

Please note: This module contains several lessons, walkthroughs, videos, and challenges which will help you learn the skillset required to complete the Exercise at the end of the lesson.  If you feel you have already mastered the skills described in a particular lesson within this module, feel free to skip it

2.1 Working with basic functions

How to insert common functions in your worksheet by utilizing the AutoSum and Insert Functions commands. You will also become familiar with how to search and find various functions, including exploring Excel's Functions Library.

2.1 Walkthrough
2.2 Video (5:36)


Try completing 2.1 Challenge to try out your new skills

2.2 Creating simple formulas

How to create simple formulas in Excel to add, subtract, multiply and divide values in a workbook. You will learn the various ways you can use cell references to make working with formulas easier and more efficient.

2.2 Walkthrough
2.2 Video (3:46)


Try completing 2.2 Challenge to try out your new skills

2.3 Worksheet basics

How to organize your workbook, rename and color code worksheet tabs; insert, delete, move and copy worksheets

2.3 Walkthrough
2.3 Video (3:24)


Try completing 2.3 Challenge to try out your new skills

2.4 Lock/Unlock cells, protect worksheets and workbooks

2.4.A How to (un)lock cells and (un)protect a worksheet to in order to protect your formulas, etc. while still allowing user data entry.

2.4.A Walkthrough
2.4.A Video


2.3.B How to protect a workbook so that users cannot insert, delete, rename, move, copy, hide or unhide worksheets

2.4.B Walkthrough
2.4.B Video


Try completing 2.4 Challenge to try out your new skills (Note: for this challenge, download the Excel file and practice locking and unlocking the cell with the formula and protecting/unprotecting the worksheet.)

2.5 Dropdown lists

How to create drop down lists by using a comma-delimited list, a cell range and a named range to define the option in a drop-down list. You will also learn how to manage dropdown lists.

2.5.A How to use comma-delimited list to create a dropdown list

2.5.A Walkthrough
2.5.A Video (1:01)



2.5.B How to use a cell range and named range to create a dropdown list.

2.5.B Video (4:37)


2.5.C How to create input and error messages for your dropdown list when people have not entered correct value (as defined by the dropbox list).

2.5.C Video (2:47)


2.5.D How to prevent changes to your drop-down list data. You will review how to hide the columns, rows or the entire worksheet that contains the dropdown list data. Learn how to lock and password protect the cells on the worksheet and the entire worksheet.

2.5.D Video (6:20)

Module 2: Exercise

The child protection coordination group needs to start collecting information from partner agencies regarding the number of beneficiaries their activities are currently reaching.  You have been provided with a simple Excel response monitoring tool, but you realize that the tool is missing formulas for automatic calculations, data validation/dropdown lists to ensure correct data entry and protection measures to ensure formulas and worksheets are not deleted.  Using the skills learned in the various lessons of Module 2, follow the steps below to improve the Child protection coordination group’s response monitoring tool.

Module 2 Exercise Video: watch this video if you would like to receive step-by-step guidance on how to complete the following exercise

  1. Open the Excel file ‘Partner Response Monitoring Tool'
  2. Format the data as a table (Caution: make sure you have the correct Header Row)
  3. Add a ‘Total Beneficiaries’ column at the end of the table and add a formula summing the male and female beneficiaries reached
  4. Rename the worksheet ‘Data’
  5. Use data validation to create a drop down list for the ‘Organization’ column. Do this by:
    1. Create a new worksheet called ‘Lists’
    2. Create a list including the two existing organizations (World Vision and Plan International); add UNICEF and Save the Children to the list
    3. Format the Organization list as a table
    4. Open the Name Manager (on the Formulas tab; or with the shortcut key Ctrl + F3)
    5. Create a new name, call it ‘Org’ and define where the named range refers to (e.g. A2:A5)
    6. Open the Data Validation (on the Data tab), select ‘List’ as the validation criteria and for the ‘Source’ type “=Org” (or paste it by using F3)
    7. Return to your Org list and sort it in alphabetical order
  6. At your child protection coordination group meeting, you realize that International Rescue Committee has arrived to the emergency; add them to the dropdown list options
  7. Repeat all of Step 5 and create a dropdown list for the ‘Activity’ column; use whatever activity options you would like
  8. Before sending this file to partners you want to make sure it’s properly protected:
    1. Unlock the cells where you would like partners to be able to input data (Hint: do NOT unlock the ‘Total Beneficiaries’ cells as this contains a formula you want to protect)
    2. Protect the worksheet (Review tab)
    3. Hide the ‘Lists’ worksheet
    4. Protect the workbook
  9. Practice inputting fake data
  10. Save the file with an appropriate file name and send to Sara Lim ([email protected]).

Have you emailed your exercise to Sara Lim?

  • Yes I have!
  • Not yet

Module 3 - Cleaning and Analyzing

Cleaning and Analyzing your Excel files

Assignment: Complete the Module 3 Exercise

Please note: This module offers several lessons, walkthroughs, videos, and challenges which will help you learn the skillset required to complete the exercise at the end of the lesson.  If you feel you have already mastered the skills described in a particular lesson within this module, feel free to skip it

3.1 Find and replace

How to use ‘Find and Replace’ feature to quickly find specific text and replace it with other text.

3.1 Walkthrough
3.2 Video (1:22) 

3.2 Remove duplicates

How to remove duplicate values in a column.

3.2 Walkthrough
3.2 Video (0:24)

3.3 Pivot Tables

How to create and use PivotTables. PivotTables allow you to easily summarize and manipulate your data in order to analyze it.

Challenge: Try completing 3.3 Challenge to try out your new skills

3.3. Walkthrough (1:22)

3.3. Video (Part 1) (4:39)


3.3 Video (Part 2) (3:20)

3.4 Creating a chart

3.4.A How to create a static chart

3.4.A Walkthrough
3.4.A Video

3.4.B How to create a pivot chart (see 3.3 Challenge to practice making a pivot chart)

3.4.B Walkthrough
3.3.B Video

3.5 Data visualization

These fantastic presentations show how to very simply and effectively visualize data in charts and tables.

1. Remove to Improve
2. Pie Chart Edition
3. Clear Off the Table

Module 3: Exercise

OCHA informs you that they have decided to collect weekly updates from all Coordination groups on the progress of their top four core activities (aka: response plan indicators or monitoring framework) in terms of number of beneficiaries targeted and reached. Your top four core activities are:

  • Provision of psychosocial activities for children in child friendly spaces
  • Delivery of Awareness raising sessions on Child Protection risks
  • Community reintegration support for children released from armed forces and/or armed groups
  • Training of social workers and NGOs staffs on psychosocial support

You have compiled and have been updating a beautiful excel worksheet to track partners’ response. Your task is to prepare an analysis tool that not only displays this current information but can also be updated easily for weekly analysis and reporting.  Using the skills learned in the various lessons of Module 3, follow the steps below to create this analysis tool.

Module 3 Exercise Video: watch this video if you would like to receive step-by-step guidance on how to complete the following exercise

  1. Open the Excel file ‘CP_3W_2016-02-29_v2' in your Module 3 folder
  2. Insert a PivotTable
  3. From the PivotTable Field List, move:
    1. The ‘Activity’ field to the ‘ROWS’ area
    2. The TOTAL TARGETED field to the ‘VALUES’ area
    3. The TOTAL REACHED field to the ‘VALUES’ area

NOTE: Ensure that the targeted/reached fields are displayed as ‘Sum of’ and NOT ‘Count of’

  1. Filter the PivotTable report by the first of the core activities for which you will be reporting to OCHA (“Provision of psychosocial activities for children in child friendly spaces”)
  2. Change the style to one of your preference
  3. Remove the ‘Grand Total’ row (from Design tab, Layout group)
  4. Create a Pivot bar chart for your data
  5. Format the chart to be visually appealing
  6. Save your chart as a Template
  7. Create a PivotTable for each of the other three activities on the same worksheet (leave space for the charts)
  8. Create PivotCharts for each of the activities (Hint: use the template you created from step 9)
  9. Arrange the tables and charts on the page to be visually appealing and easy to report
  10. Save the file with an appropriate file name and send to Sara Lim ([email protected]).

Have you sent your file to Sara Lim?

  • Yes I have!
  • Not yet

Module 4 - Information sharing (due by 13 June 2015)

Information Sharing & a little bit of ard-core Excel

Assignment: Complete the Module 4 Exercises outlines at the end of this document.

Please note: This module contains several lessons, walkthroughs, videos, and challenges which will help you learn the skillset required to complete the Exercise at the end of the lesson.  If you feel you have already mastered the skills described in a particular lesson within this module, feel free to skip it

4.1 Skype – Setting up Skype

It only takes a few minutes to set up Skype for the first time. The video shows how to setup Skype, by creating an account on the Skype website and downloading and installing the Skype software.

Video (3:56)

4.2 Skype – Adding Contacts

This short video shows how to add a contact on Skype for Windows Desktop.

Video (0:37)

4.3 DropBox – Quick Start

A Dropbox tutorial for 2015 highlighting all of the primary features of using the web version of Dropbox

Video (18:09)

4.4 Mailchimp – Beginner’s Tutorial

MailChimp is a fantastic free way to send email newsletters to groups of people. This tutorial shows you how to set up an account, import existing contacts, send an email campaign, see email reports, add subscribers manually and create a signup form.

Video (22:02)

4.5 Excel - Functions

The part of a function
Working with arguments
Creating a function
The Function Library
The Insert Function command

Walkthough
Video (5:16)


Try completing this challenge to try out your new skills

4.6 Excel - VLOOKUP Function

What exactly is VLOOKUP?

Walkthrough
Video (4:32)

Module 4: Exercise

Using the skills learned in the various lessons of Module 4, follow the steps below to share information through various channels.

Skype

  1. If you don’t have Skype, download the program and sign up for a free Skype Account: http://www.skype.com/
  1. Search Skype for Sara Lim (sara.lim) from Comoros and request to add Sara Lim to your Contacts, and ask the burning question related to CPiE you were interested in for ages. If you are already on Skype and Sara is one of your contact, no need to do it again.
  2. Create a new group called “Core Skills Training Coordination Group” and add Sara Lim

Dropbox

  1. If you don’t have an account, sign up for a free Dropbox Account: https://www.dropbox.com/.
  2. Establish a new folder (“Core Skills Training Resources”).
  3. Upload the “UNICEF Cluster Guidance to Country Offices” document.
  4. Share a link to the document with Sara Lim ([email protected]).

MailChimp

  1. Sign up for a free MailChimp Account: http://mailchimp.com/.
  2. Complete the background information and confirm your account.
  3. Set up a contact list (“Lists”) and import the contact list from Exercise 1 into MailChimp
  4. Set up an email (“Campaigns”), include a “Child Protection AoR Logo” as a header, a link to the “UNICEF Cluster Guidance to Country Offices”, and send the email to the complete contact list.

Excel – Exercising VLOOKUP Function

You have just arrived in a country - which is facing internal displacement of its population - on a surge to cover both CPiE Coordinator and IM functions. You have been provided with 2 datasets:

  • “CPiE_Operational_Presence_Data” that shows the number of CPiE organizations operational in the country by location
  • “Displacement_Data” that shows the number of displaced children in the country by location

Your first task is to assess if the organizations are operational in locations that are the most affected by displacement.

Module 4 Exercise Video: watch this video if you would like to receive step-by-step guidance on how to complete the following exercise

  1. Open both Excel files “CPiE_Operational_Presence_Data” and “Displacement_Data
  2. Copy the table “CPiE_Operational_Presence_Data” into a new worksheet of the “Displacement_Data” workbook
  3. Using the VLOOKUP function, bring over the number of organizations into the displacement data table (note that some locations have the same name…)
  4. Save the file with an appropriate file name and send to Sara Lim ([email protected]). In the email, write one of these 2 sentences to Sara: “I think organizations are well responding to the emergency” OR “I think there is some coordination missing…”

Have you sent your file to Sara Lim?

  • Yes I have!
  • Not yet