Personal Journal and Tracker in Google Sheets | Chris Gage | Skillshare

Playback Speed


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

Personal Journal and Tracker in Google Sheets

teacher avatar Chris Gage, Analysis, Forecast and Business Planning

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

4 Lessons (29m)
    • 1. Personal Journal and Tracker

      2:48
    • 2. Terminology and Set Up

      8:53
    • 3. Pivot Table Use

      6:26
    • 4. Easy View and Next Steps

      10:42
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

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.

36

Students

--

Projects

About This Class

Creating a personal journal and tracker in google sheets, with the capability to update online and on mobile so you do not need to access your computer to add your details.

  • Including using forms for smart entry and updates.
  • A brief overview of template methods and formula.

Meet Your Teacher

Teacher Profile Image

Chris Gage

Analysis, Forecast and Business Planning

Teacher

Hello, I'm Chris. I like spreadsheets!

Come and follow my Learn with Chris Roadmap to see what I am working on.

I am an award-winning Head of Planning with a distinct focus on Customer Service.  All in all, I like technology and ways to become more productive and efficient.

I have recently become a published author of Ebooks; as a person with Dyslexia, this has been a real achievement for me. Check them out here if you like my courses.

If you want to ask me any questions or get involved in my community, feel free to join my Learn with Chris Linkedin or Facebook page.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

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.

Transcripts

1. Personal Journal and Tracker: Well, hello and welcome to Learning with Chris. And today we're going to be looking at how to make a personal journal in Google sheets. This is of course just the introduction video. You won't be seeing my face for every video of course, but it's always nice to see how long. So what is a personal journal? Well, by that I really mean something that you can break into. Whether they work really aid, whether it personal life as far belief. And now, you may ask why we then into journal and Google sheets. Well, functionally you don't you can have your Django anywhere many people physically, right? And I do encourage that I actually write a journal and keep an online journal. There's many apps that cost money, that can be very good, but of course the cost money. So why would you use Google Sheets? Well, the interesting feature of Google sheets, if you've watched any of my other videos, for example, is forms and their forms or something you can use from your smartphone, whether that's an Android or Apple or indeed many other devices as well, where you can tape in. It's basically a web address that equal to and you can type in your details. So for example, what's my journal? This is how I feel today, but also as many people are prone to do it like takes some numeric information as well. So what I am going to show you and he's coming videos is how to make a journal and the DAG of your copy of a basic journal, admit how to manipulate some of the information you can use for it. So not just taking notes about yourself and how your day is gone. They also have many overs of you've worked. How many aim I was have you exercised or how many minutes have you exercise? And much entertainment treatments, so forth. Just so you can start getting a run in picture on how your, your DSB. And it really, this may not be for everybody, but this probably would give you the sort of idea or other things that you may be able to do with Google sheets. And obviously that's a recurring theme in our learning. So we will give you this. And of course you can see on the screen to the right of me, to the left of me, sorry, I'm reversed. I've got our personal journal template and they'd just remember, I often don't remind people, did this and I do apologize. Download a copy or sorry, make a copy for yourself. I will be giving you access functionally to what you're seeing. So my actual sheep, but you won't be able to long-term save enough in there. So just make sure you do make a copy and you'll be able to use this version. And just to give you a little sneak preview, we've got a plan on some of the things that we're going to do. We've got a little bit of organization there. Hello test data, and a little pivot will go through that buy up. So just won't be too long, maybe 567 minutes. Parents sued. And hopefully you'll have a working and understandable journal that hopefully can help you. I personally use Google Sheets. I have lots and lots of information and if I'm a data person rather than physical journal person generally. But I do hope you enjoy and I will see you for the next episode. 2. Terminology and Set Up: Hello and welcome back to learn with Chris. Okay, so let's make a start on our personal journal. So then the little introduction you're here because you probably want to record either diary type activities or general D2D activities, as well as potentially a little bit of detail on that. So as I've mentioned before, I tend to record things like how many hours of work tenants terrorist him a family, and probably most importantly, of course family and most importantly, most importantly from a data aspect is exercise ours. So but I don't do enough of. So we've covered the introduction as I see the concepts fairly simple. However, that it can't be a little bit complexity on and making it work the way you want to. So first, I just want to talk about terms and forums. And by terms, I mean terminology and forms are very much mean Google forms, how we do it. So let's just have a look at plot here. So the plat form creation. Now, you probably watched another video from mine or somebody else's, you mean, or this is how a formal lookup will go into a little bit more detail in a second. But foreign Krishna. So for the purposes of this, I've obviously RED meat that's and i have put detail in because I do need to show you and I want to give you something that you can actually use yourself. I also very much encourage you to manipulate what I've given you are indeed start again because it doesn't take too much time. So I just want to talk about the terminology first and then I'll show you how to create the form and at least edit the formula I've already created. It couldn't be easier. So let me just start here. So form creation, timestamp, but know that it's a standard field, you cannot get rid of that. And by field, I mean a column in your dataset, which again, I will show you later. That is literally when you've done something. So for example, I'm filming myset, Dean 49. And so that would be the D8 plus 1549. And I believe even seconds can be extremely relevant, not that relevant for what we are doing. The reason being is because we're going to add a date field, as you can see over here, the reason I've added a deep field, even though there's already a timestamp. It's predominantly because you may miss OD and wanted to do it again. I do that all the time. And you tried to make sure I do it once a day, but some things to do two or three year after three or four days because I forgot to do this mid. And I was trying to think of options that you may want to use. Mooc seems like a really relevant when I actually number myself and how we're feeling are 1-2-3-4-5, 1-2-3-4-5. I've changed this a little bit. I'll show you rather than tell you, but this is mid journal entry, this text, this is a long field entry, will you he literally right in your diary notes here. And what colors? This is a physical number. Relaxed hours, again, terminologies, just what I've named this, you can edit this, I'll show you how to do that. You can get rid of it. I'll show you how to do that. Relax though. For me that you know, whether it's watching TV, I like to watch football. Soccer. I play the old computer game, and I do things I've s For me, that's relaxed dollar exercise our speaks for itself and family hours. That's when I'm specifically spend and finally tame. And that might seem too much. And it probably is for a lot of people. If you don't want to make your own forms, you can just see all these numbers and there'll become irrelevant. So if you don't wanna make it on one, but want to use this, please feel free. Okay, so this is what the actual physical form looks like. So let's go to it. This is the form I have created earlier. As you can see, it's a direct replica of the form that's copied and pasted that note. I just want to very quickly show you how you would go about making this. So you've got to form, sorry, you've got the tools. If you've already made a form, you got to edit form under form to create a new form equal to tools. Create a form. And there we go. And as simple as that, we didn't taste foreign. And I'll just add a couple of questions just so you can get the idea. And let's do the one that's the most obvious. So that's deep. Mlk choice. We don't want more choices is choosing what we know. There's some more complex ones, but there's also some fairly obvious for us. So we will choose d and that will then give a field to enter that he can't enter that Worster aiding. But it's fairly obvious it's a standard calendar button is most definitely a required field. Okay. And let's add to one more question or question we'd really like to do it. Let's do it. Because that's probably one of the more complicated questions. So what's your mid today to be one multiple choice? Do you want check books is do we want to drop them? Short answer? Well, Realistically I've equivalent dropped things because we can't have two bits. So just, oh, it's done not nascent easily for me there I taped in one and a given option. And there we go. There is R for that, of course you can add more details and that need to be very clear you want to make, if you're gonna start a skin, you want to make it form forced because all your formula that we'll go over to show how you can look at things visually will be based on columns and the lake that do change if you add or take away things from the forum, and I'll touch on that later. So there you go. There's your functional form. You can also customize the theme. Let's make it blue. We call test for me. You can also choose an image. Maybe you wanna put pitch to your family. I have a picture of my daughter in there and you can make it a little bit more formal. So there we go. There's a form you don't need to click ST SEN just gives you there the wavelength to it. Let's close up. And as you can probably see here, there is a new tab. So I'm going to be named that you will actually see this physically because you'll be accessing the sheet worse you doing the training. And there we go. So you can see it's come up in the order that we taped them, timestamp, which is the standard field as already mentioned, the deet and the MIT. So let's go to that life form. That is the life form and that would be the web address. So something I2. And this is the real power of sheets coming out here, is I put that web address in on my phone, on the Chrome, for example, or what's your Safari or wherever you decide to use and save it onto my, my home screen. And it becomes like a little up and it opens up perfectly and up whether it is iOS or Android. So I really use this as an up rather than anything online available. Really gone when. Okay. So we're seeing that just a day and I was in one Lucy as a terrible PIT. Submit. Okay. I could submit more responses. Of course, I'm not going to go right now. And here we go. This should come in. That would go very, very quickly. Timestamp. So exactly when I did it, that's the date that I'm actually referring to from a journal. And that's my MIT. Of course, I've went down a little bit more detail. And indeed equal to this page. Please don't pay too much attention to this detail right now. But if I wanted to edit the form more, I can add n here. So some things you can do that won't really break your former infinite. That is, maybe change the spelling or change. You don't want to read rearranging questions, and I'll show you very briefly why. But for example, if you really wanted to put work covers before relaxed, I wish you could change that there. And indeed we've put responses in here. You can't see responses summed up here. Obviously, there's not many responses here because it's just a test one that I've developed for this. But there is actually some very, very nice details you can get from this forum injury will cleave that they're just know. So just very, very briefly, I want to show you the data that's on the speech. That's the questions. Ignore the data. This is all just fake data to energy can probably tell mood, test, working hours, relax, diverse together, say, and this is not my DD. There's famous test. Once you've got your form set, you really don't want to be edited it. So just be very, very clear that this is the form you want. Or at least don't do too much before you change. If it's a week, you know, it's not the end of the world, but if it's a years or if information, you probably don't want to be changing things around too much unless you wanna start again and keep your old data. So as I say, this is the details that we've decided to go with. So it's functionally, that is the terms and the forums. Now, this is very simple and I probably covered up maybe too quickly for some of you are not quick enough for others. I would very much recommend looking at my other videos, maybe go into a little bit more detail around Google Sheets and R&D. Just ask questions in the comments. That's perfectly acceptable and I will do my very best to answer it as I'm sure other people well. And we for the sake of this episode, we will finish on the next episode, we'll have a little look at creating a pivot table from the data. And we'll do that before we make any sort of fancy visuals. Then in next episode after that, we'll look at visuals and we'll just talk a little bit about the updates you could make on a Reviews only four episodes as CDs. But just so you can have a little look at this, and we thank you very much and I'll see you for the next one. 3. Pivot Table Use: Hello and welcome back. Okay, so we're on our personal journal and we're going to create a pivot from the data. Now we briefly glanced at the data. As you can see here, it's really just a lot of tests with some random numbers at the moment. And the reason I put this much detail and is it so I can show you some visual output, which will be in the last step. So however, we're going to look at making a pivot table. And this isn't a detailed video about making pivot tables. However, I will briefly show you how you would go around it and then I'll show you the one I have already created. Now. First of all, I want to answer the question that may be asked by some of you why we don't make a pivot table? The short answer is it's probably the easiest way to see what you've written in your journal at any given time. Although we'll make a visual output is far harder to see or your diary entries or journal entries in sort of a visual aim. I ph, however, I will talk about some of the things that we can do to show those sort of entries in the next video. But first off, if you want to make a pivot table and you here, you may be familiar, at least with the concept. It's actually very easy, easier than Excel, a potentially not quite as powerful as Microsoft Excel, but simpler. So you got, you select your columns, sorry, E or E. It doesn't really matter. You got the data and you go to Pivot Table. It's telling you what the ranges now you can't do it the other way to range can call DEA pivot table and then select the data. I find it easier this way. We want to insert it into a new sheet. Generally that will be what you do. However, you could insert into an existing sheet that's probably for more advanced purposes then we would generally using these videos, but feel free to play around with that you can always undo. So let's just make this for the sake of this. And we will rename this very quickly. But you don't necessarily need to do. But for the sake of this video, okay, now, I've already renamed something Test. Test. Okay? So there we test pivot. Now, straight away, Google sheets will suggest a number of things to you. And it doesn't really matter for the purposes of this video because I've already created one, but let me just show you something. So we probably want things by rows. So we want dates and would probably gone to one journal entry. And we don't want tools on either of them. And we're going to want that. So not very clean. It's got to be said. So we're probably going to want to do a couple of things to clean this up. And this is just to give you an idea so I wanna wrap the text. So I went the former, I've selected the column, the former, and I went to text wrap in which refers to how their textbooks. And I went to wrap. So they're all going to stay in one color like that. Now, in retrospect, I oops. We've just jumped. Let's go back. Retrospect, I probably should have used other words than just test because it makes it really are critical. But that's how it is. So then we go back as a pivot. I'm not going to focus on this because Pivot Tables are covered in many, many other videos, but many other people in detail. But functionally, you can take away, I would always suggest using something like D8 as your predominant role. But you could use something like workers. So there's more work covers. I don't have totals for that, but I can see on the first of April, I realize I've got this new key standard format, but of course you can change up the US as well. I've worked the hours there. There's ways to make this easier, which will know lookup the pivot I've already created by, I would suggest you just have a player educationally brick and if in a pivot table, because you're not really doing anything to the core detail. You're only playing with the output of it. So have a mess around half a plea and maybe see what you can do yourself. But here's a jumbo pivot which you already have access to. And I've created an discipline. So what is this one? It's similar principle. What I've done is I have created some filters, so I won't turn it off, but felt was created. So it's just deep descending order. So you can see here. So the newest first basically. And indeed I've added this year, which is a slicer. So you can choose specific Dietz. Again, have a play with this. We have covered this in other videos, but you can do details. The it's before or after. There's a number of things you could even look at text and so forth. So what this is showing me on the pivot table is on the third of August. I felt good. My entry was tests that I worked for five hours, which is probably why I felt good because that's not a very long day. As you relax for five hours and then one hour of exercise. And I spent five hours of them type with my family. So I'm seen 16 hours. So I'm presuming a slip fully. And I'll show you some of that individual. And that is probably the best way to be able to look at your diary entries. And then there's other things you can do. You can take out this detail yourself and just have a look at. This is really personal preference here. It's quite easy and it's quite powerful. I've already set up the rules. And I would suggest using just rules for this. And you can add sort of totals and things. You may wanna do that work hours, but that's going to add sort of a lot to your pivot table. And the pivot table can get a bit clunky looking. I'm not a huge fan of how this looks, but that is how it comes through in Google Sheets. You can do a little bit more and Microsoft Excel then this, however, for my purposes, I've anybody really leaped my journal entries. And I generally go back 123 Mbps and think, oh yeah, what was happening on that day? So that is how you put your detail in a pivot table and how you look at it. And of course there's the test pivot. And indeed, if you're using my sheet whilst you're doing this tutorial, play around in this pivot table, add some values, filters, just see what you can do. Pivot Tables are very, very important in both cookie sheets, Excel and indeed Lieber office or anything else really. So if this is and the subset of D to free to play with fairly understandable of a mixture of numbers and characters are the characters. This is probably a good one to have a clear emf. So create a pivot table. We've done that. Ok. So we'll end this little video here. In the next video, we'll summarize a little bit at the end, but also just talk about formatting for dia and easy viewings of AMI and potentially father upgrades do anyway. I will see you for the next one. 4. Easy View and Next Steps: Hello and welcome back. Okay, so final video on our simple personal jungle in Google sheets with forums. So what have we covered? Covered the concept of it. I'm sure by now you know what we're doing. And we've covered terrorism forums, which is functionally what are, what is the terminology Basically what it's the questions that we're asking and what does the form look like? There are also covered creating a pivot table, which we've done here. And indeed here is for you to play around with. And this is really turning this data into something a little bit more manageable specifically for looking at your journal entry type data. May finally missed last episode, we're gonna cover format data for easy viewing. The cleaner sentence in the wild, that basically taking the data and turning it into something you can client-side and see basically how you've been doing. And which seems a very odd thing to see, I've found this very useful and hopefully not too, if you will as well. Also some updates you could do yourself in the future. And just a review, basically what we've done. So let's just jump straight to it. Organized tab. Here we go. So first off, you've noticed there's a few charts, I'm sure, but may seem a little odd. But many of us, and I imagined people who have come here to do a journal in Google Sheets probably want a little bit of physical output to their jungle rather than just that sort of, albeit very, very good for you. And especially for mental health books that you may writing or app that you want to also see that d, a. So this is what I've done. There is just a very, very small setup. I personally have quite a lot more output than this. And I would encourage you to either just use this because this will function for you or indeed make your own. Okay, so first off, there's just two links. Here. I won't click them, but basically this link will take you to the actual form, which I've already filled in just so we can see one in November because actually created this tutorial and August journal notes which will just take it to the journal pivot, which we've been after few tips. Okay, so what are we looking at here? We're looking at a small data table. Now, I'm not gonna go into massive amounts on what this is in terms of these formula. I have covered that in other videos. And if you want to look at them, they're also in skill share. So please do that. If you want to have a quick glance at this, ignore initially the if editor. So average Fs, this formula is really about welling, kinda speaks for itself, I think Looking for an average beast on Fs. So an average based on some questions basically. So it's looking at the journal based data, and it's looking at Column III. And it's looking at the column b is greater than and equal to DNA D9 isn't this? So the name is very clear. The April 2020 and is looking at column B again, and is it less than me? So what making quantified from that very easily is, is it in April and is it before me? And it's looking at column B and The Journal, which just so happens to be the deep. And many people would probably just use column E. I am just a weirdo. I sometimes miss the Audi, so I always add the d is a question. You may not be as ridiculously bad that that is me. These are all the same but based on slightly different columns looking at, for example, relaxed, our exercise hours, family hours. I'm not unaccounted, is basically total knees up and taken away from 24. And also just a small fn near if there's no details. So what I've done here, this error is basically if an arrow pops up, which would happen if there was no data, it just makes it blank, and that is the signal for Black. So after you write all this average, if you can very easily write an if editor open bracket, all the same stuff. And then after they're the normal formula, just do a little comma. And then you've got, you can use an affair if it hurts. Actually, arguably one of the most powerful formula. And either Excel or Google Sheets or anyone else similar. It's very, very handy. And we did remember that one if you can. These are almost identical to the average f's, but that COUNTIFS. So it's the same principle. Looking at a certain column. If it's greater than and equal to the deep, as you can see, it looks complex, it's not, trust me. And also with an added element of its looking at column C, which is the mid and are physically typed in. Fantastic. And asked them the same formula again. So as you can see, it's just the same formula again, but changed that to good. The reason again is because I've put them into these sort of categories. Positive, good and fantastic, negative, bad and terrible. Hopefully not too many terrible's and neutral, bulky. You may very well do that definitely Indeed, if you use one of these 45s, it's going to be a lot easier. I just wanna show you what you can do. So functionally, that's all we've done in the DIA table. So let's just have a little glance at what these charts are showing us. This is just showing us attain so unaccounted. And I personally just call it a sleep, but you may want to call that something else is generally averaging and unfortunately I don't get that much sleep. I'm worth about five or six hours ago. But there you go, undetected and see your family hour's exercise owners, unfortunately, that's more accurate. I'm relaxed hours and what coverage you need to decide what they really mean. That there is a nice-looking low chart to show how its went month a month. Of course, I've done a November, one of which was just six hours in each, just so you can see it very, very clearly. This is just a nice little chart just to show how it's moved and changed over the mumps. Now obviously this isn't real deal, this is in close to real data. I've maximize this chart because I think so it looks quite nice. And just to give you an idea of what that means. And so that's the chart editor. You just click here, you've got to edit chart the ranges and every fund. This isn't about making charts, combined ranges and so forth. And you can do charge style in here. So customers chart style and you can maximize. And that does that. Just a very, very briefly show you something in case you don't really need to charge if you wanna make a chart, the easiest way to do it. Select the DNA like that, and click on insert chart. And there we go. Now you've got a lot of editing to do and to make it the way you want. But functionally that's all you actually have to do. We won't leave that they are due. Well, I'd suggest is you just have a look at how I've set up the charts and maybe play around with how they look. So let's talk about this average of family ours by MIT. Very important to me, this is something I actually have. Thankfully, I've come very happy young family and hopefully all you do too. But I just want to know average family. I was by mid in this test, it wasn't deliberate bit. And average of family always by mid, good is good. So an average of work covers by mid. Interestingly, a probable your mind, the more work hours I probably feel bad rather than good. But that, that's what's worked in the test data. These are just examples of what you can do with the data as an AMI output. Now, just to sort of wrap this up a little bit, other suggestions I would have for you. Here is potentially some formula with flaked similar to this, although maybe just typing in the DPP. And it would bring up your journal entry for that D. That would be quite an easy formula. It would be very similar to these. And you could bring up the journal entry for maybe the month, maybe you'd have all the days in the month and the journal entries come through there. I tend to write quite a lot, so that doesn't suit me too well. So maybe it would just be one or two days she would have. But that would be quite easy to do if you're interested in that. Do leave a comment on these videos. I can certainly show how to do that. I hadn't intended on doing that in this video because it's a little bit more complex and formulae, but it's not out of the realms of what we can do within this scope. There's also other things. Some of these may not be relevant to you. Personally. I have these two, but in a different format. This is the mean one. I look up and this is just another variation on it really. They're the ones that I focused on. I'd like to see I'm a month a month. Obviously, a lot of people have suffered during these lockdown restrictions due to D2 and TB and a horrible year basically. And personally hasn't. I live quite remotely in rurally with my family and things have gone well, but that's not always the case for everyone. So I think it's quite important for you to look after your own mental health psychologists or psychiatrists. Please don't take anything I'm seeing to be medical advice. But this sort of chart helps me because it's all very well. People might look at this and think, well, why do you need to do this char for your own mood from April 2020, where you don't, but I don't remember how I felt in me 2020 and that was only a few months ago. And of course, you can just change the dates here as long as it's monthly deed. So if you keep this for five years, you can very easily and systematically changed this. What I'll do is I'll do an update to this with some more advanced formulas in a separate video for a little bit more detail. Because although this is one use for this sort of thing, personal journal, what I actually use it for personal database, which really means a journal. But plus, when I also applaud the things like, well, not so much peace slips, tax information as well. And I use that because otherwise I run my own server with the obese, actually faint Google sheets for the smaller things like that that don't need huge computing power and it's actually much better and it is much simpler. So that's where we are. So as a review, we've discussed what we're do. We've discussed the terms. We know how to make a forum where given your test pivot and you've also got pivot table, we've looked at the format for ease of viewing. I really just wanted to give you ideas what you can do for yourself. Of course, use what I've created, feel free and I encourage you to meet more basic updates and indeed finish review. So hopefully that was helpful folks. I tried to make these quite short and sharp and you just saw there hopefully helpful. I can see some of the comments and e-mails have had from students. Absolutely terrific. Really appreciate that. And this is obviously something that don't do full-time. I just want to help people. I'm our head of planning in the real world, which means I spent a lot of time in D OBCs. But I like to make my life a little bit easier, simpler, and cost-effective. And I think Google Sheets and Google Forms specifically, it very good for that. If anyone's got any suggestions on the tape of video or type accretion, but we'd want I'd be willing to do a custom video. And I'm not talking for feasible infant just through skill shear. And let me know and we'll see what we can do. Just remember to make yourself a copy of this template and of course, asking the questions in situ that you meet. We thank you very much, have a good day and I'll see you for the next session.