Converting Data into Information Using Excel | Jeffrey Schreier | Skillshare

Converting Data into Information Using Excel

Jeffrey Schreier, Don't be intimidated by Excel!

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (43m)
    • 1. Course Introduction

      1:21
    • 2. A little database know-how goes a long way

      5:22
    • 3. A single formula to master your data

      12:36
    • 4. A single reporting tool to create information

      11:18
    • 5. Yes, you can create good data from bad

      12:25

About This Class

038efdd1

Welcome to my course "Converting Data into Information Using Excel"

This course is designed to help you grow beyond using basic Excel spreadsheet creation and formatting skills, and show you how to use Excel to add value in ways you were not previously exposed to. I specifically cover proper database creation, the vlookup formula, pivot table reporting, filtering and several text formulas including the "If" formula.

My sharing philosophy is to go beyond just explaining Excel techniques to showing you how to use them in ways that will be helpful to you. Everything I show comes from what I consider "best practices" during my 30+ years using using spreadsheets and Excel in my ongoing career.  

Please watch my introductory video and please enroll if you are interested.  I am confident that you will find it worthwhile.  The spreadsheet I used in the class lessons is attached in the class project section and you may use it as a learning aid to help you with your class project.

Transcripts

1. Course Introduction: Welcome to my Excel course on skill share. I'll share with you Excel tools and techniques that allow you to greatly increase your ability, the manager data, and turn into information. The course has four main teaching goals. The first goal is to show you how to structure your data into practical and easy maintain tables that connect to one another through common fields. The second goal is the teacher had to connect these tables through the V look up formula into one large virtual table. I say virtual because the data only appears here through formulas, and here is the finished product. Every column of data greatly increases information you can report on, and all the fields that are in pink were all brought forward through formulas. The third goals the teacher had to use pivot tables. Repair reports like me run over and over again whenever you want, such as when you're dated changes. Here you see seven reports that we're all run from one pivot table and one set of data, the fourth goals that teaches some Excel text formulas, which will be particularly helpful if you're downloading data from your bank or from the computer program at work and these be modified, as you can see, if my resume I've been literally doing this kind of work for decades. And therefore, I'm very confident that tools have chosen a teacher in this course will serve you well. I hope you'll join me. 2. A little database know-how goes a long way: we're going to start with a lesson on database structure. I know this is an Excel course, but you'll see why this is important. As we move forward. There are only three rules, and you know right now, by following them you will save time when working with your own data and find it easier to navigate and find data and existing databases. That said the characteristics of the three tables you saw in the introductory lesson the first tables orders and it has an order number. They the book, order the customer in the quantity. The books similarly has related information named the book the Great. It's targeted towards number of pages, sales, price and cost and customers has the customer, of course, and the commission percentage and then the address of the customer now usually be working with one table that's transactional in nature, which in this case is the orders table and other tables that can bring in additional information by connecting to it. And then here we have the books table and the customer's table, so it's simply the orders table. Having just a book code and a customer code, you wouldn't really get much information But if you wanted to know the name of the book, if you want to know what City of Customers air from, if you want to know what grades doing better with thing at all that information by connecting the tables together? How did the tables connect the tables? Connect with a primary key, and that is unique. He that identifies the record in the table looking at books and customers. It's pretty obvious that the key here in books is BC, and over in customers a CC. So those two codes are unique to the book and customers, respectively. And whenever you want to make a link to those tables, you would use these codes now become clearer as we work with the vehicle formula in Excel, the orders tables really a different type of table. On any given day, the same customer can order the same book in the same quantity multiple times. So in this case you have an order number, which is unique, non repeating, and that really solve the problem of having a key field. You just used the order number just like that. We have covered what is known as the 1st 3 rules of normalization. I'm gonna show them to you over here separate tables with data that is related. We saw that no repeating groups and individual tables. As I said, the book number of customer number cannot repeat as the transaction number cannot repeat. And there was a primary key that identifies each set of related data. Those three rules. And because you met these three rules, the database is considered to be in the first normal form. So now we're getting technical. If you want to learn more about it, you can certainly Google rules of normalization, and you'll see there's many more rules. But for the purposes of this course, we can stop right there before I move on. Let's do a couple of things first. Let's recap by These rules are important going back to our data. The reason date is group logically and easy to find tables is really common sense, because if you're searching for data, you want to look for it in the most logical table. That that's pretty obvious. The reason that the tables are separate is if, for example, he didn't just use this customer code, but you used the address and the name in the orders table. You have to repeat all that information again over and over again. They become very inefficient. The second rule regarding not having any duplicates if you had duplicate information. For example, if the book number repeated twice when you try the link, the orders to the books you'd have multiple orders because every time it found the book number, it would show was a record. So that would become redone that no, that will become clearer later on the course. The third rule regarding the primary key obviously didn't have the primary key couldn't really link the tables together. So that's the reason for the three rules and why they are both practical and necessary before we end the lesson. Let's a little example to show the universal nature of what you just learned. Imagine we're working in the finest department of a law firm, and your Boston is statistics on labour incurred by office. For a number of cases. Based on your newfound knowledge, you can intelligently esque I t department for three tables number one, a transaction table that captured daily hours incurred by each employee by case that must exist. Otherwise, they couldn't build their time. We know what they always build their time. Second, an employee table. So you could see what office the employees from you see it more clearly here. And third, a case table. The case table gives you additional information about the case, including the name the lead partner that brought the case in in the office. Now you know where to find the data. And the next trick would be to link it all together, the poor on the information, and you'll learn how to do those things in the upcoming lessons. I'll see you there. 3. A single formula to master your data: it is time to learn how to use a V look up in Excel. What we're going to do is combine the tables that were in this lesson, the three tables in tow one table and as a reminder of the reason we don't have them combine along is it takes a take a great amount of work. Have to type in the book and customer information for every orders. So I have an example here of how the V look of work, and I made this very detailed. But I can show you that once you become familiar with it, which won't really take that long, you won't need to go through the steps. You'll do it intuitively. The steps so perfect. Look up. You have to choose the table. You wanna add information, too, so we're going to build on the orders table. Then you have to choose the table that has the field you want. And that's obviously from our previous lessons, the books and the customer's table. The third thing is, you have to enter the field names that you're adding now you can do that before or after you do the V. Look up. I'm doing that before. So I've taken the orders table. I've inserted some fields book title the sale, which is actually the price times the quantities wouldn't do something a little bit more complicated. The cost, which is the cost of the book times, the quantity, the commission, which is going to come from the customer table and then gross profit, which is actually our own formula. And then lastly, you have to locate your primary key field because without a field that links the other tables, you simply can't do if you look up. So just to recap which totally wanted information to where you gonna get the information from what feels specifically bringing over and watch your primary key once you know that you could do if you look up, the actual formula itself is right here, and you know, Excel prompts you as you go through the formula, but I think the problems are a little bit confusing. But there's four pieces of information that you need. I'm going to color coded here. So the primary field in the base table if we go back to the base and I called it the based table to table that were adding information to ah highlighted this d 16. Yeah, column cell D 16. That's the field as the primary key. So it's the first piece of information and develop. If you look up, want to know they don't want to see all the fields, A look up table. So you actually have to highlight all the fields. You look up table. We're going to do this shortly. The column you want. That's very important. Which specific column do you want to bring over and will highlight Will type that number in . And finally, this last one number four is always false. Now there may be situations where it isn't, but in my 20 years of doing V lookups, maybe once I didn't say false. What false means is that you don't want to accept the closest match there isn't a match, there is imagine comes up with an error message, and then I go find out why. So the first we look up to bring over the book title, I'm gonna actually do this. But just to go through it in the example First Field D 16 that, as we said before, that's the primary keep field and then you're all fields, you look up table. The next piece of information right here is in 16 to s 22. If you move over here to the table, you see that end 16 as 22 is the data that you're looking up. Then I want to bring over the title, which is column to write here. So just type in the two and the false. That's it. So this may be confusing right now, but I'm just gonna type it right in. So equals V. Look up as 59 is, the one thing tonight is the information, but that's d 16. So it puts t 16 in there. And I would say, Ignore the problems you're getting from Excel right now and 16 to s 22 is right here. I have to bring in the whole thing and 16 s 22. Okay. And if you look above there's no dollar signs. I've got dollar signs here. I'll explain the dollar signs in a minute. I'm purposely leaving them at right now. Column two. And finally, the word false. Okay, Presidency Green Shadow. The book title came over and we know we did it right. It came over from s 51 59. If I did it wrong, you'd have our Arab Esther's there. Now, if I try to copy this down, it won't work. Why? Because, as you may know, from Excel, Excel changes, references and formulas every time you copy something down unless you lock them in. And the reason you want excelled to do that under normal circumstances, if you've all done at sums and you copy it across a bunch of columns, you want those references to change here, you don't. So let's show you. You can lock that you should lock them in as you do the formula. But I purposely did it after did the formula. So d 16 you want the Colin toe lock in? Because as you copy across to the right, you don't want a column to change. But you do want the road to change because it as you copy down you want it to look at the B C field in Rose 17 Row 18 Road 19. So you simply put a dollar side in front of the column and that will lock in that column so we won't change as you move to the right. Did you definitely want all the references from the book tables to stay the same so you can type in dollar signs? Another trick would, you may know is type in F four and therefore automatically puts dollar signs. And this is while you're in the cell reference itself puts dollar signs in front of both the row and the column. As you press f 16 you get the four choices which is in front of both in front of the column in front of Roe or neither. So we're gonna accept that we're gonna move over and accepted here, and that's it. So now let's copy this down just to show you how it works and you can do a couple of things in new copy. Highlight the whole thing to paste bone. Ever have a string of of rose with informations filled in If you just go to the little hand on the bottom right of the seller, You know you there when you have that that that little X appear and double click automatically does that. So here reviews If you look up, we've successfully I copied the book titled Down Okay, now we can do is we can now use this formula and bring in the sale price and bring in the cost. So to do that, we simply a copy. This value here is formula here over the sales price and over the cost. And now you say, Well, it keeps saying the book title. Well, that's true because we didn't change the column. That's all we have to do. So what column do we want? What number we want? The sale price is count this 12345 Okay, so go back to the formula and we make this of five and that we want this one to be column six. So there he goes. We're making good progress now and we want the actual price times quantity. So that's easy. So we just this times quantity. So there's only one. So you don't see anything fancy there and this times quantity that's done. So now we're going to bring in information from the customer table equals we look up and we're looking for the customer name here. This time in F 16 is the link to the customer table. So it says, and I told you to ignore the instructions from Excel, but go over them quickly. So look of value. That's that's what they call it. So f 16. We want a dollar sign in front of the column again. Common. The table array is what they call the information in the customer table. So bring that over four to make sure you lock it all in. Now, what field is the customer ins? That's the 2nd 1 go down. I'm so yes, the 2nd 1 The commissions in the third. Okay, let's do calm to false. I don't I would copy this over to the commission Changed too. Home three. That's all set. Now we do want the sale times a commission to get actual dollar amount. So as before, little formula. Nothing too fancy times the sales price so still right up to there. So now we're taking some information that we built, and we're using it to create the commission. So this actually is a dollar amount. So the commission on this sale would be 65 cents, which is $6.50 times the 10%. Finally, gross profit is to sell. Mine is the qualities in accounting terms. So this is just a shout out to the other accountants out there. But you certainly don't need to be to understand this. So across. Gross profit on this sale is $4.60. So to sales price minus the cost, why is the commission that's a gross profit? That's all done. So let's copy these down. When we do them all at once, you can go to the little handle here and double click. That's done. And we gotta bring down this. I think we'll left justify this. This is my own toolbar. I'm a big proponent of building your own toolbar. So this is mine, And I just put my you put your favorite things up there. That's copy this down, and I have all your data combined the day do you want on one table? And there you have it. So in the next lesson to talk about doing pivot tables and how to report on all this information now that you have it all combined into one table and I'll see you there 4. A single reporting tool to create information: now moving on to a lesson on pivot tables, which will probably be a relief after the look up Look up, I think is a bit intense. Pivot table is a nice break because it's really not very difficult that all do a pivot table and they're so powerful. So before we get into the mechanics, this wanna point out that one of the things I mentioned earlier is if you make a change to one of the tables you're connected to, the change automatically pushes through it, and I just want to demonstrate that. So if you look at the very first title, which is green Shadow, and if we went over here and we changed the sales price, you'll see the very first row here. The information will change, so it's changes to $14. You see the very first road. The cost of sales price rather increased the $14 and the gross profit chains accordingly. So it's just change that back. Okay, so now we're going to do our first pivot table. So it's highlight all the data Insert pivot table, no top parts already done like the table arrange. I always, like, arrange first, then to insert pivot table and then says, Where do you want? Oh, don't worry about external source. Where do you want to put it? That's the important thing. So I will go into a new worksheet here. If you want to put it in existing worksheet, that's fine. Click a cell in the worksheet where we want to pivot table set up, and that will work just fine. But we'll do a new worksheet. Okay, so what's happening is the pivot table is now showing you all the fields that were in the table back here. All these fields were now brought forward over to the sheet for which it created. Maybe we'll call that call this reports. Using a pivot table tool is really pretty intuitive. You have four boxes to drag your fields into filters, which I don't use because I filter my data before I get to the pivot table level columns. We just drag custom over two columns soup that looks like they see the columns to go across . In your report, I move that to Rose. Now they go down his rose This way, you can't put customer devalues, really. You'll just give you account so values is where you put the fields they want calculation to be done on. So if you want to know your total gross profit for that table, each of saw if you just drag gross profit in there, you would see the total amount. What we'll do is we'll demonstrate a number of reports view, and by doing that you'll learn how to use it. So I want to do revenue by title. So I want the books to be in my rose and there they want to drag it there, the automatically show ups. The beauty of this is that it doesn't look right. Just put him back. So I put the book title back. If I dragged that back with Rose are or the different fields are, it's gone. So let's do that again. So I put the book title here, and I want the quality sold. And also let's do the revenue. Okay, now, this is for the entire period of time that was on that table because I highlighted the entire table. So he sold 46 of Bold Star and the value of that was $276. So we want to create another report that we want to do. Let's do revenue and quality by customer, so you have to go back now to the main table. You can just use this table, so just highlight the information here, which is the results I will do. Copy. Let's go over here and let's let me use Control V, which is what I prefer paste and I have a new pivot table. It's set of exactly the same as the old, except you want this to be by customer. So it's easy enough to just drag your book title there and bring in your customer field instead. And I have a report how much is sold by customer in the dollar amount, and you see that the total quantity and total dollar amount agree. If they didn't agree, you'd have a problem because you're working off the same database and just taking a different slice. The first lice was a slice by title. The second slice is by customer, so if you wanna, you could be a dollar signs. And now, if you want, they can't change the role labels. That's one of the the drop bags here, unless you copy and paste special somewhere else. Because once you change that, you lose the connection to the pivot table. Let's copy this down for 1/3 report. If the report would be by date. So drag customer back here. Hey, on the date Clear it is this is a bigger table and goes outside the definition or the size of the original table. But that doesn't matter. So again, 229 books sold is the dollar amount. Okay. And again, those total 15 52 to 29 equals of the tables. Now we've built three tables. Yes, we'll add another pivot table over here would be number four. Does Rose expanded. Okay. And we're going to call this one by customer and by date. So it's a small small change to an earlier one to Christina on Let's add date. This is an example of how you can combine a couple of different rose and a couple of different columns all into one table. Table number five. This one goes way down, so it's it will sneak this one over here. 50 able is gross profit by title. So you already have title here. We'll get rid of quantity. We'll get rid of sale. I'll just bring in the gross profit. I wanted to one more pivot table to illustrate how you can create a matrix using the pivot table so that me copy this once more and I go down further was, I think we need many more room. I'm not sure. And all I'm simply going to do is take roll labels as the title, which is what we already have, and then just move customers to the columns. And by doing that, actually, let me change out the gross profit for quantity. There we go. So now we have a look at how our customers are doing, selling the titles by quantity for the period of time the database represents. I think this gives you another dimension to the data, and I wanted make sure illustrated because I think could be very useful. So this gives you six reports that we created pretty quickly from one that was combined using three look up fields, a couple of other features I want to show you regarding pivot tables I think could be helpful to you. Let's say you're interesting, curious, fried what makes up the gross profit for Curious Friend. Just double click it and a table appear with all the book sales for Curious Fred that Aaron that pivot table. So if someone contact you from your company and says, Hey, can just give me some detail in this particular book title, you're able to do it pretty easily. It creates its own she to do that. And you can say that something else or you can email to the person. The other feature is if you're over here, it is. One has split out, for example, the booksellers that make up the customers that make up curious friend double click it, and you have a choice of what feels you want. Toe spanned on Click Customer will expand on all the booksellers that make up curious Fred . Plus, it gives you a little cross for the other fields as well. A couple of extra features that you can do with pivot tables, and I certainly would encourage you to explore the and get familiar with them, and you'll find that really become viable to you. So that ends our lessons on pivot tables before I go is one of the touch on filters because I mentioned filters earlier as something that I do before I get to the actual pivot table. So let's go back to the pivot table data. So enough familiar with filters. I'll show you how to use them. Highlight all of your data, including the field headings, and then click data and then filter. You'll see these little drop down arrows. They give you a lot of flexibility to see certain information, whatever you want to see. So, for example, customer of You just wanted to see and click select all to both de select and select all the fields, so I just diesel elected slept all by clicking on it. I just want to see a good read customer sales click on Good reads. And now I just have their sales. If I want to sort them my book, I say sort a dizzy. And now I have just the sails for good reads sorted that way, and you can if you want to bring this data elsewhere, you could do copy, pay special values somewhere else, and you have that data if you want. Look at the order numbers. You actually see every order number, so it's a quick way to find order numbers. There's some filters you can start by colors, live things we could do with the filters. So I really encourage you to explore filters and to use them, and you'll find them really come in handy when working with data. So in the next lesson, I'm going to show you some formulas you can use to get your data in shape in case you've downloaded data or got it from another source, and it just isn't really ready to be used in a database. You'll see it next lesson. 5. Yes, you can create good data from bad: welcome back. So far on the course, you've learned how to construct improper database. You've learned how to use the V look up formula to combine data from disparate tables into a single table, and you've learned how to use the pivot table to report on that data and create information . Now we're gonna learn something which I think is very valuable. It's what to do when you get data from the computer system at work, or maybe from the bank or whatever is in your own data isn't really in the proper format that you needed to work with. The example here is where you have the item number and the book title in one cell. A need to get that item number broken off so we can use as a primary key. So it's really quite simple. In this case, you would use the left formula. You sent your cell and number of characters here, an example we see equals left. So left is the obviously the name of the formula equals left. B nine b nine is right here. We just want for characters so we do equal left comma four. There's no need to lock in anything because we're not going to the right. We're just going straight down. So you have to lock in the column through his old is F 1 57 if you drag that debt, which is correct if he dragged that down. So there we have the primary key feels that you need. So what happened? Now, if your data set up, we have the book title first and then the book code. Well, you just simply use a right formula. Really, really just the opposite of the left. So right. And as G nine come of four, I have to correct my example here and just copy that down That gives you that breaks off the primary key. But what about the title you need that broken out also. So that's where we would come into the mid the mid formula because you don't know how many places it is. You want to start at the break, but you know how many what the field length is. So if it's on the right, it's pretty easy. But Mitt is a little bit more involved than left or right, so made you start with mid cell. The cell is the cell you want Seimas? The other formulas. Now the star character. Where you going to start? And finally, how many characters you want now? Here it's constructed B 21 and you want to start the sixth character? Because, you know, the title is the six character of 12345 and then six. So you want to start with six now, fortunately enough to have the exact number of fields. If you say 100 is only 20 or 10 whatever it is, it's only gonna pick up. The number of characters are so you're not gonna have a problem with having 1990 spaces. So we type in equal, mid to sell are going to do six character and we got put in 100. Okay. And the name of the title of book is curious, Fred, so that that works. We copy this down, and now we have between these two text formulas, we have the title of the book and primary key field. Have you had 300 or 3000 of these? These fields that needed to be parsed? You can see how easy it is. He's doing the formula. You just copy it down. Now we're going to learn how to use the finding if formulas but no data gets a little trickier by trigger. I mean, we have situation. Here were both the book code in the book title or a different number of characters. So you have to know how to separate them in that situation and what uses Use the fine formula because the fine formula would tell you where that space is. You can't count on it being in the fifth or sixth or seven position, because it's gonna be different if every record and you may have 1000 of records. So if you know with the spaces, then you go ahead and you can do your formula formula using the results of the fine formula . First of all, let me tell you what the components are of the fine formula. First you put on what you're looking for. Then you enter what sell their in. And then you say, what character you want to start searching from with for our purposes and almost all my purposes is one equal find. I'm looking for the space, which is and always put actual text in quotes so it doesn't mistake it for a formula or a cell reference. So open quote space. Close quote. We're looking here in c 11. I was starting with the first cell, so it tells you the space is in his character. Five. We just copy that down and that would have that information and you can see, obviously varies for each row. Now that we know that we can go ahead and we can parse out the book code two equals left. This is familiar to us just going to replace a number with a formula. So we want to pick up left and the number of sell the number of characters. You want to pick up his result of this formula minus one. So why is it minus one? It's minus one because we don't want to pick up the space character in our results. You can see it works, and we'll copy this down. So even though the book code is different numbers of characters, it knew that it wanted one less than the space. So this is done. Now you have to go after the book title. We'll do that now for the book toe title again. We're going to use the mid formula except that the title may start at different places within that cell. So we can actually just cheat here and copy this down. Obviously, the same result is above and they were going to use mid so equals made to recall they were looking at this cell. Then we're going to put in the reference here Now, this time we want to start at one higher than the space because we don't want start the space you want to go space plus one plus one. We could put in the 100 like we did before. Never get. Now we get our book title. So it's very similar to the mid formulated before. Except instead of putting in worry Want to begin as a number you put in the formula plus One. Okay. And you copy that down and you've successfully parsed out both the book code and your book title. Make it look nice. Okay, So last thing we have learned is the If then if then is a little bit tricky, because it's really not something that you've concern in a textbook is really something to make up understanding the logic of the formula itself. So in this case, I have a situation where the date on Lee appears at the beginning of each group of records for that date. But if I want to set up a database, I have toe have it copied down, and you may get a lot of reports like this where there's several fields that have groupings and the group header only appears at the very beginning of the section. So what do you do? You add a column and you put a formula in here that basically says, If there's something in here I wanted over here, If not, I'll copier from above Now the very first records. A little tricky. I'm going to suggest the ages. Copy that information over the first record to give yourself a starting point. This will help you with the formula. So their formula is. You ask a question. That's the first part of the formula. The second part is, what do you do if it's true? The third part is what to do. If it's false, the formula is equal. If is there anything in the date field right here? So you put the date field equals quotes and quotes with nothing between them means blank. So if there's nothing in them, then you want to pick up the information from above. If there's something in them than you want to use that information, it's really a simple is that that works beautifully. So in this case, let's go back to the question. Is age 10 equal? Nothing. H 10 does equal nothing. Then it's above. Okay, now when there's something in there, it will pick it up so we'll just copy this down. We have to copy to begin to the bottom. Just copy to here. You see that every time it changes over here and column major changes over in column I. Now it's just examine this one here. So in this case is a blank. Well, actually, it's not blankets. There's something in there, so it skips. The first response to the second part of formula goes right through the third part here. It's blank, so it goes above, and then again it gets to a situation where there's something there. It just falls and actually picks up the value of the cell. That's the F then formula, and you can copy it very quickly all the way down to the bottom and you can see it. It'll work every time. This is a formula that I just came up with, and I think people do this all the time using the FN's. So what you do now is you would copy this all the way down and put it in. Put it over here if you want, and then do pay special values. That's it. And you can delete. I'll move this over so I dont deleted you canal the lead this commune needed anymore. So we insert the column you put in the formula and then you get your result and you copied over his values and you're all set. That's really a very, very simple solution to what can be a very difficult problem. So I hope that, um, going over these examples helped you between parsing and if then because you're gonna run too difficult situations with your text. But once you get your text in the right shape, then you're gonna be able to set up a proper database and you'll be in business. So this is the last lesson. This course I thank you for taking it. I hope you enjoyed it. Most importantly, I hope you learned and you've increased your skills working with data in Excel. Oh, there's more that have covered. I tried to give you a basic set of skills about making too confusing, but in a future I will definitely get into text. The columns, filtering, sub totals, charges, a whole bunch of things you can learn. But I wanted to teach it to you in a way that you really grasp not only how it works, but how you can use it. So once again, thank you. I hope to see you in the future.