Microsoft Excel Data Analysis and Dashboard Reporting : Dealing with Raw Data
One of the biggest challenges that you're gonna run into as you look to present data from Microsoft Excel is the data itself. It's the biggest challenge. Data comes in all sorts of formats. Sometimes it's really simple formats. Sometimes it's really elaborate. Sometimes it's all over the place really inconsistent open in front of you. I've got an example of what I'm gonna consider raw data. Now perhaps this data that you're looking at here came from some other system some database in the backend somebody sent you a CSV file a text-based file somebody pounded it out inside of an Access database or dumped it into Excel for you wherever it's coming from or maybe it's even a Word document whatever we got data now looking at this data. It doesn't look like much again. I'm considering this raw data it's really simple it's a bunch of text and a bunch of numeric values and. I can see there's some some mixed data inside of here. There's perhaps some data that's not formatted the way that. I want it when I go to present this data. I want consistency in the data. I want it to look good now. This is just one example. This deals with customer information so I've got a few columns. I've got company name a customer ID and contact name and so on and again as you can see I've got text-based and I've got some numbers and some mixed data as well and here's some more numbers for our phone and a fax now again. I want to make this more presentable. So we're going to talk about some concepts of analyzing the data cleaning it up trying to make it more presentable. Get it ready for that dashboard or get it ready for that final report so in those concepts that we're gonna get into your talk about functions things like text based functions now you can use text based functions to help you extract data from your raw data how you can use text based functions to clean data up and we'll talk about it various other functions to help us master analyzing this data here kind of doing some data mining looking for the data that we need and cleaning it up as we do so now this is just one example of raw data here.
I've got another tab called order info. Hey once again really simple came from some other source a text file and so on dumped into Excel. Now it's up to me to present the data well first. I need to clean it up. Get it presentable. Get it consistent. And it's formatting this on so you see here. I got things like a numeric date. I got text values. I got this employee ID employee ID not remember which is just a number well. I don't know about you but I'm not gonna go around memorizing a bunch of employee IDs I want to know who these employees are right. What are their names. So we'll talk about other functions to help us not only extract the data that we need but perhaps substitute or insert data that we do want in its place maybe at number 6 means. Andrew fuller and number 4 means Nancy and so on. I want to see their names. I don't want to see this number. Same goes with this ship via 1 2 & 3. What's 1 2 & 3. Let's clean that up. Let's make it more presentable so again. We'll talk about some functions to help us do this. I want consistency. I want my dates to look a specific way and I want it to be consistent throughout so again raw data biggest challenge. You're gonna run into now. We got to make it look presentable we got to clean it up. We got to create consistency in there and overall just have a better experience as we work with this data. So that's really what we're gonna get into you throughout the course and then once we got the data looking good. Then we'll create the dashboard. We got the consistent good looking data now. We're going to present it in dashboard format.
I've got another tab called order info. Hey once again really simple came from some other source a text file and so on dumped into Excel. Now it's up to me to present the data well first. I need to clean it up. Get it presentable. Get it consistent. And it's formatting this on so you see here. I got things like a numeric date. I got text values. I got this employee ID employee ID not remember which is just a number well. I don't know about you but I'm not gonna go around memorizing a bunch of employee IDs I want to know who these employees are right. What are their names. So we'll talk about other functions to help us not only extract the data that we need but perhaps substitute or insert data that we do want in its place maybe at number 6 means. Andrew fuller and number 4 means Nancy and so on. I want to see their names. I don't want to see this number. Same goes with this ship via 1 2 & 3. What's 1 2 & 3. Let's clean that up. Let's make it more presentable so again. We'll talk about some functions to help us do this. I want consistency. I want my dates to look a specific way and I want it to be consistent throughout so again raw data biggest challenge. You're gonna run into now. We got to make it look presentable we got to clean it up. We got to create consistency in there and overall just have a better experience as we work with this data. So that's really what we're gonna get into you throughout the course and then once we got the data looking good. Then we'll create the dashboard. We got the consistent good looking data now. We're going to present it in dashboard format.