Beyond Excel II: Building In Coda to Help Your Team and Clients

1. Introduction: everyone, it's Al Chen. Thank you so much for taking this class and skill. Share beyond Excel to building and kota for your team and clients. If you haven't already, please take the first class in this. Siri's called Beyond Excel one where we look at an overview of productivity tools, common problems with the work flows of using these tools. And also we get a foundation of how to use coda, which brings all your kind of data from metal sheets, docks and slides into one single source of truth. Help you analyse and present your data to your team into your clients in this class. Beyond Excel to, we're gonna focus exclusively on building en coda. One of the big things about Koda is that you don't have to be a developer or programmer to build amazing tools for your start up for agency or for your clients. Coda has meant for everyday users. Eso Sometimes when you're hacking together a tool, it's in Excel or Google Sheets. You're in a building conflict. Many application within excel allergies, and we're gonna take some of those same techniques and apply them to building some really awesome tools in coat one take away from this class is that if you are a freelancer or entrepreneur, just understand that excel and sheets are not necessarily the best tool to use in all different scenarios. I highly encourage you to use coda when you really need transparency and collaboration. For your data. Teoh share with your broader team. Thank you so much for your taking this class. If you have any questions, feel free to leave a comment in the class notes. 2. Your Data As Tables, Cards, and More: in this class, we're going to use Coda to view your data as in a in a table as travel cards and also in a gang chart view. We're gonna be able to view your data many different ways in this class, so please dive right in. So this is the start of class to just to reiterate if you haven't taken class one yet beyond Excel one, please go back in the series and take the first part of this three part series class called Beyond Excel One. To give you a good foundation into using coda, you also need to register for a coda in order to utilize the tools in this class. Eso please go on to class one if you haven't done so already. So in the first lesson lesson two of this class, we're gonna look at a basic table of data. So this is kind of the default table view what you're used to, of course, right. And if we're gonna look quickly, look at the structure of this data and we noticed we have different tasks, so it's almost like to do task or maybe tasks. For a project manager, we have the team that's responsible for this task. The status of the task you know, Don versus in progress versus not started, and also the start and end date of when that task will be complete and also who the task is assigned to on your team. So while this table looks really nice and organized, let's see if there's better ways of looking at this data versus just looking at it from a table, right? So if you hover over your tasks table titled you notice there's this little kind of like grid button. It's also called layout. Click on that layout and look at the different types of layouts you can build in Kota. Now what I show you might be a little different from what you see on your screen, but generally you should see table cards and a charts and for you might say, can charge. So right now we're looking at your data, these list of tax on a table view, let's quickly switch over to cards and see what happens. So this is now basically looking at your data, almost like on a trail. Oh, carve you. But the problem is that this is not really that useful because there aren't different lists like you would see in trouble, right? This looks like it's this one giant list. So how do we tell coded that we want to see this list of data based on the status of the of the task right? Remember, if you can click on this road detail icon to bring up the exact data for every single row. So let's go over here. Go to the label button again. Click on that in this time. Here's where we have to tell Coda. We want a group the cards by a certain column rights. In this case, it's group it by status. Let's go down to status it that and close this window. And there you go. This exactly kind of what you might see into trouble board, right? You have your dun dun list. You have your in progress here, but not started. You can, of course, customized these lists based on what you have in your data, but it's really cool Is if you drag and drop one of these cards into from in progress and done it automatically moves over it, just like you wouldn't troll. Oh, but in the underlying data in the table. This status also changes from in progress to done right. So before phone interviews, return managers was considered in progress. If we go back to the table layout and you notice how everything is grouped by this top status, which we'll talk about this grouping and in the next few lessons just go here to go to status and click on that and go to group and on group for now, just a view your original table data. He noticed that we talked about how vote phone interviews 32 managers used to be in progress, but now it's considered done. So something really cool about senior data as cards is that you can change the data in the card and it will be reflected in the underlying table. Right? So this is one way of viewing or data, which is from you looking at it basically like a like a trouble board with different lists . In this case, our list are done not sorted in progress. So let's look at our data now using a chart and wrote a look at specifically a gang chart. So one thing to do before you build that Ganesh heart is. I want you to go back to Original Table on Group Your column. So go back to the original view of your table, right? This is the raw table that we saw in the first sort of this lesson. I want you to go to team and hit group by and we don't learn you'll. You'll see what the grouping does later in the few lessons, but signable on the left were now and now you'll see that your data is grouped by the team . Now, if you go to lay out on the on the table near the table name cook layout, go to chart and then click on GAN chart. You should have that as the default option. You will automatically suggest the start and end dates for your different tasks. Remember, our table had started and dates for tasks, and we can customize The X axis is optional, but let's see what Kota did for us right off the bat. Now, this is a really useful view, right, because now you can actually see the timeline of the different tasks within different teams . So the retailer validation has many more tasks in the customer validation team being kind of an idea of like how these different tasks are laid out based on the start date and then date. And as you can imagine, when you hover over these different get charts, you can view the individual details for just that individual task right here. And what's really neat is that if you drag and drop the end, the beginning date here or the end date here, let's say it's dragged us all the way to August 31st. Guess what? That August the refers is now the new end date for store visits to specialty Stoller's unders. So I go to beauty. Few details here. The end. It has now changed a 31. And guess what? On the underlying table number, how we do this with the cello cards if you go back to table. The end date also changes in the underlying table, so everything is related to each other. Whether you look at that as a gang chart as a trail oh, cards view which you're used to and Tello despite status again, everything is related to the underlying data table so that you know that your data is always staying consistently. Ah, sing consistent between all the different layouts that you're putting your data through. So yeah, that's justice. The that's basically it for this lesson. There will be more charts coming out soon, like bar charts and line charts and pie charts. So this class may even be outdated as time goes by because we'll be introducing a lot more charts for you for you to play around with in Kota. So that's it for this lesson. Hopefully, you learned a lot about different layouts, and now we can move on to the next lesson where we dig deeper into tables. 3. Digging Deeper Into Tables: in this lesson. We're gonna dive much deeper into tables and Koda, and we're going to discuss display columns how you conduce lookups to other tables and pull data from other tables. How you can add a filter to a view of a table and also a really nice feature encoded called data map, which lets you quickly see how all your tables are linked together. So go ahead and get started and let me know if you have any questions. Okay, welcome to lessen 2.3, taking deeper into tables. Now, you should have a pretty because you should have a pretty good familiarity now with tables and code on how they work. So we're gonna dive a little deeper into kit tables, and the first thing we're gonna talk about is the display column. So if you are coming from a database background display column is kind of like the primary key. But don't worry. I don't know what that means. This'll example make it very, very clear what that is. So you noticed that I have two tables here. If you go into the dock for the class, I have a A table called teams and a table called tasks, right? So as you can imagine, the text tasks to table Rather, is a bunch of tasks that my teams have to complete, along with a team that's responsible for it. The status sarin date and who's assigned to and the team's table is essentially just details about my team, right? So, for instance, the Edge Team North, you can see them to hear their base in Toronto and that completed 67% of their tasks. And you notice that above the team and Tasks column, there's like this little bookmark icon, and that actually represents the Display column. Now what does that actually mean? Why is that even important at all? Well, I want you to click on the team column here in the tasks to bathe table. You notice that this is an interesting contact, which we haven't discussed in detail, but that little grid represents a look up. So when you do a V look up in Excel, that's essentially what a look up does index in Kota. So if I click on the drop down menu and team go to for my column, he noticed that it says, look up from table Right again. This is just another another of column type or data type you can use in Kota. You know, we have currency and tax unlike us up, but we have looking from table. Oops, look up from table and I'm looking up from the teams table, right? So was that telling me? It's telling me I'm telling code. I want you to take whatever is in the team's table and put it into my tasks table here. I'm calling this column team, so basically the when you do that, the selection list becomes just the items that show up in your team's table. So if I were to add another team here, it's called this NGE team South pulled this and then say this is based in Austin right now . They have zero closed task is your whole tests. I just made this up. But now that clicking this drop down NGE Team South now she was up a selection. So basically, uh, look up from table column format allows you to look up other values from these other tables that you have in your doc. But the display column and the team stable happens to be the team table, right? So this is actually the column of data that gets pulled back into team in this table. Let's see what happens if I change the city to a change of this play column in my teams able to city. So if you click on the drop down here into City, go to set as a split column with that little bookmark icon and it see what happens, you noticed the team now changes to all the different cities that are located in teams table. Brooklyn, Seattle, Toronto. Awesome. Right here, Right? So this doesn't really make any sense for us, right? Because we want the team column in our tasks to table to show up the actual team name and not the city names. I'm gonna move this back, moved to display column back to the team and change back to what we were. We really want, right, and so essentially allows you to look up from other tables. Now here's a question for you. What if I want to add the location of my team in my tasks table as well? Because we know the locations in the team's table. It says City and the all the cities are located in, Let's say I want to add a column here. My task table that just also shows the column has a column for where my team is located. So I could hit the placenta here. Gonna go to rather I want you go to team, click on the drop down and go to insert column, right? Right, And let's call us that's column team location, Right? So now if I want to reference the city from my team stable, I could just use the reference I created in this team. Calm. So this fall along with me here as I show you what the form of that would make, I'm gonna write equals right someone. So since I already used the, uh, team name here the team column when I write the word team, this actually starting with Capital Team. So Capital T, it's referencing this column, but in reality is actually referencing this table as well. So I can say team dot City like that You notice how it added the reference to here. Right? So the first step is to add the column named Team. Since this is a look up table, look up from table of the team's table. I can say Team That city, which references the City column from my team stable. If I enter here, it brings in the city name from my team stable all the way down to my entire column White, which is really awesome. So now I want what I want you to do is to create a view of the tasks to table. Remember from class One, we talked about creating abuse. I want to create a view of this table that only shows all the done tasks. Right. So right now I see a bunch of task and some were not started somewhere in progress. I want to show, table or view rather with just the done tests. So go down to the bottom of Dakota, Doc, go to the plus sign, go to table and then go to tasks to write was inserted view of tasks to click on that and you get the exact same table or view of this table. But now we want to filter this toe only have Sorry. I want to filter this table to only have where the status equals done and already kind of auto filled for me. I want to do that. It entered and now it shows just the tasks that are done. So I can quickly see okay here all of my tasks. And here the ones that are way have already finished in our project. And so what's really important to keep in mind is that he stressed this in class one, but I want to trust it again. Here is you always want to have a big master table and you can always have many different views, right? I could have filtered this by team location by status, by assigned to you can praise many abuses you want. But the main important thing understand is that you want have an underlying master table that holds all the data, right. That's really important to know. So a few tables, lots of views. If you walk with anything, just recognized you want a few tables and lots of use. Finally, I want you to click on the link icon next to the table name to see how views and tables are related. So if you if you recall you, you probably noticed there's like this little link icon next to the table view name and also next to the table name. If you click on that, you'll get this right hand pain that shows up that talks about how different things are related. So the view of tasks to table is right here. It's kind of like a child of the main tasks to which is our like, main master data table. So if you ever have any questions about how your views are related causes, you can imagine. If I create another view down here, your doc can start getting pretty, pretty big with various views, and you want to be able to quickly know how everything is related. You know, if you ever need to know, like how many views you have in your code, Doc, this is a really handy way of doing this. If I created another table of the task to of you. Rather, let's call this view mawr view of test, too, because on that link right there, you notice there's another sub kind of view of the task to table right. You can also click on this this icon right here called the Doc map, and I'll give you the same kind of view of all different tables in your doc. You can click on an individual table to see all the different views associate with that so they could see details. Here you get to this view that we saw earlier before. So this doc map iconic kind gives you insight into all different tables in your tables and views in your doc and really helps the organizing self has your doc. It's more complicated. So we talked about look ups. We talked about the display columns. I just want to quickly show you what this would look like. An excel. So in Google Sheets here at the same exact data, we have a team table. We have a task table. Now, if I want to add the team here or the location of my team here, you know, I just think this rafter like inserts. One left. I would have to, like, move this back over copy and paste is back. So there's no space in between. Do locate team location. And if you're if you know how to use the look up, you do something like E V. Look up equals we look up any team north, just be eight here, comma and Then I would have to select all these cells. Oops. Sorry. All these cells. And then I would also have Teoh apply the dollar sign. Tim, apply the dollar sign to make sure that these cell references don't move. Because I'm gonna be dragging this woman down. I'm pulling in the second column from my team. Zabel doing zero for false hit. Enter and you can get the team name as well here. But now I have to drag and drop this all the way down. Right, which is doable. But imagine as you add more casts, you have to keep on adding dragging this from there down or also, it's not going to work, right? Let's say the range team north and now the team will show up and in Kota. Since you only write the formula once, your entire column will automatically fill as you add more rows. Here is an example of how lookups work and how it can differ from Google Sheets and excel. How it's a lot easier to do look ups from Kota. So this is a pretty heavy lessons of definitely rewind and watch again. If you have any questions, I know the team location aspect of this table is probably the most difficult aspect. Understand? So give any questions. Feel free to ask me or email me up here or ask questions in a question mark. What, you ready for the next lesson? Feel free to click next. 4. Building Relationships Between Your Data: in this lesson, we're going to discuss how you can build relationships between your data in Kota. Your you can relate your tables together in many different ways, but we recommend four main ways to do this, depending on your situation. The 1st 1 is of one big table. Number two is doing a star schema. Number three is a master detail relationship. Number four is multidimensional, and we'll dive into what these mean in the lessons. So please go ahead and get started with this lesson. So in less than 2.4 were talking about how to build relationships between your data. And we kind of saw this ready in less than 2.3 if you took that lesson where we had a tasks table with a bunch of tasks. But we pulled in data from the team, which is from this team stable, almost like the We didn't look up to the teams able to get the location and a team name, and so there's many different ways you can relay your data together, but this is just one of the main ways what we call a star relationship because you have one big master table and much of small tables in the side that add a little more detail to the master table. So in this lesson, I'm going to walk you through four different potential ways to relate your structure, your data together and why you would use those those structures. So the first type is this having one big table and, as you can imagine, having one big table. It's just one big table. There's no kind of like special relationship between other tables. You just have one table with all your data. And if you follow along in the PowerPoint slide that I'll show on screen the reasons why you'd have one big table like this. In this case, we have tasks and the team name very similar to what we had in less than 2.3. The reasons are you would have a one big table is you have a small team, you have a relatively small project, and you have probably one type of data with many associated attributes. Right so far. In this case, our main type of data in this case is just the tasks the tasks column, and there's many different attributes associate with the tasks right. But there's only one type of main type of data, which is the tassel. You have teams associated tasks, status start and so on, so forth. So those are the reasons for the one big table, small team, small project and one type of data with many associated attributes the next type of table structure where you can really your data is a star structure, and we can. We saw this in less than 2.3 with our tasks and the teams. But we're gonna build this out a little more. You notice how I have it has three table plus a teams and in individuals table, right. So these are kind of like the small aspects of the star, and this is the main kind of like master table. So if you notice here, the team state is pulling from the team's table. Here we have the team that a team state and we get thinking that here. But we also have a column, a look up table in the A scientist column, which is pulling from the team individuals table right here. It's a really small table, but it gets gets a lot. We get details from this table we need for our master table. So we pull in the rate which is right here from the team individuals table. Right. So this is an example of a star structure where you have one kind of big master table. Then you have small, little individual tables here. We store additional information about the, uh, columns in your table. So in this case, we had Team State as one look up and rate as one look up. So we're what are the scenarios where you would use a star relationship? So as you look in the slide, you have a larger scale. Project is one reason you also more probably one consistency and naming rights. So maybe in team State, someone could write, uh, Idaho or Mississippi. But by enforcing that there is a relationship here. You enforce that Canada in Washington or in New York are the only states that could be added to this table. And there's no different ways of spelling it. There's no capitalization issues. You're pulling exactly what you want from that table. So you have consistency and naming. Another reason for using the star relationship is you want a reference data in multiple places. So this becomes apparent here, where we have multiple date data and other tables and you want to put it into the master table. And then also, lastly, another reason to use a star relationship is that you have 1 to 1 relationships between your data. So you know, Adam Davis here has one hourly rate. The engine Team West has one city, Seattle. So there's a 1 to 1 relationship, and you could easily pull that relationship into your master table. So those are the four reasons for a star table. Now the next type of table structure gets a little more advanced, and it's called the master detail the table. And I'm not going to show you the detail, I guess behind this, uh, this structure. But essentially what is happening is you might have one big long table like this, right? This is kind of like your detailed table, and the master table is kind of like a summary table, and it shows come summary stats, right? So in this case, these air formulas that are looking at the task table we're trying to count the number of members assigned to each team and also the total hourly rate if you add up everyone's hourly rate. If I'm the project manager, I know exactly how much will things will cost for the entire team. So this is kind of like a summary dashboard table of my detail right here. That's why it's called master Detail. When are this narrows? Where you to use the master detail table structure? Well, the first type of scenarios where you have a large data set. So if this table was really, really big and you want to quickly summarize that data into a master summary table, this is basically what you would do. You have, uh, formulas in these columns to summarize all the details. And of course, another reason is, if you want to have, like, a nicer dashboard looking feel in kota, you know, in Excel or Google sheets, you typically will do this by doing a bunch of some ifs or be lookups to create a nice formatted dashboard encode A. This is the same concept where, if you want to have a nice summarized view or nice summarised table of all your detailed data, this is where you would want to use the master detail table structure. So those are the two reasons why you would use the master detail table structure. The last one is called the Multi dimensional table structure, and this is definitely not used as much. But if your project is really large, you definitely see yourself using this. And I didn't show all different tables and how they're all related together because, as the name states, it's multidimensional is there's many different tables with many different look ups. And here the reasons why you would use the multidimensional table structure, as you will see in the bullet points in the slide Number one, you have a very large data set. Number two. There's also no clear hierarchy. So you noticed that with these previous structures, the hierarchy was very clear. You have one master, one detailed table and maybe have a master of you. Or maybe you have one detailed table. We have a bunch of smaller tables that connect to it, so there's a clear hierarchy in terms of how the data is structured with the multidimensional tables you notice in this screenshot. I don't have the actual tables, but you know how there's a vendor table, a budget table remodels Task Table ship A shopping list able All these tables are all related to each other, and there's no 1 to 1 relationship. There's many too many. So, uh, that's when you have a larger data set, and there's no clear hierarchy of how these tables are really each other. They're all important in their own right. The third reason why you might use this table structure is that you have lots of different questions. Answer for your data, right? Maybe have a question about remodeling tasks. Maybe the question about vendors or budgets or shopping lists. These are all questions that could be answered by having a multidimensional table structure . And finally I referenced this earlier. Is that you? Your data is many to many and not one toe one You notice up here for the table to structure with the star schema. The Adam Davis has one hourly rate. Mary Jones has one hourly rate. One team has won city and state and a multi dimensional world. If you can imagine such a world bathroom here may have many different tasks associated with it. It might require many different supplies, and so there's many relationships not just 1 to 1, but one data type could have many different many different attributes. Associate with it. So those are the four reasons why you may use a multidimensional table. And I would say, when you're setting up your coat a dock and you're starting to organize a project or manager team making sure you get the table relationships, make sure we get really shift between your data Set up correctly is really key to make sure that your Kota Doc functions the way that you wanted to. So that's the kind of the basic gist of how data can be related. If you have more questions, feel free to read this article. I put in the lesson notes are starting in this doc right here. It also describes all the reasons why you would use these tables table structures I mentioned. But there any questions? Feel free to leave a comment in the notes of community the community notes of this class. But other than that, let's move on to the next lesson 5. Grouping is the New PivotTable: in this lesson, I'm going to teach you how you can. How grouping is the new pivot table in Kota. I'll show you how you can group your data along different dimensions. How you can move rows of data between different groups to help you organize an analyzer data and a much more user friendly way. In this lesson, we're going to look at a table in Kota and try to group similar rose together. So in accelerate sheets, you might be used to using a pivot table. You can see a screen shot of a pivot table right here and excel, and it's pretty complicated if you don't really know what you're doing. Pivot tables are very powerful feature and excel in Google sheets, but sometimes all you really need to do is this. Group your data along a certain column so that you can better organize and see your data on your on your under a sheet or on your dock. So in this table right here, we have a basic table of on boarding tasks, so this might be a list of things you might see when you are hiring a new employee and you want to give them a bunch of instructions on how to get set up on their computer. You know how, instead of one A ones with other team members and also the amount of time it takes for all these different activities. So we have a list of various on boarding tasks for your new hires to look at. And if you notice in column two, there is a focus area, and so your company could potentially, you know, bucket all these tasks and discern things. So you know, when you look at our values thes air, kind of like you know, the core reasons of why your startup company exists. And then there's product specific training here. The important thing to note here is that there are many duplicated values because many of these tests are soc with our values, many of these products or product training there. So they associate with our products. So how do we view this list in a much more easy to understand way? So that's where we can use grouping so you can click on the drop down here under the focus area on the menu and then go to group and the most common used group is usually group column along left, so I want you to go down a group, calm along, left and see what happens, and your table instantly changed toe kind of look like a pivot table, but not exactly. Your table data has not changed at all, but just the structure and the formats. Ginny's little bits. And now you notice that the focus area used to be calling to, but now it's grouped along the left. It's now you can quickly see all the tasks associated with our methods or values or products, and a little more clear to understand versus using a pivot table or setting up a pivot table from your list of data. Little, just one click. And what's really neat is that you can add rose to each focus area now, or each group rather. So let's say I want to add a new task for my new employees. Take on under our methods. So you just have to go here, hit that plus bun at New Row. Let's call this new task, uh, you know, read our vision, Doc. Most companies have a vision or statements like that. Let's say you know the employee has started this yet, and that's a that's going to take. Maybe 10 minutes should be too long. And this is also an interesting column type called called Duration. AH, duration basically gives you an ability to see the, you know, the total duration of a given task, which is pretty common with a lot of things we look at in project management. So since I added this road reader vision docked to this group, if I ungroomed this column now focus area. If I got a group on a group column, you notice that reader vision Doc is the very bottom, but it gets associated correctly with our methods. So that's a really important concept right there is that you can add data to the groups and automatically tags or adds that attribute to that given row. So that's much different from a pivot table where you can't really change or add data to the pitiable. It's kind of based on a snapshot of the data as it is, but with Kota, everything is very dynamic, and you can add and delete rose from groups. Someone group this back again together, group along left. I can delete this row that I just created by going to just by clicking here and deleting row and now that rose complete removed from my entire table. And I can my own group this so show that that road has been removed. You can see how part of this is compared to using a pivot table in Excel or and Google sheets. Another neat thing is, let's say you know, lunch with Mentor is actually a part of the our methods group. You can actually is Dragon dropped by holding this down and moving this up here and now you can see how this room move from our values, our methods literally by this doing a dragon drop. This is also something not unavailable in a pivot table in Goshi, its or excel. So I've been talking a lot about excel in Google sheets and why pivot tables are not as flexible as these groupings. Why don't we just take a quick look at what that would look like in Google sheets? So this is the same exact data that we have from code up. Uh, only notice if I wanted to look at all the task grouped along our products our values, you know? So I can't really do that with this list. The only way can do that is by doing a pivot table. So this is a pivot table that is built in Google sheets. It looks very similar in excel, you know. So I can I now have satis complete. I'm let me to get rid of that for now, given the status. And now I have our methods are processor values. But I can't actually get a list of all the tasks associated with these different groups. Right? I can look accounts. I could get sums, you know, mathematical related things. And at the same time, I also can add or delete Rosa data from this pivot table. The only way it can change its pivot table is by changing the underlying data here. Right that the pivot table was built off of. And so you can see how flexible a grouping is within coda in this table versus having two separate sheets of raw data and pivot table where you can't easily update records and rows in the table. So that's really the gist of this lesson for grouping. And hopefully you'll learn a lot about how you can group in structure data in one single table, and if there any questions, feel free to leave a comment in the class notes blow. 6. Summarizing Your Data: in this lesson, we're going to discuss just how you can summarize all your data within your table. We'll talk about how you can summarize data for specific columns in the table and also how we can hide certain aspects of your table to give it a much more cleaner. You I as you're using that table data within your canvas, so please go ahead and get started with this lesson. So we've played a little around with a lot of different data. Now let's try to summarize data in Coda. So Coto has a lot of built in summary tools to help you quickly see the totals, every columns that counts of your columns, someone's upward. So let's look at that similar table that we have on boarding tests that we saw from Lesson 2.5, right? Just a quick note. This is a view of your on boarding tasks. Remember, we talked about views building off tables, right? This is the core table that we have. This is simply a view of all our on boarding tasks, and again you can see where the views are built off of by clicking on that little link. Next, the title. You can see how view of on boarding task is a child of on boarding tests, right, and you can also click on the dock map here toe open up the entire list of tables and you can see the details for on Bring tests right here. So let's go back to our view here. And let's say I want to show the counts of all the tasks and the total expected time it's gonna take to complete always on boarding task. When I have a new hire, join my team. Right? So all you need to do is go to the column that you're interested in some writing data for Click on the Drop down arrow and they go to summarize and you know it to be of various away . To summarize data count to some average, since I just want to see how many tasks I have. I can obviously actually see all the task by looking at the number of road here. But let's say I want a summary view. Go to summarize and they go to count, you know, decided puts like a nice count number right below their right. Let's say I want to find some of all these times. Basically, I want to know what's the total time it's gonna take to on board a new hire. Clinton dropped down here. Go to summarize instead of count, Let's go to some. And it gets a nice format of 16 hours and 30 minutes. So it looks like the on boarding for new hires is quite a long time for for this company, eso that's kind of you can quickly summarize data. So now let's try looking at how this looks when you group stuff. So in the last lesson, we talked about grouping data by focus area. This time I want you to group by status and we're gonna do something really need to get rid of the counts in the summary summary data. So let's go to status on the drop down, go to Group Group along left and this same kind of step we took in the last lesson. But instead, now we're grouping by all the in progress not started in complete tasks that we have to do . So this is a nice, handy way for a new hire toe. Look at Okay. What are the task? I still need a complete versus were the ones that I haven't started yet. Right? So it's a nice handy of you. And you notice here that since I added a summary count in a summary some I can see now for just the in progress task how many tasks I have And also how many? How much time would take this for these three tests? So I have, like, kind of like a sub total, if you will. And this is similar to kind of subtitles Work and pivot tables in excel and Google sheets. But I can quickly hide these. If I want to buy going here and they're going to none, they're going here, going none. This allows you to give you a cleaner look of your table, uh, so that it doesn't clutter it with all the sub totals and all that kind of stuff. Finally, you noticed that when I group things, Kota automatically gives me these numbers right here That gives me a count of the number of rows in that group. Right. So this group has nine rose. There's nine different tasks for on boarding. You can also get rid of these counts to make your table, even cleaner, which I like to do to make my table super clean. We're going here, right? Click Any group Goto hydro counts And there you go. Now it's a super clean table that you can use for your view to show to an employee or to a new hire and just makes things a lot cleaner to look at. So we've been talking about cleaning up your table to make a look nice and pretty. So what if we want to make it even pretty here, right? It's a lot of times you might want to mix your text with your table. So maybe I want to do something like this where I say, Hey, new hire here are your on boarding tasks. So you know this now I can see I have this nice table and this employee can read this sentence about doing an on boarding. But now I have this, like, kind of pesky like view of on boarding title in the way. So how do I clean up even more about this table to make it look even cleaner? So I could do is I could go on and click on this grid icon layout icon here because on that , and then go to show title this left click on that and remove the title now looks like even Maura cleaner, right? You know, just like how those we call these little laws in jizz, they actually can hide high. Now when you move your mouse away, move your mouse and hover over these again, let's try to hide a few more things. I can also hide the column headers. So now I just have now. I don't even know what these things are, but I kind of have an idea of what they are. But usually, if you have multiple columns, you don't want to hide the column headers. You can also hide the grid lines to so now it's like they're not. There aren't even any lines in your table, so this is like a super bare bones table. We call them like headless tables because there's no kind of title. But this is in a case where you want to really clean up your table to make it easily presentable to your audience, whether it's an employee or to management or to a client. So that's the lesson here for how you can summarize or data and also clean your table up after you've some riser data. So please move on to the next lesson. 7. Conditional Formatting: in this lesson, we're going to discuss conditional formatting. You're probably familiar with doing this. If you use Excel or Google Sheets with for Koda, we're going to talk about how you can apply conditional formatting Teoh one column or multiple columns of data, depending on what you're looking for, for for many purposes. So go ahead and get started and let me know if you have any questions by leaving comments in the class community. So if you're coming from Excel or negotiates, you're probably used to doing conditional formatting. And conditional formatting is also available in Kota. But it does differ a little bit in terms of how you can actually highlight certain columns versus just one column of your data. So we're gonna continue using our table from lessons 2.5 and 2.6, which is our on boarding tasks table for new hires for a company, and you look at the current layout of this table again. We know that we've Status Column to show this might be the comet. The employee uses whether or not to say there have completed the task or if they haven't started the task. But right now it's not really easy to see which tasks are complete versus not started versus in progress. So let's add a conditional format to this column where we can see things that are complete , not started or in progress. Right? So the way to play conditional formats and Koda is by going over here, you can go over here to hover over the title and click on this capital a small a button, and we are going to add a new rule. Similarly, how you would add a rule in Excel or Google sheets? Let's add a new rule, and we want to focus on the status column. So here you can type out the condition. Or you could just wait for the drop down and click on the item that matches what you're trying to create rule for. Let's go to status equals and a skirt with the not started tasks, and I want to play a background color, which is this icon right here of read write for our background for not started. It was already Bykota highlights. The entire row for every single task has not started, but we only want to highlight just than not. The status column So how do we do that? So in the applies format rule to drop down by default, all the columns are selected. Let's only select the status column you notice. As I'm de selecting these check boxes the coms get Ah, don't don't get the rule And there we go. I've applied the conditional format to just the status column for tasks that are not started. So let's quickly had to more rules for in progress and complete. So let's add new rule here. That's this. I'm going to type it out just to show you how I can type out the rules. Well of status tab over equals in progress. Seconds are typing in in progress. Progress. They're going to hit a tab. This I'm gonna make a yellow for in progress tasks like that. Same idea more to de select all the columns that only that I don't want to highlight and just do status is now only in progress calms our at our highlight yellow. Let's add one more rule for all the complete items. Let's go it again to status equals complete hit tab. And now I want to make this have a background of green and it's again the select all the columns that we don't want. And there we go. We just apply it a conditional format to just the status column and now as an employee or new hiring and quickly see what tasks I need to complete. And it looks like I have quite a few tastic complete since I see so much red in my table. So hopefully this was useful for you in terms of learning how to conditionally format and looking forward. Teoh seeing you in the next lesson. 8. Dropdowns to Control Your Data: in this lesson, I'm going to talk about how you can use drop downs to control your data within a table. We're going to discuss how to create a drop down, otherwise known as a control in Kota and how you can filter your table based on this. Control will also discuss how you can name your control or name your drop down so you can easily use it within your coat a dock. So please go ahead and get started with this lesson. So in this class, this lesson we're going to discuss drop downs there also called controls in Kota, because they essentially allow you to control how your tables filtered, reviewed in addition to other things. So let's quickly look at what a okay our table looks like so ref familiar with what OK are is its objectives and key results. And so we have a list right here of key results, description of the key result who was the lead and also the status of whether or not the team achieved the key results. So it's kind of like a way to measure whether not your team hit certain goals or objectives in this case and Also, this is shown by various quarters from Key one through Q four. And so what if we wanted a really dynamic and easy way to filter this list based on the quarter right? You could obviously creative filter yourself by heading into filter here what you've done for in previous lessons, but we're gonna do something called the Control. So we're going to create a control that lets a select 1/4 in the control, and it will let us look at data just for that given quarter. So again in control is very similar to what drop down or in Excel, Google Sheets. It's known as like a data validation kind of dropped down. So let's just write some text really quick. Pick 1/4 right, and I want you to go to the plus sign here, go to control down here, and then go to single select and inserts. Basically get a little nice drop down menu into your code a doc and it's right in line with your text. And so when you click on it, there's gonna be blank where we have nothing in the control. So I want you to go over the control hover over it, right? Click it, and now you can see Hartigan add things to this control to make it more to add values that you can actually slight. So the first thing you want to do is let's give us a unique name. Let's call it 1/4 picker because it essentially this drop down, we'll let us pick 1/4 and then, in the acceptable options, were not going to add Q to Q three Q four Russian use a formula using the array formula, which creates a list of all the quarters that the person the user can slept. So click on this little F icon here, and then we're gonna rights around the formula or Ray. Pick that and then left parentheses. That and now we have to give Dakota this array formula all the values we want the user to be able to select in the drop down. So we have to do. Here is the double quotes Capital Key one. And then, after the double quote, put a comma and then do a Q two double quote kind of see the pattern here. Q. 33 then Q four. So you're you're selectable options now should look like this. We first named it Quarter Picker, and then we are giving it the values. Q one to Q two Q three key for in this array from the So I have to do this hit enter, they kind of locks it in. So if you click outside of this menu box, go back to the drop down or the control. You notice that you can now select Q. One. Q. Two Q three Q four right, nice little drop down, also known as control. So let's say we want to link. We know this is drop down. It doesn't really do anything except give you the drop down. We want this table to be filtered by what's shown in this drop down, right, cause right now the table has everything from Q one to Q four. So the way we set this up is we have no link filter this list of data based on this quarter picker, so we can actually filter this less again by clicking on the filter icon. And in this case, we're gonna write a formula to use this picture that we has created. So the key formulate key form that he used here is called matches, but the first thing we have to do is write the quarter column. So 1/4 notice I want a Tavel recorder. It selects this terror. Raoh. So 1/4 dot matches like that matches and we want the court to match the quarter picker we just wrote right here were created right here so I can write quarter picker. You notice it's already kind of it's quarter picker. I notice that kind of auto feels right here. Reckon press tab, or just click on this to insert at once your formal breeds quarter that matches quarter picker hit Enter and your list should have filtered by just Q. One. So now I want you to go over there and click on Q two and see how the list dynamically changes based on what you select in the drop down. So I'm looking at just queue for okay, ours and cures ALS. You can also change a select list so that it allows you to select multiple quarters at a time. So if you right click here, allow multiple selections, you can make that on, and now I can look at both Q four. Let's say I want to do Q three as well, and now I can see both quarters. Cute. Thank you for based on this quarter picker. Right? And so this is very similar to a select list which we talked about in Class one. Except this is Ah, select list is a type of column, whereas this is a control that lets you actually control your data. So if you had to do the similar kind of concept in Excel a typical sheets you may have done this before, where you do a data validation drop down. It's a lot more complicated, and I don't believe there's a way to do this actually, in Google sheets or Excel from a similar as Dakota. So I here I have the same exact table from ROK. Ours list Teoh here and let's say I want to filter this list by Q one. I could obsolete creative filter by going here and filtering this right, but said, we're going to do a data validation. So I go here, actually, the first thing you have to do if you're negotiators like do this like you won have to put like the quarters randomly somewhere like that, right? And if I want to create drop down ideo here that I go to data of validation that I have to like, click on the range that want toe honey from it save, and now we can actually create Drop down with different quarters. But there's no way to tell this list to control this list the filter based on this drop that I made. So the analogy of creating this control and Excel or Google sheets, is that data validation picker, which is not really as user friendly as this eso. Hopefully you can see how powerful these controls are or select, list or drop down. What if you want to call it is in using this filtering this table and the unique thing about thes controls that you could give them unique name so that when you reference them in the table, they have a canoes, quarter picker or select picker, or we want to call it so. That's really easy to filter your list, and we'll talk more about naming your controls in naming your formulas in future lessons. But this class, this lesson I just want you to learn how you can use a control to filter your data so over You found this useful and they look to see you in the next lesson. 9. Formulas for Getting Things Done: in this lesson we're gonna focus suggests on formulas to get things done. We're gonna talk about the formula language in Kota and how to do some pretty comment formulas that you're used to an excel in chief such as some if count if, and also things like average men. Max. Some all these farmers will talk about how they can be more flexibly used in Kota compared to Excel or Google sheets. So please fall along with this lesson and let me know if you've any questions in the class Kennedy below. Oh, right onto one of my favorite lessons in this class, which is formulas for getting things done. I love formulas in Excel go sheets. And when I discovered that Coda has just in it, just as an advanced language from the language as excellent Google sheets, I was super excited, super excited and jumped on it right away. So I'm gonna explain to you how formulas work here in Kota. So we talked about having formulas in the table itself already, right? So before we get to the formulas for the table, let's quickly look at this table that we have here. It's called sales Pipeline and I think we saw this in the last class and the Class one Beyond Excel one. And it's a list of companies, industries and also whether or not the sales person is closed. The deal with that given business right? And we also have some numbers here. The number of licences for that given company, the total per license costs and the total license costs, which is just licenses times per lessons cost. So there's a pretty typical like deal pipeline sales pipeline table you might see in Excel or a hub spot or in Salesforce. The total license cost is simply a formula of number of licences, times the pearl essence costs. Remember how we talked about how there's no cell of references? It's all naming. You actually use the real names of the columns to create the formulas. So these air formulas with in the table, But we're gonna start doing formulas outside the table and in the canvas right here. So the first task is how do we find a number of deals and total license costs for life sciences companies? So these air notice there's a few life sciences companies here in industry, so it's first or to find a number of deals, so you can quickly see that there's 12345 Just one count because it's less is small. But let's try to do this using a formula. So the way you start writing formulas in the canvas is you start with the equal sign, just like you would and Excel or Google sheets be the first type in the name of the table. So when the mobile table was called sales pipeline, right notice code a kind of auto completes the table for you. Hit tab here and now it's selected the entire table. Now we want to filter this table on Lee to the life sciences company. So how do we do that? Well, you use the filter formula. So right after sales pipeline, you hit a period or a dot. So this is the one thing that's different about quotas, that you're not creating a bunch of parentheses and message. If statements and all that kind of stuff, it's all just using will be called chaining. So when use, these dots are kind of like chaining commands onto each other, and I walk talking more about what that means that in this lesson, but just for now at the dot and this time right, the word filter I want apply filter and you'll be doing this Allies creating regular table name and then running filter hit tab. Here again, we want to filter this table based on the industry that is life sciences, right? So I can go down here. I can select industry equals and already kind of auto filled for me Already knew I wanted to love sciences, but I can click on life sciences, right? So now we know that this table, this formula rather is going to filter this table cells pipeline by the industry Life sciences. So that's great. Now we know that this tables in a filter quickly, but how do we get the actual number of deals? This is where we use the count formula. You may be using the count formula in excel or who she's using count. If this is basically a version of count, if but now instead of writing count, if parentheses right dot count and you know it's the first formula you have is just the regular count formula. And so now this is essentially doing account if of all the life sciences companies. And then we add the count on the end. So you notice these dots are basically allow us a change from commands onto the table. I can hit, enter and I get a number five right? So I can again, to the left of this, I can say number of life sciences deals. And what's cool is as I add more data to this table. Let's I do company G. It's also a life sciences company, and this is like enterprise license and bubble blot. This number increased from 5 to 6 because it's looking at just this column industry that equals life sciences. And this counting all those rows that match this filter really cool stuff, right? And you can imagine doing multiple filters, multiple columns of data. So that's really how the filter from other works. Now you can count the number of deals. Let's talk about the total license costs, right? So this is kind of doing a some if in Excel review sheets. So let's down here is right cost of all life life sciences deals. Let's are running the formal again. You should be kind of getting use this now equals sales pipeline dot filter Were industry equals on this case you want or sorry, we actually want to do just the some of the license cost so felt that you don't need to go now. We do know that it's right. Industry equals life sciences, right? So now we have filtered all our entire table based on again on the life sciences industry. But now we want to reference not than the count of deals, but the actual total license cause, which is this last column right here. So here we've to do another chaining of command where we put dot and we referenced that actual column name. There's even actually selected right from here. We're gonna wait till we get this total license cost. Click on that. So now we've basically said take the sales pipeline table, filter it by industry equals life sciences. And once you've done that filter, take the total license cost from that the resulting filtered rose. And on top of that, we want to write the some formula which is right here, which basically sums up all these rows. But Onley sums up the total license cost column. Right. Knows how I have multiple chaining of commands here now shaming, chaining of commands. And that's one. The really key features of code is that you're not using various parentheses but rather chaining things together. I ensure right here I now can see that the total cost of all licence deals is $1.77 million which is basically summing up everything here that matches the life sciences industry. So this is again the power of the code of formal language, and I can't stress enough how important it is to understand this kind of chaining of of commands. So if you think about it, it's really kind of a few key aspects of Cheney of table. And then you have a filter and then in action, right? So if you look at this formula, the table is sales pipeline right there. The filter is industry life sciences, and additionally, we're changing the total lessons cost cause that's the column actually of data we want. So that's also part of the filter, and the action is what do we do with the resulting data or rose from this filter or doing a some right and in this case, for licence deals. We did a count, right? And so what if we wanted to find the average Max and men of all the deals based on total license cost? Let's start it. Start writing out that from, like here so equals sales. Pipeline dot filter Rocky We can just do total total license costs dot average. So that's six. Quickly write some words excess. So we know exactly what the number is. Average of all deals license cost, average of all deals. Now we know that we're reading this, uh, this this formula we're looking out the table, which is things. Table the filter while it's not really a filter, but you're so kind of pulling in a certain column of data, which is total license cost, it is right here and replying the action of average to this This resulting column of data. Let's do this for Max and Mintues of Max of Total Caught total off. All deals there also and do men of all deals, and this is very similar to what we before. Except action will change his times. A sales pipeline dot Total lessons cost dot max and then sales pipeline dot Total lessons cost dot men and now you can quickly see Okay. So, out of all the deals myself pipeline, this is the average around 20 $60,000. The biggest deal is a $1,000,000 the small steals $20,000. So hopefully, through this example, you've seen how you can use these different formulas thes actions rather to do kind of similar to what some, if and counted is in excel in Google sheets. But this is a lot more flexible because you can do all kinds of filters and all kinds of columns of data based on what you're looking for. So in Google Sheets, I mean, this is very simple. If I wanted to do the count of the deals right, You know, it's not as intuitive because count of all life of all life sciences deals, you know, you're doing something like this. Were you doing count if and then you have to count. Were the range of the select actual range. You can't use the column. Names like industry, if they do range, were this, you know, uh, equals life sciences. It was totally doable, but just not as naturally feeling as natural. This feels user intuitive as doing where use of writing. You kind of like reading out a sentence, right? I'm taking my table. Want to filter by life sciences? I want to count it. But in Excel it's like, OK, I have to do count if with these parentheses than reference some random range of data and then put life sciences right, Same thing applies for total total license costs of life sciences. It was expanded a little bit. I mean similar deal. But being sequel some if right and then some. If is we're looking at this range, you know, equals life sciences. And then I have to reference some other random range of data, which is over here total license cost enhancer. It's the same exact number, but it's just not as, uh, easy to write the formula, because now I have to like, reference to random ranges of data. And what's in what's interesting to note here is that if I added a new company here company G Life Sciences, these numbers don't change because he knows how the references on Lee stop that Row eight here in Google sheets. But in Kota, you can add as many roses you want here is also has the right industry name. All these farmers will automatically update. So you don't have to worry about your cell reference right here going out of whack because you forgot to include all the rows, right? So hopefully you can see how extensible and flexible these formulas are with, uh, summarizing your data using formulas. So hopefully this lesson well taught you a little bit about using for most to get things done. How When you add rows of data to the table, nothing breaks in your formula, whereas in Google sheets or Excel, things will break if you don't properly reference the rose quickly. And also that you can use this regular English language to kind of like right out the formula instead of having a reference individual cells. So hopefully you enjoy this lesson. And the next lesson we're going to talk about some of more advanced formulas that are definitely not available and accelerate go sheets. That makes sense for the modern workplace. So please stick around for the next lesson. 10. Advanced Formulas For Modern Workflows: in this lesson, we're gonna get into advanced formulas for modern work flows. So these air formulas that you won't see in Google sheets or excel but are very useful for today's online world. Everything is working. The cloud and everything is very collaborative. So we're gonna use formulas like modified, modified by created and also things like user This document these air from Liz, you're probably not very familiar with, But they will be very useful for you once you realize how you can use them in Kota. So please follow along with me and this lesson and let me know have any questions in the class community? Okay, so hopefully in the last lesson you have learned some basic about basic formulas. And now, in less than 10 we're gonna talk about advanced formulas for modeling workflow. So these air formal is that were are Babel. Lakota that makes sense for the moderate replace were a You're always collaborating, collaborating with your teammates and be you want to make this data very visible and transparent to everyone. Looking at the data in class one we talked about by default. All your tools should be collaborative in nature and be transparent so that everyone can see your data if you want them to. So that includes your teammates, your clients, your friends. So these air formulas that are made for more modern or clothes that aren't available in excel in Google sheets. So one of the most commonly used formulas is called This Row, and you'll start to see this a lot more when you're doing lookups other tables on stuff like that. But it always refers to the display column. So let's say, for instance, um so actually, first, look at the data for this table. My tasks. It's a very simple table. We've seen this data before. Were you a bunch of tasks? Which team is responsible for that given task and also it conditionally formatted column for status to whether or not that task has not started done and also who is assigned to. So if I do and we'll talk about display calm, which is that book murkier? If I do equals this row, it automatically references the display column in that set for the table. So if I said the display column to here knows how changes to retail validation if I change it Teoh assigned to display calm here they're now it's Mary Jones. So there's a lot of times when you need a reference just the display column in your table, where you can just use the this row formula. So that's first this road. Let's go with that for now. And so one of the biggest common one of the biggest questions that people have in, you know, Elissa data after managing a project with multiple team members is who modified the row last, who modified this task? Who modified the status of this milestone. And that's where we can use e different formulas like modified by created by so on, so forth. So let's create a column of data that talk that says, Who was the last person to modify one of these roads so it might have multiple people multiply? Multiple team. It's looking at this table if I do equals modified by right here, modified by, you know, So says returns the user who modified the previous item. So if I do modified by this row, I can see whether who was the last person to modify that specific row in my table. So no surprise here. I'm the last one that modified this all these rows. But if you had a teammate that was using the same Dhaka's you and they change this drop down to customer validation or they edited this text right here to include some random text , their name would show up right here. As the last person who modified that given row, you can see how cool this will be as you are using, uh, Koda or templates with your various team members now last modified. That's also very interesting one, because now you can see the actual date and time that that that person modified that row. So if I do equals modified, just modified, this row returns the daytime representing the last time that the Rose modified by entering here and you notice here, that gives you a really detailed look at when each role was last modified. So you noticed, like today's game June. So I modified a bunch of these rose. Um, in the last few days, let's say I want I modified this right here. The last day was June 4th. Let's say changes to not started. No. So I moved to June 6, which is today. It's 1:30 p.m. Right now in the East Coast. Eso now you can see not only who modified that robe. Also, when that rose last month in terms of time, you also change the format of this. I typically likely just see the, um the, uh the date not necessary. The times I can do this and go to date And there you go. And I can just see the actual date of when it was last modified. So really common pattern is especially for managing projects is time since modified. So what does that mean? Sometimes if you have a bunch of tasks that are straggling, you want to know whether or not the person has touched that given row or task in the last day, Last 10 days last month, so common formally might use in this kind of project mansion scenarios equals now, which it's represents a time for right now, they really right now minus modified of this road. So it's thinking about what this family does. It takes the current time. That's right now. And it subtracts out the last time that that role was modified. So that means if the row wasn't modified for a long time ago, then now is going to be a big number or the resulting from that would be a big numbers. That's answer right here and see what happens. So why is this really interesting? Now you can see that. Okay. Assuming phone interviews, retail managers was a task list that was really important. It wasn't hasn't been modified in almost a day. So that might be alarming for you if you know, if your project manager and you want to know whether I'm not your tasks on time and he knows that this road has been touched in a day or a week or what have you done? Be kind concerning for you as a project manager and you might want to ask whoever's modifying. We're working on that project. Hey, how come you haven't changed this row in like a day? Whereas some of these things I mean, these air rose, I recently just change. But two minutes ago, you know, actually, one day their bodies air one day ago. But you can see how the this will give you an indication of how much time has passed since that person updated this given row. in the table, so that's really kind of it for this lesson. I mean, a few other things you can do in the canvas that are interesting is, um, created by is also a pretty common Let's say we call this column created instead of saying , knowing when this role is modified, you can see when this role was created, so created, and I can see everything was created in June 4th. So I create this table with a few days ago. Eso nothing crazy they're created by also gives you the user who created that given rose specially. This helps if you want to know who adds new rose to your table. Who created that row? Two other forms I want to point out or user and this document. So if you go into the dock and the canvas and you write equals user and time over, it always gives the current logged in user, which in this case is me out Chen. But if your teammate logs and they'll see their name and it's really useful because sometimes you'll see you can create document where you condone, filter or limit things suggest that given user user name and Also, another common way to look at all the tables and document is U equals this document. Now you can reference almost every single thing in your coda, Doc. And they say I want to see how many accounts of how many accounts of the task there are my task table. You quickly see data like that. So play around with using, modified, created, created by and user. In this document, as you're building at your docks, feel free to email me or if the question mark have any questions because thes from those are definitely newer, uh, in Kota And they're meant for a kind of more modern work for us where people have to know, like when things are being modified and when things are being updated and you don't get the kind of level granularity and accelerate bubble sheets. So this was advanced formless form on work clothes and hopefully found this useful for your Martin were close 11. Named Formulas: in this lesson, I will discuss what named formulas are in Kota. You can name any formula that you write. You can name a formula you can name a control. We can even name this regular values that you put into the dock or the canvas and how you can reference these values almost like variables within your doc. So please fall along with his lesson and let me know if any questions in the class Katie Okay. And this lesson. We're going to talk about one of the most powerful features and coda, which are named formulas. So the analogy to this concept and excel Rigo sheets are defined names where you can, you know, kind of like a sign a cell angle sheets or excel a given name and then represent name. I'll swear in your Excel file ago sheets while. But the difference with Koda is that with name formulas, you can use these name from those anywhere in your doc, whether it's in a table or of its in the canvas on. You can also get rich data about that given name formula when you hover over it. Now I'll show you what that means in this lesson. So let's look at the table we have here in this stock. We've seen this before. In the last few lessons. We just have different our start up sales and are different company names and the total deal size for licenses. And let's try to filter this list by just the clothes. One deal status. So we did this actually, in Lesson 2.8, where we did a drop down to select. Use the drop down to select the quarter right, and we've been able to control the list based on the quarter. Let's do this now for the deal status. So let's go over here and sort a few things and sort of your returns say, pick, deal status. And now we can go to the plus sign in sort of Control and do a single select lis and click on that, and we want to populate this control now with all the different potential deal statuses. So we do that is right. Click here and let's give this a unique name. So let's call this deal status picker, right? Like we just like I taught, tell you in the first post point right here, so now for the sexual options. I want you to create a Formula One that this is similar to what we did in lesson 2.8. We have to create the array Formula One into left parentheses. Roanoke right out each one. So the first ones closed one. And then we have to add a comma after close one. Us do closed, lost. And we also we have here. We have prospecting and building a business case, and I think that's it. So now you can now these air the four values close one closed, lost. There's, ah, close lost prospecting and building business case or the options you can select from this drop down or control. So now if I click on here, you know, So I have those four options close one close loss. But nothing changes here yet because we haven't linked these two within filter this table by this picture yet. So let's actually do that. So let's go here, go to filter and we want to do the deal status. Right? And we've talked about. And lesson 2.8 matches is the way you can control basically linked together your table to filter by what's in the deal status and we're going to pick. Remember in Listen to your name. We cleared that quarter. Picker control. This time we have a deal. Status picker. Let's click on that hit Enter And now let's you know it's on my list. Already filtered by closed loss. Let's click on closed one. And now we can see all the deals that we have just for closed one deal status. Right now we know that this drop down or controls called deal status picker What's interesting is that I can actually reference that elsewhere in the stock's gonna be I want to show this guy might be of this could be a view down here. I could say something this like this. These are my startups. And now instead of, um, writing, you know, closed one deals I can actually reference that variable about going equals deal satis picker tab over and enter Close one deals. I mean, it's just like a nice, like header, maybe making, uh, yellow. And now you can see that close one actually is just referencing this picker, which is deal status picker. Right? So if I change this here, guess what this also changes along with it. So it's a really dynamic way to build text into your into your doc. Right? So that's how you can use named controls to help you filter list. But also Teoh give you tax to put into the coda, Doc. The next one is ready from in the canvas, showing total close one sales and another from left for all potential sales. And we're gonna name these formulas. So that's over here, right? Um, total of close one sales colon and in total potential sales, total potential sales just going to be a total of, um of everything. So I said, Let's change us back to a section. Make this a multiple sections. That's like everything. So we can see Lord eight again. It's like all there we go. We have a table back. You notice how this changes to all the different potential selections in my control here. So total potential sales, basically sales of all all these costs and then close one cells are just going to be these sales that are closed one. So we did this actually, in lesson 2.6, I believe, or two point eight 792 benign will be used from formulas. So let's do this now for here. So total of all closed sales. So again, this starts with the table. Right? So my start up sales that's right here hit down every three times a tab. My syrup sales dot now the filter filled her tab over. We want to see where the deal status equals close one writes with equals and then closed one. So you set up the filter, right? And now we want to take in just the total license cost. Columns were going to total eyes this cost and then dot What is the action? Remember, it's table filter action. The action is some. And now we know the total lessons costs with only the clothes one deals now, total potential sales is just the sum of all this. So this is pretty simple to organise to my start up sales dot total license cost, which is kind of like the filter were pulling in just the total lessons cost column and then some, which is the action. So looks like our clothes. One deals are getting pretty close or told potential sales. But let's say I want to, uh Coco. The ratio of close one sales out of our total potential sales, right? What is 1.17 divided by 1.8 million? Well, instead of having to write off those families again, I can actually right click here. Sorry. Go here and you notice in the formula, there's little dropped on a row. It's kind of hard to see people click on that. You can actually give this family a name. Let's call this closed one sales This kind of where you would like to find a give it to find name and accelerate Google sheets. This is how you do it in Kota so close on sales had entered. You lock it in great by hover over this thing and it click on the drop down menu here. I can call this potential sales right. And now, if I want to find the ratio of close one sales, a total potential sales, let's say it's called this ratio of closed one sales to total potential sales. Instead of writing out the whole formula here, I could just reference the name I gave this these individual values right? So now I can just say well equals close one sales have that divided by, uh, total. So I think it was potential sales. Yep. Have that. And now I can get the ratio of 1.17 to 1 20 million. And actually, I just realized that these, uh, this actually make a lot of sense because we have to do this in. Since these numbers are in dollar signs, we would actually have to convert these into percentages. So this actually is not as useful in this context. But you can see how I can actually reference potential sales by going like this. And I can quickly use this number on dykan. Say something like petrol sales times to take the potential sales and multiply it by two. Um, well, yeah, in terms of being will get a percentage is you would actually have to make sure that the numbers themselves are also in the format of the percentage percentage sign. Finally, if you have a huge sentence, let's say I have a huge tension. Saying something like this is a really important sentence. How would I reference this sentence somewhere else without having to write it out? All word by word, while you can actually use a formula. So if you write, the formula equals double quotes. This is a really important sentence. Yeah, and click on the drop down here and say Call this like, important sentence. So guess what? This tax, you know, looks like Tex is actually a formula. Because if you click on this, it's a formula that's simply equals This is a really important sentence. So now if I want a reference that sentence somewhere else I could just right equals important sentence it tab over an inter, and it just copies over whatever is stored in that value. So this becomes really important, Especially if you have, um if statements, because, you know in Cody can also, if statements like this equals, if two plus two equals four, then this is correct. This is incorrect. You can also give this if statement a name like if sentence and then you can always reference that by saying equals if sentence, so name from other super powerful. I just give you a flavour of all the different ways you can use name formulas. But as you play around with, uh, making your coda, Doc more dynamic name for most of become very important for you as you build out your doctor, your teammates, so hopefully I'll see in the next and final lesson. 12. Connecting Your External Data Through Zapier: in this lesson, I'm going to discuss how you can connect your external data through a service called Zap e ER. Cota has a deep integration with the happier service so that you can take data from, let's say, starred slack messages you can take and trees and Google forms. You can take deals from Hub Spot or even mentions on Twitter and have them automatically show up in a coat a table in your doc. So please follow along with me. This is a really interesting class and how you can use external data and feed them into your code. A doc. If you have any questions, feel free to leave comments in the classic charity below. Congratulations. You made it to the final lesson in class to connecting your data connecting your external data throughs. Appier. So if you don't know what's happier is it's kind of like a service that lets you, uh, connect your data from Gmail from Google docks from forms into other services that you want to move in and out of, and Coda has a deep integration with his happier so that you can actually take data from Gmail from Google forms from slack from hub spot from Twitter from inter common from all kinds of various applications. Um, that you can see through his happier and pork import them into in Dakota. So I'm going to show you what those A p er, uh, website looks like and how you can build your own zaps so that you can take data from its a Google forms or from slack on and then import them into code tables. So if you have ah, zap your account, it look like this. And I'm going to just look at a draft of a Zappa was working on where I'm trying to take things from slack and put them into coda. So generally you have to have a trigger. And this trigger, you can say maybe every time a slack message gets starred you want that to create a row in a code table, or maybe every time that there's a new channel in slack. You want that to create new road in court. So depending on what you want to do, you can also, uh, select other triggers. So you could say, Let's say I wanted to dio, um a you know, something that happens in Mel Chamber, Google Sheets or Salesforce or Gmail. Whatever you want to dio, you can say, Let's say a Trela card gets updated. You want that to update a row in Kota? So what that means is you create trigger that causes an action in Kota, and in this case, you can essentially create a row inside a code, a table in whenever docks. So once you've connected your, uh, Kota Doc, too zap thier, you'll be able to see what rows and columns of data are what tables or data are available for you and the way to actually link things together. I won't you step by step, but essentially when you go to your account settings, you're going to see a code applies a p a p I key, and you want to copy and paste this key into zap e er so that you can actually see all the tables and all the docks that you have associate with your code account and connect. It was happier. So what I'm doing here is every time there's a say in this trigger right here, every time I a new card is updated in cello, I could say that I want to. I'm not gonna collect my counter. Now let's assume I kept everything. I will create a row in my coat, a table, and this is collected by a code account. And it's going to create a row in my beyond Excel to dock, which is, uh, this right here. It's gonna create a row in the class syllabus table, which is up here, and and then and then here. You can actually select literally the name, the lesson. What you want the, uh, fields to be when you insert in that cello card? So play around with this If you're if you have other data from again from trail Oh, from Hub Spot Twitter, even intercom Zappa has a whole bunch of absolute can connect to. And then once it was that it that it comes out those APS you can create rose into whatever table you want in Kota. So that's it for this lesson and this class. Thank you so much for taking all the lessons here, and we'll wrap up soon with the final video and hopefully get started on your project. 13. Outro: thank you so much for taking this skill share class beyond Excel to. Hopefully you'll learn a lot, and you can see how powerful code is for building tools or the modern workplace. Gone are the days when your data is stored in a silo, and it's hard to pull it out of Excel or sheets or out of sin application. Or you have to export that data into some other tools. Dakota is a single source of truth where all your data can be stored from all your different sources so that you can see an analyzer data and share data with your teammates as they as they need to see the data in the next class. We'll look at some of the most popular docks and templates used by individual freelancers up to a large Fortune 500 companies, and we'll talk about how they work in how they're constructed so that you can use them for your own clothes. So please stick around for a Class three and let me know if you've any questions along the way.