What was the last document you created? I’m guessing it was either an email, a Word document, or a Google Doc. Perhaps it was a Slack message but since when you use Slack you create several hundred messages a month (or day), so let’s not count that. Why wasn’t it a spreadsheet (Excel or Google Sheets)?
The most likely reason you didn’t create a spreadsheet is that you wanted the freedom of an open page (hint: View > gridlines turns them off in Google Sheets). Maybe you wanted to insert pictures (hint: use the Image function or insert > image). Or maybe you just like the formatting that seems to happen almost automatically in Cloud-based apps these days. But really, why didn’t you start with a spreadsheet?
Spreadsheets are structured. True, they end up looking that way when they are done. Why not use a tab in your spreadsheet to test an idea (growth in a metric over time, adjusted by factors that change) and produce a sensitivity table to help illustrate a business challenge you are facing. When you are thinking about taking a metric from x to y by when (improve yield by 40% in 30 days) it’s important to show the impact of meeting or missing your expectations.
A suggestion: start with a written statement of what you are trying to solve, e.g. “produce a month over month forecast for the sales team based on a certain number of sales per month and an average sale of $x.” Using the problem statement will frame how you set up your data – you need to have available data series for the problem that match your problem – and start you on the way to presenting the “dashboard” version of your solution. Keep it simple to start by labeling cells that affect others as assumptions, like “sales increase per month.”
Spreadsheets are structured like modular pieces to assemble into a larger whole. Start with the smallest item in your model or argument and test it, then use either a Named Range or a consistently placed cell (e.g. all of your calculations are in a single tab in the same column) to organize where you will find the results. Spreadsheets are more flexible than you think.
Those formulas are hard to copy from one tab to another. Anyone who has ever worked on a spreadsheet has had the experience of copying or changing the data and seeing the dreaded #N/A or #VALUE or #REF show up in place of the data or calculation that they wanted. (And if you have no idea what I am talking about, open a spreadsheet tab in sheets.google.com, enter a formula e.g. =A1+A2 and then break the formula by putting in nonsense characters).
To test your ability to copy data from one tab to another, create a new tab, remove the gridlines for that tab, and title it “Dashboard”. If you can pull the data from the other tab (hint: just use a simple reference, e.g. “=YourOtherSheet!A2” to pull data from a tab called “YourOtherSheet” in cell A2) you can apply things like formatting and font choice to make your dashboard look like a document, not just a spreadsheet.
This method of data construction and display just plain feels weird. Building data-driven dashboards is a method to insert data conversations in your documents. The data needs to first exist in the format (or in a format that’s convertible to the format you need) and then you need to think about how to combine it. And you need to think of the visual display at the same time to make this a useful technique.
Spreadsheets aren’t scary if you think of the presentation layer as a sketch. You might even draw what you want to see on a Post-It note or find a dashboard that you like on the web and take a screen shot as a starting point. Then, consider what kind of data goes into the boxes and graphs to understand what you need to store in the data layer. Still confused, or want to learn more? Ben Collins is my go-to resource to explain Google Sheets to anyone. The best way to learn spreadsheets, however, is to start one.
We all use search forms every day. There’s usually an advanced search form of some sort that allows you to enter a boolean expression. You know Boolean expressions as “This OR That” – an inclusive search that increases results – or “This AND That” – an exclusive search that limits results.
When you need to filter your results, a powerful method is to make a list of companies, individuals, skills – whatever you are using to get more precise answers – and create a Boolean expression to get a better initial set of results from a search engine. Yet when someone gives you a list of 10, 20, 50, or 100 items the idea of making a delimited list of “Item1” OR “Item2” OR “Item3” is more difficult than you’d like to be.
Never fear: Google Sheets to the rescue! This is a perfect example of a task for a Lazy Programmer™ to solve, as it’s much easier to have a spreadsheet count values and rearrange text for you than it is for you to do the work by hand. Let’s take a look.
As an example, I created a utility spreadsheet that takes a range of values, counts those values, and outputs a result string that delivers a boolean expression to paste into your favorite advanced search page. Whether you’d like to create a list of criteria to find your next job, search Twitter more effectively, or simply create a customized search for Google, this expression builder can help.
This spreadsheet has two formulas that do the work for you. The first one joins a range of values into a concatenated string (turns list of Orange and Apple into ‘Orange’ OR ‘Apple’ OR ”):
There are five functions in this expression:
JOIN combines an expression of values into a string connecting each item with the string ‘ OR ‘;
INDIRECT tells the spreadsheet to wait until the range expression in the formula from A2 to the last unblank value in column A is completed and we know the number of the last row to select;
TEXT converts a complex expression into a value – in this case a calculation of the number of rows in the column of this spreadsheet minus the number of blank rows in the column of this spreadsheet – so that INDIRECT will take result of this expression and substitute “5”, the last row above that has a value. This results in a range from A2:A5 to feed back to JOIN;
ROWS counts the number of rows in a range – the standard number in a Google Spreadsheet is 1000 – and returns an integer to feed back to the expression inside of TEXT;
COUNTBLANK returns the number of blank rows in a range, so that our calculation of rows minus the number of blank rows leaves us with the number of rows containing values (4) or the total number of rows including the header (5).
Now that we have our formula, it creates the string:
'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit' OR ''
From the rows of Orange, Apple, Banana, Grapefruit above. And there is one problem here: that we need to remove the trailing “OR ”” that resulted from the expression.
Our second formula, REGEXREPLACE, removes the last “OR ”” from the string. RegEx is sort of like Black Magic, so you will probably need to use a helper site like Rubular. This string looks for a specific string right before the end of the line (represented by $)
OR ''
and replaces it with nothing.
This leaves you with the strong you want to paste into a search form:
'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit'
You’re all set! Now, use this utility with any number of rows in your list of items to create a Boolean expression made for you automagically.
(photo by https://unsplash.com/photos/HOtPD7Z_74s)
What’s the most important thing you do at work?
Most of us, when asked “how do you create job security”, default to explaining a way of interacting with others that only we can do. If you have unique skills, of course you would want to create a solution where you can solve the problem. It’s romantic to think that you – the cowboy or cowgirl – can race into the important situation and solve the problem where no one else can, or do it faster than anyone else.
Described differently, “I am the only one who can get it done on time and under budget” also looks a lot like “I am a bottleneck”, or “my company is now vulnerable to the ‘Bus Problem’, where if I get hit by a bus my company will have absolutely no way to do the things I know how to do. These statements now look a bit different.
A Corollary To What You Do Today
But what if creating personal job security looked completely different and had more to do with creating systems everywhere you go that help everyone else in the company raise their game? In this version of the bus problem, maybe the solution is to make bus schedules (so that all buses run on time), and develop contingency plans (like snow routes) for what happens when there is inclement weather or other unexpected behavior like traffic?
The best way to solve the problem of institutional knowledge sharing is to share that knowledge. Duh. But it means more than simply barfing out that information in whatever messaging suite happens to be the flavor of the month. True knowledge sharing means that you can isolate the facts and share the strategy implications of changing course, that you can write a procedure anyone in your company can follow, and that if you are not in the office the process works without you there.
What does this look like in practice?
Let’s say for the moment that you are responsible for updating the team on a new feature in your product. As a consumer of that information inside of the company, each person in each role needs something different. Sales might need to know if the price of that product changes or if specific customers had been waiting for it. Marketing might need to know if there are marketable features that could be shared with a wide audience. Engineers might want to know if there are new things to test and build. And Customer Support needs to know the typical things customers will ask and how to solve their problems.
Compare your original goal of becoming the only one who can solve a critical problem with the goal of sharing information with everyone in the company at the right time to ensure a productive product release. If you don’t create systems that ensure people on your team know what they need to know before you can tell it to them, you will fail. Your participation in the process should be the reinforcement of the knowledge, rather than the only way they know that information.
Start today by writing down an important thing that no one knows into instructions that person can follow, and then take the day off. Train a trusted resource, take the day off, and see how things went. If you get to “One in a Row” on this problem, you’re ready to tackle the next critical business process you own until the whole business can run without you telling them which buses run next on the schedule.
Photos used to be easy to manage in the pre-digital era. You bought some film, took some shots that you hoped would turn out well, and then printed all of the pictures. Or if you were more picky you decided to print only the good ones. It was expensive to print, time-consuming, and difficult to store.
Fast forward to 2017 and things are very different (duh.) Storage is almost free, it’s trivial to store tens of thousands of photos so that you can print them on demand, and relatively easy to store if you have an access device like a phone or a computer. Scrolling through a photo album looks much different than it used to, but also opens up many more opportunities for creativity and organization than ever.
What Happens if You Have A Lot Of Photos?
If you have a lot of photos (let’s call this 10,000+) that you want to load and maintain accessibility using a cloud service of your choice, managing these photos can be challenging. Here’s the Job To Be Done: determine how to synchronize 10+ years of photos made pre-cloud services so that they update with all of the handy-dandy cloud services without breaking the bank and while maintaining the discoverability we like from cloud services.
I’m choosing to optimize in this example for a consumer or pro-sumer set up where the main goal is viewing the image or movie on lots of different devices. I’m also viewing this from an Apple-biased point of view, so a spoiler here is that the recommendation will not end with the option to use Google Photos and Just Forget About it. (Though this is a perfectly fine answer).
What and How Do You Back Up?
There are a few obvious options when considering a backup procedure for your photos (or for your whole computer).
Here they are, in order of “solves everything” to “solves a point solution like photos”:
back up the whole computer to a shared location (Carbonite, Crashplan, or similar)
create a local backup NAS using RAID and AWS
continue with lame local back up to a single hard drive
upload photos to a cloud service like Google Photos or iCloud
Option 1: Whole Computer Backup
Backing up the whole computer to a shared location in some cloud somewhere seems like a great option if you have an unlimited symmetrical internet connection. For people with a fiber connection, this one would work great. For those of us with a typical cable modem connection for internet, have you thought about how long it takes to upload data?
If you haven’t thought about it, here’s a handy table from that article:
Most of us are looking at the 10Mbps link speed and somewhere between 100GB and 1000GB to upload. Once you start talking about hundreds of gigabytes or more of data this might take days (or almost a week) to complete unless you want to take the radical step of using an Amazon Snowball and putting your data in deep storage.
More practical services like Carbonite or Crashplan will still take days to run a full backup.
So what’s a way that you could back up everything yet still keep a copy locally?
Option 2: Network Attached Storage
Being geeky as I am, I love the idea of purchasing new hardware to back up files on the computer seamlessly to a local storage server on the network and then seamlessly upload the files as needed to a cloud service that maximized the savings.
This is a fun idea, but it’s not cheap either. If you were going to set up a home NAS you might investigate a Synology NAS (2 or 4 or 8 bay) which will set you back a minimum of $600-800 including the right number of hard drives for the storage you need. There are other, cheaper solutions available from Western Digital but frankly I’d be worried about uploading to someone else’s cloud not named Apple, Amazon, or Google.
The goal here was not to optimize for local storage but to find a place to upload about 175GB of photos and videos that have accumulated over a decade.
Maybe there is a simple, practical solution – a local USB drive is fast, easy, and cheap (I have a 2TB Western Digital Passport, and that’s good for the Sneakernet at my place).
Option 3: Local Back Up to a Single Drive
This is the least bad option (given that I’m using it today) but it doesn’t protect against the inevitable hard drive failure that will happen at some point. Local back up also doesn’t protect against loss from theft or fire and fails to solve the basic problem of “how can I use these images more effectively instead of looking at them in a file tree once every 6 months?”
As a backup (not the only backup), I think this solution actually works quite well. The primary benefit of being able to put a 2TB drive in your pocket is that you can easily move a large number of files between computers even when you have a relatively fast wireless internet.
So we’ve got a solution for local backup, and haven’t yet landed on the right solution for cloud backup.
Option 4: Upload photos to a cloud service
There are lots of cloud services you might choose to solve this problem, though I lean toward the paid version to gain a little bit of leverage around getting the data out should one of the services go away in the future.
So which one should you choose? The free one (Google Photos), the paid one (Dropbox), or the more expensive and integrated one (Apple iCloud)? Perhaps you like to solve your own problems and would like to buy raw storage using AWS. For this solution I’m optimizing for ease of use and a turnkey system.
Google Photos gives you instant upload and permanent storage, and a decent photo editing and management service. It is also optimized for the Android ecosystem – it works for iOS, but doesn’t show up natively within Apple Products unless you are super clever about how you set things up.
Dropbox offers plenty of storage (1TB for $99/year) but is more of a file synching service than a photo synching service. It is extremely handy for sharing large files and less easy to find that photo you were looking for (the Carousel photo app notwithstanding).
So, Apple has trapped me again into making a choice based on the discoverability of files and lock-in to the ecosystem I use most.
Getting the files to the service takes a little work
You’re not quite done. If you pick iCloud like I did there is some work you need to do first to ensure that you don’t fill up your hard drive.
Open Photos while holding down the option key – this will give you the option to create a new System Photo Library in the place of your choosing.
Make a new System Photo Library on an External Hard Drive
This gives you the ability to let OSX’s magic “Optimize space setting” expand to whatever amount it needs to without taking up space on your primary hard drive. If you don’t care or have a gigantic hard drive, have at it. But until Apple changes this setting this is the only way to separate your iCloudified photos and videos from your hard drive space.
Next, enable this new Photo Library to use iCloud
If you don’t want to create an ever-growing photo library, deselect photo > settings > general > copy items to the Photos library
Next, select photo > settings > iCloud > optimize mac storage or set to download originals if you’d like to make a backup at the same time. You’ll need to have enough iCloud storage to store your originals (but you knew that anyway).
Now, you’re ready to import photos from your external hard drive.
Use the Import option in Photos (file > import) to import the photos you’d like to add to your iCloud library.
When this is done select the Import New Items option to add these to your Photos Library.
There’s one more thing you’ll need to do – create a smart folder of “referenced” photos – before you can add these external photos to iCloud. Now, use the File > Consolidate… option to add the photos to iCloud.
My happy place is an art studio where all of the items have their own section.
I love to draw. Ever since I can remember I’ve created doodles, pictures, paintings, and other kinds of art. And it generally comes naturally to me – the kind of skill that other people call “artistic” and that I call “just drawing” – until it doesn’t.
I’m not sure what this gap feels like to people who don’t draw, so I’ll try to describe it in terms most people find easy to understand: imposter syndrome. When I don’t “feel” like drawing, I come up with every excuse to avoid that practice. I stay away from art materials and all of those wonderful colors. I stop drawing because there’s no chance of messing up.
That’s really not fun. Sometimes it has lasted for years. I am not sure of the first time I had this feeling but I would guess it happened when I enrolled in a Ph.D program in History instead of renting an Art Studio and drawing for a living. Maybe not drawing was a good thing, though.
If I hadn’t taken a break from drawing I would have spent much less time with computers. I might have missed out on learning to program. I might also have not engaged with new technologies like mobile and social and local commerce.
I am drawing again.
It doesn’t take much to get started again on drawing. Just a little bit of time.
The hack that got me going again? Repetition. Small pictures. Doodling. Pretending “this drawing doesn’t matter.” Because the real benefit to creating and writing about it is a pattern itself – the self-reinforcing loop that happens when you make stuff, and look back later to see whether it’s good – and its absence is an anti-pattern.
So if you see me stop drawing, ask me to draw you something. Give me a commission. It doesn’t need to be paid, and it can be just enough to give me an idea. Making art pays off for me in many more areas of my life than the artwork I create. That process of making is a pattern that leads me to a place where I build amazing things.
Information Maven 