Sign in or start a free trial to avail of this feature.
5. Time-Based Charts
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.
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
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
One of the most common charts you'll create for dashboards are time-based charts, such as the example shown onscreen. 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 Blue Maker, we're 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. To find which month each customer is in I'm going to create a new column called payment month.
I'll then used the text function to convert the payment date into the month in question. The text function first accepts the date, which is simply the first of Jan, and then accepts a string of letters that tells Excel what output form you'd like. For the three-letter abbreviation for month, I'm going to write three M's.
Then close the bracket and press enter. This tells me that the Praxis Corporation has a payment month of January. I can now autofill for the remaining entries. Let's name this column with control F3, and I'll call it payment month.
I'll return to our dashboard input where we'll 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 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 SUMIFS formula So I'll write equals SUMIFS and the sum range will be revenue.
The first criteria range will be status and the criteria will be existing. I've anchored 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'm going to anchor this cell to allow me to copy and paste the formula.
I'll then close the 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 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. Control X to cut.
We'll move to our dashboard.
Control 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.