Converting Data into Information Using Excel - Part 2 | Jeffrey Schreier | Skillshare

Converting Data into Information Using Excel - Part 2

Jeffrey Schreier, Don't be intimidated by Excel!

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (37m)
    • 1. A quick primer on the course content

      1:14
    • 2. Text to Columns can be a great data creation tool

      8:12
    • 3. Find_Replace is not just for word processing

      2:41
    • 4. Concatenation is a lot easier to use than to say

      4:35
    • 5. More with the powerful filtering tool

      10:09
    • 6. Subtotals has many great uses

      10:00

About This Class

07c3a5cc

This course is an addition to my first course by the same name, but it will stand on it's own if you want to take it first.  Both courses together will greatly increase your data handling and reporting capabilities in Excel.

  1. Text-to-Columns - for breaking data into multiple columns.
  2. Find-Replace - a simple way to get rid of unwanted repetitive data.
  3. Concatenation - this time we learn how to combine text from multiple columns into one.
  4. Subtotals - a great grouping and reporting tool.
  5. Filters - we revisit filters to learn more about how they can greatly improve your data handling and reporting capabilities.

The spreadsheet used in the class is attached in the project section so you can use it as a learning aid.

I hope you enjoy the class and please leave feedback!

Transcripts

1. A quick primer on the course content: Welcome to my course, converting data into information using Excel Part two. This course has five additional lessons and working with data and learning how to report information. The first real lessons are a continuation of what you learn on how toe manipulate data and make changes to it. To get into the format, you need to get the information you need. The last two lessons are reporting lessons that teach you how to get information from your data. The first lesson is text the columns, which is a very useful tool for separating data from one cell into multiple cells. The second lesson is on finding replace, which is a tool that you may have used in word. No fine has great uses in Excel. The third lessons Concatenation, which is sort of the opposite. What we learned before. There's teachers there to take information and multiple cells and combine into one cell the fourth lessons where we get into filters, which is great for sorting data, selecting data and reporting on data. And the fifth lesson is sub totals. Well, you learn how to group data, come up with totals, create reports from that information, and I think you find this lesson very useful as well. So I hope you take the course and I'll see you in less than one. 2. Text to Columns can be a great data creation tool: our first lesson text. The columns will teach you how to use a tool that is very useful for separating information into multiple columns. In the first part of the course, we use formulas to separate data, which is still very, very useful, and the text the columns tool isn't meant to replace it, but it is a good additional tool if you'd have in your tool belt. So let's take a look at data that we used in the first part of the course should look familiar. Item number and book title. Using text, the columns weaken. Separate that into multiple columns to put into our database. But first, let's just review that text to columns tool. We click on data than text the columns, and then the wizard appears. Step one of the wizard asks you. Is it the limited? Or is the data fixed with the limited, as it says, coming in the data that when you use it along with the tool, it will separate the data into multiple columns? So I have an example of how that works fix with is when the characters perfect number of spaces apart, that when you use the text the column tool. It'll separate it neatly into columns. That's something that is best demonstrated. But believe it or not, this data right here is exactly the check number and the pay or exactly the same number of spaces apart. And the amount is exactly the same number of spaces away from the pay, so we'll do that demonstration very shortly. So after you tell the wizard whether it's the limited fixed with and here in this case, I said, it's space the limited, so it automatically will separate the data into the columns will give you a preview of oil look like in separate columns. And then the third part of the wizard is where you tell it, have an opportunity to tell it whether each field is texts or just general think it will be locked clearer once we actually go through it. The Wizard. But that's really all it is. So we're going to highlight the data in this one column column F and we're going to click. Data takes the columns so the wizard appears. That's asking you, Is the data delimited or is a fixed with Well, as we discussed it is the limited and it's limited by space is there's no Commons. There's no tabs that we can see or apostrophes. Now here is the reason that we used formulas back in the first part of the course because their spaces between the item number and they're also spaces within the book title itself. So really doesn't work very well because if we go ahead and do next and we say Space the Limited, we can click Off Tab but really doesn't matter. But the good thing is that if there was a tab and we clicked on Tab, it was separate. But there isn't so. It's a good way toe actually use the tool if you're not sure how it's the limited or if it's limited at all. So I click space and we say it is Space the Limited. But if we go ahead and use the tool, it's going to split up the book title between Curious and Fred or Broadway in cheer and really just doesn't work. Titles also come out strange, but that's always easy to fix because it's just one title row and there's could be thousands of data rose No. 11 option is to split out the item number, But then you still have to get rid of it to get the title, so we'll go through it, show you how it works. I do next, and I would click texts as I said before and make all these columns text and then that's it . We just click finish. There's already data here and ask if you want to replace it so we can say yes. Okay. And I would splits it into the three different columns. It isn't really useful. That's because while we have the book number broken out into a column, now we have to go ahead and bring the title back together. And there is a way to do that was to demonstrate in less than three called concatenation. But right now, here, better off using the formula. But I do have to Other examples where the text the column tools. Very useful. So here we have checked pay amount. This is different data. The same exact thing data text, the columns and in this case is fixed with. And I know that because I counted out the number of spaces. So when you click fix with in any click next, it separates it out perfectly for you. You can always try this. If you think you've downloaded some data from your Bangor from company data system and you think it might be fixed with, just go to the Wizard and go to Step two and see if it is so we know it is. So now we go ahead and do next. And General, these are numbers you can do you can do. Text went to anything else to them because there are numbers that we use for math purposes to add or joining with its so Texas fine text. Also for the pay. And you see, even though there's spaces in the pain aim, it's not splitting them. Why? Because it's not using space as a delimit ER and then under a mound. He's asking if it's general or is asking if it's text in this case you want to stay with General is this clearly isn't text make look finish and says there's already dated there. Do you want to replace it? Another option is to put the results somewhere else, but really is fine putting it right here. So now we have a split check number, pay a mountain separate columns, Even though it said, General, um, those treat them is dollar amounts because you can see as I scroll down, you can see the fellow amount right here being added up. So that does work fine. You can put a total of the bottom here networks. Really well. The less example is the limited where use commas. I only use commas where they should be, because often you download data and will be the commas, semi Coghlan's or tabs. And we go through the same steps data Texas columns, the limited cough space, click on comma. And now we see it's broken down into three different fields. Click. Next text. We'll go general here. Now, let's change the destination. Usually don't do this, but, um, just demonstrated, we'll click on eight year, okay? And they will do finish here. You have the information perfectly separated. We have the same exact results. Whatever we used the the limited or fixed with. But the difference was how the data was prepared. Statistics the columns gives you a lot of flexibility for splitting data berate tool to have in your tool belt. Okay, The next lesson will be fine and replace, which would be a quick lesson. And I'll see you there 3. Find_Replace is not just for word processing: this lesson called fine replace is gonna be a very brief one was one that I enjoyed doing because it's something that I learned from a person who really did have that much Excel experience but just shows that sharing is really important because she shared this with me and I've used it many times since. The fine replace tool is something we all become familiar with and word when we are editing a document. We want to replace a word with another word, and there's maybe several instances of that. Word and document will also can be very useful excel. And in this big, clear example, here is the data that we saw in the previous lesson. And the only difference is that the word check number colon, actually appears before the check number. And you really don't want that in your database. That will be a data heading should be in the actual data itself. So the fine replace tool is also a lot but a tool for getting rid of it. Then using text columns or using a data formula, and I'll demonstrate that. But first, let's go through the tool, so you click either control F the bring up find or control age to go right to replace. It really doesn't. Doesn't matter a man. What you do is you enter what you want, replace and fine. So it's gonna go to every instance of what you want to find, and they simply leave. Replace blank because you just want to get rid of it. So it's right way to get rid of text in a document that I just don't want to be there, and you don't want to place with anything else to demonstrate this. Now you just click on the column. Worry. Data is in that you want to do the fine replace, then click control H, which takes you right to the replace tab on the fine replace tool. And what are we looking for? You can either go to the cell itself before you start the process and copy it and paste it in. But simple enough type in check number. Move that out of the way. There's Ah, colon, and then it's space because you want to get rid of the space as well, you know, replacing with anything and they just click. Replace all. It was all done. We made 30 replacements, and that's it. Couldn't be simpler. And now you can go ahead and do your regular text. The columns. Or maybe it's a situation where that's the only thing you have to do. So is the fine replaced lesson, as I promise is a quick one, but really, really useful. The next lesson is Arkan Cat Nation, where we're going to start putting data back together again. 4. Concatenation is a lot easier to use than to say : less than three years on concatenation. Concatenation is a term used in excel for adding information together into a single cell. We're going to use an example with data from the first part of the course, and here you see customer information and you see the address information, which is in separate cells. So the purpose of Cat Nation is to bring the information to a single cell. So what we're going to do is we're gonna take the address and we're going to combine into one cell. The purpose would be is, if you do, frequent mail merges or you do mailing labels. You don't have to bring in each piece of the address you should have in a one cell, and you can put that single cell in your database for storage so that if you want to do something by address or by city or by state, you can do that. And they also have that that column available with the full address. If you need it, we'll do the example right here, and I'll bring in the name of the customer. Then I'll bring in the address, and this is where you learn concatenation. The big difference is that when you do King cat nation, you use an ampersand instead of using a plus sign. So ampersand Zehr used to add text on the plus sign is used to add numbers. As you know, the other thing you have to know is that when you're referring, when you're adding text into your formula, you have to put it in quotes. Otherwise it will think it's a cell reference. But let me do this example and then you'll understand. So we're gonna pick up the address. We pick up the first part, and then what we do is we use the ampersand because you want to add the city. But before we had the city, we have to put in a comma, and that's what we have to put it in quotes. So do comma with you. Quote comma space, close quote, vamper sand and then a city. So so far we have 27 Lakeview Road comma Chicago. Let's bring in the State and the Zip. So we do plus, and now we want to do the same thing. Comma space, close quote, ampersand the state and then finally will bring in the zip. And here we won't use the combo. Just used to spaces. And is it? And now you have in a single cell that address stored and it's really a simple is that and that we can copy this down. I think that's it. So those are the five different customers with their addresses combined. If you want, you can copied up here. If this was an active database you just copied up here and you just keep it there and you say full address. Okay, so that's the first first example of concatenation. Obviously, you can use this for a lot of different things. The second example I'm going to do is with names. Sometimes you have names first and last, you say. Well, I'd like to store the name in a single cell. Last name first using what you just learned you probably figured out for yourself is very simple. Just to equal last name. We'll do a comma in a space. So in quotes Sorry if the ampersand first and then in quotes, comma space, close quote and then first name. That's it. Copy this down and you're all set and again, you can just copy this over into your table and have full name. We'll call this So that's the less not concatenation. It's simple, but very powerful. Asian know how to use it, and I'm sure will come in handy to in the future. Okay, we'll see in the next lesson, which is on filters. 5. More with the powerful filtering tool: less than four is on filters, and I call it Filters Revisited because in the first part of the course we did a little bit of filters at the end of one of the lessons. But here I want to spend more time on because they're really useful. There's a lot of things and you do with them, and I'm gonna demonstrate five different filter ideas when you do a filter Miss going to click. If you just highlighted the whole she and then did data filter it would put the little filter drop downs right where the titles are. So what I usually do is go through a little bit extra trouble, but insert a row first and then what I do is I highlight all the data and then I use a stand. I do data filter while it's all highlighted. An advantage there is. If you're sending this to somebody else, they could see the titles very clearly. So let's get Let's get started. Let's try a sort and you know, you know this is already sorted by date. It's sort of by order number, but let's sort of my book title so you click on the down Arrow into that particular column , and it's a sort, a dizzy and that's it. And it's sorting all the other columns along with this one scrolling down. You see everything that's sorted now. You could sort by customer also. Okay, what happens is, since you've already sort of my book title now, you sort by customer customer becomes the dominant sort. For then the book title is second. You can move things around. Also. You could cut and paste this column, put it over there while you're in filter. That really doesn't upset anything. That's, um, that's a use of the sorting feature. Now you can see there's a little up our right here, so it's indicates you that there's a sore going on. Using this main field, you have clicked it down. Our other choices are Zito A, which would go in reverse. I guess them is the highest letter. We have an alphabet, so it's starting from am working its way down. He also sought by color. So at the very end of this lesson, I might talk to you about sorting by color. That is, that is useful. So in case you missed the old sort feature. We have multiple different levels of sorts. The arrow to the right of sort by color indicates that there's something else there. So you you click on a hover on it. Custom sort pops up, click on that and now gives you the opportunity to use multiple sorting levels within the filter itself. So that's covered as well. Let's go put these records back in order Number order. She's obviously very easy. So next thing I'll show you another value from another value of the filter tool is that it's very easy to find a record and especially we have thousands of records. So you click the down arrow here and his go to search, and we'll assume there's work 120 in there somewhere and enter. And there it is. It brings up that record, or if you're not quite sure what number it is. But there's a certain record you looking for. You can scroll down. And so, yeah, it was 175. You can click on that one that will come up. I don't want record 1 20 anymore, So you just click on the down arrow and just get rid of it. Okay, so let's bring back all the records and do that by clicking. Select all until all the check marks in the boxes on you click. OK, and now your records are restored. So the next example that I'm going to show you using the filter is when you aren't bring up a group of records. Really very simple similares. What we've just done here you have five different customers. We click off the select all button and we just want Main Street books. Okay, now we just have Main Street books and you can sword, You could say, Well, I just want to see bold star within Main Street books. Click on Bold Star and I've got those records. Now, if you copy and paste these records actually just brings the roads that you see in your and your filter let's get rid of those Let's restore all the records And again, this is a great tool to, ah, experiment with. I want to show you how to use a number of criteria defined records in a table. So in this example, you just want to see your your top sales. You want to see everything over $20. That's what you're interested in. So you just do number filter greater than type in 20. You get those records, so that is very useful. And his little green lines just indicate where records are being shown because they're below the criteria. If you also want to see profits above a certain number, you could, uh, do profits above $20. Also, you can bring that in. Now you're looking at the higher sales with the highest number of profits. Bring back all the records I was doing this. You can, as I showed before this copy and paste another sheet and just title it. This is what criteria is, and then you have that information you could forward that on to ever want to see it or just keep it for yourself for future reference. All the records are back, so I just want to demonstrate Under number Felt there's a lot of different choices on his equals, does not equal greater than less than between is pretty simple. Eyes greater than a cool too 20 but under 30 and they were getting records are between 20 and 30. We're going toe click on select all to bring back all the records again. Okay, Andi. Lastly, um, under criteria above average, something you may want to see its figure out. What? The average sale isn't giving a records that above that. So So you bring back all the records again. And the last thing I want to show you is how to use colors and why they could be valuable if we going. If you're searching down your records and you said, well, I'm interested in, um se mindful books and any sales over see greater than, um, three you can do is go and highlight them with a color. These will be records they want to go back to, so we'll use This is kind of a like color, and the eyes will click this color. Then you say, Well, I also want to see um, another particular customer want to see good reads and here and quantity, I just want to see quality of five. And you highlight that also and use the same color. If you go back now, I will bring will restore all the records to make sure that we get rid of all the filters. So you have no filters now. you can come in here and really pick any column because I call it all the way across on just go to color filter and bring up the highlight records. So here's and he had the three for mindful books in the one from Good Read. So here's an example where as you go along, you can used colors to mark records at the end. Bring the records in, and you can copy and paste them if you want. If there are multiple colors, I'll just demonstrate that real quick. For whatever reason, you were interested this thes two and you picked red. Okay, now you come in, and when you go under filter by color, you see there's both colors and you can pick the red one, and they could pick the other color separately. So let you have multiple colors, and you could kind of toggle between You're the one. So that's a lesson on filters up that gives you a deeper insight into the things they're available with felt. There's it's a great tool for locating records, copying and pasting them to another spreadsheet, and as your data changes, you can go in and and obviously run these filters any time you want. So I hope you enjoy that lesson. And the final lesson in part two is less than five Subtitles So we'll see you there. 6. Subtotals has many great uses: Lesson five. This sub totals stubbed toes A really handy for getting totals by whatever criteria you choose and then taking that those total lines and copping to another sheet or below your data. I already sorted the data by book title and also by customer, and I did it to a regular sort. I did data sort and I chose those two criteria. So it's already sorted in the order that I want to do this up Totals by. You have to do that because when you do sub totals first, to do this sort, then you do this up. Total good Total's by first my book title because you have to do your sub totals one step at a time. Move your cursor across, moved down to the bottom. Okay, and now do data sub total, and it will give you suggestions, but you just do it the way you want to do it. So you're sorting by book title first. Then you're adding quantity. All the fields air in America, sales cause commission and profit. Okay, replace current sub totals. This one isn't clicked. It really doesn't matter if it is or isn't because there are no current sub totals pays break between groups. That's really something special. Imagine helps you for printing. I've never used it. And then summary below blow data You can every total lines above the data or below the data . Maybe President. Accountant. I've always had a below the data, so I always have that line cooked Before we leave this I don't forget to tell you that they remove all is after you're done. Subtitling your data and you've got what you want. And you've maybe copier took copied your total lines with sheet data back to its original form. These his way to do it is the click removal. So right now you do some total lines by book title, and we're gonna total on these, Uh, he's numeric fields. Click OK, and you have your totals. We'll go back to the top, and when they do while I'm thinking of it, isn't go to ah frieze pains and was going to freeze the top row in there. Okay, so what you'll see is that now we have these three columns that left as part of the sub total. These three com's came in. Let's take a look at them the 3rd 1 is really the one you're in now. Number three, you're seeing all the data. But you're seeing the subtitle lines, which is what sub total adjusted for you. And then you have his grand total line here. I'll just make this Why Enough Signet's everything Number two now just shows you your total lines and that's by book title and the number one is simply a grand total. So we're exact Really? Well, now one thing to do at this point is what I do is I highlight my total rose from going to be sending around the data and also sometimes is copy him, copy them down or copying to another section on the sheet. So let me show you had a highlight. Now isn't a simple as doing is giving it a color, because when go back to three, it's high. Unlike with the filter is highlighting everything in between the total rose as well. So let's undo that now. This is something that is really valuable. Someone showed it to me and I use it all the time. If you just want to highlight what you can see that hot, then, um, bring the mouse over it and then hit F five for Go to And if someone didn't tell you it was here, like me telling you now, you would never know it's here. Physical sells only and click OK was doing now is only dealing with what you can see, and I click the color you want, and it clicked. Number three and just have highlighted what you want so visible sells only is a great tool to know about, and I have it on my toolbar here. I have it out right. It says, Go to special and I just click on that click on Visible sells only. There's some other things on here, but that's the one I use, and that's it. So a great thing to have a new tool bar again. F five. Most people probably go out that way. That's your go to, and five go to is when you want to go to different cells on the sheet and you want to get there quickly. If you know the cell reference, you can just do that. But I never do that, but I certainly have special go to special visible cells all the time. Now what subtitles will also let you do is I'll let you put in sub totals within within this up totals. So we're gonna do that with a customer. So again, we have the highlight the date again because the settings are remembered by excel, which is which is nice. So data sub totals over here and we're now we're going by customer and was important is the's. That's up. Total ad sub total to was fined. The sum is fine. Make sure replace current subtitles is not checked. It is checked. It's going to go in and wipe out what you're seeing. The tolls you're seeing and put the new totals in with. Sometimes you may want that, but, um not not in this case. Click. OK, now we've got within the book title. We've got Subtitles by the by the customer So this look is number four. That's everything again. We'll look going over to the left over here. Number three. We're getting all totals both by that out by customer and by book title and number two just by book title. If you want to do if you wanted the color, your thes totals, your customer totals will be living more laborious. But you certainly you certainly could do it. I would do is I would do it step by step because you won't get in the way of the other. The tolls already already set up, but you can highlight that. Hold the control key down. Just keep going. Very important. Do your visible sells only. Let's give it a different color. Obviously, I was Just do that. Okay. And for and now you've got the total is highlighted by customer in the highlight of my book titles. If you want to copy this data down, that's something else you may want to do. And if you just want your your totals, copy down. Let's do this here, as is interesting, will bring in the title is Well, if you do you copy and then pays Here. You will. You will basically get everything okay. Don't want everything so again visible Sells only is is the remedy for that We'll do is we'll highlight everything we want to see. Will bring in the grand total would do F five special visible cells on Lee who controls C for copy and paste. There you go. You just got your totals. So any time you're copying and pasting data out of a sub total grid, just do it like that highlighted first visible sells only then to copy and then do paste. Okay, so let's undo that and let's go in and let's get rid of all this. Let's highlight the whole range. Okay? We'll do data data sub Total removal. Now we're back to our original data was really that easy. So subtitles gives you a lot of different options for totaling data for doing counts. But the things remember our to highlight you're a total your total row if you want to, and use visible sells only when you're just seeing those total rose and also use visible sells only when you're cutting and pasting to a different sheet or below the sheet. Iran. So that's the end of part two of turning data into information using Excel. I hope you enjoyed it. Please leave some feedback. I'm really interested in what he thought of it, and we'll see you next time