Necessity -> Invention. Are these two really correlated? If so, then why do people find it unusual when someone overcomes terrible difficulties. Shouldn’t terrible difficulties always result in the invention of a solution?
But if the norm is that most people do not overcome terrible difficulties, then where did the saying (necessity is the mother of invention) come from?
Necessity sure can be a great motivator. But not always. It can’t be motivating when a person doesn’t realize their necessity. Immersed in their situation, shaped by it, pickled by it, they may not think anything is wrong. Or maybe they do feel something is wrong, feeling despair, guilt, terror, rage, jealousy, masochism, amusement, self-pride at their own endurance, but don’t conceive of the possibility that there is something different that they can do.
If you study Darwin, perhaps mistakes are the mother of invention. A nondeliberate evolution, the result of random errors. Penicillin discovered in a dirty laboratory, caused by accident, not the planned variables.
Perhaps boredom is the mother of invention. Or happiness.
I think invention is the mother of invention. Every now and then you simply have an inventor. One who doesn’t mind a mistake or two (hmm, what if we take these byproducts of failure and find a use for them). And what happens is that the inventor will, partly motivated by the sheer joy of it, poke and prod and research and build and rebuild until they have a solution, for which they hopefully will one day have a problem.
And here is where teachers come in. Teachers take these pure products of joy, these inventions, and they force students to memorize them. Here is the kicker – you can’t memorize an invention. Invention means exploring the unknown, not memorizing the known. And the students are paralyzed with fear of forgetting a step, of misremembering the components, of placing the wrong shape in the wrong hole, the wrong number, the wrong rule at the wrong time.
If you don’t want to micromanage someone, you have to let them become an inventor. You have to say, here are some problems – wait, scratch that, reverse – here are some knickknacks – go play with them. We can give them the problems later. It is no accident that kittens spend their early life playing before they grow up and hunt. Or perhaps it is an accident. A fun accident of genetics that instilled the instinct to play before the necessity to hunt.
You can invent all the Excel templates you want. But don’t expect anyone to have an easy time learning to use the template that you invented. Until you teach someone how to invent their own Excel template, you don’t do any good by giving the template to anyone.
But how do you teach someone to invent something? Do you put them into that paralyzed state of the student trying to memorize one step and then another? Or do you tell them to just go out and discover?
Give them something to play with. Tell them – once a month, I want you to build a report out of this raw data. The data can be retrieved from that mainframe over there. You will ask that mainframe just for the data for these investors, for this time frame. Once you have that data, you will filter, calculate, present the data, you will show details of certain aspects of the data, you will find out if sums of data meet certain criteria, you will find out whom to pay what portion according to these rules.
Once the person knows they have to create such a thing on their own, they may confess they have no familiarity with the tools, in which case, show them. Show them how you retrieve something from the mainframe. Show them the way you limit the query to just these investors and this time frame. Show them how to type in a formula. Show them cool ways to present the data. Show them ways to drill in on detail for data, and ways to see if criteria are met. Maybe you do it once to show them these tools exist. But then you wait for them to ask. Because they won’t absorb it until they ask.
If simply telling someone to “go out and discover” ways to accomplish such things doesn’t work, then maybe you are dealing with people who are not inclined to explore or initiate. They need instructions line by line, step by step. If so, it is a big responsibility to supply those things to them. This is why most companies follow something called “Best Practices.” The goal of these Best Practices is to allow as much production as possible, as much morale, as much utilization of resources and development of people as possible.
Most people already know (that vague jealousy, remember) that there is something different that other people can do. But they need to know that there is something different that they themselves are capable of doing. Best Practices are designed to provide an environment that tempt people to explore. Such practices can be as simple of having two monitors at a computer, so a person can have a wider work surface, or subsidizing a staff member’s education costs. It could be giving feedback on someone’s work, so the person knows if they got 100% correct, or if they have a blind spot they didn’t realize. It could be subscribing to a research database. It could be allowing cross-training. It could be having a suggestion box.
Best Practices can be as complex as having a change-management process whereby problems and solutions are brainstormed and solutions are implemented with the understanding that implementation requires effort, repetition, feedback and patience while the kinks get ironed out and the vulnerabilities are discovered, and that it requires the input, support and understanding of a team, and that it might fail and that’s part of the process, but that the people involved need to feel empowered to fix things that fail and to not give up.
These are the things that by consensus companies have discovered to be most conducive to having a workforce that is capable of inventing and/or producing. Of course there may be better ways. I think that a better term may be “Recommended Practices.”
Here are some of my “Recommended Practices” regarding inventing an Excel template:
- Color code cells with formulas blue – so other people don’t overwrite them. You can highlight all formulas on the home menu at once by clicking Find & Select -> Formulas (or by hitting control+g and choosing special -> formulas). Once they are highlighted, you can color all the cells with formulas blue with one click.
- People will still overwrite formulas with hard-coded values, even when the cells are colored blue. Figure out a solution to this. I do not recommend password protecting the cells, because I find it insulting to the user or to the next creator, and is contrary to the intention of allowing people to understand and invent. So far the only solution I have found is to create a macro that makes a cell bright pink when it is supposed to have a formula but instead now has a hard-coded value. Since macros are often disabled by default, there must be a better solution – please let me know it.
- Color code cells intended for input yellow – so other people know cells that can be overwritten or the blanks that should be filled in
- Label columns
- If you are filling in text fields, only one concept should be shown in each cell. For example, if the column header is “PAID?” a yes or X is easier for the user to glance at than “they paid $800, but paid late, and with cash, and in two installments, and they are still short by $100.” If the info is that vital, make separate columns: open balance // due date // pmt 1 amount paid // pmt 1 date paid // pmt 1 method // pmt 2 amount paid // pmt 2 date paid // pmt 2 method. Most likely such info is just noise, so if you are filling in such data, try to simplify the amount of info you provide, so the user can hide the columns that are extra inapplicable data.
- As much as possible, do not hard code numbers in formulas. Instead, have all formulas be functions of other cells – so others can see the components of the formula labeled in their own cells, in case the components need to be changed. If the cell references are too confusing for people to follow the logic of, consider using name references instead (it may be easier for someone to understand a formula that says FUTAwages * FUTApercent then to read a formula that says AE21 * BH7). There may be some drawbacks to using name references, so weigh the pros and cons. It may be hard for others to edit your template if they do not know how to work with name references. Also since name reference are absolute references, the formulas do not adjust when dragged across cells, which may call for some tedious editing. The ultimate goal is to allow people to understand which values are being used in a formula, and where those values are coming from, which can be nicely accomplished with name references.
- If possible, break formulas down into sub functions on separate lines, like in school when you were told to “show your work” – so other people understand the process. Label your reasoning on each line (for example: first line: beginning inventory. Second line: plus purchases. Third line: less sales. Fourth line: ending inventory). Sometimes this is not feasible, since intermediate lines get in the way of charts or make it impossible to do calculations on rows of contiguous data. In such cases, consider if the data can be pulled from another tab or location on the worksheet where the formula is broken out.
- In general, do not obfuscate. The template may be one that you yourself only use once a year. Assume you will be pulling it out having totally forgotten how to use it. Leave yourself some hints, or better yet, spend the extra time in making the template so clear and simple to use that you can see immediately how it works.
- Learn the purpose of filters. Learn the purpose of subtotals. Learn the purpose of pivot tables. A pivot table will filter and subtotal for you, however sometimes a simple subtotal or filter will be faster and more useful than getting all fancy with a pivot table.
- Learn how to make an IF( ) function, and a nested IF( ) function. Learn similar logical functions such as AND( ) and OR( ).
- Learn the joys of conditional formatting. Also learn that applying a filter may do a better job of drawing your attention to items of certain values.
- Learn how use the various lookup functions. Learn their limitations and what functions to use to overcome these limitations (for example, VLOOKUP requires that the value you want to retrieve is in a column that is to the right of the column with the value you are supplying – learn what to do if you must retrieve a value on the left side instead of the right).
- Learn how to copy->paste values in case you ever need to work with a bunch of data as their value instead of as their formulas. This can be useful for some actions such as concatenating cells and then wanting to show the result as new numbers, or new dates, or new beings unto their own right.
- Ask the internet if a function for a certain task exists. You will be amazed at the number of functions that exist.
- Learn about functions and add-ins specific to your field. They are too numerous and diverse to list.
- Learn how to use the Formula tab to trace and evaluate the values of cells in a formula and troubleshoot errors.
- Find a coworker who can show you stuff, watch a YouTube tutorial, use the tutorials built into Excel itself (for example, Excel 2016 has a “Take a Tour” in its Open Template screen), read an online tutorial, take a course, pay for a webinar, buy or borrow (love your library) a manual, whatever it takes to learn Excel.
