Excel Tip 12   Working with Big Data Part One   Visualization and Manipulation

Excel Tip 12 Working with Big Data Part One Visualization and Manipulation


Hello, and welcome to excel tip number 12. We’re going to talk about how to deal with Big data So this is a big piece of data and it’s weather data for Phoenix, Arizona It’s something called a typical meteorological year data And so since it’s a typical meteorological year it pulls data from a bunch of different years and that’s why you’ll see a bunch of different years in this first date column and It does hourly data so it’s every hour for a typical year, so it turns out it’s 8760 hours or 8761 Rows since we have a header Row this is a big amount of data so I’m going to show you a few tips and tricks to Deal with this data first off sometimes when you’re scrolling through this data, you want to make sure that you can see the top row. so one way you can do this if you go to the view tab and go to freeze panes and Freeze the top row so what that does is as long as you scroll, You’re going to be able to see the top row No matter what column you’re on even if you’re all the way at the very bottom This is nice because now you know what data you’re looking at Another thing you can do is Freeze the first column, so if you have in this case, we don’t really have a long data set But if you had a long data set or you wanted to go all the way over to the right here You could still see the first column So that’s freezing the Rows and the Columns And sometimes that’s you know sort of helpful So we just unfroze everything so now another thing that’s helpful is to split So if we just go ahead and select this row number four and go to split What this does is it gives us it gives us two Excel windows that are looking at the same sheet So for example in this case if I want to look at the very bottom of the sheet in the bottom and I want to Look at the very top. I can do that This is helpful too so if you want to look at two different places in the excel sheet at the same time You can do that, and you could also split side by side so again this gives you two sort of Viewpoints or two scroll bars so you can look at two different places in the excel sheet at the same time. So this sometimes comes in handy the one I use most of the time and we’ll leave it here for now is I usually use the split and so I can see the very top as I scroll down here Okay, great. So that’s one thing the other thing that we can look at to Sort of look to get to data Let’s go back to the home tab and go to find and select. so we could find some data But I’m going to show you the go to Perhaps I want to know what the 555th hour looks like so if I look at that and I hit okay, it takes me to cell A555 so sometimes when you’re dealing with Incredibly large data sets This is really helpful because you don’t have to scroll through the whole thing and make sure you’re in the right spot So that’s really nice so the other thing we were to look at is If I want to select a lot of these cells maybe I want to select only the first 500 of the solar power column so if we go up to 500 and actually the better way to do this is to go to A500 and Then what’s nice is that I can do a couple things the first thing I could do is I could hit let’s say under solar power I could hit control and just scroll all the way up this takes a little while So now I have selected all the solar power column But the other thing I could do is if I go back down to the 500 column and Unselect everything I could do that and then hold shift and select the very top Row So now it selects everything in between you can see when I scroll up it selects everything in between So control, just to sort of review that, control Will select so if I’m holding control right now It’ll select individual cells and I could drag and select more cells, so I’ve just selected all those cells and what shift will do is it will select all cells in between what you select, so I’m holding shift now and now if I go over to this corner Basically if you do two corners of a rectangular with shift, it will select that whole rectangle So if I select this one hold shift, select that one selects that whole rectangle So that’s what shift and control do and those can help you really select a lot of Data So the last thing is let’s say I want to do a formula, so let’s say I want instead. I want the temperature in Fahrenheit and We know this from before that this equals 9/5 times the temperature in Celsius Plus 32 so what I could do and You know this isn’t very much fun is I could just drag this all the way down This is going to take me a long time. So if I’m doing a lot of formulas, this is going to take forever so It’ll work so you can sort of see now. I drug it down in it and it works But the other thing that’s nice about this is that if I double click on this bottom right one It’s going to fill the formula in and we can see It’s going to fill the formula all the way to the bottom of what we did before The other thing I so, let’s start from scratch here if I take the formula 9/5 times Celsius Plus 32 and if I double click here It’ll go all the way to the bottom so I can scroll all the way to the bottom and see that have filled in right like that one last thing to fill in a formula is Let’s select all that by holding shift and selecting and all one last thing I can do to fill in a formula just to give you guys a Good Basis for all this is I can copy this cell I Can hold shift and select the very bottom most and I can hit control V. So that will paste the formula. I remember the formula is going to change Depending on where I’m at in the sheet So there you have it. Thanks for watching

Related Posts

Combat artists

Leave a Reply

Your email address will not be published. Required fields are marked *