1. Loan visualizations
In this lesson, we'll be taking a brief break from looking at the formulas to look at some of the aesthetics of our dashboard.
A good visualization will help show relationships between balances and time which might not be immediately noticeable just looking at the schedule.
2. Available visualizations
There are a large number of visualizations which are potentially available, including line charts, bar charts, scatter plots, histograms and many more.
In this course, we will be mainly focused on the line and column charts to display the change in values over time.
3. Line graphs
On an amortization schedule, a line graph is typically used to show the change in values over time.
Time is always shown on the X axis, and can be either dates or periods.
Values are shown on the Y axis, and show balances which are changing, such as cumulative principal paid or current balance.
4. Stacked column charts
On an amortization schedule, a stacked column chart can also be used to track values over time, but it is more focused on the relationship between interest and principal.
Like in the line graph, the X axis should always be time.
For the Y axis, the graph details the relationship between interest and principal as the loan amortizes.
5. Creating a visualization box
To create a visualization, first you need to click on the insert chart option on the menu. It's important to click on a cell which doesn't have any values, or it will create a default chart.
This will bring up a box which will show No data. Click on the 3 dots in the top right corner and select Edit Chart.
Next, you can change the chart type. For our example, we'll be making a line graph.
6. Adding data
To add data to the visualization frame, you need to click the green icon at the right of the Data range option.
From there, you can select a data range. In our example, we might pick the cumulative interest paid. You need to select all the values in the column, as well as the title.
Next, you click on add another data range, and repeat for the rest of the columns you would like on your graph.
7. Missing elements
At this point, we have added our data, but the graph is far from complete.
The graph is still missing a title, and none of the axis are labeled.
8. Adding titles
To update the titles on your chart, you will need to enter into the customize menu.
Within that menu, select chart and axis titles, and you can enter the title text for your graph.
By using the dropdown menu, you can also enter titles for your vertical Y axis and horizontal X axis.
9. Adding the X axis
Adding an X axis is almost identical to adding any other data set.
You need to click on the icon in the X-Axis and it will bring up an option to add data, just like with adding all data.
Select all the data for either your periods or dates from top to bottom, just like when you added the cumulative values.
This will add the dates or periods to your graph, and you will have a full, functional visualization.
10. Time to get graphing!
This was a very quick overview of the visualization capabilities of spreadsheet software.
You'll be working on creating your own visualizations in the next lessons, but if you'd like to go deeper into visualizations, there are full courses available for visualizations on spreadsheets.
Enjoy!