Monitoring with Excel
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 22:13
