Monitoring with Excel

Show video

Hello and welcome to Excel for Monitoring,  presented by energypedia consult incooperation   with ICT4D.AT, the Austrian Network for  Information and Communication Technologies   for development. This video will support  you in gaining Excel skills for monitoring.   You will learn how to set up a spreadsheet-based  monitoring structure for your development program.   When dealing with monitoring data in the context  of development programs, Microsoft Excel has   a lot to offer. Excel gives a structured  format for convenient data entry and input.   It also enables quick data analysis by  applying basic formulas or creating charts.   Further, you can create customized dashboards  or overviews with visualizations of datasets,   thereby illustrating indicator progress.  In practice, as you might know, monitoring  

officers are often confronted with large Excel  spreadsheets with lots of data which are often   sent to the entire team by email with a request  to update them. Such spreadsheets can be confusing   and there may be several different versions  of a file being updated on numerous ends.   We believe that M E Systems should be relevant  and useful, with data structured in a way that   informs decision making and enables projects  to prove and improve their impact. All in all,   in order to fully make use of what Excel has to  offer for results-based monitoring, it requires   evolving from simple spreadsheets to a real  tool. In order to achieve this, we believe that  

monitoring officers and those in similar positions  must have enough ownership over an understanding   of the monitoring tools they are using. That's  why we created this online tutorial. Our team   at energypedia consult is made up of IT and M&E  experts, and for this course we've drawn on our   experience gained in over 10 years of developing  and setting up Excel tools and web-based   monitoring tools for our clients in order to  improve their results-based monitoring processes.   In this online training ,we will present  basic principles and formulas as well as   more intermediate features paired with  examples from the field that will guide   you in using Excel as an M&E tool. We want you  to know how and when to apply formulas in the   practical context of development cooperation.  Part of our philosophy is that we don't use   Macro,s Visual Basic or Power BI. We won't cover  more in-depth statistical analysis here either,   so you should be able to follow along with  the most basic Excel skills. If you do not  

yet know how to enter functions and formulas and  do simple formatting, we recommend that you first   become familiar with Excel by watching some of  the many free basic tutorials out there online.   This course is self-paced. In addition to  following along with the video tutorial here   you're invited to actively apply the newly gained  skills in our exercise file. Both the exercise   and the solution file can be downloaded  by following the link in the description.   There you'll also find the links to a  sample file of a real monitoring tool   which we'll be presenting here, as well as well  as a collection of the most useful functions in   English, German, French (and Spanish). Please  feel free to pause this tutorial at any time   to do the exercises on your own and come back  later. If any questions remain unanswered or  

come up for you while doing the exercises  on your own or applying them in practice,   we invite you to join our live Q&A sessions.  Energypedia consult hosts the quarterly free   webinar "Introduction to results monitoring with  Excel", which you can register for on our website   following the link here in the slide (or in the  description below). In the tutorial for Module 1,   you will learn some basic skills that we'll be  referring to later in the course so that we've   got everyone on the same page. We'll show you  how to use tables to sort, filter and calculate,   how to create relative absolute and mixed  cell links and how to name areas of cells.  

If you already feel confident about these basics  feel free to skip head to Module 2. This is the   exercise file that you can also download  by following the link in the description.   First I'm going to show you a very practical  feature which is how to format data as a table,   create a total row and then filter the data.  Assume you have a project running that involves  

an initiative where you're offering for example  sexual and reproductive health education and you   have a certain number of people that you want to  reach in each village. Each village belongs to a   different county of the respective country. This  is a fictional name of a fictual list of villages   and fictional numbers as well and these are the  number of people that you have reached in each   Village so far now first of all to format this  data as a table you will select all of the data   under the Home tab go here to format as a  table and select any style that you'd like   I'm going to go with a light style here I'll  leave the Box my table has headers checked and now you can see that there I have  these little arrows pointing down   for each column which will allow me to sort  and filter as well first now we're going to   calculate these empty columns though so that  I can show you some more features later so   in order to calculate the number of people  remaining we're going to take the people to   be reached which is our Target minus the current  value and now when I press enter it will give us   the results for the entire column I don't have to  copy and paste the formula and pull it down now   for the percent of people reached we're going to  divide the number of people reached by the Target press enter and the cell has now been formatted as  a general cell and if we want it to say percentage   we can go here and select the percent style or use  the shortcut Ctrl shift and percent if we want to   create a total row at the bottom we click on any  cell within our table go to the table design tab   that will then appear up here and select total row  now it will automatically generate a total but not   only that for you you can use various functions  not only sum also average or maximum or minimum   let's select average here because that makes more  sense more sense for percentage and then when you   select the other cells you can actually do  that for each column so here maybe I want   the the sum of total people remaining and  here the sum of total people to be reached now the great thing about this is that the  total row also works and adapts itself to   any filter that we apply so in the exercise  here we're supposed to set up a filter that   shows us all Villages where more than  50 of the target value has been reached so we will select number filters here  there are many options to choose from   we want the greater than option um to create  this custom filter we will say greater than 0.5  

because that is the equivalent of 50 percent click  ok and now anything that's 50 or below has been   not removed from the table it's just hidden you  can tell this by looking at the color of the rows   here on the left they're now blue and not black  and also you might be able to tell that instead   of a small Arrow we now have a small arrow and  a funnel next to it and so that way when you're   looking at in somebody's a new table imagine  someone sent you a file and uh and they've   formatted as a table just be aware if the funnel  is present that means there's an active filter   um which you can also remove  clear filter from percent reached   and we could also apply several  filters at the same time so for example   if it's just a text filter you can even use these  boxes here let's say I just want it to show me all   places where we've reached more than 50  percent of our Target value in County one and uh here's our result so the  data that is filtered is not deleted   and another thing you can take away from this  is that the total row adapts to the filter   it is a very practical feature that also  has some disadvantages and sometimes it   makes more sense for you to actually put your  own functions into Excel to understand what   is happening and I will show you some cases  where that applies in our further modules next   let's have a look at the difference between  relative absolute and mixed cell references   here our scenario is such that we have  maybe conducted a survey in three countries   um amongst survey respondents of different  age groups and now we want to know how many   survey respondents Per County were reached  of course we can use the autosum shortcut by   pressing alt shift and the equal sign and Excel  will automatically know which areas to sum up and we'll create the total for demonstration  purposes I will manually select these cells   to be added up because what I want to show you  is that when I create a formula with cell links   that look like this then they contain relative  cell relations so that means if this formula   is then copied down the cell values are  automatically adjusted to the new position   by going into the formula bar you can  check that everything went down by one row   now sometimes this is not desired and instead you  want the formula to point at one particular cell   in these cases we need to fix our formula by using  the dollar sign in this example here we would like   to calculate the dollar equivalent based on the  exchange rate so how many dollars are 23 euros   in order to calculate this for the first row I  multiply the exchange rate times the Euro value so 23 Euros are 28.29 let's see what happens if I pull  the formula down to copy and paste   32 Euros or zero dollars so what happened  here that's not correct obviously is that   because the formula contained relative cell  links it pulled this box down by one row and   this box down by around one row as well we want  this to happen because we want to know how much   is the equivalent of 32 Euros but we would like  this cell to be fixed so by pressing F4 and   then copying and pasting it down I can check  down here is this formula correct yes it is   so what we saw here is that it's really  important to correct the first Formula   before moving it down this little box here tells  you what will happen if you press on if you press   F4 several times if you press F4 for the first  time the column and the row do not change when   copied because the dollar sign appears in front of  the row and the column when you press F4 another   time the dollar sign is only in front of the row  so the row does not change when copied and when F4   is pressed a third time the column does not change  when copied so why might you need that there are   certain cases where mixed cell referencing is  required and this third exercise where we're   supposed to calculate the percentage of survey  participants per age group for each County   is referring back to exercise number one  okay so this is a bit small I'm just going to   cut out and paste this table up here so that I  can zoom in and it'll be easier for you to see   all right so to calculate the percentage of  survey participants aged 18 to 35 I will divide   the total amount of participants aged 18 to 35  by the total amount of participants in County a   and I can format as percent and now let's  see what happens if I copy and paste the   formula to the right we get an error message  so what happened here Excel moved both of these   selected cells to the right  and for our first parameter   this was actually correct however we want this  value divided by the total of survey participants   in County one so we're going to go back here  and just see what happens if I fix the total uh right so let's copy and paste it and to the  side and here we can check yeah it does add up to   100 so this worked it is still dividing the amount  of survey participants in this age group by the   total amount of survey participants in this County  now what happens if I copy and paste the formula   down one row it tells me that there were 128  percent of survey respondents in this age group   that can't be correct so what happened here here  we're still dividing it by the total in County one   but we want this number divided by the total in  County 2. so let's go back to our original formula   and think about whether we need the column or  the row to be fixed now I don't want the column   to shift to the right or to the left when I move  the formula because the total is always going to   be in column F but I do want it to move down  when I shift the formula down so I am going to remove the dollar sign in front  of the row copy and paste it down   and now see if it worked and it did so this total  column I just left here so that we could double   check whether everything worked it is at 100 and  here we can say see that it's dividing this age   group by the total in County three the last small  topic that I'd like to mention in module one is   naming areas of cells Excel allows you to find  names to Define names for cells or areas of cells   and the cells that are defined as names  are then equivalent to Absolute references   so to define a new name you have to first  select the cells or the areas of cells and   then the name can be inserted here on the top  left in the name box let's call this one survey   so now when you select these cells you will see  they are called survey and if you had several of   these tables in one sheet that you were referring  to on another sheet that could make things easier   rather than always referring to the column  and row areas so when formatting as a table   by the way this actually happens  automatically if I select all the   values of the table you will see  here this one's called table two here in module 2 we're going to have a look at  several aspects that are relevant to data input   and management traditionally data input is done by  hand and it's useful for entering text or smaller   amounts of numeric data other forms of data input  include data import or linking to external sources   and one thing that all of these options  have in common is that you have to check   the consistency of the data not only the values  themselves but also the data format must be   kept consistent if you want to assure that the  data processing will yield the expected result   generally with collaborative monitoring  all data should be structured in a manner   that allows the user to immediately  see where to enter what kind of data   another important point is to make sure that data  is only entered once if the same data needs to be   displayed in multiple places it is best to link  between data cells instead of creating redundant   multiple entries of the same data so in this  tutorial for module 2 we will show you how to   set up data validation using different criteria  and learn how to protect cells and spreadsheets   we will also demonstrate how to link to data  from different Excel sources and how to import   data from external sources imagine you want other  people to enter data into your monitoring tool and   to avoid typos or potential errors you want  them to only enter data in a certain format   this is what we're going to do in exercise one  we're going to look at different data validation   settings that will only allow users to enter  dates numbers or data from a drop down list   the default setting in Excel is that any value  is allowed in a Cell it is possible to allow   only certain values to be entered into cells  and we can do this by going to the tab data   data tools select data validation and  under validation criteria you can allow   a date time text length any value whole numbers  here we're going to choose to allow only dates   and now we could enter the  start date and the end date here   um in this dialog box but that would be very  hidden so you or someone less familiar with Excel   might want to change something afterwards or  update the tool a year from now and then not   know where to find these settings so our Excel  philosophy is that these parameters should always   be in cells because then the tool is easier to  maintain here you can see that we've already prepared something in a very light font let  me change the font so that you can see it here um so the start date is in this cell F7 which I will just select here  and the end date is in cell G7   now when I click OK I will  confirm now let's re-enter here we get this error message the value doesn't  match the data validation restrictions defined   for this cell you can also Define  the error alert so by going again to   data validation and selecting the tab error  alert I could create an error message that says   something like the date you have  entered is not in the selected range or   hello please only enter dates between January  2023 and December 2023 and I could also Define   an input message so that when somebody selects  the cell they will automatically be told what to   what is allowed so here we're supposed to  allow only numbers and it's the same procedure   um here under data validation settings  I will select only whole numbers between   minimum of 1 and a maximum of 99 so for example  you want people to only be entering ages and so   um anything between 0 and 99 or 1  and 99 is uh realistic and nobody   can then accidentally type in a hundred or a  thousand instead of 10. so if I want to enter a  

higher value or I accidentally type A too too much  then I get this error message again but anything   within the range is allowed Excel also allows  you to enter values by means of a drop down menu   a use case example of this would be to create a  drop down menu that enables the user to select a   weekday this saves time since manual data entry  isn't necessary anymore and it also ensures that   only values from the list of allowed values  are entered that way the tool remains lean   and clean and typos from users are prevented so  we'll go back to data validation and choose the   criteria list and select a source this is our  source of four options and now the reason why   this says list here and not the cell range from  E11 to e14 is that prior to demonstrating this   here in the name field we've actually already  named these four options list and this is a   use case example of what I was demonstrating  briefly at the end of the module one tutorial   um where it can just come in handy to have a  certain cell ranges named using the name field   we might imagine that you were wanting to create  different drop down boxes so you might have a list   that says that's called weekdays and a list that  says um gender to choose from so here we can now   select any of these four options one thing to pay  attention to is that if you wish to add another   option this will not automatically appear in the  drop down list so you would either have to go back   to the data validation criteria window and select  this as the new source or if your options don't   necessarily have to be in a consecutive order  you could insert a cell in the middle of the list   let's call this option 2A and this will then  automatically be added to your drop down menu so I'm going to just undo what we did so that  we can go back to see what exercise two is   all about okay so here it says that next we're  going to protect cells in this spreadsheet so   sometimes it can be advisable to restrict certain  working steps to more experienced users and block   some cells for Less experienced users by using the  protect feature which is here under the review tab   to prevent users responsible for data input  from accidentally changing the structure or even   formulas in your sheet you can protect either the  entire worksheet or individual cells so here the   task is to protect all cells except for these gray  ones so the default setting is that when I protect   this worksheet all cells are going to be locked  and so this is why when you go to any cell in your   worksheet right click format cells and go to hear  the tab protection you will see that it is locked   by default of course they're currently not locked  this only applies if you click protect sheet or   a protect workbook and now if you want certain  cells to be exempt you will first right click the   Exempted cells or the cells to be Exempted and  then we'll unlock we'll uncheck the locked box um I'm going to do that for all three light gray  cells here and now when I click on protect sheet   I could also set a password now if I try and  enter data into any cell let's say I'm going   to double click I will get this message if  I try and enter data in the light gray cells   it's still possible though a big advantage that  Excel has to offer is that it allows you to link   different spreadsheets with each other that way  you can summarize data from subordinated sheets   such as individual Pages for each  component of a project on an overview sheet   at the same time the possibility of linking cells  across different sheets helps avoid redundancies   as information from one sheet is imported  to another sheet and doesn't need to be   re-entered there here in exercise one I'm going  to demonstrate how to link to a cell from another   spreadsheet within the same file here all the  way to the right we have the tab monitoring data   let me show you there's a table that we're  just going to draw some information from   um imagine that we were calculating  and we needed the expenses here   in region one so we are going to go back to our  tab data importing we put in the equal sign head   over to monitoring data select the cell that  you would like to refer to and press enter and now you can see that in simple quotation  marks it is referring to the tab monitoring   data and to the respective cell there in the same  way that you can link cells within an Excel sheet   or between different spreadsheets of the same file  links can also be established between cells from   different Excel files from the same computer  or shared network drive for example a common   server or a Dropbox this is particularly useful  for projects where individual project components   maintain their own monitoring tool each of their  subordinated sheets can be linked to the overall   project manager's overview sheet where the data  is then summarized or perhaps you have an Excel   system with one master file and several data entry  files and then of course you can design these data   entry files to look more like questionnaires using  sheet protection and data validation so that users   can only enter specific data again this way  you help avoid redundancies as information   from one sheet is imported and doesn't have  to be re-entered so in this exercise I will   show you how to link to cells in a separate Excel  file I'm now going to open a new blank worksheet   here under file new blank workbook suppose I would  like to refer to this data from the other file   and have it appear here in this cell of  the master file in our new file I will use   cell A1 as an example and I will put in the  value 1000 and now I will link to the cell   in the master file by using the equal sign  moving to the other window and selecting A1   you can already see the formula up here book  one sheet 1 is the name because I haven't   saved this file under a different name it  is automatically called book one sheet1   and then referring to a cell and in this case  you can see the cell is automatically fixed   with the dollar signs and if both files are open  and I'll now save and rename this new file I will   also change it will also change the name in the  formula of the receiving file so let me go ahead   and save this file under a new name I'm going to  call it sending file and now when I switch back to   our exercise file you can see that it has  automatically adopted the new file name   the same is true if I rename this sheet here  for example so the default name is sheet1   if I right click and rename it to sending  data go back to our exercise file it's also   adopted the new sheet name however if the file  from which the data is pulled is closed and   someone else renames the file or moves it to  another folder then the cell relation between   the files will be broken so this only works if  both of these files are open at the same time   otherwise the receiving file wouldn't be  able to find the sending file anymore and   then you'd have a problem so this is the  weakness of these types of excel systems   it's still possible of course there's workarounds  you can repair it if this happens and connect the   files again or very clearly communicate not to  rename the interlinked Excel files and let other   users know that the file's location may not  be changed and to only enter and update data   now let's look at semi-automatic data import so  besides the completely automatic way of data entry   data can also be imported semi-automatically and  the way to do this is to create a separate sheet   within your Excel file which you would then call  the transfer sheet the same way that we did here   all the way on the far end with monitoring  data which will then contain the data from   your external sources this could for example be  data from the web for example statistics from   the oecd or wash data which we will have a look  at soon or another example would be data from   your accounting software so if Financial aspects  need to be regarded in your monitoring such as   cost efficiency or the value for money approach  it's rather easy to integrate financial data from   accounting software into an Excel based monitoring  tool you just save the desired data as in Excel or   CSV file select and copy the data in that file and  then create an extra sheet in your monitoring tool   which you will then call the transfer sheet and  paste the data and now your sheet with financial   data or statistics from the internet can be linked  to your current project values and expenditures   and I'm just going to demonstrate this using  some statistical data from this wash website here   I will download the household World file  open it here so here's data from The Joint   monitoring program for water supply sanitation  and hygiene and let's just say I need this data   to refer to in my calculations so I'm going to just copy this for demonstration purposes   now when you go back to your Excel file  you open a new you start a new sheet   you rename it we'll just call it external data and  paste it and my main point is just that when you   do copy and paste external data always choose the  same place in your external data or transfer sheet   file so if I was to copy and paste it in A1 and  have the entire table start here then if I go back   a year from now to get the data for year 2021 and  I want my cells to still be referring to the same   um spots in this table just make sure to  always paste it in the same place again   now that we've looked at some aspects  regarding raw data entry it is time for   data processing to begin in order to obtain  the desired information regarding progress as   well as results and impacts of a project the  enter data will be modified and recombined   in this module number three we will introduce  you to some of the most important Excel functions   for obtaining information regarding  progress results and project impact   here we're going to have a look at the functions  related to dates such as today year and month the   function today offers many more possibilities than  the simple output of today's date just like all   other Excel functions it is mainly the possibility  of combining functions with other functions that   raises the usefulness of this function far beyond  the use of the individual function itself imagine   you're involved in a project and want to know how  many days are left until the end of the project   term in order to assess whether it's realistic  to achieve a certain result by then for example   or perhaps you're responsible for financial  monitoring you have a set budget and are asking   yourself how much do we need to spend on average  until the end of the year to fully use up that   budget in order to calculate with dates the first  step is to let Excel generate your current date   we do this by selecting a cell  and using the function today   so we're going to open the function with the  equal sign as always enter the function today   and leave the brackets empty press return  to confirm and it will give you today's   date in the day month year sequence  regardless of when you open the workbook   for other country settings the default format may  be month day year so when you do this exercise on   your own of course it'll show you a different  date than this one here now if you want to   know how many days are left until the end of the  year start a formula by putting in the equal sign then select this day which is New Year's  Eve the end of the year minus today's date   press enter to confirm and the difference is  now the amount of days until New Year's Eve   once again we're calculating with  values that are already in cells   and of course if this was bothering you you  could make the font White um but again we   recommend keeping things visible so that you  could then next year change the date to 2024. now if you don't want to calculate with days  just with months you can use the formula month   type in the equal sign formula month  choose the date in this cell B18 close the brackets and it and press enter  and there it'll give you the month of the   selected date if you grab the cell in  the lower right corner and just move   it down you can copy and paste it to other  rows now if you change the date you'll see the month changes as well the same can  be done for year for the formula year which then returns the year of the selected date   once again I can change the year and it  will automatically update here as well   now for exercise 2B let's imagine you have a table   with a column heading balance and  then the current month and year   and you have a list of data below your heading and  then the next month you receive new data and want   the headline to change accordingly ideally this  would happen automatically rather than manually   in order to minimize the amount of Maintenance  work you have to put into your Excel tool   so instead of having to remember to change the  heading once the next month comes around we will   create a formula that does this for us I can  add text to a formula by adding quotation marks   now for each new element of the formula we need to  add an and sign next we want the month and then in   quotation marks again the slash because the slash  is going to be static that's never going to change   we add another and sign and select the year  that we'd like this formula to refer to   we press enter and here we have a Dynamic  cell contents that will change when the next   month rolls around let me change this to April  and here you can see that it is automatically   updated now if you've been paying attention in  the last modules you will notice that we have   some text in a formula and that's something that  we've said we want to avoid because uh anybody who   does not know the exact setup of a formula like  this might accidentally remove some quotation   marks if they wanted to change the word balance  to budget for example and so again it's best to   use text that is already in cells which we've  prepared here so instead of the word balance   we're going to refer to the cell e24 that contains  the word budget and we will want an empty space   between the word budget and our date  I'm just going to leave the colon away   um so I added an and sign and  again the upper quotation marks for   whatever is static in the formula and then just  a space in between and another quotation mark   to close that static area the and sign for  the next element which is still the month   the end sign for the static element which is the  Slash and then again the and sign for the year   and now if I wanted to call this balance  again it would automatically update as   well we have a very clean and lean formula  that doesn't have any text in the cells   in this next sheet we will be covering  the functions if some if and count if   in the first exercise compare the  actual value with the threshold value   you're supposed to generate a formula that will  display the words over 100 if the actual value is   above the threshold and below 100 if it is below  in practice for example this allows you to trigger   an Excel cell to make a statement on whether  the expenditures of a project exceed the planned   budget or not I'm going to open the formula if  and here in this window it already tells us what   the function does if checks whether a condition  is met and returns one value if true and another   value is false and now I open the brackets and  first it is asking us for The Logical test so   we're testing whether D9 is larger than E9  and if this is true semicolon value if true   I want it to return the words over 100 and if it's  false I want it to return the words below 100.   so this needs to be in quotation marks here we go of course in this example it's not  hard for you to decide whether this value is   above or below 100 but if you have an entire list  it gets more complex and then of course a formula   that calculates lots of data for you will support  your work and as we've already said we want to   create a tool that's easy to manage so what if  the threshold value changes next year say it's   raised to 110 and our function still Returns  the value below 100 well that would be true   but it would look strange because the threshold  value would be at 110. so as always we've already   prepared something here in these light gray and  this light gray font it says below and over 100   and now I can also replace the text in the formula  with these cells by just referring to these cells it's already a bit better but still not optimal  because now we would still manually have to   change this field to below 110 and over 110  of our threshold value changes it would be   even better if in these fields we  had a formula that automatically   adapted itself to whatever threshold value we  put into this cell so back when we worked with   dates we learned that we can connect content  like words and cells with the and sign here   so we're going to create a formula with the equal  sign where the word below is in quotation marks   then we add the and sign and refer to the  threshold value actually I need a space in here and the same we can do over here it's  going to say over and the threshold value   so now I can I can raise the threshold value   and it'll automatically change these cells  here which this formula here is referring to   exercise B is similar but instead of above  or below the threshold we want it to say   yes if it is larger or equal to the threshold  value otherwise it should say no so here we've   prepared our text in our cells and the function  here is if e d11 is larger or equal to E11   it will say yes otherwise it  will say no so no 40 is not greater than 150 so this threshold value cell you  can tell here is referring to this threshold value   we only have to enter it once let's go back  to 100 so what if what if it said a hundred   equal it says yes so everything worked next we're  going to look at the function sum if that means I   want Excel to create the sum of a set of values  if a certain condition or Criterion is fulfilled   sumif searches the specified area for the given  search Criterion and then adds up the numbers   from the sum area next to it this allows you for  example to add up how many people were reached   in each of these regions this could be the number  of people trained or the number of beneficiaries   here we called it number of people reached and  you're also spending money in these regions   and now you have a list where each row  is attributed to some region but it's not   sorted by region and in practice of course of  course this list could be much longer and now   we want to know for each region how many people  are connected and how much money you've spent   now if you paid attention in module 1 you might  know that there's an easy way to solve this   by formatting as a table and  then just creating a filter to show me all region all rows with for  example the region of Africa and then   creating a total row that will just sum up  the values then I could copy or just manually insert the values in this list now the problem is though that um if these values  were to change it wouldn't automatically adjust   here in our results but say that the number  of people reached increases with time because   we're still working in certain it's not a  project that's completed yet our expenses   are raised as well we would want that to  be reflected here in the cells that are   displaying the sums for each region so that's  the disadvantage of using the format as a table   function and that is why in this case we're  going to actually apply the formula sum if   so when you type in the formula sum if the  first parameter that it's going to ask you   for is the range this is the area where Excel  is supposed to look for the search Criterion   it is the range of cells to be filtered that's an  easy way to remember it see if we were using the   format as a table way of solving this problem then  we would have easily thought of just filtering   the region column so that it  will only display Africa here   next then after the the comma or for you for you  it might be a semicolon or a comma depending on   which country you're in the next parameter is the  search Criterion this is what we're looking for in   this range of cells we're looking for Africa make  sure that this cell is identical with the list   especially in spelling so if this cell said  Africa with a lowercase a axel would not be   able to recognize it in this range I could of  course also put Africa in quotation marks here   into the formula but as I've explained we prefer  not to have any words in our formulas only cells so in this row number 26 I want to have  the sum of all people reached in Africa   which means that the next parameter is the sum  range this is the column of the table that we're   adding up and I want Excel to sum up this range  here if this Criterion is true then I close the   brackets and this gives me the number for Africa  now I'd also like to copy and paste this formula   because the logic is the same for the other cells  and for the other regions just that in the rows   below I want the search criteria to be re to be  Asia in Latin America and not Africa and here   in the cells to the right I don't want it to sum  up the number of people reached but the expenses but be aware if you take this formula and copy it  one row below all of these references will also   move down by one row in this case it's not tragic  because Africa was already covered here in our   first Formula but of course that's no guarantee  so we're going to apply what we learned in module   1 about absolute and relative cell links we can  always choose whether to only fix the row or the   column or both so now we have to think about it  here we can see that the range is always going to   be the same we always want Excel to look for our  Criterion within this list of regions it should   never move down or to the right or to the left so  we can fixate fix the range here by pressing F4 or adding the dollar signs manually of course   as for the search Criterion the region if I copy  copy the formula one row down it should take the   next Criterion but I don't want it to switch to  the right if I copy the formula to the right I   want it to continue to stay in column C and not  move to column D so I need to fix the columns only and I can do this by pressing F4 three times as  for the sum range we do not want this to move down   but we do want it to move to the right  when we're asking for the sum of expenses   so I will only fix the rows and not the  columns so that we can switch to the right by pressing F4 twice   for this range so that means I can now copy  and paste the formula down and to the right and if I want this to appear  as a specific format a currency   or accounting for it to  look the same I can go here   under the Home tab to numbers and select  what I'm looking for in this drop down menu exercise three some if using data from  another spreadsheet is basically the same   as exercise 2 except that the data is in  another spreadsheet in the same workbook   so for this exercise we're going to refer to the  top monitoring data and in the same way as above   I can refer to the data in this table for my sumif  formula just that now the range is somewhere else   so first I'm going to open the function sumif open  the bracket then go to the sheet monitoring data   to select the range and you will see that up here  in the formula bar it is also telling me that the   range that I selected is in this other sheet now  it's important to close the first parameter with   a comma or a semicolon depending on your version  of excel because otherwise when I go back to the   comparisons and sums sheet it would think  that I accidentally selected data from the   wrong sheet and it would overwrite it so now  that I'm back here I can select the Criterion put in a semicolon and then go to the  sum range on the monitoring data sheet now I'm going to close the formula so what went wrong here I'm actually giving you  the perfect unintentional example of what I was   just explaining that it's important to either  close the parameter or the formula while you're   in the other sheet otherwise Excel will think that  you would just accidentally choosing data from the   other sheet and it would it automatically switched  back to comparisons and sums so let's correct that go back to monitoring data select the sum  Range close the formula here press enter   and here we have our solution so now we are  going to just have to in order to copy and   paste the formula go through the same procedure  as earlier where here we fix for the sum for   the range we fix the rows and the columns for  the Criterion we only need to fix the column   and for the sum range it was the exact opposite I  am only going to fix the rows and not the column now I can copy and paste  the formula as we did before now why are these values slightly different I  think it's because the monitoring data here is   slightly different than in our table yeah  so nothing to worry about everything worked   in exercise 4 we will learn how to count something  using the function countif with countif the   amount of non-empty cells of an area can be  determined which match a specified Criterion   so here we'd like to know how many times  each region can be found in our list of sites   I'm going to start the function countif  refer to this range the list of sites   close this parameter and refer to the to  the Criterion which is the respective region   it returns 4 which we can which means  that this region is in the list four   times one two three four it's a  short list so we can verify that   and now let me show you what happens if I  move the list down without fixing it first you can see here that the range has moved down  by one row and it doesn't include the first row   of the list anymore so again I'm going to go  back to our original formula and fix the range so that I can copy and paste  it in our next tab sorted lists the goal is to create a table displaying  the most cost intensive countries   and their respective costs so the goal is right  here at the bottom this is what we're working   towards the top three countries and the top  three costs other practical examples of ranks   and sorted lists might be top countries in terms  of beneficiaries or people reached and so on   there are many ways of doing this one is using  pivot tables which automatically generate sortable   lists we'll give you an introduction  to Pivot tables later on in this module   and of course we learned that we could format  this as a table and then sort largest smallest   here let's sort the costs smallest to largest and  so we can actually we could just refer to these   top three countries here in our final table   so the issue here is that we might still be in  the middle of a project cycle in in all of these   countries and so the costs can vary with time  however we'd like our top three list to always be   um reflecting the current status imagine we  had a management cockpit a dashboard that   was only displaying this top three list with the  respective data in other sheets in the background   so there's a better way of solving this because  this would involve you regularly going back to   this table and remembering to sort smallest to  largest so that you always have the current top   three in your dashboard and there's a way of doing  this automatically though we could also do this   and go straight to exercise one C and solve  it all in one big formula but we're going to   do it step by step so that you can understand  the process in the first exercise exercise 1A   we're going to filter the three highest values  from the table above by using the formula large   the formula large Returns the case largest  value in the data set for example the fifth   largest number the third largest number what it's  asking for first is an array and I will select   this array here and then the K parameter  refers to the first second third largest value   I could put a 1 here but since I have this  number here I'm going to select the cell and   just see if this is going to work now it's  giving me an error message so if I'd rather   have my values and cells I have to remove the dot  and now our formula works the advantage of again   of not having numbers or text in our formulas  is that if I decided that I'd rather have the   first second and fifth largest value all I would  have to do is change the number here and not in   the formula to copy the formula we want the  parameter K to change but we don't want the   array to move down because as you can see here it  wouldn't include the entire list of costs anymore   so I will not fix the parameter but I will fix  the array so here we have the top three costs   the first the second the third highest value  now you might have noticed here this little box   that says that if two values are equal you have to  create dummy values for the large function to work   this might be true for your version of excel  in my version of excel 2016 I can actually   create have identical values so let's say the  costs in Honduras go up to eleven thousand   and then this is actually reflected in the  table it is displaying 11 000 twice I know   that that hasn't always been the case with Excel  so I'm just going to show you uh this work around   by means of creating dummy values so what we would  do is we would uh create a row that displays these   costs plus a fraction of the row number so that  they would all be unique values so what does this   mean I will take the costs and add the row with  empty brackets divided because if I was just to   add the row it would add up to five thousand and  seven so I'm going to divide the row number by   a very large value so that we have  a tiny fraction at the end here and I'm going to do these for all of these  values and then here in our large function we   could actually refer to this array instead and it  would still display 11 000 here but if you were to   add the comma you would actually see that it's  in fact referring to these dummy values but even   if you were to do further calculations using  these numbers because it is just such a small   fraction it wouldn't actually change anything  substantial for you let me just un undo this   so that we have our old values again now we  know what the top three costs are but we do   not yet know which countries they're occurring  in and that means we need a formula that returns   from the column country the name Rwanda if the  value is eleven thousand now you might think   that we could use the sum if formula but this one  only works for numbers and it wouldn't be able to   return text luckily though there is a formula for  matchmaking again I could all do this all in one   formula but I will demonstrate step by step the  question here now is which row within this Matrix   say this is Row one and then this is row  two three four five six seven and so on   does the number 11 000 occur so the function match  looks up the value and Returns the row number   so I'm going to tell it to look  up the value eleven thousand   in this lookup array remember we're  working with the dummy values right now   and then it'll give me some options  for match type I want the exact match and so it's telling me that the value 11  000 is in row eight within the lookup array   so let's verify one two three four  five six seven eight that's correct again I need the formula to work for the other  cost values as well the value that I'm looking up   should change that's c21 but the area the lookup  array should stay the same so I'm going to fix it   using F4 and now I can copy and paste it down   I see I hadn't yet copied and pasted  the large formula that's referring   to the dummy values to the other rows  now we're in the correct lookup array   now let's see if we can use these row numbers to  find the country with the index function the index   function returns a value or the reference  to a value from within a table or a range   we are going to be searching within  the column of country names that's   our array for row number eight which  was our Return of the match function   and the column is the list with the names  of countries in this case it is column   we'll call it column number one I could also  call it column number zero that would work and in order to copy and paste  it again I have to fix the array   and pull it down now we can verify yep the 11  000 top number one costs when Rwanda the ten   thousand were in Senegal and the nine  thousand were in Mali and now here in   exercise three I'm just going to be referring  so here I uh it's just simple cell references to the other tables that we've just created   and in practice this table as I said might  be on a different sheet called a dashboard   or a country overview page and now I have a  dynamic table of the top three costs per country now we've reached module 4 data output  and visualization our last module   classically Excel is used to  show results in a simple table   of course tables are an appropriate tool  for the presentation on organization of data   however they often tend to be confusing and  it takes a long time to find specific data   or to extract the desired information from a  large data set an appealing visualization of   the data instead offers substantial advantages  in the communication of important information   visually optimized data output helps users to  quickly gain an overview over the progress of   a measure during this step technical knowledge of  formulas or calculation methods is less important   what is needed instead is creativity in the  choice of the optimal presentation format   the goal needs to be that decision makers and  also external persons are able to understand   and process important information in  a short amount of time in this module   you'll learn how to bring meaning to data using  conditional formatting you'll also learn how to   visualize qualitative and quantitative indicator  progress using traffic lights and drop-down boxes   we will also learn how to make clever choices  of diagrams so that you don't even need Legends   for explaining them and can free your data output  from unnecessary information we'll also introduce   you to Pivot tables and charts finally some  of the elements we covered will be summarized   in a management cockpit a creative and fun way  to display all of your most important data and   progress at a glance in our real life example we  will show you a monitoring tool that makes use of   many functions and data visualization techniques  that we covered in this course let's start by   having a look at some simple conditional  formatting to help bring meaning to data   a good option is always to set up formatting that  automatically adjusts itself when values change   a simple example would be to switch the background  color of a cell to Red if its value exceeds a   specific benchmark or for the cell to change their  color to green yellow or red depending on the   degree of Target achievement here in this exercise  we have a data set from 2022 for example from   a survey and a data set from 2023 and we would  like to see if anything has changed over the year   so in order to set up conditional formatting we  have to go to the menu tab home go to conditional   formatting and then choose from one of the  many rules we're going to say greater than and I want it to be a green fill with dark  green text if the number has increased   since the year before as you can see Excel has  automatically added the dollar signs and fixed   this cell so after so if I were to copy and  paste this rule it would always be comparing   each of these values to this one cell so I will  just go here and manually delete the dollar signs now to copy the rule for conditional  formatting to other cells   do not drag the black cross as if  you were to copy formulas because   as you can see otherwise it would just  transfer the cell values and overwrite   the other cells so let's undo that instead go  to the format painter here under the Home tab   which is the paint brush on the top left select  the cell which already contains the conditional   formatting rule that you set up click on the  paint brush and now paste it to the entire table and now we can tell at a glance in which areas  something has changed in the course of the year   of course this is a very generalized example that  can have many practical application scenarios   next we're going to have a look at visualizations  using color scales and traffic lights   Excel allows you to highlight successful projects   at a glance using these color scales to  be found under conditional formatting of course you're the one to decide whether a  higher or a lower value is to be considered   successful so in this case here green is  automatically applied to higher values within   the cell range and red means it's a lower value  you can also have it the other way around where   red is the highest value or use different  color gradients you can also use icon sets   and then you can tell at a glance that project 1  Project 6 and Project 7 are the most successful   in terms of these numbers we recommend using  shapes though in addition or instead of colors   because these will also be understood on black and  white printouts and by colorblind people as well   when working with color scales be aware that as  a default setting Excel is always working with   percentiles and you can change this also to be  um absolute numbers either in percent or in in   numbers because otherwise when these values change  and for example let's say we don't have any more   any lower values at all anything that's  below 40 percent then all of a sudden the the colors that were orange or yellow to  begin with will turn red because it's always   just looking at so because it's always comparing  the numbers within the selected range of cells   this so I if I wanted Excel to look more at the  percentages in and of themselves rather than   compare these values within the range and base  its color gradient on the comparison then I can   select percent here rather than percentile so  that's a fine difference just to be aware of   sometimes things aren't as linear as that  though and you can't necessarily say that   a higher value equals better progress  than a lower number or vice versa   in some cases you might want to do the  assessment on your own and visualize it   so that you and others can quickly tell  if there are any issues to be solved so here in exercise 2 A and B we're going to  have a look at customized indicator assessment   so in this exercise here we are  going to learn how to set a status   in order to assess our indicator  progress manually using drop down boxes   this is something that we already covered earlier  on in module 2 when we were looking at data input   and if you remember we will have to  go to the data tab data validation   and select a list to choose from we've already  prepared a data source here so these three options   on track minor issues and off track are going to  be our source and now we can decide for ourselves   whether this current status is on track or not so  let's see here the target is that the discussion   on sustainable Wildlife Management has started  in the National Parliament currently the topic   is not yet on the official agenda and only three  key parliamentarians have become interested in it   um I would say we are kind we definitely  have some minor issues if not off track here   now let's copy and paste our drop  down box using the little black cross for indicator number two our Target is that  a national law on Hunting rights is passed   currently the law has been drafted but is  still being revised and we could say for   example yeah this is on track we weren't  expecting it to be any further ahead   now we're going to do the same for quantitative  indicators and in addition we're going to be   working with traffic light arrows  to visually display the status   foreign we could create a formula that  would based on the achievement in percent   tell us whether we are on track or off  track or have some minor issues but   um sometimes certain indicators are more complex  or more challenging so it makes sense to do a   manual assessment so maybe progress of 10 percent  here for indicator a isn't even that bad because   you've only just been measuring indicator  a for half a year and considering that time   frame you wouldn't have expected more progress  or maybe your project term ends in five years   and considering that 10 progress might actually  be quite good but Excel would of course only be   able to see the relatively low percentage of  the achievement and judge it as slow progress all right so now we can say yeah  actually indicator a is on track   um there's some minor issues with indicator  b because we were expecting uh it to be even   further ahead than 80 percent and indicator C  is off track now for our last column

2023-05-02

Show video