 Hello everybody, and welcome to Creating Better Spreadsheets with Microsoft Excel. Just one note about using ReadyTalk. If you have any questions today, you can go ahead and chat those into the chat pane. If you lose your Internet connection at any time, you can reconnect using the link that was emailed to you, so just the way you did right now. And I did put a number in there into the chat pane if you need to call in. It should be playing through your mic and speakers automatically. And just as a reminder, we are going to be recording today's session, and the archive of the recording will be up via a Creative Commons license later, probably next week. But we will be sending out the recording to you later today along with the presentation and materials and links. And if you do want to go ahead and talk about this via Twitter, you can do so on the Twitter hashtag. So again, welcome to Creating Better Spreadsheets with Microsoft Excel. I'm Kyla Hunt. I'm going to be your facilitator today. I'm the Webinar Program Manager with TechSoup Global. And with us today is Mark Liu also from TechSoup who is going to be talking with us a little bit about keyboard shortcuts and pivot tables. And we also from Microsoft have Kate Lieberstein who is going to be talking about the organization Feet First and a little bit about how she used Excel to help them with their strategic score card. So again, first up we are going to have keyboard shortcuts from Mark Liu. And so in just one second I'm going to go ahead and give it over to him to get started. Again, if you do have any questions, go ahead, type those into the chat pane, and we will go ahead and respond. And for some reason we do not get to your question, never fear. We will be following up with any unanswered questions today. And in your confirmation email or reminder email, we did send out the link for the captioning of this webinar. I will go ahead and put that link into the chat pane if for some reason you do not have access to that email. Okay, thanks. And at this point I'm going to give it over to Mark. Hello, can you hear me? Okay, so hi everybody. Thank you Kyla. I'm Mark Liu. I'm with TechSoup and I will be talking about keyboard shortcuts and pivot tables. And let's see. So I'll talk about what are the advantages and disadvantages of keyboard shortcuts and how to learn about them. And when I do pivot tables I will talk about when to use them and then do a demo of the usage of pivot tables. Now Kyla has inserted a survey in here and I just wanted to ask people how often do you use keyboard shortcuts in Microsoft Excel? Frequently, occasionally, or never? And so I see we are getting responses. They are up to a hundred already. How do we do this Kyla? Kyla, do you want to meet yourself really quick Mark? All right, so it does look like we are getting a lot of responses. I'm going to go ahead and close the poll in 5, 4, 3, 2, 1. And it does look like 43% of you use it occasionally. 34% of you or 35% of you never use keyboard shortcuts. So that's really good for us to know. And 22% of you use it frequently. So that's really great I think for us to know for Mark going into his keyboard shortcuts section. So thanks guys. Okay, so I see a lot of you use them already. So maybe you are here to hear about pivot tables because there is pretty straightforward with keyboard shortcuts. The idea is that instead of using the mouse and menus, like mouse in over to the edit menu and then clicking on edit and then mouse down to copy and clicking on copy, then you just use a shortcut like you hold down the control key and press C. That's the copy shortcut. Or alternatively if you want to open a file you go to the file menu and then click on that and then go to open and click on that. The keyboard shortcut is Alt-T, hold that down and then the letter F and then hold the Alt-T and then the letter O. So here the next page are some examples of very commonly used shortcuts. So the most common editing commands are copy, paste, and cut. So those are set up as Control-X, Control-C, and Control-V. Another one that is very useful in Excel is Select All, which is Control-A. That will select every cell in your sheet. Another very useful one is Undo the last operation that you did, the last editing operation which is Control-Z. And then some other basic ones are Open File, Save File, and Close File. And those are Alt-F, Alt-O, not zero, letter O, Alt-F, Alt-S, and Alt-F, Alt-C. And you can see there's a pattern. They try to use letters that make sense. So Alt-F is for File and then O, S, and C are for Open, Save, and Close. It doesn't always follow that convention because I think there's limits to the alphabet but it's pretty common to have hints like that to help you remember. So what are the advantages of using keyboard shortcuts? I use Excel very heavily myself. I spend probably a couple hours a day in Excel. And I find that using keyboard shortcuts for me is very valuable because it's easily twice as fast as using the mouse. And why is that? It's because rather than using, moving the mouse and pressing several keys like move the mouse press, move the mouse press, you just click one or two keys. And you also don't have to move your hand back and forth. I'm a touch type. This is probably many of you are. And I like to keep my hand on the keyboard so I don't move the home placement of my fingers. So if I'm moving my hand back and forth from one to the other, then I can lose it. And it just simply takes time to move my hand to the mouse and back. It takes a whole second. But if I'm spending hours in Excel, those seconds add up. And that's why I think it's twice as fast. And also, it just simply takes less precision. If your hand is already in the touch typing position, you can find the keys very reliably without even thinking about it. You're sort of programmed into your muscles by them. But if you're using the mouse, you actually have to place the cursor over an icon or place it over the menu to get in the right spot. You actually have to have some eye-hand coordination. And for some people like me, that's a little bit of demanding. So I'd rather not have to do that. So that's the basic idea. What are the disadvantages? Well, the main disadvantage why a lot of people don't use keyboard shortcuts is that you simply need to learn and remember the letter combinations. And very often people just don't want to bother. They've already got too much stuff in their brains and this doesn't seem worth it. But the good thing about it is it's not all or nothing. You don't have to always use keyboard shortcuts. You can use them when you feel like it, and you can not use them when you don't feel like it. So if you're just casually getting into Excel for a few minutes, you don't have to bother. But if you're going to spend an hour in Excel, the next hour in Excel, it might be worth it to refresh your memory and start using the keyboard shortcuts during that session because you could be spending a lot of time. And you don't have to worry about whether you'll remember tomorrow. All you have to do is learn it for today. So how do we learn about them? I'm going to show you a spreadsheet. And before I open it up, this is an example spreadsheet. I just happened to be working with this data myself, so I used it. It's the IRS Business Master File of Nonprofit Organizations. So this is publicly available data. As nonprofits, all of our organizations, this information that the IRS makes available to anybody. And I, in fact, went to this URL that I've got finished here and downloaded the data myself as if I was somebody in the public. I didn't need to log in or anything like that. There's 1.5 over 1.5 million nonprofit organizations. I just pulled a random sample of a thousand records. It's not even random. Like I grabbed the first thousand rows or something like that. And I'll show you how you get to see key tips. So let's go to share it. Oh, it works. Okay, so this is my spreadsheet. This is my example. So like here's the names of some organizations. Here is their employer identifier number. It's a unique number for every organization, every business, or nonprofit has a number like that. And if I scroll down, and I'm using keyboard shortcuts, that's why you don't see the mouse moving. I have a thousand, I'm in row 1001, so there's a thousand records in here. And this file has a whole bunch of different fields describing, let's see, it goes all the way over to column AF, so there's about 30 columns of data. And it has assets and income. It has a name, doing business as, address, city, state, zip code. Column I is the 501C number, so the subcategorization. So these are 3s which is public charities. And then down here I've got a 501C4, and a 501C5. So there's different kinds of nonprofits and not all charities. So this is an example of just a spreadsheet. So key tips tell you what the keyboard shortcuts are. So the way to see the key tips is to press down the Alt key, and you can see up at the top there are these little icons, squares with letters inside. So F is F, home is H, insert is N, and there's an example where it's not I. Maybe you probably had I reserved for something else, page layout as T. So if I hit F now, I'm holding the Alt key. It goes to the file command, instead of commands. And here again I've got key tips. S is for save, save as is A, open is O, and C is closed. And so I've been holding the Alt key this entire time. Now if I hit A, it will bring up the save as dialog. Not that I really want to save it, I just wanted to show you. So that's key tips. So you can learn your keyboard shortcuts just by holding down the Alt key, and it tells you, hey, get to them. So you can do Alt N for insert. And now, for example, if I want to do a line graph, it's going to be another N. If I want to do a column graph, it's going to be a C. If I want to insert a picture, it's a T. So you can learn what they are just by doing this and paying attention to those little key tips, and then you can reuse it. So that's how you see the key tips. I've got to double-click here. I mentioned earlier that there are some shortcuts which are used to control key. Those are faster because you only need one letter usually. Those are still supported by Excel. These have been around in Excel for a long time, but they are not visible to the key tips. The key tips only show you the Alt keyboard shortcuts. So you can get this information from the Golden Excel Help. To get to Excel Help, you can use the F1 function key, or there's a help icon. It's a little circle with a question mark inside. Search for keyboard shortcuts, and there's an article called Keyboard Shortcuts in Excel. And we're planning to make a URL for this article available to you. And it's like a five-page article that tells you all the different shortcuts and gives you a lot more information. So this is just an introduction. I wanted to show you what they are and how they can speed things up. But I'm not going to try and teach you the shortcuts because there are literally hundreds of them. So now I'm going to move on to pivot tables. And so we have another survey. And the question is, how often do you use pivot tables in Microsoft Excel? Frequently, occasionally, or never? And somehow I guess that the answers will be different this time than they were for keyboard shortcuts because pivot tables are a relatively advanced function. And we're waiting to see people's responses that's still coming in. Do you want to do this, Kyla? Kyla Sure. All right, we're going to go ahead and close it in 5, 4, 3, 2, 1. And so it looks like 84% of you have never used pivot tables. And so that's really great for us to know. 14% of you occasionally use it. And only 2% of you, about 2% of you frequently use it. So that's really great for us to know that you are mostly all beginners at this. All right, let's go ahead and check. Kyla Okay, so we had 4 people who said they frequently use them. If I had voted myself, it would be 5 because I use them constantly. I use them all the time. So what are they? What you need is a tabular array of data. I mean, you could use it with 2 rows of data, but I don't think people would find it very helpful in that case. So you would be talking about hundreds of rows of data up to a million rows because Excel will allow a million rows in an Excel spreadsheet. And the way you bring it up is to insert pivot table, pivot table. Those are the menu options. And when do you use them? So that's why I brought up this example of the IRS business master file. You use it when you have lots of data. You have data fields to summarize, things that you might want to sum up or average. So an example of a data field in an IRS is simply like the one in the field of income, another one was assets. You have categorization rows for columns and rows and filters. So these are things like the state or the 501C subgroup. They're good for presentation because in that example of the IRS business master file, you just have this massive spreadsheet with many, many, many rows of data. And it's hard to see anything in it because there's just so much data. A tabular view, which is like a two-way table, is very intuitive. People are very used to looking at them. And that's what a pivot table produces for you. And it's very well integrated with Excel graphs. And it's really good for exploration. So like in that example, you might be wondering, are the nonprofits in Maine different from the modern nonprofits in Florida? Or which subsection like IRS 501C3 versus 4 has the highest income? These are questions you might be wondering about and you can use the pivot table to answer them. So let's go into it. So the type of data you have. So typically in a pivot table, each row represents a different instance of the data event. So the kind of data I work with at TechSoup Global is I looked at we have a big product donation program and I'll get tables where each row in the data represents one donation. So I will say who is donated to, what the product was, how many copies of the product, what was the retail value of the product, the date that the donation was given. So that's what I use. I know the kind of things that we do at TechSoup is not that typical of nonprofits, but the kind of things that you might see yourselves are things like you might have one row representing a client who is using your services and his use of the services, his or her. Or it might be one donation to your organization. Or one row might be one volunteer performing an activity for your organization. Or it might be one client participating in an activity being offered by your organization. So that's the kind of information you have in each row. Then the columns, you have different fields to describe the event. So typically you'll have something like a date and a time. You have identification information is who is the client or donor or volunteer or participant, maybe address information, telephone number, email address, contact information, things describing a participant. You might have information about the type of service. In my example it was what is the product? Probably what is the product name and what is the catalog number we have for it? But it might be different services or activities that your organization is involved in. And then there will be quantities. For me, number of products requested. For you it might be the number of services used or the size of the donation or the amount of time the volunteer gave, or the number of activities the volunteer was involved in. And each column must have a heading. Okay, so what I'm going to do is, and so the result is a tabular summary of the data. The text with global, for me it's like donations by country by month. Or you might be interested if your organization has multiple locations, how many services were offered in each location, or the number of donations by different donation type by week, or the number of volunteers from an affiliate by month. These are the kinds of reports you might get with a pivot table. So I'm going to go into Excel and here's my data. So the first thing I'm going to do is select all my data. So I'll do Ctrl A. Now everything has been selected. And I'll go Alt A for data. Where was it? I don't usually use this one. V I think. That's the wrong one. Alright, I'll start over. I'll do it the way I usually do it. D P. No, I doubled it. D P. Okay, so I've had my data selected. And here it's set me up for a pivot table. The pivot table is going to appear on the left. And over on the right is sort of a wizard type of a price of things. And so what I'm going to do is just take this ill name and drop it down into values. And it says count of name. So this is a piece of data I'm going to get in this pivot table. So I have a thousand different organizations. And then maybe I'll go to Income, just to try another thing. And you can see I can put in two different things. So sum of income. So the total income of these thousand organizations was, these are dollars. So this is like $3,526. So they have an average income of $3.5. So this is a pretty low income group of organizations. Let's see what it says for assets. So I had assets of $3,000. I can also change these here. If I click on this little down arrow, then I got this menu and it says value field settings. I click on that. And you can see these are different kinds of computations that the pivot table can do for you. So I've already had some. I've already had count. So I can show average. Let's click on okay. So now I've got an average assets of $3. This is awesome money. So this is $3.82. So you can see you can put different values into your pivot table. And then it gives you an ability to do this by rows and columns. So let's say, let's pull up state and put that into rows. And we can see in my data, I have the states of California, Colorado, District of Columbia, Florida, and so on. And it just so happens out of my 1,000 organizations, this group was very heavily oriented towards the state of Maine. That was just the random collection I got. That doesn't mean that Maine is the nonprofit capital of the United States. It's just the collection I pulled up. And let's go to that subgroup number. So let's do, let's put that here. So I see I have names. I have different subgroups, 1, 2, 3, 4, 5, 6, up through 92. Right here, this column is subgroup 3. So these are the charities. And out of the 1,589 were charities. Let's see. I don't even know what subgroup 7 is, but there were 90 of those. So that was relatively common. If I look at this cell right there, there are 549 501c3s in the state of Maine out of the 1,000. It's almost a little over half of all 1,000 were there. And over here in this set of columns and continuing, these are the income for the nonprofits in those states and subgroups. And over here we have the average assets. Okay, and so the number formatting is not very good, but I could fix that. That looks a little bit more reasonable. But this is a very complicated sort of messy pivot table. I was just trying to show you some features. This is not a likely combination. Let me just get rid of some of these things. I'll get rid of income, and I'll get rid of assets. So that's a more typical kind of view that you would have. So you can see you can set things in different places. I can move, I can switch. I can put the subgroups in the rows and the states in the columns, and it took me one second to do that. So I can adjust the display to be something that works for me that's more intuitive. I'm going to go back now. I can add more rows. So for example, I can put city under state, and I can see for California I actually have 1, 2, 3, 4, 5, 6 different cities, Los Alamitos, Los Angeles, Rimboland, San Diego, San Mateo, and Soledad. So there are only 7 organizations in this data set of 1,000 from California, and they were spread over 6 different cities. Now of course if I go down to Maine, I'm going to see a lot of cities because I had a lot of data from Maine. Those are all Maine cities. So you can have any number of row labels down here in the lower right. I can have state, state city. I can add zip code that's getting carried away. Another thing you can use these for is you can use them for filters. So for example, I can put the state as a report filter, and up here in the upper left is the state all, and that's a drop-down menu. And if I pull that menu down, I can see the list of states. And suppose I wanted my table to only show Maine. Okay, I click on ME for Maine. And now all I've got is the data for Maine. And whoa, there's a lot of cities for Maine. And there were 934 of the 1,000 rows were in the state of Maine, and these are all the different cities. So I could change this to show only California, and I should see only 6 cities. So you have tremendous flexibility in your display and your pivot tables. And this allows you to pull up different kinds of data and organize it in different ways. And this is a small data set. It's only 1,000 rows by 30 columns. So it's really fast. You can see it's instantaneous. You have a really big set of data like 200,000 rows is going to be some little delays. But that's one of the features. It's so easy to manipulate, and to organize the data in ways that are interesting to you. Another thing it does is it's very easy to, it's very well integrated with graphs. So I could like, select this data, and then I can go Alt, N for Insert, C for Column, and get a column graph, and there. I have my 6 rows, and it's showing, let's change this to a line graph because it's a little confusing. It pulled up 2 columns of data. No, no, no, that's not what I wanted to do. Change chart tape. Let's make this be a line graph. Oh, there's a lot of blanks. So type 3 and type 5. Now I'll show you something interesting. Let's go into the data. Let's go to the top. Let's change second batches of church of Palmer, New York to Palmer, California. And I still have my 7. Now I've just changed the data underneath this pivot table so I need to refresh the data. So I do Alt, Alt for Data, Alt for Refresh All, A for Refresh All, and suddenly I see Palmer, California. I've just added it to my data, and I now have 8 nonprofits in the state of California. And Palmer appears in the graph. I didn't have a group number, I guess. So you can see how everything is tied together and it's very highly integrated. So I think that's it. That was everything I wanted to show you about pivot tables. So it's very intuitive. If you can get the pivot table to come up and show on the screen, you can just poke around and you can figure out how to make it work. So are we going to open up this floor for questions? Yeah, we do have a couple of questions that have come in, or quite a few questions that have come in. I'll go ahead and ask a couple now, and then we'll go ahead and let Kate do her thing and then see what questions we can get to at the end as well. One thing that we were asked was if you could show how to insert a pivot table without the keyboard shortcut, so using a different route than using an actual keyboard shortcut to insert the pivot table. That's a good question because I'm so used to using keyboard shortcuts, I can't remember the routes. That's what I was trying to show you earlier. Let's select the data again. It was data, oh it's needed to insert, insert to the table. Yeah, so there's insert to the table. There you go. That's what it is. Okay, I knew it was someplace. I've been using shortcuts for so long that I've never used it, so I had forgotten what it was. So I learned all this stuff in Office 2003, and the user interface has changed. So I'm still using 2003 commands very often instead of Office 2010. Yet another question? We did get a couple of questions. Susan was asking, how are you putting the fields in each box below? Were you doing that as a drag and drop? Yeah, I was dragging. There's various ways you can do it, but I was just like dragging. So I grab it, click down, and drag it over. But you could just double click I think, or click, and it automatically puts it someplace, or you can drag it from this corner. I'm dragging it now to another corner. So there's various ways you can do it, very typical kind of user interface. Okay, great. And then we had another question that was just about Excel in general. And if we have time, you can go ahead and try to show this. And if not, I can go ahead and try to find an answer to send this person later. But Susan was wondering how to copy spreadsheets and preserve the formatting or apply formatting from one sheet or call them to another. I'm fiddling with my mute button. So suppose I, let's go over here, assets. I think on any assets that are worth seeing. Let's turn this into a dollar. Okay, so I've got this formatted. These are as money. I could then copy this. I'm using a shortcut, I'm copying. I could come over here. Now I want to set this one up to the same format. And let's see, home, coffee, taste, is that the taste options formatting? So here like this paste formatting, okay? Let's see what happens. You see, it tasted, it didn't change the values. This one stayed a one, but it gave it the formatting that I pasted in. So under paste, there are all these different other paste options, formatting, a link, pictures, so there's special ways of pasting. You can just paste the values. So I actually use that quite a bit. I use taste value sometimes. I use taste formatting sometimes. There's both useful commands. Okay, great. So I think that the rest of the questions, the rest of the questions we'll go ahead and get to after Kate's section, but I do want to make sure that Kate has enough time for her. So I'm going to go ahead and jump to her first slide. And if the operator could go ahead and unmute Kate at this point, that would be great. Please go ahead. Thank you. Hello everyone. My name is Kate Libersky. I'm a finance manager at Microsoft. And I also sit on the board of directors of Seed First, which is a Seattle-based non-thoughts organization. And they were the beneficiary of the strategic support card, but which we will talk a little bit more in-depth. Let me move to the next slide. So I will give you a little bit of an overview of what Seed First does, which will help understand the support card metrics a little bit better. So Seed First is a pedestrian advocacy group based in Seattle. And we focus on issues such as transportation, public health, neighborhood design, as well as environment and social justice. And there are a number of projects that Seed First gets involved in, among which are something called Safe Routes to Schools, through which we help local middle schools and local schools help design the premises around the school in a way that keeps the kids safe and allows them to walk around the area without parents having to worry as much. We also have something called community maps, walking maps for various neighborhoods that allow folks to explore different neighborhoods on foot, as well as use it for transportation. We also get involved in a variety of pedestrian advocacy issues that take in place where we either provide our support or offer dissenting opinions on various policy decisions. But that's kind of an overarching view. So just to give a general sense of the mission, the organization is to promote walkable communities because we believe that walking every day is good for your health, transportation, environment, community, and pleasure. We're a very small organization. We currently have 4 full staff members and a few part-time staff members and 10 board members. So my involvement with Seed First began actually as part of a consulting project. I first learned of Seed First in 2009 and in December of that year Seed First undertook a two-day strategic planning forum with the board and staff to help them identify their new mission, vision, values, and goals. And once they have done that, they have invited Taproot Foundation, which is the consultancy nonprofit, to help them create a strategic scorecard which would then provide a consistent and effective way to communicate with the board on a quarterly basis. It would also help facilitate annual tune-ups of the strategic plan to make sure that all of the different aspects that were identified during the planning forum are still relevant, depending on developments, as well as help make sure that the plan stays alive, that all the things that have been identified that we keep a focus on those areas to make sure that all the good things that we as an organization want to accomplish we keep our eye on. So the next slide gives you the six goals that were identified during the strategic planning process that organizations took on. And as we, when I was part of the consulting team, had to kind of take in and figure out how do we use them to create a scorecard. So just high-level, among these goals were identifying and empowering community leaders to take action that would help promote walkable communities, informant and motivating policy makers to prioritize funding that would support walkability efforts. Energizing, informing community about walkability issues, et cetera, et cetera. So kind of taking these high-level goals and then figuring out how do you break them down into various objectives, how do you measure them, what kind of targets you set, and how you then take that and come up with initiatives to stay on top of the plan. So I'll just look at the next slide here in a second. So what this scorecard really allows an organization to do is it provides a summary of progress toward reaching a strategic goal. And it is geared toward a review by the board on a quarterly basis. So it would allow the board to measure and monitor strategic execution across four performance perspectives that would then provide holistic and balance the other organizational strategic progress. So the four perspectives that we focused on were financial, internal processes, stakeholder, and learning and growth. This would then help organization keep the strategic plan on top of their mind, allow them to measure their progress quarter over quarter, as well as against the targets that were set. Also focus on drawout goals that are not all necessarily explicit but that the organization lives by, which is important. It also gives a common language to discuss organizational progress. And in a way, Excel plays an important role here because there are a lot of visualization features that make it much easier to understand where their organization is in terms of where to the where it wants to be. Let's see. It also gives a way to – there are a lot of different aspects that each nonprofit is involved in, but you want to focus on a few that are big picture and that helps you figure out how healthy your organization is. And then finally, it allows – it provides clarity to the organization how their efforts, how the efforts, day-to-day work that the staff does actually helps them toward the long-range plan that they set. So the next slide will give you sort of an example of what this discord card looked like. And you can see that it's broken out into the four objectives that I mentioned, so financial, stakeholder, internal process, and learning growth. Then within each of those objectives, there are a variety of measures that we wanted to drill into. So what I'll do right now is actually share my screen with you so that you can see – I can kind of walk you through the different line items. So you all should be seeing the Excel file itself that we used to create the strategic scorecard. So what the view that you currently see is the output file. And really, this is what we would then present to the board and what the Executive Director of Seed First would take over and then present the board on a quarterly basis after the consulting project goes over. So again, you can see the financial objective. Within it there are a couple of measures that we focused on. For example, increase unrestricted funding sources and achieve revenue goals, or better, track and control costs. Within each of those we then had several measures which are, for example, memberships or unrestricted grants or fee-for-service dollars. And then you can see the targets that we set, working with the organization first. So for example, Feed First helped identify what is the high target for member fees for each quarter and what is the low target. And then they would be able to pull in what the actual number was in terms of the membership dollars. So we here show two quarters for comparison purposes. And then Excel, there is a feature called Conditional Formatting which allows you to then use these color coding to see, okay, relative to the target, how are we doing, and relative to the prior quarter, how are we doing. So for example, we said, okay, if our member fees in particular quarter were above our high target, then the circle would be green. If it were below the low target, the circle would be red. And if it was in between, it would be yellow. So in this case, it would be yellow in both quarters. And then here we set up a simple formula that allows you to say, compare a quarter over quarter and say, okay, if current quarter is above the prior quarter, then show an up arrow. If it's below, then show a down pointing arrow. And if it's the same, then show an equal sign. But again, when the board looks at it, they don't need to think through all these details. They can just take a look and see, okay, so it looks like our membership fees have gone down in a particular quarter. So what kind of decision should we make based on that? And first of all, we can drill down into potential reasons. And then what are some of the initiatives that would fall out of that particular takeaway? And the same is done for the year today so that you can see not only how we're doing on a quarterly basis, but how you're doing on an annual basis, how you're progressing towards the annual goal. And so again, we chose to do this in Excel because we felt that this is a relatively simple way of doing it, and it would be easy for their organization with small staff and limited resources to maintain. And it would be easy for the board then to take a look and see where their organization is and what kind of actions need to be taken. Now as I mentioned before, this is an output page, so there's a lot of data that supports it. And so I'll just briefly walk you through how we set it up. So we had a collection plan that gives a summary of where a different piece of information would come from. For example, for the financial section, a lot of the data would be capped either in QuickBooks, Accounting System, or in Salesforce, for example. So that's when staff fill this out on a quarterly basis. They can easily find where the data is housed. And then I could go to each tab here. It corresponds to a particular measure that then feeds into the scorecard. So for example, if you look at the financial tab, these are the different measures that we looked at. Membership fees, unrestricted grants, and fee-for-service. So on a monthly or quarterly basis, staff or someone in an organization would fill out this, just do a data dump from another system that an organization uses without having to do a lot of work. And then there's a formula set up to say, okay, pool, for example, here it will say look up for the X which indicates what time period we're in, and then pool the number that corresponds to that time period. So here you see 4900. So if we go back to the scorecard, you see that 4900 here. And of course, if I were to move the X to a different time period, it would adjust, which makes updating this scorecard very simple. So this is a high level of how we approach this project. And then of course each corresponding tab does a very similar thing depending on what measure we were looking at. And all of these are outlined in the output stage, which is the scorecard. And to this day the organization uses it. We've changed some of these parameters, some of the measures, because again if we see for example that quarter over quarter the circles are red, then either we are not focusing on some of the areas because something else is a higher priority and maybe it shouldn't be one of our strategic goals, or we need to invest a lot more time and resources into making sure that we are in organization putting staff time into improving how we're doing against those targets. Or another reason to be that the targets are not reasonable. For example, maybe our high target is too high, or maybe it's too low. So this is the kind of thought process that the board can go through just looking at something as simple as this that Excel allows us to. So this is a high level overview. Please let me know if there are questions that I can answer. But I think this is a good illustration of how Excel can be a really useful tool for both the big picture thinking as well as drilling into specific data sets and understanding who causes of any problem, or understanding reasons for success. Kate, I know we had at least one question that was just wanting you to go a little bit more into the process that was used to create it. And the same person was wondering if you could include a screenshot or a sample even without data for the score card categories? Sure, one second. She was wondering how you develop the four parts and the subsections under each more specifically. So in terms of figuring out the four categories, we've used certain literature that sort of helped us think through how the different measures their organization focuses on. What are the different four objectives? And really stakeholder is the outcome of the mission of their organization. So if our mission is to promote walkable communities, then stakeholder, for example, generating enthusiasm for walking would be an important one. Expanding relationships with community leaders and elected officials would be an important one because they are the folks that provide funding that can support a lot of the initiatives that we'd like to take on and see happen in our community. Expanding membership is important because the more members we have in our organization, the more support we have, the more the issues that are being talked about, the more they show up in the press. And I guess the higher the rate of change that we will see or improvements. And for another one we had here is increased diversity of members and communities. Again, it was actually interesting to discuss who the stakeholders are because being a pedestrian is kind of an interesting thing because everybody is a pedestrian. A biker is something you choose to be, but a pedestrian is something that we all are at some point in our day or in our week or month. At some point we do have to walk places. And so it actually opens up a lot of opportunities of not necessarily segmenting the markets to who the biker is only, for example, but it's really understanding what's important to different people. So when you think about parents, safe routes to school would be very important to them because they worry about the safety of their children. When you think about people who commute to work a lot, having a good transit system and having walking maps that allow them to find ways to get where they need to be without using the car is important to them. In terms of policies, again, the more support we have from the community about some of these issues, the more elected officials would pay attention to the areas of concern. So it's really taking those four objectives, financial, stakeholder, internal process, and learning growth, which we came up with from just reading and understanding how non-probabilization operates and then kind of within each working with feet first to understand what are the important measures that need to be addressed. And then drilling in a little more and saying, okay, what are the different sub-measures, if you will, that help support each of those layers? If that helps. In terms of, you mentioned something about a snapshot. What kind of snapshot today is awesome? I think she was just wondering if there was a screenshot or sample of the scorecard categories, and if you could even email that to me later because I know we're getting low on time. And I do want to make sure that we, yeah, and that way I can pull it out of the mall. But on one of the slides that's a Seedbird Strategic Scorecard, all of the different measures that we cared about at the time that we created this scorecard are listed. We had more, but those are the ones that we narrowed down and kept. Okay, great. Thanks. And then if you want to go ahead and get back to the slide deck by just pressing the green arrow at the top, it's at the very top of the screen. Okay, she got you. Yeah, here we go. And if you have any closing, yeah. And so this last slide was just an example of the model structure which I walked you through and modeled itself. So you can see the different tabs that we had which correspond to the different measures that you see on slide 31. And kind of how we organized it to feed into the output page that then is used for presentation purposes. Okay, great. Thanks. And I'm going to go ahead and take a look to see what other questions have come in. We probably won't get to all of the questions that have come in today, but never fear I will be forwarding those questions to the presenters after the fact and we will get you an answer at some point. We did have some questions that I wanted to bring up that were unrelated to either pivot tables or to the strategic scorecard that Kay just talked about. And I thought it would be good to talk a little bit about those. We did have a question about how to create specific formulas. So for example, we had a question asking, what do you do to subtract the number from the total such as you do an autosum for various fundraising items, then you have a corresponding autosum for expenses and you want to have the net amount. I'm not sure maybe if Mark wants to try to take that one. I can repeat that if you need me to. Sure. So he's asking what do you do to subtract a number from the total for a formula? And his example is if you do an autosum for various fundraising items, then you have a corresponding autosum for expenses, but then you want to have the net amount. I'm not sure I really understand the question. That's the problem. Could we possibly just get this in written form and I could communicate with them through email or something like that? I'm not quite certain. I understand what he's asking. That's completely fine. And then Paige was wondering if somebody could say something about setting up conditional formatting. Sure, I can talk about it. I'm not an expert. If you're better at it, you can take it. I can show for example how we've done it for the scorecards. Let me share this next screen again. So for example, we're here and we want to set up one of these circles here. So you would go to home, and then you would go to conditional formatting right here. And then you see there's several options. The highlight cells rule. For example, here you can choose highlight a cell in particular color, or font in particular color if that cell is above a certain number, if it's less than a certain number, if it's the same certain number. So for example, if we choose this one, and we say format this cell with say yellow and dark text. Wait one second. Let's do this again. So the home conditional formatting highlight So we say they're greater than this number with this text. So what you would then need to do is copy this format. And you can see that this number is greater than 50, 7, 18. And so it highlighted it in a format that I selected. So that's just one example of how you use it. But really you just have to go in here and explore the different options. So data bar is another really cool way where you can, for example, if you have, let's say we had membership fees from different categories of members. We wanted to see the percentage of the different categories that make up that total revenue number. If I had those listed I could then use this. You can see this very well in here, but you can kind of have a visual representation of what proportion came from each category. So it's really a visualization tool. And it's pretty simple. I would say just play around with it. This is where the icons come from. So for example, these circles that you see, this is where that comes from. I hope that's helpful. That's very helpful. Thank you, Kate. But that I do want to go ahead and close the webinar up. Again, if your question did not get answered I will be going ahead and forwarding those questions to the speakers. And I will try to somehow get the content of the answered questions to everybody so that way we can all benefit from those answers. So a little bit about who TechSoup is. We are a 501c3 nonprofit organization just like so many of you. And we do try to enhance or help your organization's mission by providing technology and technology resources that you need to do that. And if you go to the TechSoup.org website you can go ahead and go to our Learning Center or a blog to find some great written content. You can go ahead and try to find products and find product section on the right for our donation programs. And don't forget to sign up for our By the Cup and new product donation of our newsletters also on the right there. And I do want to take a moment just to really thank all of our presenters today. And I want to thank Microsoft for being so great in helping us put these webinars together. And then I also want to thank ReadyTalk who is our webinar sponsor for providing the tool that we had our webinar on today. So again, thank you everybody. And thank you Kevin and Meg who are helping us on the back end today. And I hope everybody has a wonderful day. Again, when you exit you are going to get a survey and please make sure to fill that out because that does help us in creating our future webinars. And the recording and additional materials should be going out later today. So thank you everybody.