The Story Beneath the Values Using Excel Charts

In this article

My first impression of Excel is a visual of rows and columns of numbers.  Without question, Excel is an excellent number analyzing application.  In addition to the rows and columns, is the powerful feature of charting.  Using Excel to transform those rows and columns of numbers into the clear visual of a pie chart or column chart is what makes Excel so useful in multifamily property analyses.

Visualizing Your Values

What are charts, other than a visual representation of numbers and their interactions?  Need to spot a trend or find the meaning behind a series of numbers?  Excel’s charting features can address those concerns.  With a pie chart that displays the operating expenses for a multifamily property, makes it very easy to see which expenses are larger by viewing the size of the pie slices.

The above example gives you a choice between numbers and a chart.  Some people may prefer looking at the actual numbers, while others find the chart more appealing.  There are times when just the numbers would be appropriate.  You would definitely need to know the actual amounts of the expenses that are going out each month or year.  A quick look at the more costly expenses can be easily seen with a chart.

Chart Types and their Purpose

Excel has 11 types of charts that can be used to help visualize your numbers.  Within the 11 types there are various styles that can be utilized.  With so many types and styles, which chart is the right one for your values? The answer will be based on what you are trying to highlight, convey or compare.

A brief discussion of each of the 11 chart types will give you some guidance in choosing the best chart for your data.  The picture below shows 7 commonly used charts.

Column

Most are familiar with column charts.  The height of the columns represents the values.  The vertical axis is the value scale and horizontal axis shows the plotted values.

Line

If you need to highlight the trends in your values that change over time, a line chart is your best bet.

Pie

Have a small number of values that you want to see proportionally to a whole, use a pie chart.  The slices of the pie are easy to see if you have no more than five or six values.  More than six values are allowable when there are one or two slices that are much larger than the other slices.

Bar

The bar chart made its first appearance in the 1700s.  Most people would say the bar chart is just a sideways column chart, take a column chart and rotate it 90 degrees to the right. But there is a major difference between the two types of charts.  In Excel the bar chart does not have a three-dimensional or layered style, as the column chart has.

Area

Area charts are not used as much as the aforementioned charts.  Think of a line chart, then fill in the area below the line, that is an area chart.  The area chart highlights the magnitude of values rather than their change of time as with a line chart.

XY (scatter)

A XY chart is also known as a scattergram.  This chart is best used when you want to show the relationship between two sets of range values.  You are looking for a trend within the dots among the two range values.  The scattered dots would generally not be connected with a line. XY charts are used mostly in statistical, medical and scientific spreadsheets.  Real estate investing would not use XY charts.

Stock

Stock charts are charts specifically for stocks.  These charts show how a stock value changes over a series of days.  Each stock chart will show values for a single stock.  If you are investing in REITs, you would need to understand what is involved in the creation of stock charts.

Surface

If you have ever seen a surface chart, it looks very impressive.  Surface charts look like the hills and valleys of a topographic map.  To create a surface chart requires a relationship between three sets of values, the X and Y axes and a third Z axis that represents the height of the values of the X and Y coordinates.

Surface charts are nice to look at, but thank goodness, real estate investing has little if no need for this type of chart.

Doughnut

The name “doughnut” chart is a perfect description for this chart.  It is similar to a pie chart, with two differences.  First, it has an obvious hole in the center.  Second, the doughnut chart can display two or more sets of values, whereas the pie chart can only display one set of values.  The different sets of values create concentric circles in a doughnut chart.

Although you can have more than two sets of values with two resulting concentric circles, be careful with more than three sets of values.  Doughnut charts are one of hardest charts to explain to other users.  But if you are adventurous, give a doughnut chart a try.

Bubble

Remember the XY chart with its set of dots?  A bubble chart is similar to the XY chart; the dots are not the same size.  Another set of values in addition to the X and Y axes must be present to determine the size of the dots.  That makes a bubble chart equal a XY chart with varying sized dots.  Bubble charts are not widely used in real estate analyses.

Radar

If you are not involved with statistical analyses, you most likely will not encounter radar charts.  Radar charts resemble the radar scopes seen in old-time submarine movies.  Each spoke represents a set of values and all spokes radiate around a center point.  Another set of values will have one point on each spoke.  The points are connected by a line that will form a closed shape.  Radar charts are another chart that real estate investors can do without.

Going Beyond Simple Charts

The aforementioned charts are the standard charts within Excel.  As you become more comfortable with Excel’s chart features, you can create charts beyond those simple charts.  Below is an example of a line chart with an interactive feature, it represents a portion of an amortization schedule.

The chart title shows the loan interest and principal amounts.  The chart title changes as the red diamond markers are moved.  The scroll bar beneath the chart allows a user to slide it from side to side to pinpoint the payment month. The current position shows the 40^th payment month with the Interest as $2,738.61 and the principal at $420.48.  This example shows how you can take a large amount of values and represent those values as a chart.

 Excel charts can tell a story that lies beneath your values.  Your chart should draw out the most important data, trends or comparisons.  After all, the chart is only as good as the data it is based on.   When faced with a large amount of data, utilizing a chart can help pinpoint what is truly important.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.