5. Time-Based Charts

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Time-based charts are one of the most common types of charts you'll create in a dashboard. Here, we'll learn how to split annual revenue by month.

Lesson Notes

4 steps to calculating monthly revenue

1 Create a new column in the dataset
2 Use the text function to extract the correct month format from the date
3 Return to the backup sheet and use the SUMIFS() formula to calculate monthly revenue
4 Create chart based on monthly revenue and paste into dashboard

Some alternative methods

- Use the MONTH function instead of the text function in the newly created column
- Include the TEXT function in your formula (note: this makes your formula much more complicated)
- Create a Pivot Table and segment the revenue by month

Keyboard shortcuts

CTRL + SHIFT + : Select all cells within data region
SHIFT + : Select next cell
CTRL + F3: Open name manager
ALT + N: Create a new name
ALT + N , C: Create column chart
CTRL + X: Cut selected item(s)
CTRL + V: Paste selected items(s)
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
ALT + N , C: Create column chart
F4: Anchor cells

Transcript

One of the most common charts you'll create for dashboards are Time-based charts, such as the example shown on screen. These charts typically require an analyst to find the Total revenue by week or by month from a data set. Time-based charts help us understand what time of year customers typically buy our product so we can budget for and market to them accordingly. For Bloom Acre, we are going to create a bar chart that shows the total existing and total new revenue for each month in 2013. Returning to our sales data set, we have a column called date which is the payment date for each customer. Defined which month each customer is in, I am going to create a new column called Payment Month. I'll then use the text function to convert the payment date into the month in question. The text function first accepts the date which is simply the 1st of Jan and then accepts a stream of letters that tells Excel what output form you'd like. For the three letter abbreviation for month, I am going to write mmm's then close the bracket and press Enter. This tells me that the Praxis Corporation has a payment month of January. I can now auto-fill for the remaining entries. Let's name this column with Ctrl + F3 and I'll call it Payment Month. And I'll return to our dashboard input where we build our chart. Here I've created three column Headings, Month, Existing and New for our data. Month will simply be the three letter abbreviation of each month. So I'll type Jan and then autofill all the way down as far as December. We do this by dragging the bottom right-hand corner. Next I'll find the existing revenue for each month. To do this, I'll use the SUMIF formula. So I'll write equals SUMIF and the sum range will be revenue. The first criteria range will be status and the criteria will be Existing. And I'll anchor by row this cell. I'll need to do this because I'm going to copy and paste this formula when it's written. The second criteria will be payment month and the criteria will be Jan. And again I am going to anchor this cell to allow me to copy and paste the formula. I'll then close a bracket and press Enter. I can now copy and paste for the remaining cells. We can now create our chart by selecting our data Alt + N C for a column chart and I'll select the stacked column. After some formatting changes I've made off-camera, we're now ready to paste our chart onto our dashboard. So Ctrl + X to cut, we'll move to our dashboard and Ctrl + V to paste. Again off camera, I'll line up this chart with our previous waterfall chart and our task is now complete. Although there are a number of ways to build these time-based charts, some of which I've included in the show notes, I find creating a separate column for payment month makes the formulas a lot easier to build.