Google Sheets for beginners - the key features you need, Part 1 | Vyara Georgieva | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Google Sheets for beginners - the key features you need, Part 1

teacher avatar Vyara Georgieva, Technical Trainer & ESL Teacher

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

    • 1.

      Welcome aboard!


    • 2.

      How to create a blank Google Sheet Document?


    • 3.

      How to resize several columns/rows at the same time?


    • 4.

      How to copy the format of a cell?


    • 5.

      How to add checkboxes to your sheet?


    • 6.

      How to add dropdown lists


    • 7.

      How to set the format of your date?


    • 8.

      How to insert a date picker?


    • 9.

      How to use Relative and Absolute reference?


    • 10.

      How to include all of the values in a column? (Sum, Count, Counta, Unique)


    • 11.

      How to refer to a range of values from a different sheet?


  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Why do you need Google Sheets? Because the business world is changing and Google Sheets is the best spreadsheet application out there to meet your needs! Welcome to the course that will help you have a smooth start into the world of data organization and data presentation!

In the upcoming video sessions on Google sheets, we’ll build the basics you need. We’ll go through the key features that will help you gain confidence and increase your productivity immensely. You do need sheets more than you suppose. Because I’m sure you’re working with others, you’re collaborating with your teammates every day. I’ll show you how to make Sheets help you. Because I’m sure you need your data and visualizations up-to-date - now, in this meeting. Not a recent version saved on someone’s computer - but the latest one, the one that was updated a minute ago. This is what Sheets can do for you.

In the course you’re about to start, we’ll first head for the the big picture:

  • how to create a blank sheet document,

  • how to resize your rows and columns,

  • how to format a cell,

  • how to add checkboxes,

  • how to format your date,

  • how to use the very, very basic functions in sheets.

It’s not by chance that each and every lecture for the course answers the question HOW - how to do this or that, how to find an answer to your problem. That’s what you’ll actually need every day - that’s what you’ll get here structured and practised with a lot of exercises.

Meet Your Teacher

Teacher Profile Image

Vyara Georgieva

Technical Trainer & ESL Teacher


I have been teaching for seven years now, always inspired by the enthusiasm of people, acquiring new knowledge. Keep It Super Simple - the basic principle to follow, every complex theory or concept turns out clear and easily applicable. Currently, I am a technical writer and an Android developer, aimed at creating educational software products.

Before going into technology, however, I have been teaching English as a foreign language for more than 7 years. Teaching is what my passion remained - as people need to be explained, to be shown, to be helped to start speaking. I have been preparing students for the Cambridge FCE and CAE exams for more than 4 years. At the same time, my adult student have been on various levels - starting from the alphabet (A0) to being advanced (C1 leve... See full profile

Level: Beginner

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Welcome aboard!: Welcome to the very, very first part of the chorus. Here, I'll help you see the big picture. What is Google Sheets and why do you need it? What can it do for you? We'll start from the very, very basics. I'll guide you on creating your first Sheets, documents from their own. We'll talk about different data types, common patterns that appear when you enter your data. Data formatting and data visualization. By the end of the section, you have added the first validation on your dataset. You'll have created a time sheet with a theme applied and for metadata. Your calf also worked with a data picker. And you have understood why the today function is not the best option in the sheets application. What is a function? In fact, how is it different from a formula? This section, we'll give it the answer before we start. Just a reminder where your files are. Your CAN downward OData files that are relevant to this section. Immediately after this video. Here you will find links that opened the raw data file as well as the solution. Also, if you prefer, you can download the respective data file immediately after the video. You'll find the sheets deer as well. All set time to start. 2. How to create a blank Google Sheet Document?: Let's start with a really, really basic question. How to create a blank Google Sheets document. The very first requirement, you need a browser. So open the browser. You are used to go to And here on the right, you see all the Google apps available and here is the sheets application. Take on it. You'll be asked to enter the credentials for your Google Drive account. If you already have one, just enter your name and password. If this is your first time here, go to Create account and follow the steps here. Once you do it. This is the page. You'll see. If I want to create a new sheet, I'll click here on the right. The circle Create button is the one that says Create new spreadsheet. The smaller page like buttons says Choose Template. Templates are presets that are ready for you to use in sheets. What I need now is to create a blank spreadsheet. So I'm clicking the Create button. And here it is. If this is your first time here, that's how the page would look like. Here is the blank spreadsheet option. Here, rather spreadsheet you can create from different templates. Okay? So here's another way to create a blank spreadsheet. As you're assigned in your Google account, go to the applications menu and choose drive. This view here gives you access to everything that's going on on your drive. Here on the left is the new button, a right-click it. And in the many you see the different options you have. Here is what we need. Google Sheets. Click it. If you wanted to create a blank spreadsheet. If you want to choose from the templates available, go to the arrow here on the side, and click then from a template, you have the same options. If you right mouse click anywhere in your Google Drive directory. I want to create a folder and a Sheets document inside. For example, here's my folder. I'm right mouse clicking inside. And here is the Google Sheets Create button. Last option is the fastest one. As you are in the browser and assigned to in your Google account. Typing the browser sheets, dots new, you'll be automatically redirected to the blank spreadsheet file. If wondering where the file is stored. Well, take a look here. The Move button, which appears to the star icon on top. When you click Move, you see in which directory exactly your file as it is in My Drive general directory. From here, if you want, you can move it to our respective folder and organize it as needed. Okay. Well, let's sum it up. How to create a blank Google Sheets document, option 1 from any browser when you navigate to the Google Apps button and choose the sheet tab option to from any browser when you navigate to the Drive application, and then go to new Google Sheets. And option 3 from any browser. When you go to sheet dots menu. Let's move on. 3. How to resize several columns/rows at the same time?: Okay, let's see now how you can resign several columns or rows at the same time in your Google Sheets file. Meanwhile, we'll take some other useful features that may save you a lot of time. My strong advice is to follow along with me. That would be really the fastest way to remember what you need to do and in which situation. So where we are starting from, I have a Google Sheet File Open. I've randomly added some data inside. Let's take a closer look at what's going on here. First. You see that some of the cells hold texts, while some other cells holds numbers. Apart from death. Do you see the difference between this cell and this one? Exactly the alignment. By default, in Google Sheets, all of the text values are left aligned. All of the numeric values, vice versa, are right aligned. Of course, that it's really easy to change this to center to left align or rough or right align your whole data. Just be careful. This is a great visual hint of what data you have in front of you. Why is this so important? Well, think about it. Can you add the numeric values of two texts? No, Of course. Or at least the result you have at the end will be quite different. So do be sure your data values are read as they need to. Keep the default formatting. Advice. What does he have here? The values in cell Britain and second are longer than the current size of the cell. When you enter a longer text in a single cell, this is the default behavior of Google Sheets. The text overflows. It goes over the adjacent cells. If they are empty of course. Or it could be cut. If India adjacent cell, there is another value I entered. How to check the settings applied here? Click the cell you need to format and then go to format text wrapping. The default setting, as I mentioned, as overflow. You can go to Rob if you want the cell to be automatically resized when a new length of Texas and dirt or to clip if you choose not the whole contents of the cell to be shown. If you want the same formats to be applied to the host protein, the fastest way to do it is to choose the blank rectangle right under the name box. Ready. Then go to format, text wrapping, and choose the settings you want. All right, let's go to resizing. Now. I know what I want as o of their roles and cones in my document to be of the same size. So how can they resize several roles for us at once? Step one, mark all of the roles you want to resize. Hover over the selected roles. And when you see that this arrow appears, you're ready to go. Keep in mind that on Windows you see the double arrow mouse cursor. Now you can manually choose the height of your role. You can either narrow it or widen it. Another way to do it, you can automatically resize the columns you want to. Let's imagine I wanted the height, my roles fit, the height of the data entered. No problem. Sheets can do it. Select your roles in case again, hover over until you see the same mouse pointer. Now just double-click. And here it is. The rows are with the height of the data insight. Here comes the third way in which you can resize the height of your roles. As you have the rows selected, a right mouse click from the context menu, choose resize. And here it is. You can either enter the row height in pixels or choose to make it fit to the data. If you want to resize the cones in your sheet, the steps are exactly the same. First, you select the colons in question. Then you may hover over your selection and manually choose the width you want. Or you can also double-click in order to make the width of the cells fit to the data. Or you can right-click so that the context menu appears. Here. You also have this mode drop-down arrow or adds the header of the colon which appears on hover. Again. It gives you quick access to the context menu and the various options you have here. All right, let's sum it up. Now you know how to resize the height of your roles and the width of your colons. You know that a double-click is enough to make the cell fits the data. You do also know how to manually resize your colon centroids, as well as how to do it when you want the data to be pixel perfect, it work so far. Let's go on with the course. Very, very basics. 4. How to copy the format of a cell?: So here's the scenario we are having. You've just received an extract of a Google Sheets document, which looks like this. It's quite Mason. There are a lot of different formats of light here and there. Neither the colors are consistent, nor the fonts, nor the height of the cone centroids. We have a few minutes before the next meeting where you need to present this sheet exactly. Which means you definitely don't have the time to check the status of each well-formatted cell and manually apply them after that. Fortunately, there is a formatting trick you can take advantage of here, the paint format. This is the tool you need, the paint format brush, how it works. It copies the format of a cell you choose, and then applies it to a cell or to arrange of cells. But your choice quite straightforward again. So let's do it. I want the style of the first two cells in the header to be applied to the wrist. I choose one of the cells. Let's take citrate, then click on paint format. You see this dotted arrow which appeared on citrate. This is your visual indicator for the formatting of the cell that was taken. What I wanted to do now is applying this Capet formatting two cells D3 to eight stray and selecting them. And we are ready with the header. It looks quite Morgan systems now, let's go on working to add to the depot. I think the very first row of the data is the one that quite properly formatted. This is how I'd want the whole dataset to look like. Just be careful this time the formatting I will apply not row by row but colon by Cohen. Start from column B. I'm taking the format of cell B4. Paint Format, select B5 to B8 than columns C and D are with text data as well. I needed it formatted the same way. So let's be the WHO C4 paint Format. Select the range C5 to D8. As you've mentioned, arranges. Let's take a look at what they mean. Arrange a is a group of adjacent cells in your spreadsheet. Even single cell can be arranged visually. Here is how our range looks like. To refer a range of adjacent cells. Use the address of the cells which are at the top left and bottom right corner. So we have B5 to C7, F6, G9 to G ten to 13 to see 21, and a 14 to 18. The separator between the starting and the end cell reference is a cone. How we can name a range, how to use it, how to refer to arrange from a different sheet. These are questions we'll cover later in the course. Let's come back to the paint formatting. Now. We've reached colon, which holds numeric values. By default, as you've already known. Numeric value should be arrived aligned, whereas a text value should be left aligned. Which means there is something wrong with the formatting of the value in cell E6 defined and the font size are also different. To make them consistent, I go to E4, then paint Format, select the range from E5 to E8. Alright? And the last range of cells, Let's take a look at them. All of these values that are current sets or should be at least, although some of them are formatted in a different way. Now, there are missing currencies. They are ourselves for multi-task percentage. So the first step, I'll just take the format of four and apply it to the range from F5 to age eight. All right, That's quite whether if you want to either increase or decrease the decimal places of your values, Here's the easiest way for you to do it. Let's see, in Cohen G, I designed that you won't need the decimal places sincerity that. So I will just mark the range of values without the header, of course. And I'll choose the button from the manure, decrease decimal places. This one here. Click it twice. The decimal places are not visible anymore. If you want to have them back, just go to increase the decimal places. So the very last step here, I went to all of my corns and off my roles to be the same width and height. I'll start with the colon's Marco, ones from B to H. Right mouse click and choose a resize Collins. I'd like to make 120 pixels each, let's say. All right, Just now I see that the header of colon F is not visible. Manufacturing price is what needs to be written. Well, let's do it in two lines. Option plus Enter, insert the brake line you need on Mac, out plus Enter, does it on Windows. Okay. Let's now align the height of the cells. Mark rows 328, right mouse click or resize rows. Let's make each row 40 pixels high. And then that's how the table we extract looked like when we receive that. Let's go at looks like now, we are ready to present it in the upcoming meeting about formatting tips and tricks will speak later on again. What we've covered in this video was painted formatting. You know now how to copy the format of a cell and apply it to a different cell to a range of cells. You also know what arranged as well. Let's move on. 5. How to add checkboxes to your sheet?: Here comes the next question, how to add a checkbox to your sheet. This time, we'll build mini-project are very basic school attendance log that we will improve later on in the course. So here's how the local would look like. We have a table with a title students. The colon headers come in the first row. In the first column are the names of the students. Then come the weekdays from Monday to Friday, and checkboxes where you can mark whether the student was present or not. Once we start talking about formulas and functions. And here a colon to calculate the total number of absences, the percentage, and so on. But let's start with the basic table. In the course files that you find this sheet as a starting point. We are given the data here. What we need to do is formatted and add the checkboxes. Let's start with the checkboxes. They need to go all the way from C6 to G9, mark the range, then go to Insert tick box. And here they are. In each tick box. By default, you can add a tick like this by right-clicking with the mouse. Another way to do it is to move around with the up, down, left and right arrows, and then click the space. All right, I want now to give a consistent look to my spreadsheet. I don't want to use just a random colors here and there. Because of that, I rely on the Themes Gallery to open it. The gallery, I mean, go to Format team. The teams panel appears on the right. What's a team? Just the percent of colors. You can use. A log your sheets. If you want. You can customize the colors used here in order to match your company branding guidebook, for example, we won't go into customization now, if you want, take a look at it after the video. What I chose is the retro theme. Now, when you go to the color palettes, the one for changing the color of the font, and the one for changing the background. You see the team cowers or added. If you want, you can change them as well. Great. Next step. Let's format our table as such in sheets. Unfortunately, there isn't the option to insert a table directly from the menu assets in Excel. What we are doing here is this mark the range where your table is, including the table headers. Then click create a Filter button. Here it is. These three horizontal lines, one under another. Show you the level at which the filter was added. The column headers, that's exactly what we need. You can also see this border that was added around the table. Check how the table we need to do looks like narrow trick hours here. One for the header and then two for the rows. The colors in the roles are alternating. This is the key word here, alternating to add alternating colors to your table, mark the whole table, and then choose Format alternating colors. The alternating colors panel appears on the right. From here, we can choose whether the header to be in a different color as far as the footer, the very last row of the table is called a folder. In our case, they are representative of who they are. So our uncheck, the checkboxes. You also choose the colors that should go with each element. We've already added a theme to the sheets. So let's be consistent and go with the team covers. The header will be direct blow. Each row will be white, each even row will be orange. Okay, Then what else we need to do? The check boxes should be framed and filled in green. All right. Mark or check boxes, text color, dark green. The team cover again, the covers, texts in the header needs to be changed to white. Then the height of the cells should be widened. Mark rows five to nine and resize them. Another thing that I notice, the whole contents of the table is vertically aligned in the middle. Let's do it. Marketing range, go to the Vertical Align button and choose middle, and the title. Selected, and increase the font. I'll go to 18 points. Our resize the row as well. Next step, we are adding this green bottom border here. Select the cells from V3 to jittery, go to the borders bottom in the sheets menu. Choose the color first. This is dark green from the team. Cowers go for the Bottom Border option and adjust the thickness of the border. That's the result we got quite similar to the one we started with. One last step here. Let's remove the grid lines. As your report is getting quite precarious way, go to view gridlines and uncheck the tick. That great work. Now, you know how to add checkboxes to your sheets. How to add a theme and customize the team covers if necessary, how to insert a table and add alternating colors to it. Here's the task for you. Take your time and make this to-do list. What's new here is the drop-down list you have to insert in these two columns. I'm giving you a hint, use the hip-hop should go and search for dropdown. Here's the article, you will come up what? Go through it. And I'm sure you'll be able to do it. Why this way? Because finding answers to your questions as what will help you improve your skills immensely. All right, so in the course files, you will find that the sample data waiting for you, try it on your own. In the next video, I'll show you how this was done step-by-step. 6. How to add dropdown lists: All right, here we are. Welcome to the first challenge in the course. Here we will practice what we've already covered. We work with a drop-down lists for the first time. The basics, as always, are important. So let's get started. What we need to do here is a table. Let's start from here. So I'll mark the range B5 to 11 and create a filter. Here's a table. Let's fill in the colon data. Colon e needs checkboxes inserted, mark the cells. Then from E6 to 11, go to Insert tick box. And here, there you can add a value to each of these checkboxes. Visually, that means to have the tick here or not. Next step, depart. You needed to work on your own. The drop-down menu. If you've pass through the help article I showed you in the previous video, you've seen there are two ways to add values to a drop-down list. Take a look at this. You can either enumerate the values you want to appear, makes the list static, as you've just heard, call that something without the chance for these values to be changed. Of course, if you don't do it manually, or you can choose the values to be taken from a specific range. This way, if you change a value within the range, the drop-down list, we will update automatically. That gives you more flexibility and saves you a lot of time. Let's take both options to insert a drop-down list. Go to the cell you want these values to appear and then choose Data, Data Validation and a dialog box. So with a lot of new options will appear. What is data validation, how to use it? In which cases? We'll speak about this later on in the course. Let's focus on drop-down lists only for now. The default validation career theory here is a list from arrange. Click the drop-down arrow, and you see all the other options. What do you need to know are the first two, list from orange and list of items. I'll go for a list of items first, the static drop-down, which we spoke about. In this field, you can enter your values separated by a comma. Developers are low, normal, and high. I'll leave the check box here activated. As I want this triangle which indicates the drop-down list to appear in the cell about warnings and validation. Help will speak later on. So I'm leaving the default values as well, safe. And here's the drop-down with the values we've enumerated to apply the same validation rule to the other cells from the range, I will just drag this one down as soon as this big square appears in the dialogue right corner. The next drop-down in Cohen d, I'll add as a range of values. I also want the drop-down list to be inserted in all of the cells at once. Which means I select the range from this x to d 11, then go to Data, Data Validation. And the validation criteria this time will be list from a range. Here, you can either directly enter your data range manually, or you can click this button on the right and choose the range. I prefer this second method always as the risk of making a mistake is quite lower. So our selected the range is automatical a filled-in, OK. Save to save the validation. And we are then, now, as you see, the drop-down menu was populated with the whole range. So far so good. Then for the alternating colors, as again, we have three different colors here. One for the header to further rows. That option will help us out. Before that, don't forget to check the team of light. I'll go for the retro team format theme and choose your team. Then add the alternating colors from format, alternating covers. Check the end result, the header we need to make their credit. Make sure you're selecting from your team scours. The first color will be white, the second one, pink. Okay, Let's change the color of the folds in the headers now to white. There arose height should be recently stiletto, but let's say like this, the values as I see and the whole TiVo are vertically aligned in the middle. To do that, I'll mark the whole range of the body of the table. Then to go to Vertical Align, Middle, we're almost there. The drop-downs would be in green. Mark them, go to text color and select the dark green. The title. Change the font here to 38 points, let's say at a tick, right bottom border below the title cells. And the last step, hide the grid lines, view gridlines. And we are ready. If you've previously completed the task on your own and it is somewhere close to this one. Great work. The new point from the day, how to add drop-down lists from a selected range and from manually added values. Let's move on. What we'll do next is to add date. What do you need to know about these when working with dates? How to add a date picker that's coming soon. 7. How to set the format of your date?: A deed can always give you a headache is the second of March or February. How to change the format to if you want to look like this, just the date and the month or like this. The date, the month, words, then the ER, the options to format your data in a spreadsheet are really a lot in fact, and it's worth checking them out. We'll be using the same to-do list we created in the previous lecture. Just this time, I've added two more cones. They started and they'd finished. Let's fill in the date started colon for the finished date, I'll show some extra tips in the next video lecture. All right, So I started the first task, let's say on the 23rd of September 2021. The second one, I started on the 24th of September 2021 again, and the third one, 26th of September. But as you see, the three dates are formatted in a completely different way. Sheets, however, recognizes the three formats as valid dates format. When you double-click whichever of the three cells, you'll see the calendar that appears. However, it's not quite appropriate to have dates in a different format in one colon, in one document even. Let's take the formatting options in order to keep the consistency of our file. Go to the more formats, button, More formats, more deed and standard formats. Here are the list of possible date formats has increased our lot. We can preview the different formats here and go for U1 that really matches your needs. I prefer to keep it simple. The D and the month are quite enough in our case. Typically customizations you have for each of the deed parts. Just click on the respective part of the date. From here I can, for example, remove the leading zeros for the date as well as for the month. But it's a this will be the final format of our deed fellows apply and the format is changed. And the fastest way to apply the same formats to the whole colon is by the paint format to select the cell with the desired format. Paint Format. Select and arrange where you want to follow the formats to be applied to them. The dates format he had been successfully updated. Now, if I enter a date in a different format like the four top March 2021 and I press enter sheets automatically will recognize and to apply the format I selected in advance. All right, the first step has been done. Now you know how to predefine the format of your date values. What if I want to see the Date Picker on a click instead of typing each date. Or another case, I want the moment I change the status of my project to complete, the date finished so-called to get filled in. The task was completed today. Let's take a look at these advanced techniques in the next video. 8. How to insert a date picker?: All right, let's continue with our to-do list and the date formatting. The first thing I want to do is change the way that the beads are entered in the date started colon. What I want as Honor click the calendar to appear and the date to visit elected from there. So far the calendar is available only after the deed has already been entered. The way to do it is to add a data validation. So at the cells where you want your data validation rule to be added, C7, for example. Then open the Data Validation window from data, data validation. Set the criteria to a D8. What we need is to be just a valid deed safe. Now, when I double-click the cell, the calendar appears to apply the data validation to the whole colon. I will just select the cell with the validation and we'll drag it down from the lighter blue square in the downright coroner. Alternatively, you could have selected the whole range that will need your validation in advance and then proceed to applying the validation rule. Okay, then the next step for more interesting, what I want now is the date finished cell to get automatically completed once I mark the status of the To Do task as complete. And here will be the first time we'll start working with an IF function in sheets and functions in general, a bit of theory before moving on to the task. When you hear a function, think of it as a predefined set of actions. The program you are working with is using. In order to provide you with the answers you need. Each function has a specific syntax that you don't need to remember. In fact, you can always check them out. First, always comes the equals sign. Then the name of the function telling the program what the function's code. Or for example, some, if you are summing up something, count, if you need to count something today, if you want to understand what's the date is now and so on. After the name come the brackets, always an opening and the closing bracket. What happens between the brackets? Well here the functions differ. The sum function, for example, needs arguments. Arguments that are called the special values of function needs in order to return a result. Other functions that need arguments are count. And if we will practice these with examples, don't try to learn anything by heart. Now, on the other hand, are functions which don't need any arguments. An example for a function like this is today, you'll see it in action in a while as well. Another important point, some functions are return only one value, sum, count, and if are the examples here again. Whereas some other functions return multiple values, such as sort for example. But we won't focus on this group right now. Let's come back to our Sheets, documents and check the logic we need first. We wanted to have the date in column F to be set to current date. If the value in column E equals complete, otherwise, the value should remain unchanged. In other words, if something happens, something else should happen. Otherwise, something else will happen. Well, what we need here is definitely the IF function. Let's check the syntax. A function that always starts as I told you with the equal sign. Then if the name of the function enter. And here's the detailed help or intelligence where you can read what arguments this function expects, what they mean. Plus you have an example as well. So if a logical expression forward by value if true, followed by value if false. This is exactly how we defined our problem. If, and here's your logical expression. The value in E6 equals the equal sign you need complete in inverted commas as this is a text value and text shadows are always summoned by inverted commas. Then add a comma and the value should equal the date, The, the function we've mentioned to d, followed by brackets. Otherwise. Now we are in the value if false argument otherwise don't change anything in the cell. Or this is marked as an empty string, as an empty text fellow, just add two inverted commas and don't forget the closing bracket. That's it. Press Enter. Let's test that E6 equals complete. Here's the formula that was autocomplete that from the previous lecture. You know how to change the format of the date. So we won't be doing it again. I'm leaving it to you. Everything seems to be fine. Well, not exactly. Something very important you need to remember cells. So with today formula will update each and every day, which means every other day you enter your sheet. Date here will be different, which has to guess automatically means these dynamic date here are completely useless. Even more, they can be dangerous in a real life scenario. This constant, a re-calculation, will definitely make your file heavier and forward. In short, you know how to use the if clause. Perfect. I showed you how this could be achieved. Let's now go to the practical solution. What you need to fill in the current date and be sure that it will not change. It's a shortcut. Control was semicolon on Windows, command, semicolon on Mac. That's it. Great work today. Now you know how to add a date picker to a field. You also know what a function is and how to use the IF function. Let's move on. 9. How to use Relative and Absolute reference?: How to refer to a cell so that I can use it in my formula. How to control the way my cell reference changes as I move between the rows and columns. These are questions you've certainly asked yourselves when it comes to Google Sheets. The answer, you need to know how to use relative and absolute cell reference. Let's take a closer look. Here's the file will be working on today. What we have here, a sample list with items sold in a store. The clothes are grouped based on the great area, whether they belong to the old or to the new collection. This as well is the criteria which you find what they are price S will need to apply to discount to forward the items from the old summer collection and one discount for the clothes from the new collection. Before we move to solving this, let's cover the theory. I think mentioned there are two types of cell references in sheets, relative reference and absolute levels. By default, All cell references are relative references. In other words, if you haven't intentionally changed anything in your cell reference so far, you've been using a relative cell reference all the time. What's this means? You have a formula at row four, D4 equals E4 multiplied by C4, while copy it across multiple cells. This formula changes based on the relative position of rows and Cohen's from before, multiply it by S4, you go to row five and the formula is B5 multiplied by C5. Then at row six, B6 multiplied by six, and so on. But what if you don't want a cell reference to change when copying it down, well, then you can use an absolute reference to keep a row or Cohen constant in the formula, or even both of them, the roar as well as the colon. How to do this by adding the dollar sign. If you want the colon as well as the role not to change when the formula is copied. And a dollar sign in front of the colon reference, as well as in front of the row reference. Dollar sign B, dollar sign for if you want to only the role not to change when copied as the dollar sign in front of the row reference on left, B, dollar sign for. And third option, if you want the colon and not to change when Coppard as the dollar sign in front of the colon reference of life dollar sign before. Okay, let's now come back to our task. To calculate the new price of each old collection item, you need to apply to discounts, the standard one from column C and the extra discount. Be careful here. The general discounts changes for each item. It's irrelevant. The extra discount is pre-defined. The value in this cell is absolute. In Google Sheets terminology, we need relative references when we apply the general discount and absolute reference. When we apply the extra discount, the formula for the first set of items and entering in this sex. How to calculate the discount percentage? Pectin math basics. What we need is from the original price B6 to subtract the discount price B6 multiplied by the percent discount C6 by default. As we said, we have relative cell references, so no need to do anything else at this stage. Step two, let's apply the seconds discounts now, this time as a constant value. So I will subtract this time B6, the original price, multiplied by the absolute values cell in each tree, dollar sign, E, dollar sign, trim. Copy the formula down and the petals are updated. If you check the formulas now, the E3 reference does not change within the reference range. We have B7 multiplied by eatery, B8 multiplied by E tray, and so on. The second set of items, you won't have any problem with the cell reference here. We have a similar case. You need a relative reference when it comes to the original price, the first value of the formula, and an absolute reference when you refer to the discount constant. In other words, h six minus H6 multiplied by a dollar sign g, dollar sign three. Enter. And we have the value calculated. Double-click in the downright coroner to have the formula copied down the coordinates. We are ready. All right. So far so good. Today you learned or revised or watch the difference between relative and absolute cell reference as as well as in which case says what you need to. Yes. Let's move on. 10. How to include all of the values in a column? (Sum, Count, Counta, Unique): Here comes the next scenario. I wanted to find the sum of a set of cones, or the average, or the minimum and maximum value. How can I do it? In a word? Functions are the answer. Let's check it out in detail. This is the dataset we have at our disposal today. This is a part of a payroll table. We have the names of the employees, their job title, the year that data refers to, and the total salary received. What we need to do is calculate some values here on the right, total salary, average salary, highest salary, lowest salary count of employees, and unique job titles. Let's start step-by-step checking the functions that will help us to find a total of 70 colon values. The function you need, this sum equals sine, starts typing some. Here's the formula. We need. Some sum of a series of numbers and, or cells enter. And we can check the syntax of the function. I prefer the detailed view from the arrow here. As I've mentioned in the previous video, there's absolutely no need to learn anything by hurt. When it comes to spreadsheets, what you actually need is to learn how to use the hopping hint. So the sum function may have two arguments. Value one that in our case will be arranged as one. These are the values that will be added together and to value to an optional argument. These square brackets are the indicator that an argument is an optional one. Relative to, could be an additional number or additional ranges that you may want to add two value one, when to use the second argument. Well, it wants to assume two ranges, for example, simultaneous left. Our scenario in fact is quite simple. So let's just go for one range equals silk, and in the brackets is six to eat 20. Enter. Here's the first value we have. Then the second value we need to find is the average sour. Let's check if there isn't an average function in sheets, just start typing. And here it is. The average function returns the numerical average value in a dataset ignoring text. Let's work with the same range then. Equals average in the brackets E6 to eat 20. And here is the value we need. Then highest and always sour. These are also common functions that you may need to use quite often on daily basis. Max and Min. The range is the same, the syntax is identical as well. Enter and two more values were added to the overview table. The next value is a little bit tricky. Take a closer look here. We need to find the count of the employees, starts typing count and took the functions we have here, especially count and count a. Let's see, was the difference. The count function returns the number of numeric values in a dataset. Whereas the count a function returns the number of values in a dataset. In other words, use counts. If you are counting numeric values, numbers, you count if you are counting text. If I try to use the count function while counting text values, check what we have. Count of employees equals count B6 to be 20 and 10. And the answer is 0. No values were counted. On the other hand, if I use count a and apply it over the same range, the answer is 15. Now, remember that one and take really careful what values each function returns. Last task here counted unique job titles, unique sounds as a valid name for a function as well. So starts typing unique, and let's check what to have. The unique function returns unique roles in the provided source range, discouraging duplicate rows that are returned in the order in which they first appear in the Source Range. Reading the description, what do you think would happen if I directly apply the function over the range in Koln sie, shall I have the count of the jobs I need? Let's test it. Equals unique. The range is from C6 2020. And what we have is a list of the unique job titles, not account. However, that's a step in the right direction. The values are here without the duplicate. Perfect. So to take the count, let's just apply the count a function we saw a while ago, count a because we are counting texts fellows again. So equals count a. And after that unique function, again from C6 2020, Don't forget the brackets Enter and that's it. Done. All right, the job. We've just started exploring the most common functions you need in sheets. Now you know how to check the syntax of a function, how to understand what venues to expect as an output as well. You've also seen that you can use nested functions. It means one function in another one. As we've just had the unique function, we think the count a function, it was nested. And that's just the beginning. And let's move on. 11. How to refer to a range of values from a different sheet?: What comes next? Here's the task for today. What I want is to copy and paste some of my values in this current step, as well as in another tab. I do also need to face the format of the cell, the formula behind it, or only the value I want to paste in rolls into separate Cohen's. Can I do it in sheet? Yes, I can do even more. What we need is the paste special option. So let's check it out. We'll be working again with the payroll overview we created the previous lecture. I went first to copy the header of the table select Command C, command V. And here it is. By default, the values as well as the format are pasted. Ditto extra options I have here are two paste values on lead and to paste a format online. The same option you have if you right mouse click and go to paste special. What else we have here? Let's copy the value in C6, for example, senior web developer and see what we can do. I'm pasting can't hear is the third option activated. Split text to columns. Choose the separator between the different data values space in our case. And here it is. The two words are split into three different cones. Keep that option in mind. My turned out to be quite, quite useful. I can choose to copy and paste to only the format of a cell or range of cells. Unlike this folder, for example, I'm coping cat, then paste special paste format online. And here it says, the format is pasted. The values are missing. Device versus scenario is also possible. I can choose to paste the values online without the Bindi formatting. Keep in mind that when referring to formatting, this means not only the colors, but the font and the currency formatting in this case, they are back to the default ones as well. Let's move on. Now. I'd like to copy some venues from one camp to another. There are some tricks you need to keep in mind here. I want to copy the names of the employees first. So Command C, new tab command. And it looks fine, the same formatting, the same dataset. But check it out if I add a new name here in the initial data. In the first step, this change won't get visualized in the corporate dataset in the second tab. So this means that if I want this result, I need another way to refer to the, to the range. That the only way if I wanted to have it dynamically updated, what they need is the formula. Start by typing the equal sign, then go and select the range you need in turn. So far, you can see the first value only to have the whole range. Just be sure to have the formula down. And here it is. Now, if I add a name here, this name automatically come here as well. How is the reference? Dan took the formula bar. After the equals sign comes the name of the sheet followed by an exclamation mark, then comes the range. Keep in mind that if there is a space in the name of the sheet, then the name should get surrounded by single quotes. Okay, What does I can show you? I want now to make a reference to the pay roll over review we have here. Let's try the same way. Equals sign, then select the range and an error saying an array value could not be found. The problem here is that we're referring to more than one colon and more than one line. So what we have now is an array to help sheets reads the data correctly, or you need to do is add curly bracket. So here's the data. We needed. Another function that you may find useful in different case scenarios is the transpose function. What it does is to exchange the places of the row values and common values. Let's test it here. I'll just add the whole reference as an argument to the transpose function. And here is the result. One more time. Transpose is a function that makes an array of values as an argument. This is what we've just done. We've used as an argument and array referred from the previous step, the result, the cell values and row values exchanged their places. So far so good. Now we know some more feature sheet has the paste special option, the dynamic reference between tabs, the transpose function, time to move on.