 Welcome everyone to Excel for Beginners. We are glad to have you joining us today for our webinar. Before we get started I want to make sure everyone is comfortable using ReadyTalk, the webinar platform we are on today. You can chat in to let us know if you have any questions on the little box at the lower left side of your screen. You can let us know if you are having any audio issues, have questions for our presenters, or need help anytime throughout the webinar. Today's event is a 90 minute event so we hope you will be able to stay with us through the whole thing. Most of you will be hearing the audio play through your computer speakers. So if you are hearing an echo you may need to close out one instance of ReadyTalk in order to hear just one audio stream. If you have any issues with the audio you can feel free to dial in using the alternative phone number that was chatted out in the chat window earlier, and we will put that out there again for you. If you lose your connection at any time feel free to chat in to let us know and you can join again with the confirmation email you received earlier. Now if you got the reminder email an hour ago or the confirmation email within the last hour you would have received on the side of that email the slides for today, the sample class data that we will be working with, and a glossary of terms for your use as well. So feel free to check. Those are linked on the right side of those emails. We do record these webinars and make them available on our website at techsoup.org slash community slash events dash webinars. So look for it there. You can also find information on our TechSoup video channel on YouTube and on our slide share. Within usually a few days we will say for this one since today is technically a holiday for us here in California. You will get an email from me with a link to the full presentation, the recording, and any resources we discuss. If you would like to tweet with us today you can do so at TechSoup or the hashtag TS webinars. So feel free to use those. I would like to quickly introduce our presenters and get us rolling. My name is Becky Wiegand and I am the webinar program manager here at TechSoup. I will be your host for today's event. And we will be joined by Ariel Gilbert Knight who is the director of content here at TechSoup and she is our in-house resident expert on Excel and my Excel mentor. I go to her when I have questions that I don't know how to solve in Excel and she seems to always know the answers for me. We are also joined by Rachel who has her degree in business economics and accounting, and she has joined us to share her expertise from having taught a 7-week practical Excel class for the San Francisco Public Library. And she has rejiggered some of that content to do a 90-minute event for us today. She has also helped create lesson maps and assisted in writing California High School curriculum, volunteered hundreds of hours teaching in classrooms nationally and internationally. And she really loves helping people to learn. And we are really grateful to have her here helping us learn about Excel. We are framing this as Excel for beginners. And so we will start with some basics. So for those of you who may rank yourself slightly higher than beginner, never fear, we will cover some stuff that's interesting to you. And with the program as robust as Excel, you'll find that there are probably five different ways to accomplish the same thing. We'll cover some of those today. And hopefully you'll all walk away having learned some great information. You'll see on the back end Susan Hope Bard who will be chatting out with you and flagging your questions for our presenters. So our objectives for today, we are going to get a basic orientation of Excel to start. We'll talk about formatting Excel and cells and columns and rows. We'll look at the navigation a little bit so you understand where to find things. We'll talk about filtering. Then we'll cover some of the basics of manipulating data. Then Rachel is going to take us through Formula Theory and Structure talking about how to put formulas together. Then we'll look at the lookup which is a little bit more advanced than just beginner, but we thought we'd put that in there since many of you ranked yourself as 2s and 3s on the pre-registration survey. And then we'll look at the basics of inserting graphs and charts. I'll talk briefly about where to get Excel if you need to upgrade to a newer version or you want Office 365, or if you want Excel trainings. We have some of those available through TechSoup's catalog as well. And we'll do our best to answer your questions throughout. We will have a big crowd today. We already have 630 some people in the room so I can't guarantee that we'll answer all of your questions, but we'll do our best to get through as much as we can. Quickly about TechSoup, for those of you who may not be familiar with us, we are here in San Francisco at our headquarters office. So go ahead and chat in to let us know from where you're joining today. But in addition to being in our head office in San Francisco, we are also here. Every place that's blue on this map, TechSoup is providing and facilitating donation programs around the world with companies like Microsoft, Adobe, Symantec, Cisco, all kinds of technology providers around the world. So if you are joining us from outside the United States, you may want to visit TechSoup.global to locate your country on the map and see if there's a donation program in your area or a meetup where you can meet with other technology pros for social good in your area. Just quickly looking at our impact so far, I won't cover all of these, but in all of those countries around the world and in the US we have so far facilitated $5.2 billion, that's a billion with a B, of technology products and grants for the social good, those go to nonprofits, libraries, foundations, and churches around the world. I'm proud to be part of it. So let's get us started with Excel. Go ahead, I know we asked you this on the pre-registration, but since we have a lot of people who don't show up on the day of the event, we want to make sure we are targeting this and tailoring this as best we can to your needs. Click down on which of these levels you would self-identify as how you feel about Excel. Are you really brand new to it? Are you maybe fairly new and get a little bit of the beginner basics with level one? Are you using it regularly but don't really feel very confident? Maybe you're a 2 or a 3, or maybe you're the Excel Jedi Master who is just joining us to see how we teach this because maybe you teach it to your own community. So go ahead and let us know. This helps us get an idea of where you're at and it also helps us set expectations for people in the group. If we have a lot of folks who are identifying as very, very beginner, we may spend a little bit more time on some of the real basics, or we may put our toes into some of the more advanced things if we have a lot of people who identify as slightly more advanced. But we, like I said, are targeting this as Excel for beginners, so we will cover the basics either way. I'm going to go ahead and show the results. And it looks like 40% of our audience identifies as a 2, so that probably means you use it fairly regularly but not necessarily very confidently. You're not creating really elaborate spreadsheets that are all linked together and interacting with one another, but you're using it because most of us do use it in our day-to-day life, or at least our weekly life. So I'm glad to have you all joining us. For those of you who have never used it and level ones, we will spend time on the basics. And to do that, and to bring us into that, I'd like to have Ariel Gilbert-Knight, our content director here at TechSoup, start us off with the basics. Take it away, Ariel. We're glad to have you on the program. Hi, everybody. I'm Ariel Gilbert-Knight, and I am the content director here at TechSoup. And what that means is that I spend a lot of time working on TechSoup's educational initiatives and also a lot of slightly less fun time crunching numbers about the results of those initiatives. Now, I'm totally not a numbers person, but that's where Excel comes in really handy. With a few really basic tips and tricks, I can look like a total data super genius. And if I can do it, I believe you can too. So as Becky said, I'm going to cover some very basic information before handing it off to Rachel who will demonstrate some of Excel's fancier features. Now, some of this will be review for a lot of you. But one of the things about Excel is that there are often multiple ways to do the same thing. So even if you're pretty familiar with Excel, you're a 2 or a 3 or even a 4 on that poll, I hope you'll still learn a few tricks and tips to be more efficient. So what I'm going to cover is a quick word about what Excel is not, also what to do if you make a mistake, some basics on getting oriented and selecting data, how to format your spreadsheet, how to navigate more efficiently, and then one of my favorite features which is using filters, and then a very brief introduction to ways you can split and recombine some of your data, which I think kind of seems like magic. So let's get started. First of all, Excel can do a whole lot of things, but it really isn't a substitute for a donor database, a CRM or constituent relationship management system, or an accounting system. There are other tools that do all of those things, each of those things much better than Excel does. It's also not just for numerous people as evidenced by me being able to use it, and it's really not all that hard to use. You just need practice. So we hope that through this webinar and having the practice data and the slides you'll feel comfortable and empowered and excited even to start playing around with Excel yourself or get better at it if you're already a user. So I'm going to switch over to sharing my screen. So you should now be able to see an Excel spreadsheet. And what I'm going to cover first is that I have heard from a lot of people that they're really nervous about using Excel or a little intimidated by it, but just like anything else, you get better at using Excel if you practice. No one's born good at using Excel. And one way to feel really comfortable and good while you're practicing is to know that mistakes are totally okay. We all make mistakes, but that's why I always, always, always save a clean copy of my data before I make any changes to it. So to do that, you go down to the spreadsheet name at the very bottom of your screen, right-click on it, and choose Move or Copy. Then you just click Create a Copy and click OK. What that does is it saves a copy of your data. I usually rename the spreadsheet, one of them, something clear like original data. And that means I can do whatever I want, and there will be zero consequences because I have the original data saved and clean and pristine. And also keep in mind that most of the keyboard shortcuts that you already use in other Office products like Control-Z, for example, which is Undo, will also work in Excel. So between having a clean copy of your data and knowing that you can undo almost anything, I hope you feel empowered and excited to begin playing around in Excel. A quick bit of orientation to how your spreadsheet is set up. Your spreadsheet is made up of columns which are labeled A, B, C, D, etc. alphabetically. When it runs out of letters in the alphabet, it starts over at AA and then AB and AC. It also has rows which are labeled numerically 1, 2, 3, 4, etc. And then cells are named after the column and row that they are in. So this is cell A1, cell B1, cell C1, etc. And you can always see at the top left-hand corner here which cell you are actually in. Now in order to do anything with all of these rows and columns in Excel, you really need to know how to select data. There are a bunch of different ways to do this. One of which is if you want to select all the data, you go to this top left corner and just click it and that will select the entire spreadsheet. If you want to select the specific column or columns, you just click the column name or the column letter. You can also select multiple columns by clicking and holding down and scrolling right or left. The same goes for rows. Just click the row name and you can scroll up or down to select multiple rows. If you want to do something in a specific cell, you just click on that cell. And if you want to edit what's already in there, you actually have to double click on it. And you can see there's a slight change in how the cell looks. Your cursor actually appears in the cell. If you don't want to do all that clicking and lots of people don't, you can also just click on the cell and a click shortcut is you hit F2 and that will change it to editing mode. Now it's a very bottom of your spreadsheet. There are two other handy options. The plus sign here adds a new blank sheet. And this little scroll bar on the bottom right corner lets you zoom in or out. This is really handy if you're looking at teeny little numbers. It makes it much easier to view if you've zoomed in a little bit. I've gone over how to select rows and columns because you'll also want to be able to do stuff to those rows and columns once they're selected. I'm going to go over a couple of quick ways to update what's in your spreadsheet. All of the steps are listed in the slide deck that you should have received earlier, but you'll also receive with the post event email. So there are step-by-step instructions for everything that we're going to be going through today as well as screenshots to help you with your practice. So one of the things you do a lot in Excel is you're going to want to adjust the width of columns so you can easily read what's in a particular column. To do that you just go to the top of the column and you can see in between the two columns my cursor changes from a down arrow to this little cross with arrows on it. If I just click there I can move left or right and resize the column to however wide I want it to be. I can also just double click right there and it will automatically expand the column to the widest thing that's in that data. So in this case it's expanded as far out as the longest item in the spreadsheet. Oftentimes you'll want a column to be a particular width. So let's say I want this really to be pretty narrow, but I still want to be able to read everything that's in the column. Now I've made it narrower and what that means is that it's actually really hard now to read the header. There's a wrap text option in Excel that if you just click on it will wrap the text around so you can read the whole thing no matter how wide or how narrow your column is. It makes it much more readable if you just apply wrap text. You'll also often want to insert or delete columns or rows. To insert a column you just click next to where you want to insert the column. Right click on your mouse and choose Insert. If you want to delete a column it's very similar. Just right click and choose Delete in the little pop-up menu. Rows work pretty much the same way. Just click which row you want or where you want to insert, choose Insert. And if you want to delete just right click and choose Delete. Sometimes you're also going to want to hide certain data. You might do this if for example you have a piece of data that's relevant for calculations or you want to have for reference but it isn't really super relevant for everybody who is going to be reviewing a spreadsheet. So in that case you can hide the column. To do that you just click on the column, right click, and choose Hide. You can tell the column is hidden because if you look up here at the top it jumps straight from C to E. D hasn't disappeared, it's just hidden. Now to unhide it you just select on either side of the hidden column, right click and choose Unhide. And it's right back there on your spreadsheet. You can do the same thing with rows, click the row, and choose Hide if you're so inclined. Now a quick bit of overall orientation to how Excel is set up. Up here at the top is what's called the Ribbon. This is basically just your toolbar at the top of the screen. The Ribbon is organized into tabs, so the Home tab, the Insert tab, the Page Layout tab, etc. And tabs group together similar or like tasks. Underneath the Ribbon there's what's called the Formula Bar. In most cases what shows up here in the Formula Bar is the text that is in whatever cell you've clicked on. But this is why it's called the Formula Bar. If you have a cell that actually includes a formula, any kind of calculation, if you click that cell the Formula Bar will show you the calculation that's going on behind the scenes. So it's a handy way of understanding how calculations are being set up. I'm going to quickly go over some basic formatting options. Most of the formatting that you're going to want to do, your most common tasks are grouped right here on the Home tab. So in the Home tab, in the Font section, that's where you can choose your font, choose your font size, and format your fonts. So for example, I've just selected row 1, and I've decided, I don't really want them to be bold. I just click the Bold button and it unbolds it. You can also italicize text or underline it. You can also change the background color for cells. So maybe I've decided the screen is a little too festive for this somber topic of supplier countries and projects and borrowing. So maybe I'm going to go with a nice gray. I just choose which color I want from the little paint box here. You can also format in terms of alignment. So if you want your text to be centered, which is what I've chosen here, you can have it be left aligned or right aligned. Usually your labels here are going to be centered. And this is also as a reminder where the super handy wrap text is. The other thing I want to highlight is on the Number tab is where you can decide how to format the data that's displayed in your spreadsheet. This is useful for a lot of different things, but the way I use it most often is for formatting numbers and for formatting dates. And Excel is pretty smart. It usually has a pretty good guess of how to format things, but sometimes you just have a special way you want to format it. So in this case, for example, I have dollar amounts in this column, column F, and maybe for purposes of this report that I'm showing, we actually don't need all of those pennies. So I can choose to decrease the number of decimals that display and have it only show whole dollar amounts, for example. Or I can have it show many, many decimals if I'm so inclined. You can also choose which currency you want it to display in with this dollar sign option here. And you can choose to format things as a percentage as well. If you click the drop-down up here at the top, you can also choose for date formats. You can choose a specific date format that works for you and your organization. Like maybe you really just want to show the year. Maybe you want to show the full date, March 15, 2016, for example. And you can do that under the drop-down list up here. You'll probably find that there are a half-dozen tasks that you perform over and over and over and over again in Excel. And they may be located on different tabs. That's where customizing your Click Access toolbar up here comes in handy. So I've added the things that I do most often to the Click Access toolbar. So I don't have to poke around in the different tabs to find where they are. To add things to the Click Access toolbar, you click this little tiny arrow up at the very top of your spreadsheet. And then you can choose from some common commands listed here. Or you can go to more commands and choose just about anything you want to add to that list. So let's say I want to add Create Chart. I just click that, click Add, and click OK. And now you can see up at the top of my screen I have a quick shortcut to creating a chart. If you're not really so much of a mouse user and you really, really, really prefer keyboard shortcuts, I have a tip for you too, which is to use the Alt key. If you're in your spreadsheet and you hit the Alt key, it displays in the ribbon the keyboard shortcuts to navigate around to different tabs and perform different tasks. So for example, right now, if I hit A, it will take me to the Data tab and also show keyboard shortcuts for a lot of the items on the Data tab. So you can do a lot of tasks without really ever using your mouse at all if you prefer. I'm going to hit Escape to hide those shortcuts. And speaking of shortcuts and being more efficient, one of the things that I notice as a big difference between people who use Excel a lot and people who only use it once in a while is that really heavy duty Excel users don't spend a lot of time scrolling up and down in their spreadsheets. That's because hardcore Excel users have mastered a few quick navigation shortcuts. One of the most basic ones and the most useful is knowing how to freeze panes. So right now, you'll notice if I scroll down in this spreadsheet, the labels disappear. So I have to scroll back up to see which is borrower country and which is supplier country. And if you have a lot of data, that actually gets really slow and inefficient. So instead, you should use the freeze panes option. What that does is it freezes either the top row or the left column or both depending on what you prefer. So to freeze the panes, you go to the View tab and choose Freeze Panes. What that does is it makes sure that the top row always appears no matter where you scroll. And the same thing, if you scroll right, that far left column always appears. There are a couple of other handy shortcuts to help you break your scrolling habit in Excel. These are my favorites. There are lots of them. And what you use will really depend on what works best for you. But if you want to go to the bottom right corner of your spreadsheet, you can scroll and scroll and scroll and scroll down. Or you can just use a keyboard shortcut which is Control and that will take you to the bottom right corner of your spreadsheet. Similarly, if you want to go to the top of your spreadsheet, you can scroll up or you can just hit Control Home and that will take you to the top left corner of your spreadsheet. And all of these are listed in the slides as well. You can also use keyboard shortcuts to select data. So you can select data by clicking on columns and clicking on rows. But you can also select data with your keyboard if you prefer. So if you hit Control Shift with your left hand and the down arrow with your right hand, that didn't work the way I wanted it to. Usually what happens is if you hit Control Shift with your left hand and the down arrow with your right hand, it will select all the way down to the bottom of the column, not the bottom of the end of the known universe which it just did. So breathing past that for a moment, the other thing you'll often be doing is copying data down to the end of a column. So I've just added a formula here. Ignore the formula. We'll be covering a little bit about if statements later, but this is to demonstrate how you would copy the data down to the end of this column. You can go to the bottom right corner of a cell and you can see my cursor changes from a chunky plus sign to a little plus sign. And if I click on that bottom right corner, I can click and drag all the way down to the bottom. This is a short spreadsheet so that didn't take very much time, but it takes a really long time to drag all the way down to the bottom in a much bigger spreadsheet. So I'm going to show you a much faster way to do it. I'm going to hit Ctrl-Z to undo what I just did, and show you that you can just double-click on the right corner here, and it will auto-fill all the way down to the end of the column, much, much faster. Now there are some situations where auto-fill doesn't work. So in the slide deck, there's also step-by-step instructions for nifty shortcuts you can use if for some reason you double-click and auto-fill isn't working for you. Then the next thing I wanted to talk about is my other favorite quick thing to do in Excel which is using filters. Let's say you have a list of projects like this spreadsheet and you only want to see certain kinds of projects. Filters allow you to select only rows that have certain things in them. So for example, if I wanted to look at only projects in certain countries, filters will allow me to do that. To filter your data, you click at the top of the – you click what you want to filter on. In this case, I want to filter on what's in this row, and you go to Data, and click Filter. You can see that filters are available because these little drop-down arrows have appeared on the screen. To use a filter, you just click the little drop-down arrow, and it will give you a list of everything that's available to filter on. So let's say I want to see just projects in Bolivia, and Brazil, and El Salvador. I just click those, and click OK. And it filters the list to only show projects in those countries. And the handiest thing about filters is down here at the very bottom of the screen, it will tell you how many records you're looking at. So I know without having done any counting, or any formulas, or any kind of math, I know that there are seven projects that occurred in Brazil, Bolivia, or El Salvador. To remove the filter, you just click the Filter button again, and choose Clear Filter, and it just goes back to showing all of the data. Now, depending on what kind of data you have in a particular column, you'll have different filter options available. So in borrower country, it's words, country names in the list, so I have text filters. If I go to Total Contract Amount, this is a number column. It has numbers in it, so I have number filters. I can, for example, choose to look at number filters, and I want only projects that are greater than $10,000. I click OK, and this filters the list to only show me projects over $10,000. And remember again, down here in the left corner, you can see the count of records that are over $10,000. So I use this all the time to just get quick counts of things that match certain conditions. So webinars with over 100 attendees, things like that, it's a really quick, easy way to get a count. You can tell that a filter has been applied to your data because this little drop-down arrow changes to a little filter here. So you can tell which column is being filtered on. And you can also see over here on the left that the row labels turned blue, and there appear to be some not displaying. That's another key way you can tell that a filter is being used. And if you want to get rid of all the filters, you just go back up to the Data tab and click Filter again, and the filters disappear. All right, I have two other quick things to show. One of which is how to split up data in a column. This I also use all the time, particularly for name data. So a lot of the time, you'll end up with a spreadsheet that has names formatted this way, which is last name, first name. But in fact, in order to really do anything with it, you actually want it to be last name and first name in separate columns. Excel has a feature that will do that for you, rather than you going in and cutting and pasting and doing it all manually, you can use the Text to Column feature. Before you do anything though with Text to Columns, you want to add empty columns into your spreadsheet. So I'm going to do that by clicking up here, right-clicking to choose Insert. I'm going to insert a couple of columns because otherwise when I split up this name column, it will overwrite things to the right. So I need to give it space to spread out into two columns. Now to split it up, I just click the column that I want to split, go to the Data tab, and choose Text to Columns. And that will pop up a little wizard that will walk you through step by step what you need to do to split up your columns. The first thing you do is you choose where you want to split. And it uses a lot of words that you can completely ignore. Basically, most of the time, a lot of the time, you're just going to choose Delimited here because it's commas that separate the data. You want to split where the comma is. So you just choose Delimited, click Next, and then make sure that the thing, the point you want to split on is selected here. So in this case, we want to split at the comma. So I choose Comma, then Next, and Finish. It's warning you that it's going to overwrite some data, but we're okay because we've added columns to accommodate that. So I click OK, and ta-da! I have separate last and first name fields. If your data was set up as first name, space, last name, so for example, Erin Aberg, Text to Columns also works in that situation as well. You would just do the same thing, which is select the column you want to split, choose Text to Columns, choose Delimited. But instead of Comma, you just choose Space, which means you want to split at the space between first name and last name. I'm going to cancel out of that. So you can use this to recombine or to split up name data into a format that may be more usable for you. Sometimes it's not always about breaking up your data. Sometimes it's about wanting to recombine it. So I'm also going to show you very quickly how to use the AND function, and it's AND as in ampersand. So let's say we have our first and last name split up, but actually what I really want is first name and last name together. You can use the AND function to kind of mash together multiple items in Excel spreadsheet. So what I'm going to do is I'm going to type equals, and what that does is just tell Excel that you're about to lay a formula down on it. And Rachel will go into in just a few minutes the theory and structure of formulas, but the key thing is that almost all your formulas you start by typing an equal sign in. And in this case, what I want to do is tell Excel to combine first name. So I'm just going to click the first name field, type an ampersand, and last name. I hit Enter. And that's close. It has first and last name together, but it's not really readable and it needs a space in between. So I'm going to do one extra step to this, which is quickly go in to the formula bar and edit the formula to tell Excel that I also want to speak in a space between those two names. So I do that by doing a quotation mark, a space, and another quotation mark, and AND sign. What that does is tell Excel that I want it to do first name and a space and last name in that cell. So you see if I pull that down, I have first name, space, last name for my name data. I hope you found these tips helpful. I'm going to hand it back to Becky and see if anybody has any questions. Becky thanks so much for that, Ariel. We had folks asking just if you could repeat quickly how you made a copy at the very beginning of your data. We had a couple of folks who missed that when you first started out, so how you created that original copy. Can you show that one more time? Sure. You just go to the sheet name down here at the bottom that you want to copy. Right-click and choose Move or Copy. And then you click Create a Copy Here and click OK. We're not actually seeing your screen at the moment. Backpumped backpumped is helpful that way. It certainly is a little bit less helpful when people can't see it. Let's try that again. Can you see it now? Yep, we can see it now. Okay, cool. So you go to the bottom of the screen and right-click on the name of your spreadsheet that you want to copy. Then you choose Move or Copy. And then you can select Create a Copy and click OK. And that just creates another copy of your data. Great. And somebody asked for clarification on where that copy is located. And you'll see that it's actually just added as another tab right down there at the bottom. And you can then rename that tab, which Ariel showed earlier as original data or you can rename it copy of data or something like that so that it's clear to you that you've got two different ones that are just in the same spreadsheet as separate tabs. Great. We also have somebody asking about Google Sheets. And if these processes are similar, if you're using not just Excel, if you're using some of these functions in Google's spreadsheets, do many of them work the same? I think some may, others maybe not. What do you think about that, Ariel? So some of the keyboard shortcuts do work the same if you're using Google Sheets. However, my experience has been that a lot of the more sophisticated features in Excel aren't really present or as easy to use in Google Sheets. And don't get me wrong, I'm a big fan of Google tools. But if you're going to really be doing a lot of work with spreadsheets, I've found that Excel works better for me just because a lot of the more advanced features are less available or less easy to use in Google tools. Great. We also had a person who asked, we started with some sample data here, but if you were setting up a spreadsheet from zero, like if you're just opening up Excel for the first time, do you have any quick tips you want to offer to people on how to set it up? Like we have this column at the top and it's gray. Do you recommend doing that? Do you recommend bolding the titles? Is there something you need to do so that Excel knows that it's the title of a column? Any quick short tips on that? Sure. Basically all of the things that you just said. So if you're starting a fresh spreadsheet, it is really helpful to have clear labels for your data. So I'm going to go back to the one that's already set up. And I personally like to have the labels for my data be a different background color because then they pop out a little bit more and to center the column names. And often too, I'm just going to do a quick shortcut here to bold them. It just makes it much more readable. And Excel is usually smart enough to be able to tell that your first row or whichever row is your labels, it can usually tell that that is the label row. It's pretty clever that way. Great. So I'm going to go ahead and have a stop sharing because we want to get to Rachel's section. We know there's more questions in here so we'll try to answer those as we go along. But before we move along to Rachel's, I have a quick question to see how closely you are all paying attention. Ariel mentioned at one point what key you should hit to view all the keyboard shortcuts on the screen. So for those of you who don't want to use your mouse to go find those different features in the ribbon or in the menus, go ahead and click on which answer you think is correct. And of course if you're already seeing the results on screen, there's a giveaway there that people are probably seeing what's being voted for. But we want to make sure that you're learning along with us. And we know that it's going quickly so if you've missed anything don't worry, you're going to get the full recording. You can watch it at your convenience and play and pause and play and pause and play with that sample data on your own. I'm going to go ahead and skip to the results here in just a few seconds because they still have a lot of people voting on their answers. And then we will get to Rachel's section that's going to take us through those formulas, folks who are interested in learning more about formula theory and structure and how to create formulas and apply them. We'll talk through a bunch of those. We'll look at locking formulas so you can lock them in. We'll look at VLOOKUP. We'll look at charts and graphs a little bit. So we still have a lot more to come. And we'll have more time for Q&A at the end. So again, this is a 90-minute webinar so we want to make sure everybody is able to stick with us as long as you're able to. So to show the results, lots of smart folks out there got that the alt key is what will help show all of those keyboard shortcuts on your screen. Just to clarify, control Z is your undo and that's the same in Word and other office programs. So if you make a mistake you can always control and Z to go back a step to undo a step. And I'm not going to go over what all of these other things are, but hopefully you'll get those skills the more you practice this on your own. And that's the biggest part about this is that a lot of it just takes a little bit of practice. So with that I'm going to go ahead and have Rachel get us started on her section. She's going to go ahead and start sharing her screen so we'll be continuing to answer questions and flag them in the back end. But she won't actually be able to see them coming in while she's sharing. So thanks so much Rachel. We're so glad to have you joining us and sharing your expertise. Of course, I love doing these trainings. I think it's a lot of fun. I'm going to share my screen now and go over Formula Theory. So you should all be able to see my slide now that says Formula Theory in Excel. And the one great thing about Excel is that it is set up very logically. And once you start to understand the basic format of formulas, it's very easy to say to start thinking about, okay, I want to find the biggest number. I want to find the smallest number. I want to make everything uppercase. I want to make everything lowercase. And once you kind of start to think about this is what I want to do, you can sometimes I Google what the formula is and then you plop it into the same exact format. And that will start to make sense as we go through this more and more. So if you see the top, formulas all have the same basic structure. You will always write equals and then you will put in the name of the formula here. It will have an open parentheses to start the formula. And then you will see it always ends with the closing parentheses. And within the formula you will see that there are different elements. And each formula requires different factors and different pieces. And so it will name them out for us, it will put them in, and it will add a comma to separate each of the elements that we need. A few notes on formulas, case does not matter. This means it can be upper or lower case. You can switch it within the formula. It will not change. And it's also great because Excel will prompt us what the elements are. I will go through and show us how to do a formula now. We'll go into Excel. So I'm going to show you how to do account and how to do an average formula. So I'm putting my cursor here into G3. And I will start typing the basic formula structure. I will start with the equal sign. And the formula I'm doing is called count. I will type count. And you will see it pop up down here because it already knows what I want to do. And the next fact after you type the name is to do the open parentheses. And as you see down here it is prompting me what to do. Underneath the cell it says value, value 1, value 2. I could individually go in and click on each cell, put a comma, and click on the next cell and show it what to count. Or there is another way to do this as well. You can go equals, count, open parentheses. Instead of typing in each value you can highlight the entire selection here. So I'll start at the beginning. I push shift, control, and down arrow to take me through the entire list. So now you can see it's taking me through F7 through F56. I will close parentheses and it gives me the number 50. As a note on the count formula this only works for numbers. If I wanted to count non-numeric I'd note the formula is count, it is count. Then A, and then open parentheses. And these are tricks that you learn while you go through Excel. But if something is not working there's usually a reason why in a different formula. And that's when Google is helpful to ask questions like that. We will do an average formula now which will be the same format as a review to go over the formula format. We will type in equals. We will type the word average, open parentheses. So again we can put individual numbers, individual numbers in cells separated by commas or we can do an entire range at a time. So I want to do the range. I'm going to start in the first cell. And I'm going to use my trick of pushing shift and control with my left hand. And then I'll do the down arrow with my right hand and it will take me to the bottom of the list. And then I will add a closing parentheses and push enter. And now I have the average for this section. I'm going to go back to the slides to go over the next part. So we just went over this together. And now I'm going to show an if. I'm going to talk about if formulas and theory and then show that with you. So an if formula is starting to get into different logic pieces. So what the structure is, it's the same. It's equals. It has the word if and then it has open parentheses. And as you see in this first section before the comma, we have true check. So we are checking to see if something equals something, if something is bigger than another value, if something is smaller than a value, if something starts with a letter, whatever it is we want to check that we will have two cases. So if this number is bigger than a value specified, we want it to give one input and that's the second section. If that's not true, we tell it what to input instead. So I will build this for us now in Excel so we can look at it together. So I'm in the second tab now in raw data 2003. And I am going to put my cursor in G7 here. And that's where I'm going to start writing the formula. But first I'm going to set up my formula. So I am looking at total contract amount. And I want to say I want to, for some reason, I need to separate them all into big, just small contracts. Maybe they are going to go to different teams to work on. And I want to easily do that and assign any contract bigger than $50,000 is going to be called a big contract. So I'm going to write it up here. I'm going to do $50,000 in this cell. That's the number I selected. So I will say, I'm going to write my formula in G7. I will write equals. Then I name the formula which is if. I will do open parenthesis. And you will see here that Excel is guiding me through the formula. So what is the logical test? That is what we are testing. So I want to know if this number is bigger than this number here. And a very important thing to do is to lock this cell. So we are going to push F4. And you can see that that put these dollar signs on either side of our cell. What locking means is that when I drag the formula down, it will always check if it's bigger than this cell right here. It won't drag this value down with it. So we have the first part. If F7 is bigger than $50,000, then it says the value if it's true. And I want it to say big. So I will put my cursor here and I will push F4 to lock it again because I always want it to refer to that cell. If this value is false, I want it to say small. So I will put my cursor and then I push F4 which gives me these dollar signs. As a note, these dollar signs can also be manually typed. I just prefer to use the shortcut. And also I could type in right here. I could type in small like that and big instead of having it reference a cell. I prefer to have it reference a cell because after I write the formula, I can go back and change these values and it will apply to every formula I've written and I will show that in a moment. So I will close the formula and you can see that it worked. And I will show again how to drag a formula down. Once you're in this cell, you will put your cursor on the bottom right and you see it turns into this thick black cross. And so once I'm there, you can double click that and it will bring the formula down to the end of the column. And how it knows is the end, it looks at the column it's closest to and it will take the formula down all the way to the end of that data. So if you look down here, it has filled it out until the very bottom. So big, small, big, small. So I will go in, what if I don't want $50,000 to be my indicator anymore? I'd say I want to use $100,000. What I can do is since they're all linked to this cell, I can say $100,000. And now as soon as I push enter, it automatically updated. The other thing is what if I want them all to say really big? So if I go to this cell, I can type really big. And as soon as I push enter, they will all update. And so that's why I like referencing and locking cells as opposed to typing in. I am going to go back to my slides. And here we left a place for questions as if formulas tend to be something that people like to ask questions about. Thanks. So far we don't have any questions specifically about if formulas, but we do have a clarification around locking a cell. What does that mean? So why would you want to lock cells? I think there's a little bit of confusion about why you'd use the F4. And I did type out to our users in the chat who are on Macs that it appears that there is an equivalent with Command-T for Mac users according to the ever great Google tells me that's the answer. So what is the purpose of locking cells? Why F4? That is a great question. I'm glad you asked. So to explain this, Excel is a relational database. So for example, if we, so here in this formula here, we said if F7 is greater than this value, do these things. We did not lock F7 because when we go down, we want it to then reference the cell immediately below it. And when we push it down here, in this cell we want it to reference them out to the left of it in the current row. So this is the same thing. If I were, I will unlock these cells and show you. So I'm going to clear content. So the equals F, if this is bigger than 100,000, put really big, or put small. So if you see here, it highlights all the different components I have. It has this cell, has this cell, and this cell, this cell. So now what Excel is doing, it's counting positions the way it is for my reference cell. So it knows that F7 is one to the left, and it knows that this 100,000 value is one, two, three, four, five cells above. If I don't lock it, it will continue to do that same format of one to left, five above, and I will show that now. So if I drop it down, one, if I push F2 to show you the formula, it now is referencing if F8 is bigger than G3. So all it did was it counted one, two, three, four, five above, and that's the pattern. It keeps the distance and relation from the formula with it. So if we lock it, and you can go into your formula just by clicking on the numbers you want to lock and push F4 over each one, F4, and F4, it will keep the position and it will keep the position and it will lock it. So now this is six above and difference, but it still brought this one down and didn't lock it. It brought it down with it there. Great. Now we had a couple of questions just to clarify. How did you unlock the cell when you want to be done with it being locked, or if you want to be done with it being locked? There may be a shortcut. I actually don't know if there is, but what the formula looks like, it's just the dollar signs in front of it. So you can delete the dollar signs, which is what I do. I get rid of the dollar signs, or I completely start the formula over. So the dollar signs lock it. So the dollar sign in front of the G locks the column. The dollar sign in front of the two locks the cell. That's why there's two different because sometimes you may want to lock only the column or only the cell as you get into more advanced model building. Great. We also had a question about do the 100,000 really big and small cells need to be in the same column? So do these all need to be in the same column? No, I can put them in different columns, reference them anywhere. If I do this now, so the feature with Excel, if I were to just drag them, Excel is relational, so it will remember that these are the cells I want. If I drag it, it's just like I moved it, so I can put them there. It's not the same if I copy and paste though. If I drag and move, it's like moving their home with them. If I cut and paste them to different spots, I took these people out of their house and put them into a new house. So it's kind of like if you see the cell as a house, you have to physically move the cell with them, but it does not matter. They can be completely anywhere. I can write the brief like the formula here, if this is bigger than 100,000, let's walk it to really big, walk it, and then just walk it, and it will still work. Great. We had a handful of people who asked for some more clarity on the if formulas because I think they missed a little bit of the logic base of what an if formula means. So can you explain that one more time briefly? Thanks. Absolutely. And that's a great question as well. So an if formula starts to get a little bit more complicated because it's going into a different realm of math. So with a realm of math, it's typically called to say it's a logic or logic statement. And so how that works is the first part is it's trying to see if something is true or not. So here we have if, so if, then it says is this portion true? If this amount is greater than this amount. So is this a true statement? If it is true, we want it to do one action. If it's not true, we want it to do a second option. And here, that's what we did. If it is true that this amount is bigger than this amount, put in this first step. If not, do the second step. And so it's kind of a mini test. And it always tests the first section true to see if that part is true and it will default to the true statement and it will skip on. If it's true right away, it doesn't even look at the false and it goes to the next one. Is that clear? Yeah, I think it is. I mean, I think people may be just confused on when to use an if formula. So if in general you are trying to say if this person attended our gala last year, for example, for throwing a nonprofit example, or if this person attended our church retreat, I want their name to be pulled over to this column, or I want this to be checked off in their box or whatever it may be that you can put in a formula to make that happen. And if they did not, then it also does another action to tell you that it didn't. So it's a way of helping to sort and categorize your data. And you can do a lot more with it than that, but I'm trying to look at it in really simple terms so that people understand what the purpose of it is. So I mean, for this case that Rachel's been sharing, if their contract amount was considered small, then it identifies them as small if you put that formula in there. We have a bunch of folks asking about if there is like a master list of all the formulas out there for Excel. And I don't know if there is, I would imagine there is kind of an unending list of formulas you can create within Excel. If you look at the formulas tab at the top of the screen, you'll see an option for recently used formulas and auto-sum. Those are ones that are pretty commonly used, but there are tons of formulas under each of those little drop-downs that Rachel is showing on screen right now. And some of them you may never ever want to touch. And some of them, like what we're covering today is trying to help you identify a few that we think are most likely to be useful for you beyond just the basic of, I want to add up this one column, which is summing the auto-sum feature. So I want to get us back into the rest of the presentation here, but keep asking those questions and we'll keep trying to answer them on the back end. So I think at this point we're going to go through, do you have Next on your list count? Oh, we did, sorry. I missed that. VLOOKUP is Next on our list. So sorry about that. I was looking at the wrong spot on the outline. So we're going to look at VLOOKUP, which is a pretty handy tool and it's one that still blows my mind a little bit and I'm not an Excel person. And I'll just give you an example of how I use it. When we have people who come to our webinars, for example, we sometimes will ask them in a poll, hey, do you want to opt in to subscribe to this presenter's newsletter? And so people may say yes, they do. And then later on I've got a spreadsheet of data of people who said yes and people who said no, but it doesn't match up with the spreadsheet that's a totally separate report of people's names and email addresses. So I need to be able to pull the yeses, email addresses from different sheets into the other so that I can then give that spreadsheet to the presenter and say here are the list of people who opted in to sign up for your newsletter. Here's their email addresses. And if I've got them in two different sets of data, it's a totally manual process for me to have to go through and pull name by name and email address. But VLOOKUP solves that for me. And it is quite miraculous and I still don't totally get how to do it. So we don't expect you to necessarily get how to do it if this is your first time looking at it. We want to show it though because we think it's pretty fantastic in what it can do for you. And with some practice, you should be a master of it too. So go ahead Rachel, show us how to VLOOKUP. Yes, and what Becky said about that they are confusing at first, they are. I had someone explain to me probably five or more times. Wait, what does that mean? I'm confused. Please show me. And after doing them over and over again and watching someone doing over and over, they finally make sense. And once it clicks, it clicks and they are able to do them over and over. But it do not be discouraged if you try the first few times and they are confusing because they are. So the purpose of a VLOOKUP why I like to use them to reiterate what Becky was saying is that it's fine if you have two separate sheets, it finds information that relate to each other in both sheets. So as Becky said, if you have people's names, let's say you have one sheet where they are all sorted alphabetically by first names. Everything is in order for 100 people. And the other sheet is sorted differently. There is no way to find that person easily except looking back and forth and searching for them. You can tell Excel, find this person, find the name and sheet A, and find that name and sheet 2, and bring back a certain data point. So it helps you find information when things are not sorted in the same exact way. So to go over the basics of a VLOOKUP, there is one thing you need first. So to perform a VLOOKUP, there needs to be a consistent and unique reference name for the thing you were looking up. It can be a person's name spelled and spaced exactly the same. It can be a project number. It can be an employee ID. It can be a country name. And Excel even takes into account the spacing before the name, after the name, and between. So as long as the cell looks exactly the same, it will be able to find that cell in sheet 1 and sheet 2. So in our example, we have a sheet of different projects, and each project has their own unique project ID. And we have two tabs. We have a tab of Projects in 2014, 2015, and then we have a tab of all projects that existed in 2013. So we want to look at values for these specific projects between the two tabs. So with the VLOOKUP is formula tonic cell, it says look for this 2014, 2015 project ID in a 2013 list, and then I'm going to ask it to return the value that this project had in 2013. And we will go through that together doing it step by step. So the other thing that you need to do, the other thing to keep in mind is that the project ID, the unique identifier, always needs to be to the left of the information that you want to look up. This is because Excel counts to find the column you want, and it counts to the right. So it needs to be able to see the unique identifier all the way to the left. So typically in data sets, the unique identifier is usually always the very first column. And as you start going through, you'll see that more often. And when you make your data sheets, it's good to put that there as well. So we will do one together. I'm going to show you the formula structure here, and then we'll write one. So we're going to keep the same formula structure we have. It is equals. Then we type in one word, V as in the letter, lookup, open parenthesis. And then we can click on the cell that has the unique identifier, which is the value that Excel is going to look up in the next sheet. So we say, okay, this is unique identifier. I'm going to add a comma because I'm going to do my next element. And then we will tell it to the selected range that we want it to look for the next unique identifier in. And then we'll tell it the number of columns that we want it to bring up by a number. And then we will type the last piece is that you always type false. And this has to do with if you want an exact match or a non-exact match. And I have never typed anything other than false. So you can always end if you look up with false. And now we will type it together. I'm going to go to Excel. So I am here in the more recent tab. And what I want to do is I want to bring in for any project that we have in 2013, I want to bring the amount that it had in 2013. And then I want to see if there is any difference from 2013 to what the status is here in 2014 and 2015. So my cursor is in G7 and we will start typing the formula. We will type equals. Then the name, Z, lookup, open parentheses. And as you see here it has the guides down here what we want to look up. So right here it says lookup value. So I am looking up the project ID that is how I'm going to identify. On jukoma, table array, the most confusing part about the lookup is sometimes the name. So table array means where do I want to look this up? And I'm going to look it up in the 2013 tab. So I'm going to click on that tab. And I want it to look at these project IDs. So we need to put our cursor where the unique identifiers begin. So I will put it here. And then we highlight all the way to the bottom of the list and make sure we get all the columns we want. Since we want to mount, I'm going to push shift down and then use my light arrow just to highlight all the way across to a mount so we have that in. And then I'm going to highlight to the very bottom. And as we discussed before, we want to lock this section. We do not want to shift. We do not want to go up and down. We want Excel to look only in these specified cells over and over again because this is where the top and the bottom of the list exist. So we do that by pushing F4. And you can see up here that I added those dollar signs. So we will put another comma in to do the rest of our formula. So this says column index number, which is also kind of a confusing name because it's not very intuitive. So it's just saying we tell Excel which column to bring back. So where we first put our cursor was a project D. This is column 1. And then this is column 2. And this is column 3. So we will type a 3 here because we want Excel to give us back this third column. And again, we will always end the V-lookup with the word false. We never want an approximate match. That's used for like very scientific biological studies where it's off by like hundreds or thousands of a decimal. I have never seen it in real use. And I actually don't know anyone in the medical and science fields that use it that way either. But that's what the use case is. So we want it to be exactly the exact match when you push Enter. And it will bring us back here. And so here it shows us what the amount was last year, which is great. So now I can do a comparison. I can say, okay, what is this minus this amount? And now I can see the difference it was in the two years. So if I drag these down, I can drag two at a time. You highlight both. And then you can double click this bottom once you see the thick cross. And you will see that there. As you'll also notice, some of these come up as an A. What is that? They look really ugly. So an A means that Excel did not find this project in the other list, which means it did not exist. So I know any of these NA's means it's a new project, new after 2013. And there are different tricks you can put in. We won't go through it today, but just to share information, you can do a formula that's called ifError. It's like our first if formula. And you can tell it to put a blank or to put a dash or put to put anything else instead of putting this at A there. And there's something to Google on your own time and go more into that because it's a bit more complicated for this webinar. I feel like there may be some questions on this that we can go over together now and we can even do it again. Sure, we have a question asking, can you merge two separate Excel docs with VLOOKUP or is it just certain data that you're pulling from one to another? You can merge columns. What you can do is you can bring in information for any project ID that already exists. So if I have 100 project IDs here, it will only bring in information for these 100 projects. It will not add to the list in the bottom. So let's say wellness is 100 projects and the other list is 300 projects. You cannot combine those rows in together. So it will only bring in the different columns and data points for this. So you can merge data sets that way but not on top of each other. Great. And we had a couple of questions asking, what does the V stand for? And it does stand for vertical. So it is looking down the vertical column for data and then it can go across to pull stuff. So it goes down and across. So we know it's confusing and we added this in because we had quite a lot of people who self-identified themselves as twos and threes which is somebody who uses Excel a lot. We didn't want you to be bored so we threw that in there. But we know that this isn't true beginner stuff so we hate to confuse anybody. But people want to be able to merge data from across documents or from across spreadsheets or from within their own spreadsheet. You've got one sheet. You want to pull how much did you pay for this one bill at one time last year? If you've got 5,000 things on the list and the control F to find it is not helping you find it, then a VLOOKUP can help pull that data for you. So I would recommend practicing this one on your own and looking around online. There's lots of little tutorials on VLOOKUP as well. I know that we've got a lot of people who would love to see it again, but I also want to be conscientious of time that we have only about 15 minutes left and we have lots of other questions and we still have charts and graphs. We want to show you how to create some simple charts and graphs. And this is really beginner stuff. So if you have a data set and you want to be able to turn it into a chart or a graph, there are some really simple automated ways within Excel that are already there to recommend some charts and graphs to you. So we want to make sure you know how to do that as well. So Rachel, go ahead and show us some charts and graphs. We'll still get to some questions later on as well. Perfect. And what I also want to say is if you have any questions on how to do VLOOKUP, it's great to Google and go to YouTube and you can see pretty much anything performed right in front of you. That's how I learned a lot of the things that I know now, just trial and error, trying to Google how do I do this and kind of explain it. And Google is pretty smart where it can figure out what you're doing most of the time. So I am going to quickly show how to insert a chart. It's actually very easy. So here I made a chart where it has the month enrollment and then it has the different enrollments for the months and the different years. And charts, it's good to label everything. So I have the month here, enroll in 2014, 2015, and I have all my data elements. So to make a chart out of this, you highlight what you want, a bunch of chart, and you can go to, you go up here, you go up to the ribbon, and you click Insert because we want to insert a new chart. So click on Insert. And I think in the preview I sent, I was on 2013 Excel and this is 2010, so it looks a little bit different. But what you can see here is that it has all the different charts you can do. If you're on 2013, it will have recommended charts. So I can go to column and it shows me all these options here, line, pie, bar, area, scatter, other charts. I like to click on all chart types and then I can see them all at once. For this type of information, I think it makes most sense to do a bar graph or line graph so you can see it together. So I will click this one here, and it created a chart I wanted, or say I want to do that a different way. We'll do it again. We highlight the information we have, then we can go to Insert, and I want to do a bar chart this time. I like these bar charts that are side by side. If you do these ones, they will stack the information on top of each other. I don't really think they typically don't really make that much sense. So I have it, it comes off as a horizontal. I'm going to make this a bit smaller so you can see it. It came across as a horizontal chart. And here if you right-click it, that's where you start playing around. And that is the best way to really start playing around the understanding chart. You go to Move Chart. You can move it to a new sheet. If you right-click, you can change the type. You can select new data. You can format chart area. You can do colors, fill, shadows. It's really endless and that's really as easy as highlighting it and inserting. And for those of you who don't have one of the newer versions of Excel, that little widget on the bottom right side of the data doesn't necessarily show up there, but you can always go up to Insert and Insert charts or graphs. Like we said at the beginning, there are sometimes two or three or five different ways to do the same thing within Excel and that's what makes it so robust. So if the way that we've shown today is not the way that you're familiar with, that doesn't make it wrong, doesn't make it right, doesn't make anybody's right or wrong, as long as it gets you to the answers that you need. And that's kind of the beauty of this tool and also what makes it a little bit complicated because maybe somebody showed you how to do it one way and you've seen it done a different way and it's confusing because you don't know that way. And that's okay. So we want to put it out there that there are lots of ways to do these things and Google is, you know, Googling, binging, whatever your search engine of choice is, is a great way to find more on any of these different functions and tools that we've been talking about today. In that glossary of terms that we attached to the reminder, you'll get that as well. That calls out by name what all of these things are that we've been using today. So we've called out IfFormulas, we've called out VLOOKUP, we've defined what those are. So you can go to Google and search, how do I do an IfFormula? So you can spend some time studying up on it on your own because in 90 minutes we're not going to be able to teach you everything at all. We're dropping the bucket. So we are doing our best to get through it. Before we move on to wrapping up, I have a few other questions that I wanted to ask you, Rachel. Some folks have asked about coloring the cells to make the cells look a little bit better. Is there a way to fill in cells that just have data in it rather than the whole row? Can you have it just populate individual cells? And if you do, do you have to do one by one by one? Or is there a way to just say fill in all the cells with color that have data in them? Yes, there is. So there is something called conditional formatting. And as a known conditional formatting, it takes up a lot of memory on your Excel doc which can make it harder to open and sometimes crash the file. But if it's something that has a smaller data set, it can be used there. So I will show you. So I'd say you can select the cells you want to do conditional formatting. So if you select right here, I'm just going to do these. And I'm going to go to the top, home, there's a section that says conditional formatting. And I can do a highlight cells rule. So I'm going to highlight any cell that is greater than zero with greater than zero. So I can pick the colors too. I can do green, I'm going to do green fill. So that is one way. You can also, you can do, you can apply this to the whole sheet. You can say if it's greater than a certain amount. As an auditor, I used to do this if someone gave me a data set and their numbers were different. So I highlight everything in red if it's different than what they originally told me. So it kind of brings things out. You can also go to new rule. And then you can really have a, you can go and you can assign scales. You can do only if it contains. So right here, if the cell value is between two numbers, you can get more creative. And that's something probably to also Google conditional formatting to go through that a little bit more. But it's definitely possible. Terrific. Jumping back to the charts and graphs, we had a couple of questions asking about, can you create that chart in Excel and then plop it into some other document? From my experience, it's as easy as copying and pasting, like just highlighting the whole chart. So I don't know if you'd want to show that really quickly, just where people can right click to copy a chart. And then you can paste it into Word. You can I think even save it as an image file to plop into something else. But it will automatically be in your Excel spreadsheet. And it could stay there if that's the only place you need to have it. But it also just plops right into Word as Rachel just showed. So that's pretty handy that you can copy and paste between the different Office Productivity Suite programs. Let's see, we also have a couple of questions about when you're in a chart or graph and somebody wants to change the X or Y axis on the graph. Is there a way to do that? We're not sure off hand. We're looking to see if we can find that answer. So to be perfectly frank, I know there is a way. I'm used to using 2013. And typically what you do is you right click and you can change, you say switch axisies and it will switch the both of these. And I just do not remember where it is in this version. I will Google it right now and tell you. Great, and we'll try and get that answer. But it sounds like it's something that you can probably search for on your own as well. Switch X and Y axis on a graph in Excel and you probably would find the answer. Brick asks, how do we get the bar chart to start with January? So right now it's starting with December which is at the bottom of the actual data set. If we wanted it to start with January, is there a way for us to invert that text or change it? Yes, same exact way. So I just Googled, I literally, when my phone inside, how to switch the X and Y axis. And so I can go to switch row, column, data. Oh, that is not what I wanted to do. So it is the same thing. It depends on the version you're off in, but you can have it switch a different way. Sometimes you can't. So I go in and I just reformat the order that my cells are in here. And that's typically what I do. Sometimes I have to play around with the data as opposed to going into the charts. So what I did here is I switched row, column, data but that's not what we wanted. I had the enrollment of the bars and then put this there. I still am looking to where to find how to switch the axis. No problem. And like I said, we won't get to every answer during this period of time. But we also had a person asking, in your general spreadsheet, if you want to have every other row be a different color just to make it easier to read, where can you do that? Is that also in the conditional formatting? Or is that something that you have to manually do? That is, I've done that before. I haven't done it in a few years, but I would also Google how to do every other color. And it's a setting in Excel and I would have to look for that again since I don't use that regularly. And that's how complicated Excel is. There are so many things that it's hard to remember everything. There are some formulas I still have to look up every single time, but that is something Excel does with the right formatting. Great. I'm going to go ahead and jump us back into the slides because I want to quickly talk, before actually we talk, I want to have you take another little quickie quiz here for us. Tell us on the screen, click on one of those radio buttons why you might want to use VLOOKUP. Is it to create a charter graph? Is it to find data from a table or spreadsheet? I couldn't write the whole thing on some of these because character limitations. Is it to create an if formula or do you still have no idea why you'd want to use VLOOKUP? And this is just to help us gauge whether you've gotten something out of this. We know it's a little confusing for sure, but we hope that it has piqued your interest enough to go and study it some more on your own if you're hoping to use it for your own purposes. And I'll wait for everybody to have a chance to answer, and then I'll go ahead and skip to the results. And stay with us for just a couple more minutes while we do some wrap up here. I want to make sure if you need to get the latest version of Excel or want to upgrade or want more training, we have some resources to share with you. So I'm going to go ahead and share the results and it looks like the great majority of you, 81.6% yay, got the right answer. Thank you for paying such good attention. You all get virtual gold stars from me. I'm going to go ahead really quickly and just show you. TechSoup.org, if you go to the search bar and type in Office, or if you type in TechSoup.org slash Microsoft, you will see options for Office Professional Plus, Office Standard, and Office 365. Now Office Professional Plus and Office Standard donations both include Excel and it will give you the latest version of Excel, whichever version Microsoft has available is the version that will be available to you. And it includes software assurance which allows you to upgrade if Microsoft releases another version within two years. It also allows you to downgrade if you have a machine or if you have content or spreadsheets that are in an older format of Excel and you need to stay compatible with that for some reason. You can also downgrade if you want to go back a version. So keep that in mind. We also have Office for Mac in our catalog. I don't know why I didn't put it on here. I'm sorry for you Mac users. I didn't mean to leave you out, but there is available a version for you and you can find these links. We'll share these in the follow-up resources. And I also wanted to highlight quickly, we have a new donor partner, Skillsoft. And they are, many of you may be familiar with them, they offer great free online trainings and tutorials and learning programs that are self-paced. You can go into those. You can say I want to learn about OneNote, I want to learn about Outlook, I want to learn about Excel, and they've got trainings for all of them. So they have both courses for Office Fundamentals, and they also have a package which I didn't screenshot here, but they have a package that is for Office Fundamentals and Windows 8 and Windows 10. So if you're on a newer operating system and a new version of Office, or even an old version of Office, and you just want to learn how to use it better, these Skillsoft trainings are fantastic and I highly recommend checking them out for $20 admin so you get access to a whole package of self-paced online courses to learn how to use them better. And we have it for Office 2013 and 2010 and Windows 8, Windows 10. So definitely check those out. We hope that you've learned a lot today. So we'd love it if you'd chat in to let us know one thing you've learned today that you're going to take back and try to work on, try to implement at your own organization. If you found this useful, please share this information with your friends and colleagues who may benefit as well. And importantly to us, please complete that post-event survey that pops up when you leave this webinar because we want to continue improving our programming for you because we do these for your benefit and we hope that you get a lot out of it. Lastly, I'd like you to join us for any upcoming webinars. We have a whole series planned. Next month is our Storymakers Campaign Launch, which is our annual storytelling event where we invite you, nonprofits, libraries, charities, churches, to submit your digital stories, video stories, photo stories. Tell us about your organization, what you do about your heroes within your community. And we invite you to learn how to do that storytelling through our series of webinars and a whole lesson plan that we've created. But I've linked to the upcoming webinars here. We have one on pre-planning your digital story from concept to story board next week, producing a successful video from story or from sound to screen. Then we'll be talking about Instagram for public libraries. So if you're a librarian you want to use Instagram for sharing photos and good practices on social media, join us for that. And then we'll talk about editing those videos to make them fantastic and getting them out there on YouTube and best practices for that. And then lastly, we'll be talking about photos to tell your organization's story. So please keep an eye out for those and watch for more to come. Lastly, I'd like to thank our presenters today most importantly. Rachel, thank you so much for sharing your expertise. We really enjoyed having you on. And Ariel, thank you for sharing all those basics with us. I learn every time I'm on one of these webinars. I learned some things that I did not know about Excel so I really appreciate you being on. Thank you to Susan for helping on the back end. And lastly, thank you to ReadyTalk, our webinar sponsor, who provides the use of this platform for us to present these webinars each week. Feel free to join us for more events next week. And if you're interested in presenting your own events, check out ReadyTalk at TechSoup.org slash ReadyTalk. And again, take a moment to complete that post-event survey when it pops up. Thank you so much everyone. Watch for that follow-up email from me within the next few days. And have a great weekend. Bye-bye.