10 Google Sheets Tips You DON'T Want to Miss (2021)

today i'll share with you my top tips and tricks for google sheets it's a combination of functions and features that i find really useful when working with data in a spreadsheet now as you're watching this let me know in the comments if there is any that you specifically like and also share your own tips let's get started number one scrolling tables i want to create a report for employee information i have name start date department and salary of the different employees and i've also added a chart here but here's the problem this part doesn't look so nice instead of having the data in cells i'd love to add a scrolling table here that's the same height as my chart here's what i can do press ctrl a to select this range go to insert chart now it's going to take a guess at the type of chart that's right for this and it went with column chart that's not what i want so click on this drop down scroll all the way down and you're going to see a table chart and immediately i get a scrolling table i can adjust the size of this and place it where i need don't worry i'm going to remove the data behind this but i want to show you something take a look at the name here when i click on this it sorts it automatically but the sort order of the source data doesn't change this stays the same i can quickly sort salary in descending order or in ascending order without changing the order of the original data set now for a report we don't want to show the data here so i'm going to cut it and put it on another sheet and we have our nice report number two auto aggregate chart values i'd like to create a column chart for department and salary but here's the problem department is repeated multiple times because my information is more detailed well that's no problem for google sheets let's just highlight the columns we need go to insert chart it already inserted a column chart but it plotted each single row i want to aggregate these by department so let's go to the side here and for department place a check mark beside aggregate and we have the totals now i'm just going to select a series scroll down here and add data labels to this and then it's just a matter of adjusting the size and placing it where we need in our report one thing that's good to mention is that you have control over the plot area of the chart so if you see that the title is kind of getting squashed in there you can adjust the plot area now you can also get rid of anything you don't need just press delete and delete on this and let's use the space on the bottom as well number three publish to the web with google sheets you can easily create a web page out of your sheet you can also embed your sheet in a website just go to file publish to the web you get to decide if you want to publish the entire document as a web page or a specific sheet now the sheet that i'm currently on is called publish so i'm going to select that and click on publish it asks me if i'm sure i want to publish yes i'm sure and i get the link i'm just going to copy this if you wanted to embed your sheet on your website go to embed and copy this code since your sheet is published to the web it's accessible for anyone no matter if they're logged into their google account or not as long as they have a browser and your link they can access it so let's check this out i'm going to bring up an incognito browser paste the link press enter and i get to see this page now this is live so if i change the data it's going to pull through here so let's test that as well i'll just change the first entry to a two let's bring up our browser again reload this page and i see the updated value in the browser number four column stats with column stats you get to quickly get an overview of the data you have in your columns just go to data and select column stats here i can quickly see the different departments and the count of departments when i scroll down i get to see the frequency of each value and how many unique values i have now when i go to this column where i have numbers i get more information now the count here doesn't make much sense but i can switch my view to distribution to see the salary distribution i get to also see the sum average median minimum and maximum values of my data set a really quick way to get an overview of your data number five open-ended cell references now i really like this feature because it allows you to easily account for future data let's say i want to sum the salary column let's add a quick sum here select the range ctrl shift up let's hold down shift go one cell down close bracket press enter and that's my total but the problem is it's restricted to exactly this range if i add new numbers to the bottom of this it's not going to be included to make this super flexible you can remove the row reference at the end here and this is going to go all the way down to the bottom of your sheet so anytime you add numbers here it's automatically going to be included so you can fix the starting point because in this case i want to start from d4 and not d1 but i can leave the end open number six insert date from a calendar when you have a date in a cell you can double-click inside the cell to bring up the calendar you can adjust the date if you need to now if you'd like this feature to be available in empty cells so that you can double click in the cell and get the calendar you can add data validation to these highlight the cells go to data data validation for criteria change this to date you can reject input if it's an invalid date and click on save now when you double click in a cell you can pick your date to be consistent let's apply this formatting to our dates here number seven add checkboxes to keep track to add checkboxes to your cells highlight the range you want to have the checkboxes in go to insert select checkbox adjust the color of your checkbox by changing the font color i'm going to go with orange you can activate the checkbox with your mouse or you can use the space key on your keyboard take a look at what's in your formula bar it says false when it's unchecked and true when it's checked you can use this information in formulas to control other actions in your report you can also use this information in conditional formatting so for example if the checkbox is checked you can cross out this text let's quickly add conditional formatting to this highlight the range we want to have the formatting in go to format conditional formatting that's our range our format rule is based on a formula so i'm going to go with custom formula is type in equals a4 that's where i have my check box but i have to be mindful here of how i fix the cell references i need to make sure that i have a dollar signed for a because i don't want this reference to shift i want it to be fixed to a now here i don't necessarily need to type in equals true because that's the default logical check here all i have to do is decide what i want to happen to this i could strike these through or just adjust the background color of the cells click on done number eight pivot tables for quick analysis to quickly analyze your data insert a pivot table go to data pivot table it tries to identify the data range in this case it's correct you can add your table into a new sheet or the existing sheet i'm gonna go with the existing sheet and place it right here okay and create i get the layout of my pivot table here and immediately i get some suggestions these help me get started faster average of salary for each department sounds good so i'm going to select that and i get department and the average salary i can see it added department to rows and salary to the values it has summarized by average if i want to show the maximum salary in each department i can select max to get the sum select sum now you can also add columns to this by selecting from the list of available columns in this case it doesn't make much sense to add name here to remove it just click on the x you can add multiple rows as well so we can go ahead and add name here this way we get to expand and collapse our departments and have all this information in one view to make sure we account for future data we can expand our reference here so i'm just going to delete the 20 press enter and it automatically expands it to the last available row on my sheet this means if i add new information here it automatically gets added to my pivot table number nine web functions google sheets has a set of very useful web functions if you go to insert function web you can see the list here with import xml you can easily import any data from web pages import range imports data from other spreadsheets and import html imports data from tables on the web to your sheet we're going to take a closer look at import html so let's say i want to get the list of national holidays in austria so i mean officeholidays.com i have austria 2021 selected and i want to get this table in my sheet i just need to copy this url go back to my sheet type in equals import html now paste in the url but you need to put it in quotation marks close the quotation mark next we need to specify that we want a table again we have to put this in quotation marks and last is the index number of the table because a web page can have different tables now here you just have to take a guess so i'm going to put one close bracket press enter and see if i get my table and i do this is a quick way of getting tables from web pages properly imported to your sheet number 10 functions to organize and filter your data google sheets has hundreds of functions to analyze look up and filter your data you can see these if you go to insert function some of my favorites are in the filter section you can dynamically filter sort and get a unique list of values so for example let's say i want to get a unique list for department i just have to type in unique select my range close bracket press enter and i have a dynamic list the moment i get a new department here my list updates because i'm using a function i'm just going to press ctrl z to go back you can also source your lists dynamically so if i wrap the unique function inside the sort function i get this list sorted in ascending order to have it sorted in descending order i need to add false for the third argument so the second argument is the sort column in this case i just have one column so i'm going to type a one and false for ascending so i'm going to get this in descending order now before we wrap this up let's take a look at the filter function let's say we want to get the list of people who work in the finance department and we want this to be dynamic so if we switch finance to another department our list automatically updates i first have to select my range then my condition my condition is that department which is in this column equals this one when i close bracket and press enter i get my list and remember you can leave these open-ended as well to account for future data if this changes from finance to sales i get the list of people in the sales department i hope you found these tips useful please give this video a thumbs up if you did also leave your feedback in the comments thank you for watching and i'll see you in the next video you

Channel: Leila Gharani

Views: 96,597

Rating: 4.974782 out of 5

Keywords: XelplusVis, Leila Gharani, XelPlus, google sheets, google sheets tips and tricks, google sheets tutorial for beginners, google sheets tutorial, google sheets formulas, google sheets pivot table, sheets tutorial

Id: O0k9uE4xnD0

Length: 13min 50sec (830 seconds)

Published: Sat Feb 27 2021