 Hi, I'm Vera Cole. In this short video, I'm going to show you how to use Excel to prepare your EIA data for analysis. We're going to be working with the lesson 9 workbook that you downloaded from Angel and to which you've already added the EIA data. I'm going to navigate to mine. And this is what your workbook should look like. You'll see that you have three tabs, analysis questions, population data, and the EIA data that you added in the previous step. OK, before we start, it's always good practice just to survey the data, just to get an idea of the big picture. So the title of this data is Primary Energy Overview from 1949 to 2011. The unit of measure is billion BTUs. That's important to note. And let's look at our column heads. We've got production, trade, consumption. And we have it by different energy sources. And we have totals. Then each row is by year. And those years start with the older years first, starting with 1949. And they go all the way up to 2011. OK, with that understanding, we can now prepare our data for analysis. For the lesson 9 activity, we are not going to need all of this information. In fact, we don't need the data in columns B through T. So we're going to hide that data. Now to hide columns, we're going to move your cursor to the top, left click, select column B, hold the left button down and slide all the way across to drag select through columns T. OK? From here, we're going to, again, position your cursor to the top, right click, and say hide. That data is now out of sight. It's not deleted. It's just out of sight so that we can continue to work with this sheet efficiently. OK, another thing we want to do to prepare is we want to turn on grid lines. So let's go to view and click on grid lines. And see what that has done is it's now much easier to find our individual cells in this worksheet. We're going to be adding data in columns AC through AG. So let's left select column AC, hold the left button down, drag select columns AC through AG, release the button, and now let's go to home. And let's change the font to Arial size 9. Now anything we type in these cells is going to be Arial 9. OK, let's add now some column headings. So click on AC, go to the cell AC3, and here let's add the column head, My Analysis for EMSC 302. This is a heading for several columns. This is like a group heading. So we're going to merge some cells together. So left select on the cell in AC3. You see it's column AC, row 3. Left select here, now hold it down, and drag select all the way through AG5. OK, with all of those selected, we're now going to merge and we just need to merge here. OK, and let's make it bold. All right, now let's add more column headings. Over here, our first column heading is going to be fossil fuels percentage, left, we're going to merge these cells. So you know how to do it now. You're going to select all three of these cells, select them all, then we're going to merge them, and we're going to wrap the text. OK, this column, we're going to do the title. This column head is nuclear. Again, it's a percentage. And again, we're going to merge the cells and wrap the text. This column is renewable energy. Again, it's a percentage. And again, we're going to merge these three cells and wrap the text. Now that doesn't quite fit, so we want to make the column a little wider. So you're going to move to the top, see the cursor changes, and then left click and hold it down and slide over to have the width you like. OK, that's a good width. This column head will be population, and that's in millions. Again, we're going to merge these three cells, wrap the text, width is OK. Consumption per capita, that means per person. And that's going to be in million BTUs. Going to merge those, wrap the text, and adjust our width. Now we need to add some data and some values. So fossil fuels percentage, we're going to put a formula in here. And Excel, to do a formula, you start with an equal sign. And we want to divide the amount of fossil fuels. So we just click on that cell, that puts U9 here, by the total amount of energy consumed. Click here. And we're now dividing the information in AA9 by the value in U9. I'm sorry, I think I said that backwards. Dividing U9 by the information in AA9, hit Enter. But we want this as a percentage. So let's click on that cell again. Right click, and go to Format Cell. We want to make this a percentage. We want it to have one decimal place. There you go. OK. Now what's the nuclear percentage? Well, that's going to be equal to the amount of nuclear energy consumed divided by total amount of energy consumed. Again, we want that as a percentage with one decimal place. And renewable energy, the percentage of renewable energy will be the amount of renewable energy consumed divided by total energy consumed. Enter. And again, we're going to convert that to a percentage with one decimal place. OK. Now we want to do the same calculation. And stop and notice here that that percentage is add up to 100, which is what we would expect. That's always a good idea to observe and check as you go along. OK, so now we want to make these same calculations for every single year. So Excel lets us simply copy the equations, and it'll update the information for us. So let's copy these three cells. So select those three cells, copy them, copy. And now let's select the cells. We want to paste that too. So we're going to start here. And we're going to select all of these cells, all three columns for all of the years, using our left click to drag and select. Then we're going to right click and say paste. And look at that. Excel just updated all these equations for us. So let's just check one. 1968, to calculate the amount of fossil fuels, Excel is going to use the information U28, column U, row 28, divided by the information in column AA, again, and row 28. It's going to do, I'm sorry, it's going for renewables. It's going to use the information in column W. And for nuclear, it's going to use the information in column W. And for renewables, it's going to use the information in column Y. And this is all for row A28, so that's correct. OK, we now have all of our percentages calculated thanks to Excel. Now we need to add data for the population. Well, we have a table of population data. If we look at our population data, the numbers are all here, and they're in millions. That's good. But look, the dates are in a different order. So we need to resort this data. To resort the data, just select any cell in the data. We'll just select right here. Going to go to data, select sort. And we're going to say we want to sort by date. Yes, my column, in this case, we do want my data has headers. We do want that checked. We make sure that's checked. And you'll see, we can sort by date. We can sort by values. But we actually want the oldest to the newest. So we're going to change that and say, OK. And now, look, Excel just resorted our data to oldest to newest. Now, to put this data in our other table, we need to start with the year 1949. So we're going to click here and drag, select, hold the left button down. From 1949, we want the population from 1949 to 2011. Going to 2011, at least I'm going to right click. I'm going to copy. And I'm going to go back to my EIA data. I'm going to start right here. So I'm going to click this first button. And I'm going to right click. I mean, this first cell, AF9, I'm going to right click. And I'm going to paste. And Excel just pasted all of my population data for the years 1949 to 2011 and the corresponding cells. Now I need to do a calculation. I'm going to do consumption per capita, which means consumption per individual, per person. And to do that, we need to be careful with units of measure. Because we want the unit of measure to be a million of BTU per person. Our, let's look at the consumption data, which is right here. So there's a total consumption. And we want to divide it by the number of people. But in order to make our units work out, we're going to need to multiply the population by 1,000. So you can use your high school math on the side to watch the units of measure and see how that works out. But to set up the equation, we're going to do perenns, millions times 1,000, close perenns. Oh, we don't want that many decimal places. Let's left click. We know what to do. We're going to go to Format Cell. This time, we're going to make it not a percentage, but a number with no decimal places. OK, so that tells me in 1949, our consumption was 214 million BTUs per person. Again, we want to make the same calculation for every year. So I'm going to select the cell. I'm going to copy it. And I want to select all the cells I want to paste it to. So I'm going to select all the way down. I'm going to right click. And I'm going to paste. And Excel just did my calculations for me for every single year. Wonderful. OK, now to do the analysis, to answer the analysis questions, I'm going to show you a few more tricks about how to use this data. Actually, I'm going to do that in a separate video.