 Hopefully you can see on the slide what I'm going to be talking about. At the very top of the spreadsheet when you open Excel, what you'll see is the ribbon. If you're on a very, very, very old version of Excel like Office 2003, you won't see the ribbon and everything will look very different. But more recent versions of Excel use this ribbon concept. And that's this whole section at the top of your spreadsheet. The ribbon is organized into tabs, so those are things like Home, Page Layout, Data, etc. At the very, very top you'll see a row of teeny, tiny little icons. And that's your Quick Access Toolbar. The Quick Access Toolbar allows you to use commonly used tasks, and it's a quick shortcut that you can customize. And I'll show you how to do that later. Underneath the ribbon is the Formula Bar. The Formula Bar shows you what's happening in any particular cell in Excel. So if you click on a cell in the Formula Bar you'll see what's in that cell. If it's just text like somebody's name or location then the Formula Bar will show you that text. But if you have a formula in that cell the Formula Bar will show you the underlying formula behind what you see on the screen. So if you have a really simple formula in a cell like 1 plus 1 in the cell it'll show you the number 2 which is the result of that formula. The Formula Bar will show you the underlying formula that's making that magic math happen. Next, one thing that is my golden rule for Excel is that no matter how awesome you are at it mistakes happen. That's why I always save an original or a clean copy of the spreadsheet before I start doing anything. That way if I delete something or I overwrite something or I make a mistake I can still go back and have my original data saved. And this particularly if you're not super comfortable with Excel this can be really liberating because no matter what you do to a copy of a spreadsheet you still have your original data preserved and just fine even if you really mess up the copy. And I was planning to demonstrate what that actually looks like but in the slides you can see a step-by-step explanation of how to actually do that in Excel. I mentioned earlier that at the very, very top of your screen you can customize a quick access toolbar. So in Excel there are a bunch of different tabs and within those tabs there are a bunch of different things you can do, buttons that allow you to perform various tasks. And if you don't really know where everything is located in Excel you can spend a lot of time clicking and looking around on the tabs to figure out where the button you're looking for is. That's where the click access toolbar comes in handy. If there are a handful of things that you do over and over again in Excel you want those to be really easy to get to. So to customize the click access toolbar at the very top of your screen, again apologies that you can't actually see me demoing this, there's a little tiny drop-down arrow. It's highlighted with the number 1 on the slide. If you click that drop-down arrow and choose More Commands you'll get a pop-up list of some favorite and commonly used things in Excel. You can choose from that list the things that you use all the time and Excel will put them at the very top of your screen. So no matter what tab you're on in the ribbon, what you're clicking around, and what you're doing you'll see those click access commands right there at the top of your screen. I use, put things like insert rows and delete rows that I use all the time I put at the top of my click access toolbar. But if you're really not into all that clicking around with your mouse and you're more of a keyboard shortcut person, we have a tip for you too. If you hit the Alt key on your keyboard Excel will display keyboard shortcuts on your screen. So if you hit the Alt key what you'll see is the keyboard shortcuts you can use is to navigate to any tab in the ribbon. And if you click, sorry, if you type on your keyboard any of those letters, so for example if I want to go to the Home tab I would hit H on my keyboard. Excel would take me to the Home tab. And then once I'm on the Home tab the view will change in Excel to show all of the keyboard shortcuts that are available for the Home tab. So without even knowing what the keyboard shortcuts are really Excel lets me perform almost any task that's available in the Excel ribbon just using my keyboard shortcuts no mouse required. All right, next on the list is one of my favorite things that helps avoid the bane of many new Excel users' existence which is scrolling all the time. One of the big differences I notice between people who use Excel a lot and people who only use Excel infrequently is that people who use Excel a lot actually don't spend a lot of time scrolling up and down and back and forth in their spreadsheet. That's because we have a few quick tips and we're going to share those with you right now. One of them is freeze panes. So you know how sometimes if you're in a spreadsheet and you scroll down that top row where you have your labels disappears and then you're looking at data down at the bottom and you don't quite remember what the column labels are and you have to scroll back to the top to check what the name of that column is and you scroll back down and look at your data. That's a lot of wasted scrolling time. To avoid that you can use freeze panes which is available on the View tab in Excel. So you just click on the cell where you want to freeze the spreadsheet, go to the View tab, and choose freeze panes. And what that does is it tells Excel that no matter where you scroll around in the spreadsheet you always want the row above the cell you clicked on and the column to the left of the cell you clicked on to be visible. So if you scroll down all the way to the bottom of your spreadsheet that top row will still display and if you scroll right the columns on the left will still display. This is really handy to keep you from having to scroll around and keep checking what your headers are. Speaking of scrolling there are a bunch of other ways to break your scrolling habit. One of which is a keyboard shortcut actually a number of ways of which are keyboard shortcuts. So if you want to just go to the very top of your spreadsheet you can grab the bar on the right side and scroll up or you can just use the shortcut which is just control and then home on your keyboard. And that will take you automatically to the cell in the top left corner of your worksheet. Similarly if you want to go to the very last cell in the bottom right corner of your worksheet the shortcut control and will take you there. You can also select a data just using your keyboard versus using your mouse to select data. To do that you hit control and shift with your left hand and the arrow key down or up and that will select all of the data in a particular column. One of my favorite tricks is using autofill as well. That's another way to avoid all of the scrolling. This is one that particularly would benefit from a demo. But if you've added something to a column say a formula or a label and you want to include, you want to copy that same information all the way down to the bottom of the column. If you hover over the bottom right corner of the cell that you've added something to you'll see your cursor will change to a little black plus sign. If you double click on that little black plus sign Excel will automatically fill in to the bottom of the column what you've put in that first cell. This is really handy because I see a lot of people in Excel copying data down to the end of a column by clicking on that corner and then dragging down. And if you have a really long spreadsheet with thousands and thousands and thousands of rows it takes a really long time just to scroll down. So double click to autofill, it's your friend. All right, the next thing I'm going to talk about is one of my favorite and I think most underutilized things in Excel which is filters. So imagine you have a spreadsheet that lists all of your events and all of the volunteers you had at those events and everything that those volunteers did for the events. If you just want to look at data for a particular event you can use a filter in Excel to only show those rows for a particular event. On the data tab if you click filter you'll see little arrows appear at the top of your screen. I show those on the slide and how that looks in the spreadsheet itself. If you click one of those arrows you'll get a pop-up list that shows you all of the ways you can filter a list. So for example, if I have events that happened in San Francisco and Oakland and Berkeley I could choose to filter just by looking at events that happened in San Francisco. And you can tell when a filter is being used in Excel because the icon, the little button at the top of the column changes from that little drop-down arrow to a little filter with a drop-down arrow next to it. You'll also be able to tell if you look at the left-hand side of your screen when a filter is applied because it will look like some rows are missing so it will jump right from 1 to 5 to 20, whatever. So if you see that there's data that looks like it's missing or rows that aren't displaying it's possible that a filter is being used and so if you want to see all of that data without the filter you just click the filter button again and the filter disappears. Thanks so much, Ariel. I appreciate your time on this. We're going to go ahead and move forward. First I want to go ahead and pause for just a couple of minutes and take a couple of questions that were related to Ariel's section before we hand off to Rachel. So we had some questions about the alt key for shortcuts and how that works. What's the benefit of using the alt key? Can you expand on that a little bit? Sure. So the alt key is really handy. The world is kind of divided up into mouse users and keyboard users. There are some people that prefer to click around with their mouse and there are some people who prefer to use their keyboard shortcuts. So the alt key is really useful for keyboard shortcut people. And so if you don't want to click through the various tabs in Excel to access the commands that are listed under each tab, the alt key lets you do that just with your keyboard rather than using your mouse. It's really just a matter of personal preference. One way isn't better than the other. Great. Thank you for that. We also had a question about freeze panes. And Rhonda commented that freeze panes freezes the first 15 rows of everything she does. Is there a way to make freeze panes work where you want them to? Yes. You need to be very careful to when you click on freeze panes, you want to make sure that you've actually before that clicked on the cells that you right below where you want to freeze. So if you want it to freeze just the first row, click on the second row. And that will freeze one row up from where you clicked with your cursor. So the steps are click the cell right below where you want to freeze, and then go to the tab and select freeze panes from there. And that should avoid the problem of having the first 15 rows freeze. Great. Thank you for that. Let's go ahead and move forward and get into Rachel's section. She's going to be taking us through some more intermediate and then some advanced topics. So we're going to be switching more to manipulating data and formulas, which we all love formulas. So we'll go ahead and try and get started with that. And if it's hard to see some of the slides, you can also click full screen. It will diminish your chat window so that you can see a little bit better because we know some of these slides were really created as a resource for you after the fact. We were hoping to show live, but we want to make sure that if you are looking at them with us right now that you're able to see them a little bit better. So feel free to do that if that would help. So thank you so much Rachel for joining us. We're glad to have you on. Hi. So I believe I'm now sharing my screen. Thank you Becky for giving us back online. Awesome. That worked out. Thank you so much for your patience. So now we're going to go over text to column one more time that you can see it live. So this is my sample data spreadsheet that I got from downloading a Googled free data online and got this from the government website. So let's go to the first place I was. I wanted to do text to column. As you see here, these are all last names, comma first name. This is not helpful for what we want to do. So we are going to separate this one column into two columns. The first step is identifying a column which is here. And now I need to insert rows to give space to move this. So I'm going to do that by completely selecting these rows here. If I select three rows, right-click, and click Insert, it will insert the number of rows that I've highlighted. So I have just inserted three new rows. The next step is completely highlighting the entire row that you want to put into multiple rows. I like doing that by clicking the letter. I click the letter. This entire column is now highlighted. To do text to columns, you go to the data ribbon right here, click, and then you will see this button right here, Text to Columns. And we will click this. It opens this dialog box. And we will go through this again now that you can actually see. So here there are different ways you can separate the data. The limited, which is by commas, tabs, or certain things we select, or a fixed width which allows us just to pick a space it will always be the same width as the name suggests. So after I've selected that, I will click Next. Here are my options to separate. I could do anything I want by typing other. I could type in a letter but I'd want to separate. Here I only want to separate by commas. So I'm going to select comma here. I don't really use this feature. I pick what I want and I click Next. And then here I just click Finish. And here's this question. There's already data here. Do you want to replace it? Click Yes. Unless you have information in these columns that you don't want this to write overwrite. So click OK. And now we have a column for last name. So we will update this name, last name, last first name. Now we can get rid of these extra columns. Now we are going to move into formulas one more time and I will show it on my screen and walk us through one more time and go through the theory to move us forward. So here, this is to the structure of the formula we are talking about before. So as you see here, there will always be this pink equal sign, the name of the formula, the name of the formula, the open parentheses, the content for which ends the formula, a separator by a comma, more content for the formula, and then an ending parentheses. So this is always what we need. We always need equal signs. We always need this ending and opening parentheses and separating everything with commas. And again, there's not to be up or lower case. And we will go through the problems together now in Excel. So we will do the right formula together. So here is a project ID that I was once showing us before. So let's insert a column here. It's always, I like inserting new columns and ever want to do new formulas by my data. It makes it easier. So here, we are going to call this project ID numbers. So what we want to do is we want to remove this P. We don't want to have this P here. We could have done this text to columns by the limited, or we can do it by this formula. So we are going to write our formula together. I'm going to type in this cell and you can also see it up here in the formula toolbar. So while you are here, you type equals because that is the beginning of a formula. I'm going to type the name of the formula which is right or left because we want to return the right or the left contents of the cell. So I type the word right, open parentheses. And if you look down here, it's prompting me. It is bold. It says text. What do I want the right of? And text is the same as a cell. So I will click this cell which has a lasso around it. It's in blue it shows me. So now I'm going to separate with a comma. And as soon as I do that, it's going to jump to the next portion. So I type comma and then it jumped to number of characters. Excel is prompting me through this formula which is super helpful. So I know that I want the six characters to the very right so I will type a six. I will close parentheses and push enter. And now we have the project ID numbers. And I will double click here to bring that all the way down. Now I'm going to go through some other types of formulas on a summary sheet. Let's say our boss asks us how many contracts do we have? What's the average value? And what about per year? So we're going to go through finding the total count, the average for the total, and then we're going to go through how to do it separated for only per the year it is. So I have built out this summary tab here which is very easy to do. I have just typed in a simple chart and put in some borders. And so here I am going to type in my count formula. This is also going to show us I can type a formula on one tab and have it count information on a separate tab. Excel is a relational database so everything in every tab can relate to one another. It is very smart in that way. So here in total we're going to start. So what if you follow along with me? What does every formula start with? What do we need to type in? If you just want to type that into your chat box you can start doing this together. So it always starts with an equal sign. And then the next part is going to be the name of the formula. And these names you will learn with time, with necessities, what you need to do. Let's say I type in to Google how do I count how many contracts I have? And then you would see that it is a count. And then it is pretty intuitive like this, we want to count, it is called count. Type in the name, we will do open parentheses. And here you say value 1. And this is kind of learning the terminology. Let's ask us what do we want to count? So I am typing the formula here but I want to count information on this other tab. So it is just as easy as clicking on this other tab and going to the space that I want to count. And I want to count this contract value. And you can see in the formula bar that it is updated. It is now starting with the name of the tab because I am in the raw data 2014 and 2015 tab and it specifies that here. And I am going to put my cursor in the first cell I want to count which is the very top of the line too here. And I am going to select all the way to the bottom. And my favorite way to do this is a special shortcut on the keyboard. With my left hand you can follow along with me, push the shift button and the control button at the same time with your left hand. So those are held down. With your right hand push the down arrow. And that will select all the information. This also works with the right up and down arrow keys as well. And what I always like to do with my ranges is to lock the range end. If you see here it says T2 to T51 which is the starting and ending cell in my range. By pushing the F4 key you will see that dollar signs have appeared and this locks my range. And this will start to make sense as we go through more formulas going forward. But we will start introducing it now and it will make more sense. So to end my formula I am going to do a closing parenthesis and push enter. As you see here it has updated on a completely separate tab and it has counted the total projects. Now I am going to find the average spend. So here I will type equals. And then what do you think the next part is? The name of the formula, average, open parenthesis. And we are going to do the same thing, the same exact thing we did before. We are going to select our range. So going here we are going to put our cursor in the first cell. I am going to do my drop down trick with my left hand. I am going to push shift and control the same time. They are both pressed down. With my right hand I am going to push the down arrow to select everything to the next space. You will see up here it has the range and to lock that I am going to just push the F4 button. I push that it adds some dollar signs, our range will now not move. I am going to add ending parenthesis and push enter. Now we have our average spend. Now this tab that we have has information for 2014 and 2015. I want to write a formula to count the number of projects for only those years without having to go in and manipulate and sort and move the data away to keep all the data together. So there is a special kind of formula called account if or an average if which basically says I only want to count it if there is a certain criteria. So in this case only count it if it is in 2014 or only average it if it is in 2014 or 2015 to get us those numbers. And since there is a chart there is a really cool way to do this that we are going to do together. So in the count of projects we are going to type in our formula. We are going to type equals the name of the formula is count if open parenthesis. And now we are going to look at our prompts. It is asking for our range. So what is the range? What is the range of information that we want to count? So since it is if we have to find where the years are since that is our criteria. So we are going to go back into our data tab over here. And this is all the data. I happen to know that the years are all the way over here right here. It is the fiscal year. So I am going to select my range. You can also do this by highlighting like this. I am going to lock this range by pushing F4. And then I am going to follow the prompts. So there is more to this formula so I will push comma to get to the next part and it says criteria. I could either type this in like 2014 and that would work. But I am going to show you another trick. So the criteria is the years 2014. So I am going to go back to my summary tab and I am going to clear out this space. So right here I have my range, I have my comma, and now I need my criteria. What I am going to do is put my criteria by selecting this cell, the criteria is NSL which will help with writing the next formula. So I am going to end parentheses and push Enter. So now you can see that it counted 16 different projects in 2014. So Excel, if I were to copy this cell and paste it here, Excel remembers the formula and will also understand that the criteria is right above the count. So if I do control, copy, then click here and do control, paste. It automatically counted that there are 34 projects in 2015. And I will show this to you. So if I go to this cell and if you push the F2 button, it shows you what the formula is. And here you can see that this is selected, this cell 2014 for the criteria portion of the formula. If I go to this cell and I push the F2 button, it will show me what is compromising the formula. You see here it shows my range and then now this is highlighted. So this is a really easy trick. If you have to go all the way down for multiple years, you can write the formula once and then copy and paste it all the way down and Excel will just follow the same pattern. So now we are going to go to Average Spend If a certain year. So this is to show you that just how important following the problems are because the format and the order of the formulas do change with different formulas. So we are going to start with writing the equal sign. Then the name of the formula which is Average, open parentheses, excuse me, Average If, Average If, open parentheses. So here we are going to read through this first. It says range, criteria, and average range. So first we have to select the range that has our criteria, the range that has the year, and then we need to put in our criteria and then we need to actually put in the range that is going to average which is the contract amount. So to get our range for the criteria, we go back to our separate tab with the data. This is our range right here. So we will select the whole range of years. We will push F4 to lock, comma. Now we are on criteria. I could type in 2014 but I would like to just use my cell. So I will go back here and I will select this cell here. So now this is my criteria, whatever it is and not cell. This could be anything, this could be a name, this could be location. So now I am going to separate with another comma. And the last portion is the range that I would like to average. So we will go back to the raw data tab and select where the contract amounts are. And I will select this all the way to the bottom, F4, Ending parentheses, and Enter. So again, this will work. I am going to copy this and I could do copy paste. I could also do this drag down and move across. And it will tell me the average spend for 2014 and for 2015. Now that I have this, we are going to get into pivot charts later. But I wanted to show you that you could easily make a graph here just by going here, Insert, and you can do recommended charts. And you could add in type of different bar graphs. This feature is available to you. And this actually could be its own lesson. I just wanted to introduce the idea that you can create really easy graphs by the Insert ribbon and going through the raw data. So people hear this word a lot, PivotTable. I am the first time I heard it. And I was like, what is a PivotTable? The name doesn't make any sense. I don't like it, not very intuitive. But a PivotTable is basically a way to make a chart. You can insert any rows you want, any columns you want just by dragging and dropping. It allows you to add filters. It is a way to make a cool chart. And it will give you averages. It will give you counts. It's really super handy. And it's gotten even better with 2013. A few of you have used PivotTables before. People didn't really like using them in the workplace because they weren't as updatable. If you add a new data into your original spreadsheet, they would update. But now they do. They are really, really helpful. So here I am going to show you how to insert a PivotTable. So the first step is selecting the data that you want to chart. So one of the rules of PivotTable is that every column must have a header name. This will make more sense when we are going through to make our PivotTable. So you want to check that every column header has a name which it does. I'm going to scroll all the way across. So the next step is to select your data. I like to start in the very left top corner. I'm going to push Shift and Control with my left hand. I'm going to push the right key to select all the way to the right. I'm going to push the down arrow key to select my entire data set. So once I have all my data selected here, I am going to go to the Insert ribbon. Click Insert, and then click PivotTable. So then it brings up this prompt window. When I first learned Excel to prompt, the window is really confusing me. I tried to read everything. But this is a range of the data we selected, and it's just asking where we want to PivotTable. I always like a new worksheet. This makes it a lot easier to work with. I click OK. As you see here, it created this new tab. I want to rename this right away. PivotTable, PivotTable, and give me some info what is 2014 and 2015. These are good happens to get into to help you stay organized. So you are looking at the screen. You are like, what is going on? Here is a PivotTable that you are going to create on the left. And here it says PivotTableFields. So this is all the different fields. And then this is everything we have to work with. And these are all the things we can do with that. So for example, we have, let's put in our columns. So I want to put in the fiscal year as my columns. So I dragged it. I took it. I put my cursor on top of it from the available fields. I dragged it down and dropped it into columns, which I did. And now you can see 2014 and 2015. Now I have to continue building my chart because this is not helpful. So let's put in, now I want my rows. I want to see borrower country. So I took borrower country. I clicked it. I'm holding my click down dragging it and dropping it in rows. And you can tell it's ready to be inserted when the green line comes up. So there we go. I have all my different countries listed here in the chart. And let's say I want the average spend for each year. So I'm going to go for the contract amount. So I'm going to take total contract amount, and drag it, and put it in the values field. And now it's at some. It shows a sum of everything I spent in 2014, 2015, and grand total. So really that was dragging, dropping. I could have picked any combination I wanted. Let's call it the values tab. Let's say now I just want a count. I don't want some anymore. So I'm going to do the drop down. I'm going to go to value field settings, and I'm going to go to count. And now I have a count. And I can sort this. I can filter so the countries at the most at the top say I want the average spend. I can click on this values again, do a drop down, value field setting, and click on average. And now I have the average spend. This is all here for me. I can also do a filter. So I can do, I can do, I can do borough country and a filter here. And let's say, so doing that that means I only see a certain amount of data at a certain amount of time. So let's do it like this. Let's do major sector up here. So I just, I changed what I was doing. I still have borough country in the rows, and in the filters I have major sector. So here, excuse me, oh that's another trick with the pivot table. If you click off of it, you lose your navigation and manipulation. But if you just click right back on here, it comes back. So I have this way filter, and I can do that. If I click here, if I had more than one major sector, I would have, right now all of these happen to be agriculture fishing and forestry. I could click certain ones, and it would change for each piece. So that's all. And that's how it works. The best way with a pivot chart is to think what information I want to display, and just really thinking through. You can even draw out the own chart you wanted to make, and then follow that. This is where the columns are going to go. This is where the rows are going to go. This is the type of value that I want. I'm going to take the filter off. And now I am going to show you how to make a cool chart a pivot chart. So we are going to highlight our pivot table. We are going to go to Analyze. So after we have highlighted our pivot table, we go to the Insert ribbon, and we click on Pivot Chart. And I am going to do columns. And now we have a chart that shows this. And while I have this, you can make a chart, but I want to show you how to do a slicer, which I think is really cool. So a slicer on a chart will automatically change the chart, slice the chart up based on selected criteria. So I will go Insert Slicer. And I will say, okay, what criteria do I want to mix and match? So here I want a slicer on Borrow Country. So after I was in Analyze tab and selected Insert Slicer, I get all these options of what I want my criteria to be. I want to be able to update my chart based on Borrow Country. So I click there. And this makes this whole button menu, slicer buttons. And so I can click, okay, I want the chart just to be Brazil. And so it changed to just be Brazil. Let's say I just want Vietnam. And then it automatically changed the chart to be at Vietnam. This is really helpful if you want to show us the name for all the departments and you can go down just to one. I like to send it to the different managers. People really like this. And it's a really fun way to look at charts which can be kind of boring. To take the filter off, you close here at the top. And since I only have a couple more minutes, I'm going to quickly show us how to perform the lookup now and go straight into that. Before you move on, Rachel, can you show quickly how you created the pivot chart? Because you showed the table creation, but it was a really fast movement from the table to the chart. So people didn't quite catch that. Absolutely. I'm very happy to show how I did that. Thank you for your patience with me. I probably sped right through that. So we have our pivot table here. This is our info. To do a pivot chart, I'm going to highlight my pivot table. It's all selected. And then you're going to go to the Insert ribbon right at the top. Insert. And then you'll see all these options. There's charts. And then there's a pivot chart right here in the middle. All you do is click Pivot Chart, a drop shot on the pivot chart. And here you can pick different types of charts that you want. There's all these different selections. I would like to do a column, a bar graph. And you can pick the different ones you want. I'm going to stick with the very first one and select OK. And now I will show you the slicer one more time since maybe that was a little quick as well. To insert a slicer, it's actually on a different tab. You can see at the top here that these are special ribbons just for pivot tables and these are now highlighted. So in the Analyze tab, there is an Insert Slicer feature. So you click Insert Slicer. And then you select, once this comes in, you select how you'd like to slice and change the data. So I will go back and select Barwork Country because that's the different elements I'd like to see by country. And I will click OK. And then I have a list of all the countries. And I can go through each piece and go to Vietnam here. And click through all the different countries and see what they each look like. Now we've gone through that. Back to you, do you have time to do a VLOOKUP or would you like to move on? Okay, let's go ahead and do it. Great. Okay, let's do a VLOOKUP which I love. VLOOKUPs are one of my favorite things. They are so helpful. So what a VLOOKUP is, it finds a value in one row, looks for it in another workbook, and returns another value that's in the same row as that. And I'll explain in use case. So every single project here has a project ID number. And I want to know, this is how much it cost in 2014, 2015, but I also have a list of all my projects that were in 2013. And maybe I had some of the same projects in the previous year. I want to know what the cost was in the previous year and if it existed. So one way to do this is to take the number, take it, and look it up and this, and go back and forth, back and forth, which is extremely time consuming and not efficient at all. It should never be done, especially when you have a feature called a VLOOKUP. So for a VLOOKUP, there are very particular pieces that we need. We need to have a unique identifier which is the way to look up our data. So that can be a name. It could be a first name, last name, combo. It could be this project ID. It just needs to be something that is unique to each piece that we have. So here we have our number right here. So I'm going to remember that my project ID is in this column. And I'm going to write the VLOOKUP in this column by the contract amount. So I'm going to insert a column to write this formula. So we're going to walk through this together. I'm going to say equals VLOOKUP which stands for vertical lookup and open parentheses. So now let's read through the prompts together. And the first thing it asks for is a lookup value. It's like what am I looking up in the next sheet? What am I looking up? So we're just going to type, we're going to put in the corresponding cell in this row. We are looking up this project ID number. That is our first piece. So back in the formula, and we will type in a comma. The next thing it says is table array. I think the hardest part with VLOOKUP is the language they use. What's a table array? It's not something we use in every day. Table array means the range that you wanted to look at. Where are we looking this ID up in? So we're going to go, we are looking for this in the raw data 2013 tab. The next rule with the VLOOKUP is that your starting part of your range has to be the first call limit, how the math, how the search works. So we have to start the lookup right here. Our unique identifier is project ID, and it has to be our first one. So we're going to start here. And we're going to go all the way down to the contract amount. So another way to do that, I like to push SHIFT and push down with my right key because if you look over here, it gives me a little box and tells me how big my selected area is. And this will be helpful in one moment, and I will explain why. So we're starting here, and I'm going to move over with my right key until I get to contract amount. So I've selected all the contract amount. I'm going to push SHIFT, CTRL with my left hand, and the down arrow to get my entire selection. And if you see here, it says my range has 11 columns. So I know that this column here, the information I want is 11 columns away from my starting, my project ID number, and that is an important piece. So let's finish our formula to the top. We're going to push S4 to lock in our range with the VLOOKUP. You always want to lock in your range. Otherwise, it will bring the lasso down one row each time for each row that you go down in your formula. We lock that in, type a comma, and it says column index number. So how Excel knows what information you want is by counting the number of columns that is away from your unique identifier. You have to tell it. As we saw before, we want the 11th column. It's how far it was away from each other. We'll add in one more comma. And this part is also kind of confusing. It says range lockup. The easiest way is always type false. Always type false. This means we want an exact match. I've never used true ever. I don't know many people. I don't think I know anyone that's ever used it. I'm sure someone does. This can be probably more advanced, more math features, but we do not need this in most all cases. Type false and parentheses, push enter. And now we have the amount for 2013. So let's drag this down by double clicking. And you'll see here that some of these have a hashtag NA. What that means is Excel looked and did not find this project in 2013. So you can know it just was not there. It didn't exist. NA, there was no information for it to bring back. So that is the very basics of a VLOOKUP. I encourage you to practice this on your own. These make more and more sense than more and more. You do them. I would Google how to do a VLOOKUP, read through, and just really keep on doing them. They save so much time. And those are all the things we took out. So a lot of things I was going to cover about I think maybe all we had time for it. And I want to thank you so much for listening. I know this is a lot of information. I know it's very dense. I know we had some technical issues. But I had a lot of fun chatting with all of you over this webinar. I'm going to pass you back to Becky. Thank you for that Rachel. And I know we zipped through a lot and missed a fair amount too. So we've got quite a few questions in the queue. So Karen asked, how did you select 2013 in the VLOOKUP you were just doing? So can you quickly go back into that and show us where you did that? What was the question was how I got 2013? Okay, so when you think about a VLOOKUP, you have to think, okay, I have information in two separate places. So my first workbook here is Information for All Contracts for 2014 and 2015. On a separate worksheet is the information for all projects for 2013. So I have information in two separate places. So I'm going to clear this out by just clearing contents. I'm going to rewrite the VLOOKUP with Altogether. So we're going to start equals ZLOOKUP. The first part of the lookup value which we talked about is going to be our project ID right here. So now I'm going to scroll back here so you can see the formula. So it says Table Array. And this is going to show us how we selected 2013. So I'm going to click to the tab where the other information is and highlight where that is. So I am moving from this 2014-2015 tab and going to the 2013 tab here. And to repeat what we talked about earlier, to select my data I have to make sure my unique identifier of the project ID is first so the counting portion will work correctly. So I select here just by putting my mouse over. I also like to do it by pushing Shift with my right hand and just pushing my right arrow until I come to the information that I would like. So now you can see my green lasso is all the way from project ID which needs to be my start to the end and the total contract amount. And now I'm going to push Shift and Control with my left hand and the down arrow with my right hand to get this entire selection. Now they have it selected, push F4, I'll type in 11 of the 11th column, Boys and the Fals, and Enter. And somebody was asking how you knew that it was the 11th column. So when you had that highlighted, the distance between your open column and where the data was that you were trying to pull from. If you can just show where that showed you that it was 11 spaces, that would be helpful. So people know to look for that when they do it themselves. Absolutely. Okay, let's do that again. So I will go to equals, so you look up. So I am going to put in my cell, and this time I did the second one, comma. So we are going to go into where the data is. So remember, we always start with our unique identifier, Rule of the Lookups. Lookups are just a bunch of rules. And once you get the rules down, they don't change. So I start here. So once you are in the cell you want, if you push Shift with your left hand, it's going to help you select all the rows at the same time. So with your left hand go ahead and push Shift, right arrow, push the right arrow once. Okay, if you see here in this tiny little portion, I don't know if you can see it unless you are full screen, there is this tiny little rectangle that says one row by two columns. And that is just giving us the dimensions of what I have selected. So I only have one row in two columns. That is there. So if I continue to push Shift with my left hand and push the right arrow with my right hand, and I did, now I have three here, this is updated to one row by three columns. I'm going to keep on pushing this, updated to four. Keep scrolling until I get to Total Contract Amount. And if you look here, it has now gone from one row by 11 columns. It is signaled right here in that, right there. And if I push Shift Controls down, I get that pop up here at the bottom. Another way to do the 11 is when I first started, I didn't know I could do that, so I counted. I literally counted. The first project ID would be column 1, then going forward 2, 3, 4, or you can drag your mouse across and it will do a count for you. But this I find using the little, the dimensions box is very helpful. Again, always push F4 even if it's not making sense why yet, it will start to. You always push F4 to lock in the range, 11, false. We always type false. And we will push Enter. Another way to do it, how do you know what the count is too? If you scroll across, I'm back on the 2013 tab and I'm showing another way to know what the count is. So I'm starting here, right at Project ID, and I'm just going to scroll across here to what my contract amount is. And if you look here at the bottom, this gives you a quick what is here. There's the average of what these amounts are and it has a count which is also 11. So that's another way if you pre-plan, you can figure out what number you need ahead of time. Great. Thank you for that, Rachel. And some folks are still a little bit confused about how the lookups work. And I'm really, really new to using them myself. This was some of the wizardry that Ariel had shown me when I needed help. And I was trying to get two sets of attendees from a webinar. There were two different files, two different spread sheets, and I was trying to get email addresses from one to transfer over to the other one. And I was using the attendees' names as their unique identifier. And it was super amazing to me. It was one of those big aha moments that I could transfer data between two different sheets without having to manually plug all that in or copy and paste, copy and paste. So it can do things between sheets, between workbooks, between columns in the same spreadsheet. So it is a really great feature to utilize and I'm still practicing it myself. So I'll probably re-watch this section afterwards. But I'd like to go ahead and go with a couple more questions before we start to wrap up. We had some questions about the right and left formulas that you talked about at the end and whether those can be blended. So can you blend the equals write formula for two separate cells so you could have a first name in the same cell as the first four letters of the last name? I don't know if that makes sense, but can you blend those? Absolutely. So this is usually called concatenate, but you can do this doing the equal and sign. So I'm going to do that quickly here for you. So I'm going to insert a new column and I'm going to show you how to combine information together. So this is, let's say, I want to combine first name, then I want a space, and then I want the first three of the last name. This is going to be a little bit more advanced. I'm going to go slowly, but it is going to answer the last question. So in this cell I'm going to type equals and then I'm going to say, okay, I want it to put all the contents of the first names. We'll type that here. And then I have to think, okay, what else do I want? And I want something else. I put the and sign and I want a space. Anything that you want Excel that's not explicit you have to put in quotations. So I'm going to put a space in quotes. So quote, space, quote. And that's how I'm going to get a space. And now I want the first three letters of the last name. So and I want something else and I'm going to put this and sign. And I'm going to start writing the formula. And here I don't have to put an equals sign again because I already have that at the beginning. So I am going to just start writing my formula. So here I will type left because I want the left part of it. Open parentheses. Which text do I want? I'm going to select last name. And then I ask comma, the number of characters. And I'm going to put three. Ending parentheses, enter. So now I have Aaron, AAB. And I'm going to drop this down and it will happen for every person. I could update this formula. Let's say I wanted to have a comma in here. And so this blank space, I'm going to the toolbar to edit my formula. I'm going between the quotes and I am adding a comma space. So she can enter. And now it is added a comma. I wouldn't want that between the first name and last name but I'm just showing you different functionalities of how this could work. Great. Thank you, Rachel. So I'm going to go ahead and stop sharing right now because we are nearly at time. And I would love it if – let me move forward here in the slides. Go back to the deck itself. I just want to make sure that folks know that if they are looking to upgrade, if you are not currently on Office 2013 or have Excel 2013 that you can access that through our Microsoft software donation program. And we have Office Standard 2013 available that has 7 products including Excel or Office Professional Plus 2013. That includes 10 products so it expands it a bit to offer some of those additional Microsoft Office Suite products. We also have Office for Mac 2011 which is different looking than what we just went through but has many similar features. And you can also access Office 365's donation directly through Microsoft and we help verify that you are an eligible nonprofit to receive that. And Office 365 includes for no cost a cloud version of Excel. However if you want the desktop version as well you either need to pay a per seat per month fee for that or you can request it through TechSoup and our donation program directly and use them both together. So the links on this page are included in the follow-up email that you will receive later today. And I know we didn't get to go through everything the way we had planned but we would love to know if today's webinar helped you increase any of your confidence in any of these areas. And we will like I said try to redo some of this so that you can have the full recording that you can watch that will be better than what we've come up with today with our technical issues. But you will still receive today's recording later this afternoon and I'll try and edit out some of the unhelpful moments where we were dealing with the technical issues. But go ahead and let us know how you're feeling about these areas currently. Like I said if you're on an earlier version of Excel there were a couple of parts that would not necessarily apply to what you're using in particular the Pivot Slicer feature. And the more interactivity with that is a 2013 feature. So if you liked that then I would recommend upgrading. And all of the software donations through TechSoup come with software assurance that allow you to upgrade for no additional cost within two years if Microsoft releases a newer version. So I'm going to go ahead. I know we already covered some questions and there are many in the chat that we don't have time to really get through since we are at the top of our time. But you can also continue asking questions in our databases and software forum. Some additional resources for those of you who are interested in learning more about Excel. We have a couple of articles and webinars so Excel 2013 create professional looking spreadsheets. There are other resources linked from these as well. A webinar we did last year on Excel tips and tricks. I guess that was two years ago now at this point but it was with 2013. We also have a short video Customize Your Toolbar and Keyboard Shortcuts. And one specifically I'm using Excel as a budgeting tool also using 2013. If you're using 2010 these last two resources on the list may have some use for your needs. So definitely check those out. If you would take a moment and let us know in the chat one thing you learned today that you will try to take home and practice or implement for your own organization and that would help us know how we did even with the technical issues. The links are not clickable in the slides right now but they are in the PowerPoint that you would have received earlier and that you will receive again this afternoon. So we will send that back out. We would also like to invite you to join us for upcoming webinars and events. Next week we will be talking about how to get tech donations before the fiscal year end which is coming up on June 30th for many of us. And here at TechSoup many of our donation programs reset as of July 1st. So if you would like to take advantage of the donations you can receive this fiscal year join us to learn more about the programs that rely on that. And then we will be talking about foundations integrating technology into their giving. So that is specifically geared toward foundations. And then we will talk about how to get those Microsoft software donations. So if you are new to that program please join us for that one on the 4th. Then we will have two events that are focused around Office 365. So one that is a real intro to what Office 365 is and one that is going to cover more detail on some of the new additions to the Office 365 suite. Thank you all for joining us today. Thank you Rachel, Arielle for your time as presenters. Thank you for bearing with us with the technical difficulties today. And hopefully we will be able to get you something that will be even more useful for you to take home after the fact. We really appreciate your patience in joining us and hope you will do so again. You can connect with us at TechSoupGlobal, TechSoup.org on Facebook and on Twitter. I would like to also thank the San Francisco Public Library for offering these Excel trainings and for Rachel for helping build that curriculum and for all that she's done to get this information out. And we will continue trying to deliver parts of this to you over time. Lastly, thank you to ReadyTalk, our webinar sponsor that provides the use of this platform so that we can present these webinars to you on a regular basis. Thank you everyone. Hope you have a terrific day. Bye-bye.