Excel Video 134 covers two tricks I used to make the scrolling chart work, a formatting trick and a formula. The formatting trick is easy once you know how custom formatting works. Follow along with the video to see how to get into the custom format screen and then watch how mmmmmyy is the custom format to get Excel to just show the first letter of the month followed by a two digit year. Once you’re familiar with custom formatting, there’s a wide variety of ways you can format your numbers and dates.
The formula is a little tricky because it involves three functions. The ROW function just returns the row of the cell you give it. In our example, ROW(A2) gives you 2. ROW will help us keep track of which row we’re on and which rows we want to shade on our chart.
The AND function allows you to enter a series of arguments, separated by commas. If all of the arguments evaluate to true, AND returns true. If one or more of the arguments is false, AND returns false.
The IF function evaluates an argument, followed by what to do if the argument is true, followed by what to do if the argument is false, all separated by commas. In our example, IF looks to see if our AND function is true. If it’s true, it copies the collections number in column B. If AND is false, it returns zero. By cleverly setting up AND to be true for 12 months based on our scroll value in E1, we can get 12 months and only 12 months of data in column C, which we’ll start charting in our next video.
