Excel also uses this same format for table styles and other analytics. tools. In Google Sheets, a list format is where you first of all provide column headers at the top of each column. These help to identify the data below. The other important detail for a list format is that there are no blank rows in your data. The reason blank rows are bad is they're like hitting a pothole when you're driving a car. The analytics can't work. So, for example, the sorting and filtering will get stuck when you hit a blank row. Now, blank cells are okay. If you look at the current file that I'm in, I have no entire blank rows..
And all my columns are the consistently same kind of information. So, with this list format in mind, let's go ahead and look at how to perform a simple sort in Google Sheets. I'm going to come in and actually highlight the range that I'd like to sort by. You don't always have to do this, but I recommend it, especially if your data isn't quite consistent. Once you get all the data highlighted, mine's about 200 rows down, then what I'm going to do is go back up, And go to the data drop down menu. This is where your sorting tools are located. You'll notice that you have a sort sheet option and a sort range..
Now because I have a range selected, which is on purpose, it helps to make my sort more specific. I'm going to go with this second option. You'll come in and you'll notice that I have either the ability to sort the range in column A. Or I can come in and go down to advanced range sorting options. When you really want to control your sort, this is what I prefer to do. In this box, I'm going to come in and tell my range that it has a header row. Those are the column headers I mentioned earlier. And you'll notice that when I do this, it helps me to identify what my columns are. In this case, I'm going to pick last name..
I can pick my sorter. Remember, there are two. A to Z is ascending, Z to A is descending. And then I'm going to click on sort. If I go up and look at my last name column, you're going to see that now it is alphabetized by last name. So remember, we always want you to try these things out. Go into Google Sheets, try selecting a range, and do your own sorting in Google Sheets. Hi, I want to learn how to do a two column sort inside my Google Sheets file. Right now you'll see that column C, my last names, are sorted by last name. But my problem is if I try to go in and do another sort, this.
Now from here I can see my first level sort but I want to go ahead and update this so that it has both the sorts I want and it's correct. I'm going to check off that my data has a header row. I'm going to come in and select the last name column for my first level sort in ascending order, A to Z. Then I want to select another sort column, this is add another sort column, and then I want to sort by the amount of tickets, and actually in this case it's the number of tickets sold, in A to Z order as well. So there will be a first level sort, but then when there's a tie in that first level, it will go to the second level..
Now to actually perform this sort, I'm going to click on the sort button. Now what we're going to see is there are last names that are sorted. That's my first level sort. But then if I come in and notice, after there's a tie in the first level sort, then in ascending order it's going to go and it will do a sort by the tickets column. So as I come down I can see that there are two sort orders going on here to help me further identify an order. Again, my information in my Google Sheets. As always, we want you to try this out, so open up the practice file and try doing a.
Multilevel sort in your own Google Sheets. Hi, I want to be able to filter data in my Google Sheets file. Remember, filtering is different than sorting. When you sort, you reorder the data, but all the data is still there. When you filter, think of going to a public swimming pool. We want to get rid of all the stuff we don't want in that water. Here inside Google Sheets, it's the same idea. We will be hiding the data that doesn't meet the criteria that we set. set. So how do we perform a filter?.
Well, first of all, make sure you have a file available to you to use. I'm in the practice file called trip sort and filter. Feel free to use this to try out filtering as well. And I'm going to come up to the toolbar this time, the far right hand side of the Google sheets toolbar, you'll see what looks like a funnel. This is how you can turn on filtering at each column level. When I click on this button, you're going to see that the area where I have my column headers, Highlights. This is another reason why the list format is so useful inside of Google Sheets. Now next to each of my columns, I see a small set of horizontal lines that.
Are supposed to look like a filter. They kind of do, but this is where you go to perform filtering and sorting at the column level. I love these. Let's try out by filtering, for example, by the destination column. Column E. I'm going to click on the small funnel and you'll notice that I get a couple of options. At the top I can sort each of my columns in either ascending or descending order. And the great value behind this is that the column header is not included. So you'll notice I just did a descending level sort..
Posts Related:
- Website Showcase Mockup Bundle Download In PSD Files Sheri Sk Photoshop Tutorial
- 1200 Pro Banners Templates Free Download In PSD Files Sheri Sk PSD Banners Office
- How To Set A Fixed Position To Elements In Your Website HTML & CSS BlogSpot
Now when it comes to filtering, I'm going to click back on the small funnel again and come down. - ID Card Make
You'll notice I can filter based on format. By conditions, by values. Now in my case I have text. I see a list of every possible text entry in my destinations column and they're all checked. If I come down, there's a select all and a clear option. If I say clear, it turns them all off. Then I could just check off the item I want to have filter. Or, If there are too many, I can start typing in the one I want in.The search box, it locates it for me. I want to make sure there's a checkmark next to it, and when I click on OK, It filters by that particular column. Now, what if I want to filter by more than one item? Can I do that? Absolutely. I'll click back on the funnel, come in, and I can just check off the additional things I want to filter by. I could also make sure there's a sort, and once I come in and click on OK, you'll see that it indeed is going to filter and allow me to have more than one item here..
Now, how do I turn my filter off? To turn my filter off, I'm just going to go up and click on the funnel, This will remove the filter and turn all my data back on. So it's unhiding it. So as always, we'd love to have you try this out in your own practice file. So go for it. Hey, everybody, let's create a filtered view and also a conditional filter inside Google sheets. Now remember, the list format is a great way to go for this. So the first thing I want to do is create a filter that is for a range of dates..
This is a great place to use a conditional filter. I'm clicking inside my Google Sheets file, inside the data. I'm going to go up to the toolbar to the far right hand side and turn on the create a filter button. Again, this gives me the ability to filter at the column level inside my list format. For this, I'm going to come over to the date column and click on the little, again, filtering button. Now, towards the middle of the drop down menu, you'll see that this gives me an option to filter by condition, which is what I want. Now, there are three kind of types of conditions that you can use..
You can filter based on text. on dates and on values. Now my criteria is a date that is after May 1st. So I'm going to select date is after. Below that I have to actually tell it what the date is. It's going to be an exact date and I'm going to come down and enter in a value or formula. Now just heads up, dates are actually values in programs like Excel and Google Sheets. So I'm going to type in my date And then I will come down and click on OK..
Now at first it may not seem like anything's happened, but if I scroll down through the data, you'll notice that it's only showing me dates that happen after May 1st. Now this is a filter that I will use a lot. What I can do is rather than just continue redoing the filter whenever I need it, is I'll save the data currently in this view. To do that, I'm going to go back up to the toolbar, to the filtering button, and click on the little arrow next to it. This gives me an option to save as a filtered view, which is what I want to do..
Then I'm going to come over to where I see the name of my new filtered view. And what I want to do is click on that and just rename it. I'm going to call it after May. first. You can put spaces in these filtered view names. All I need to do to finish renaming is hit enter. Now we'll see that my filtered view is turned on. I'm going to turn all my filters off and the filtered view will also be turned off. To do that, I'm going to go back up and just click on my funnel again. Again, you'll notice that when I do this, it gives me an option to say none under my filtered views..
Or if I just click away, I can come in and I'm going to just say Now we'll see that if I scroll back down, the filter is still in place, so I'm going to go ahead and click anywhere in the dataset and click on the funnel again, and it turns the filters off, and notice that all the data is here. Now to return to my filtered view, I'm going to go back up to the arrow next to my filter in the toolbar, and you'll see that any filtered views you've created will show up in a list. I'm going to select my filtered view. And it turns it back on. And I'll notice that any of the dates that don't fit that condition.
Of being after May 1st turn off. And then again, to return to the full list without the filtered view, I'm going to click on the filtering button. Say none, the filter turns off and the filtered view turns off. And at any time you don't want the filtered view anymore, you can click back on the arrow and you'll notice there's an option to delete all filtered views. But remember, what we're doing is we're combining a filter with a view so that whenever we need to filter by that item again, we don't have to go back through all the work. We just turn on the view and the view will be there for us. So try this out in your own Google Sheets..
Hi everybody. I wanna be able to create a chart inside one of my Google sheets. Remember, when you create a chart, you're visually representing values or data
Stored inside your Google Sheets file. It's a great way to help people understand data quickly and easily. Now, one acronym I like to use when I teach people about creating charts is the KISS acronym. Don't be offended by this, but keep it simple. Stupid is the KISS acronym, and it basically means keep your charts as simple as possible. If someone can't look at your chart and understand it within. just a few seconds. It's probably too complicated..So with that in mind, let's make our own chart inside of Google sheets. There is a practice file available for this. So remember if you go to the description, you can open it up. There's a link there. This one is called charts 101. Now the first step is to select the data you'd like to chart. I always like to remind people, we generally don't include titles and totals when we select our data for a chart. So in this spreadsheet, I am going to highlight cells A3 down to G7. Notice I'm not including A1 because it's a title, and I'm not including row 8 because it's totals..
Now to create my chart, I'm going to come up to the Insert drop down menu and come down to Chart. Now, it's automatically going to already assume the type of chart I want. Notice the task pane that's opened up over on the right. This is called the chart editor. Now, I can change my chart type immediately by coming right to the very first item under the chart setup tab. These are the different chart types. You have line charts, area charts, Par, and bubble, just keep scrolling.
Down and then specialty charts. Now generally when you're first starting out, the first five categories are going to be the best ones to start with. And you'll also see at the top, that based on your data, Google Sheets will make suggestions of charts that might work well with your data. If you want to change your chart type, come down and pick a different chart type and you'll notice that your chart will update. Now how do you move this chart around on your sheet? Because your chart resides on top of the cells. Right now my chart is partially covering up my data..
All you need to do to move your chart is come to the corner of it, left drag it. It's like a graphic. Now I always want to make sure that my chart doesn't overtake my data. I can also come to the corners of my chart. You'll notice there are small squares on the corners and the sides. You can use these to size your chart. It's a great idea to make sure that your chart doesn't overtake your data. Another thing you can do is you can also come in and right click. When you right click, you'll notice you get a menu that will also help you to adjust your chart..
So now that we've created our first chart, I want you to try it out in your own file. They're really easy to make, and again, they're a great way to help people understand the data that's inside your Google Sheets. Everyone, I'm ready to edit this chart. So the first thing I need to do is click on my chart so that it's selected. When you select your chart, it makes Google Sheets know that you're interested in your chart. Also, if you come in and just double click on the inside of the chart, you'll notice that the chart editor task pane opens up. Now again, the first side of this is going to allow you to set your chart.
Up by picking chart type, coming in and selecting, for example, whether you want the stack to be at 100% or standard, but another Important thing that you can also do here is take the data that's in the legend and switch it with the data that's on your category or x axis. So let's just learn a little bit about chart terminologies for a minute. I'm going to go ahead and take my chart and drag it over so it's a little bit more front and center and make it a little bit bigger for this. Your legend is of course the area where you understand what your different data bars in the chart or the data series are. Usually the x axis is going to be the horizontal axis on the bottom..
The y axes or value axes will run along either the left or right side. Now you'll notice that at the bottom of the setup, you can actually come in and switch between what's on your legend and your x axes. By checking this box off, you'll notice that they switch back and forth. Also, there is a area where I can come in, and I can also decide if I want, for example, different Column labels to be used in my data set. When you really want to customize, though, in the chart editor task pane, there is a customize area. This is where you can take the different areas of your chart and change the way they look. Chart style is the overall look of your chart..
You'll notice you can do things like make the chart look 3D. You can also set a different background area for your chart area. You can also come in, for example, and set a different color for the border of the chart, just so that it's easier to recognize. You can also come in and change the fonts that's being used in titles and legends. In addition to that, you're also going to have an area where you can update the chart axes and include a chart title. Now I currently have a chart title, but for example, maybe I'd like to put it in bold. And maybe I'd like to change the color to black to make it stick out more..
So with each of these areas, you go in and you customize the portion of the chart you'd like to edit. Another important one to be familiar with is the chart legend. This again represents what's in your data bars. Notice here I can actually change where the chart legend goes. Right now my legend is currently on the right. I'd like to move it to the bottom. When I do this, the great thing, I guess I moved it to the top. We'll move it to the bottom instead. The chart adjusts to make room for the legend. So with each of these areas, you're able to go in and further.
Customize the look of your chart. You also have an area where you can come in and customize the series. These will be the different items that are showing inside your chart. And notice. You can additionally click on a given area. It will become highlighted. I've just selected the wages data bars or data series. I can come in and actually update the color of those just by selecting them. When they're updated in the chart, they'll also be updated inside my chart legend. So we'd love to have you try this out. Go in and after you've created your chart, try going in and customizing. And there is a practice file for this called Charts 201..
So give it a try. Hey, everybody. We want to be able to remove items from our chart and also more easily move them around. Pretty, again, easy to do. You just have to know where to click. I'm in a practice file called Charts 301. Remember, if you go to the description, you can find a link and go to all the practice files there. I'm going to go ahead and make my chart the focus. So I'm going to click on it and kind of move it around and size it a little bit bigger so we can see it. Now, first of all, how do I remove something from the chart I don't want?.
There. For example, let's say I don't want to see the wages in my chart. Well, I can't just click on them and hit delete because if I try to click on them, which I can do, they'll get highlighted. But if I hit my delete key, I can't directly delete a data series out of my chart. But what I can do is remove it. So I've clicked on my chart so that the chart editor task pane opens up. And I'm going to go to the setup tab. When I come down, I'll see that each of the sets of data bars are represented by a series. To remove a specific series, I'm going to come to that series..
Just called wages in this case. Click on the ellipses at the side and say remove. And you'll see what it does is it just removes those data bars out. So now I see my chart without the wages. I'm going to undo that because now I want to just talk briefly about sizing elements on the chart and removing something you don't want. Let's say for example I've decided I do not want a legend. I can click on the legend. You'll see it gets selected, and then if I hit delete on my keyboard, it will remove it. If I ever want to bring it back, I'd go back over to the chart editor task pane. Find the legend section, which right now says none, and I just.
Move it back by clicking on the direction or placement of the legend. I can also come to the legend and just left drag it wherever I want it to go on the chart, and it will actually allow me to move different elements. As well, if I'd like to size elements, I can click on a specific part of the chart. Come to it. And I can also size it as well. So the charts are very adaptable. It's just a little bit tricky sometimes when you're trying to click on something and move it in conjunction to the rest of the chart. So as always, we'd love to have you try this out on your own. Open up the practice file. Try turning some things. On and off. Also remove some of the series out of the chart..
Remember the undo button will bring them back as long as you haven't yet gotten rid of all your undos. But try this out so you can see how flexible these charts are to editing. Hi everyone. I want to explore a different type of chart. It's an in cell chart that's great for showing trends on columns or rows of data. It's called a sparkline. They're small but they're great when you want to see something that's right in line with the data that it's tracking. Now Sparklines are not something you can get by going to the Insert drop.
Down menu, like we do for normal charts. They're actually created via a formula. So I've opened up a spreadsheet called Try Sparklines, it's in the practice files. I'd love to have you use this as you follow along with me. I'm clicking in the cell where I'd like my sparkline to go, and then I'm actually going to create a function that will create the sparkline. The first step is to start with your equal sign and then type in SparkLine. Now, of course, Google Sheets gets smart and gives you the spelled out SparkLine. I could also be typing this up in my formula bar if I wanted to. The next step is to include the range of cells that you'll be.
Trending with your SparkLine. You can either type the range in or just highlight it. And you just want to include the values in this case. When you're done, you can hit enter. You don't need the closing parenthesis. And you'll see your little in cell chart or SparkLine. And up in the formula bar, we can see the function that created it. Now, if you'd like to get a little bit fancier, you can include things like the type of chart. Sparklines can be column charts and also line charts. The default is a line chart, as you see here. So I'm now going to come in and click in cell F8, and I'd like to create a column sparkline..
For this one, I'm going to start just like I did before, by typing the equal sign, typing in the name of my function, selecting it, and then of course, highlighting my range, which is A8 through E8. After I do that, I want to continue. I'm going to hit a comma, because I need to put in some options. Inside of squiggle brackets, as I call them, I'm going to type in, quotes, chart type. This lets Google Sheets know that I want to... specify the type of chart the sparkline will be. Then I'm going to do a comma, and in quotes again, I'm going.
To type in the type of chart I want, which is a column chart. I need to enclose this with a closing squiggle, and then of course my closing bracket, and then I'll hit enter. And we'll see that for this one, I have both positives and negatives, but it is a column chart. If you ever want to get rid of a sparkline, just click on it, hit delete. And it will take it out. These are something that are pretty easy to add and also pretty easy to take out. And there's more that you can add to them, but start by just creating a few on your own. And again, it's a function, so a little bit different way to create a chart..
Try it out. simple way to help you read down through rows of data. And right now I have a practice file called CustomerInfo open, feel free to use this to try this out. It's called Alternating Row Color. Another thing to notice about my data is it does include header rows at the top of each column. So I know what the data is below and no blank rows, but blank rows will still work with this activity. All we're going to do is click in any cell inside the data and then go up to the format drop down menu and come down to alternating colors..
This will select my data. I do have a header row. Notice how that's checked off. If I had a bottom row that was a footer row, I could also check it off. Then I'm going to select from one of the prebuilt styles. I could also come in and create my own if I wanted to by clicking here, but I can also customize the colors just by going into each one. When I'm finished, I'll click on done. And you'll see that it's highlighted my header row in a darker shade, and then I have alternating row color based on what I've selected. It's just an easy, simple way to format your data and also make it visually.
Easier to read down row by row. So try it out. Howdy, everybody. We're ready to look at one of my very favorite topics in Google Sheets, creating pivot tables. Now, why pivot tables? If I were to try to take this data set and say that my boss came to me and said, I need to know how many tickets were sold at each of the destinations and then at each of the offices. How would you go about doing that? Well, you could sort. You could filter, you could add some sum functions, but it would.
Really be a pain in the neck. The great news about a pivot table is it allows you to take a really thick, long data set like this one, and quickly sort and filter it, and also analyze it and calculate it with just a few clicks. Now if you set up things, again, pivot tables work best with that list format we mentioned at the very beginning of the course. So I've got column headers at the top, and no entire blank rows. This data is only about 200 rows down, so it's not very big. But again, pivot tables can be used to analyze really large data sets..
With that in mind, let's make a pivot table. I'm going to click anywhere inside the data, and then I'm going to go up to the insert drop down menu, and come down to pivot table. It's right below chart. Now the first step is to pick the range that I'll be pivoting. It's already selected my range because I don't have any entire blank rows. But this button on the right would allow me to drag over the correct range if I needed to. Also, I always suggest the best practice of placing new pivot tables on a new sheet. That way you don't accidentally overwrite any of your existing data..
We're ready to go. Let's click on the create button. Once we click on create, you'll see that you get a new blank sheet. Again, my original data is still back on sheet one, and my new pivot table is called pivot table one or two, depending on how many you've built. Now we need to take from our column headers and create our pivot table. A pivot table is not about all your data, it's about focusing on specific pieces of data. So I'm going to start by taking the office field and dropping it in the rows area. I'm going to bring it under here and click on app..
Another way to do this is to actually click on the part of your pivot table by clicking on the add button and then selecting the field you'd like included. Again, with values, I suggest using value fields. So I'm going to pick my totals field and you'll see it's now doing a sum. Based on office. Now, I'd also like to see where this is intersected by the destination that was sold. So I'm going to take destination and drop it under columns. Now you'll see a lot of blanks when I do this. This is important. Sometimes when you first start using pivot tables, people see these blanks and think, Ooh, something's wrong, but it isn't. This is telling me an important piece of information when something didn't happen..
Now, additionally, I can also filter my entire pivot table by taking a filter down or a field down to this filters area. We'll see that the filter will be available to me right here. When I click on it, I can actually perform a filter and it will filter my entire pivot table in place. When I'm done, I can turn it off just by coming back. Clearing it, saying select all, and it turns it off. Now to edit my pivot table is also really easy. Let's say instead of the total field, I'd like to see the number of tickets sold. I can actually remove the total field by clicking on this.
X, and I can bring down a... different field under my values area. Now I've seen the number of tickets that were sold at each office for each destination. I could also actually remove the destination and we'll see that my pivot table will totally adjust. Now have any of these changes impacted the data on sheet 1? Let's check it out. I'm going back to my original sheet. Nothing has happened. The beauty of a pivot table is that you can analyze specific columns of data in your pivot table but your source data never adjusts. One important thing to remember about pivot tables is that your.
