In this purr-fectly delightful blog post, I’ll guide you through steps to meaningfully depict ranges of values in Excel, all the while crafting irresistibly cute charts—no graphic skills needed! You’ll discover a few charts showcasing upper and lower values, Means, and custom markers. Using data such as the lifespans, weights, and prices of different cat breeds (especially those majestic purebreds), I’ll illustrate how to use a logarithmic scale to pounce on precision. Get ready to dive into a world of feline cuteness that’s informative and adorable 🙂
📊🐈🐈🐈🐱🐱🐱📊Scenario: I have a dataset of cats and life spans by breeds. It includes breed name, and how long they typically live given as x-y years. For example, Abyssinian life span is given as 14-17+. I also have data on weight ranges for each breed and the prices of the purebreds.
Objective: I want to create charts for life spans, weights, and prices. For example, for the life spans chart, I want to show each selected breed’s life spans with the upper and lower limits of the range, as well as the Mean based on that range. Specifcally, I want breed names in x-axis and for each breed, I want its y-axis to show two points: one lower range of life span, another the upper of range of life span; then connect the points with a vertical line and show the Mean life span marker along that vertical line for each breed. The idea is to create a chart that’d be laid out like this:
In order to achieve this, I arranged the known data (for life spans as an example) as follows by separating the range into 2 distinct columns with the lower and upper values for each breed:
Then for the Means, I need another column for it and simply use the formula: (LowerLimit + UpperLimit /2). To be able to draw a vertical line connecting these limits accurately, I need to also calculate the range for each (which is as simple as the difference). The new table now looks like this:
Next, I need to insert a Line chart with markers selecting all the column except Range — a XY Scatter plot won’t work for me here as Excel will not show categorical values along the x-axis for Scatter plot, and I definitely want the cat breeds along the x-axis which are non-numeric. A Line chart on the other hand will allow it. The chart now looks like this:
However, I don’t want the lines for any of the series, so I removed them by formatting the data series in Format Data Series pane. After removing the lines, we have the following:
Next, I want a vertical line connecting the Upper Limit and Lower Limit datapoints for each breed. So I added Error Bars, and set the bars heights by specifying custom values from the Range column in the table. This creates perfect vertical lines connecting all datapoints dynamically…as (and if) the data changes (value changes for any, or new rows of data added, or rows deleted from the table), the chart will always update accurately. The chart now looks like this:
Now, we are getting there! All that is left to do is customize the markers, which is done in the Format Data Series pane. I changed the markers for the upper limit datapoints, and average so they stand out; additional formatting was done according to my taste (e.g. colors, width, font, etc.), and finally added an image as a marker for the Upper Limit to give it some finishing touch.
The finished version of the life span chart now looks as below with additional breeds and customizations.
At this point, I am quite satisfied with this chart. Next, I want to also take a subset of the full dataset and plot another chart to highlight some extremes, say, shortest and longest life spans. For that, I used a bar chart with custom images for the breeds [attribution: images were re-edited and resized by me for this educational purpose from the original images at cats.com] where the images are stacked so they always fit within the bar’s length and cropped off at exact values. The data labels at the tip of the bars make it very easy to follow. This is how that chart looks like:
That looks cool and awfully cute!
I also want to create charts for the weights, and purchase prices for the breeds. Again, they are all ranges, not an absolute number, so we can use my technique above to create those charts as well. Additionally, I want to showcase the extreme values for both weights and prices as we just did in the above chart. Let’s take a look at those charts below. For the weights, I’ve used a scale image (because!) and for the prices, I’ve used a $ icon (because all prices are in USD).
You may have noticed something in the last two charts. There’s suddenly the values in logarithmic scale! The reason to use logarithm (base 10 used here) is to improve the readability and usefulness of the charts. The standard deviation of the price dataset is large as you can image ranging from $400 to $25,000…to chart them without rescaling would force Excel to fit the largest value (25000) in the given plot dimension which would make the smaller values appear all similar and sunk to the bottom (in the Line chart) and sqished together on the left edge (in the Bar chart)!
You may be wondering are these data real or just made up for the exercise. They are real as far as I can tell. I sourced the data from pets.com and Country Living although some contradicted each others data, in which case, I calibrated the data to find a more realistic number based on some research. Yes, the range of a purebreed Savannah ranges from $1,500 to a whopping $25,000 at the time of my research (December 2024) with the Mean at $13,250!
I hope you found this post helpful and interesting. Explore this site for more tips and articles. Be sure to also check out my Patreon site where you can find more free downloads and optional fee-based code and documentation. Thanks for visiting!
