79% OFF online library of tutorials for mastering Google Sheets
This tutorial is not available for free users. Please purchase a subscription to view this tutorial.
Hey there stranger!
Please sign-up or sign-in to watch this tutorial.
Create an Auto-Update Sales Chart: Trailing 12 Months
About this Tutorial
And the idea is essentially we wanna have this chart. based on the information here, and we're gonna be filling it out on the bottom right. So if your workflow matches this, right, you have a, a ledger of sales data every month and you just want to see the last 12 months, right? You just wanna see the trailing 12 month sales on one chart.
You can do this on separate sheets, you can do this on the same sheet. So essentially, I put this all in one, but you can actually put this on three separate sheets and. Refer to the other sheets. So that's how I would do it. I would say, Hey, input the sales data here. Then on another tab I would have this work chart or work table.
And then on a third sort of dashboard or summary, I would add this last 12 month sales. That's how I would do it if that's the only thing I was getting, getting data on. But how we're gonna do this right here, I'll show you how this works. So I'm gonna add in, say another, let's say we had a really good memory, we went back to.
Original great months and we, I'm gonna just hold down that, that's not right. Pull down that. So we'll get the next month and I'm gonna enter that. And so now if you notice here, it already entered that number here, so that changed. So let's do that again. I will, let's just do this. I will down this and see now we changed.
So if we entered the numbers like one, two, wait, this is even the wrong year. Oh wait, it is the right year. It's, sorry, it went over December. Okay, so 1, 2, 20, 22. Then we type in one, no, two, sorry, January, February 1st. Then we're like February. What is the end date? February 28th. February 28th. And let's put in 5,006.
So that changed in this format. Just needs to be formatted to number, actually, you can just click up here, format this entire column, but there we have our really nice month. Maybe it wasn't that nice of a month. Let's go back to two and see that. Automatically updates. So how does it do that? First, well, the idea is we create this trailing 12 month table and we are gonna get this automatically with a couple of interesting, like index.
And we're gonna use count all and just create this stable one that looks at this, this table and says, what's the last 12 entries? That's essentially the idea here. So how are we gonna do that? Well, first we need to know how many rows there are. There's 21 rows. What's interesting about Count All is that even.
You had say this table wasn't manually inserted, but maybe it's a accumulation or a collection of a bunch of other things and we go equals some of, you know, a two day, three of some other sheet. Right. And if it is zero, it's going to, it's going to show up. If there is a number here, it's absolutely gonna show up.
If it's like, Hey, if is blank and we'll say B 22, then do nothing. If it's blank and do something, do the sum. If it's not, see now this doesn't have anything here. Even though there's a formula here, as long as it is essentially null, meaning there's nothing there, not even a space, not even a. You can't use something like conditional formatting here.
You have to just use literally nothing instead of zero. But that's interesting is that now this count all is counting all here, but it's saying there's nothing here, so we're not gonna count it. And you can change that. And then maybe you put in the date and you go 3, 1 20, 22. And then when I did B 22, so then now we get, let's change.
that to the, once we have some information here. Wow, that's a big month. But , let's just do a two. There we go. Or no, not even a two C . One. C2. That's what I meant. So see now we have, this doesn't show up until you have something. Columns, same idea. If you have, like, if it's maybe instead of if it's blank, maybe if it's zero, don't do anything.
You can also do that. But that gets us account of all of the columns. Then we want the last row, and I'm using an index I'm saying index A to C. And the, it takes three arguments. It takes the reference of like, what do you want to look. Then it's asks what is the row you wanna look at? And then what is the column you wanna look at?
We know the column is the third column. We'll want to get the sales data. But the row is changeable, fungible. It, we, it's programmatic, right? It needs to be based on some formula, which is this count all. And I'm just saying F1 here to say as this changes, this changes. And if you notice, if we delete this now, it says 21 and it gets the last row, which is 26.
If we want the next to last row, we just do minus one. We do index of count, all minus one. That's it. And so essentially we can get the last 12 by doing minus one, minus two, minus three, minus four, all the way to 12. And so we create this table we create a table that gives us not just the sales, we want the start date as well to get the month.
And now this 12 items, or actually 24 items. gives us, now we take this chart of this instead of doing a chart off of here, we know we only want the month on the sales. So what I've done is I grabbed the start date and then I've changed the formatting here. So all I did is go up to format. Actually I'll do it to this one here.
I'll show you how I did that. Format number, go to more formats, more date and time, and then I choose the, the month and the year. And it shows me month and year, I can do it. Maybe two digit year might be better.
And so now we can do either full year 2021 or April 21. And now we get, I'm gonna just copy and paste this formatting. Oh, I don't want to do that actually. I just want the copy, the format. So I want to go Copy, right click paye. Special paye. Now they're all the same. So if you notice in here, this is index of A to C count all C to C minus 11 is the, essentially the el, what is that?
The 11th? No, minus 11 is the 12th one. So the 12th from the bottom is this one. So that's March, and you get March, April, may, June, all the way through. We can even, let's say we don't have 2021. Let's say we only have, what's 12 months here?
Let's just delete a couple of these
and we see it automatically updates, right? December, 2020 until November, 2021. It has our sales here. And then this chart. All I did for this chart, actually I can show you is I highlighted start date in sales. Chart and we're gonna pick a bar chart here. Yep. Bar chart shows the dates. Pretty simple. I made a couple of changes, like I took off the borders just so that it looked a little cleaner.
You can change the colors here. Anything you want to do you can change the name of it, like you could say last trailing. 12 months. And now this is a, let's delete that one. And this is an updating chart. Every time you update your sales, you'll update this chart.
So to review, what we did was we took a simple data chart or table start date, end date sales. Figured out the last RO row by using count all. We then indexed that we got that last row by using the index, and then we got all the, the 12 bottom rows by doing index and using the row count all for the row, minus one, minus two, minus three.
We created a table here. This is index and count all, and I just went and hand typed all of these negative minus 11 minus. The column is the third column here. The this column is the first column, so I hand typed all these and then I just took a chart of all of these and I got a automatically updating chart as our trailing 12 months increases, month to month.
Hope this is helpful and please feel free to email me any questions. Bye bye.
Better Than Happy | Redesign of The Feelings Wheel
Best Header Font Ever
Learn to Love Your Sheets
Sheet Review! 150 Active VCs by LemonIO
Anika Asks: How To Set Text Overflow All The Time
Introducing: Brutal Calendar
Add a Checkbox to Turn on Dark Mode
Create Drop Shadows! This makes your dashboards pretty.
Merge Cells for Dashboards
Dark Mode / Better Font Color
Better Font Colors
Magical Things You Can do with Checkboxes - Google Sheets
Export Your Beautiful Sheets to PDF
Consider Labels as Opposed to Headers
Add Icons To Your Sheets With a Domain Name
Coloring Cell Blocks for Others to Enter Data
Great Sheets! Corona Hiring Sheet
Great Sheets! Community Information Board by Seedtable.com
Roast: Hotel PPC Channel Cost Calculator
Better Header Fonts - Best Fonts To Use In Google Sheets
Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell
Basic Keyboard Shortcuts To Speed Up Your Productivity
Basics - 5 Ways to Change Row Height
Anders Asks: Can I Highlight Whole Row if Certain Columns have text?
How to Refer to Other Cells - A1 and R1C1 Explained
Change the Default Font
Biggest Flaw In Dashboards with Dark Colors
Basics - 4 Ways to Change Column Width
Basics - Structure of a Sheet: Index() Row() and Column()
Communicate Better with Gridlines, Border Styles, and Border Colors - Google Sheets
Use Cmd + Y To Do It Again, and Again, and Again
Create an Auto-Update Sales Chart: Trailing 12 Months
Google Sheet Basics - The Absolute Basics
Secure Your Sheets by BetterSheets.co
How To Create An AutoFill in Google Sheets
Build a Thermometer for Savings Goals
Make Your Lists Spicy Hot in Google Sheets
Restrict Access to a Cell if Another Cell is Blank
How to Use Smarket
Combine Data from a Tab and a Totally Different Sheet | ImportRange and Curly Brackets!