My All-Time Favorite Spreadsheets

Over the years, I’ve created hundreds and hundreds of spreadsheets. Excel has been my go-to tool for almost any problem. It was not easy to choose my all-time favorite. What criteria do you use? I chose the ones that I felt were innovative, that I personally use often, and would hurt the worst if I lost it.

Here they are:

  1. Personal.xlsb: The personal.xlsb is actually an idea from Microsoft to help you store your favorite macros for easy use. The macros that I’ve written allows you to quickly and easily Paste Values or Negate a number with just a key board shortcut. Microsoft has made it easier to Paste Values in recent years, but I’d still prefer to ctrl+c and ctrl+shift+v to copy and paste values rather than clicking around.
  2. Timesheet with Log: With a lot of Excel timesheets, you just reuse the same spreadsheet each pay period. I like the idea of keeping a running log of how I’ve used my time. I’ve used versions of this spreadsheet for close to 15 years. This one can be tricky to customize. If you run the Turn_On_Calc macro (Password = Jimmies), you can see the hidden hidden sheets. In Project_Activity and TS_Filler, there are PivotTables. If something isn’t showing up in the timesheet, you may have to go into these Project filter and make sure the projects are checked. You don’t want the Projects starting with “SA” to be checked because that will cause a duplication.
  3. Mass Mail Forms: I’m unaware of any tool like this that so greatly enhances the email mail merge. For those of you that are mail merge pros, what if you could quickly and easily mass mail custom attachments? That’s what this does.

Useful Links

SumProduct Function for Allocations

This is blog already caters to a very niche group of people, but this post is probably for a niche of the niche…niche2. I’m kind of an Excel nerd. My primary language, the first tool I go to for any problem is Excel. When other people want to send out mass emails, they sign up for Constant Contact or Mail Chimp. I made a spreadsheet to do this.

I had kind of dismissed the SUMPRODUCT function as pretty much irrelevant and useless until one day while helping a church with a Narrative Budget. I was trying to allocate budget lines based on certain percentages in adjacent columns using my regular method when it hit me: Is this the job SUMPRODUCT was intended for? It worked beautifully so that’s why I’m sharing it with you. It saves a lot of time and reduces the risk of errors.

Useful Links

Making your Excel Reports Prettier

The sad truth is that often looks do matter. You might have a wonderfully accurate report with plenty of meaningful insights in it, but sometimes the formatting, or lack there of, can cause people to miss those insights or even dismiss your report. Why is that? For numbers people, usually their eyes are searching for those insights. They are looking for meaning in the numbers. For everyone else, they need help in knowing where to look. Formatting can do that. They also have a lack of trust in numbers and a more professional look can help them trust more. I know this doesn’t make sense to a numbers person, but that’s life.

There’s nothing fancy in this video. Just some basic formatting and page layout tips. I go through it pretty quick because it is quick and easy. In five minutes, you can easily transform a report from plain to…more or less professional.

Useful Links

Memorial Tracking Spreadsheet

We have so many fantastic churches in the Dakotas UMC. One of them that has been a huge help to me personally is Yankton First UMC. They’ve given me some great experiences and wisdom…and now a new tool to share with you. It’s a clever spreadsheet that allows you to easily track Memorials or other small, short-lived funds. The reason you use a spreadsheet is so you don’t clutter up your chart of accounts. You can have one account in your books for Memorials, and this spreadsheet would then break that amount down.

Most of us bookkeepers do this. Here’s a screen shot of one that I used to keep at Bismarck First UMC. It worked…but it’s not pretty. Also, I could never give someone a printout to show them the balances unless I wanted them to see my mess. That’s why I really like Yankton First UMC’s spreadsheet.

Useful Links