To continue with my 12 Days of Christmas Theme, in my last set of gifts, I gave you Catholic-themed gifts and here I will give you some Excel-themed gifts (and yes, I will be running up to Epiphany, instead of just running the Octave of Christmas)

While I am going to excess, I will be doing some generous re-gifting, in time-honored tradition.

Excel Themes

First, a video I made some months ago talking about using Excel themes for graphs:

That shows you how using themes can change the look of your graphs very quickly. There are built-in themes… but what if you don’t like the standard selections?

Make Graphs with Art-Related Palettes

A little while ago, I came across this github repo where somebody created palettes for use with the coding system R based off artworks from the Metropolitan Museum of Art: MetBrewer from BlakeRMills

And I thought: why should the R folks have all the fun?

So I picked out nine of my favorite palettes from what he’s created thus far, and turned them into Excel Themes. I saved all those, and made sample graphs, and packaged all these up in this compressed folder on my dropbox: https://www.dropbox.com/s/62ouurpl403edq9/Excel%20MetBrewer.zip?dl=0

Here is a video explaining how to load up these Themes I ported over into Excel.

Here are a few of the sample graphs from three different themes from the set of nine:

According to Blake’s code — I haven’t tested these myself — these are all color-blind friendly palettes. I hope to come back to dataviz themes in a future Christmas gift set.

But wait — what if I didn’t use one of the palettes you liked?

I’ve got you covered!

Make Your Own Custom Themes

Excel Errors and Horror Stories

Here is the “re-gifting” area, if you consider this a set of gifts at all.

Since about 2006 or so, I’ve been writing articles in the actuarial community warning about spreadsheet errors. I joined the European Spreadsheet Risks Interest Group, because evidently there’s no such interest in the U.S. (or, rather, the amount of interest in the U.S. is such that the handful of us with serious interest had to join EuSpRIG).

Many years ago, I managed to get over to the UK to talk at one of the EuSpRIG’s conferences, with a talk titled “I’ve got 99 problems, but a spreadsheet ain’t one”, and the biggest controversy at the conference itself (not my talk) was a big discussion over whether we should use named ranges. For myself, I take a middle way. I name data tables, and ranges used in VBA code. That’s about it.

The EuSpRIG has amassed a truly scary list of horror stories with respect to all the bad things that have happened due to spreadsheet errors (and yes, we do get into the philosophy that it’s user error, not that the software actually screwed up…. except when it does).

Let me dig up a few items from over the years as well as examples from the pandemic, just as reminders of how sloppy use of spreadsheets can cause pain.

Subset of Articles:

2005 – Philip Bewig, How Do You Know Your Spreadsheet is Right?
2008 – Mary Pat Campbell, To Err is Human; To Correct, Divine * 2015 – Mary Pat Campbell, Spreadsheet Errors: Looking Over Your Own Shoulder
2016 – Mary Pat Campbell, How to Keep Your Spreadsheets Out of the Headlines, A Summary
2021 – Institute of Chartered Accountants in England and Wales, How to Review a Spreadsheet

Pandemic spreadsheet error:

This related to somebody using the old 1997-2003 Excel file format in the UK for their formal case-reporting system, which has a limited number of columns and rows compared to the current versions of Excel.

What had happened, in September/October 2020, was a loss of data as the files were being sent in unlimited text format (no big deal) and then imported into Excel (um), and then nobody ever checked that the number of records that got imported equaled the number that had been sent. This is the most basic form of data validation… which I always thought stupid doing when I had underlings do this at an insurance company. I thought it obvious that of course we’d feed in the same number of insurance policy data and get out the same number of policies. I didn’t realize that no, the reason we had to do it was that yes, at some point, there would be people who would lose that data and not notice.

Always do the basic stuff.

LinkedIn post October 5: The Wrong Tool for an Important Job

Excel is intended as a general-use giant calculator, but there are many other systems (with controls) that are better for tracking data sources and flow through reports at the end. Access would have been a better choice than Excel: it can handle larger files. I’m not a database expert, so others can recommend better processes than I can, but I do know that Access would have been better than whatever they thought they were doing.
Heck, using PowerPivot within Excel would have been better.
My main point is that Excel is a completely inappropriate tool to be used for something as crucial as updating official government statistics about COVID-19. Just as Excel (or any spreadsheet software) shouldn’t be used for financial reporting in multi-billion-dollar organizations.

October 9, 2020: COVID round-up: Current Mortality, Bad Spreadsheets, Problem with COVID testing, Longer-lasting effects from COVID, and more

Be careful in data handling – this is not just an Excel issue, but Excel’s issues are well-documented, such as file size limitations
Twitter has actually been a great resource throughout the pandemic. You do need to be able to detect the knowledgeable from the full-of-shit, but in general, the knowledgeable will give you links, detailed breakdowns, etc. Thanks to the twitter crowd! You guys are great! [Seriously, people need to learn to filter the junk. Twitter can be a fabulous resource for keeping current]

Oct 13, 2020 COVID Round-up: Excess Mortality, COVID Death Undercounting, Spreadsheet Errors, and More

Many of the worst Excel disasters were created by experts… who have no control mindset. We’ve been keeping a log for years, and you will see names such as Goldman Sachs in there.
….
In most of these cases it’s BEING AWARE OF POTENTIAL ERRORS.

So… BE AWARE!!!!

And make pretty graphs.

Enjoy!

STUMP - Meep on public finance, pensions, mortality and more

Merry Christmas! Excel Gifts -- Some…