Why I Dislike Paste Special Values In Excel

Excel shortcuts are important for making you more productive. Ever since I learned my first keyboard shortcut, I’ve tried to “automate” my work in Excel as much as possible using Excel keyboard shortcuts — sometimes doing research on Excel operations that are not commonly used just for the sake of knowing that a shortcut exists for X operation.

I’d argue that Paste Special Values is the most commonly used Excel operation and keyboard shortcut (ALT, E, S, V for the Excel 2003 fans out there). After using Excel for 10+ years, I discuss why I stopped using Paste Special Values and why you should too.

Anti-Patterns

Anti-Pattern —a literary form that describes a commonly occurring solution to a problem that generates decidedly negative consequences

This is the definition pulled from SourceMaking. While this definition describes a literary form, it can be applied to other fields like software (or in this case, Excel). I wrote a post mentioning the absurdity of lugging around regular suitcases before the rolling suitcase came long. We were perfectly fine with holding suitcases by the handle since, well, that’s just what we’ve been taught to do (thanks Samsonite!). After you start using a rolling suitcase, why would you ever go back to the old handle suitcase aside for nostalgic reasons? The holding a suite case by the handle is an anti-pattern, in my opinion, because a new solution came along resulting in better consequences.

The lure of Paste Special Values

There are tons of articles about how Paste Special works, easier ways to access Paste Special in newer versions of Excel, and YouTube videos on how to do every form of Paste Special.

Interestingly, if you search for Why should you use Paste Special Values, few articles come up on why you would use Paste Special Values in the first place. Here are a few excerpts from articles that attempt to discuss the why behind Paste Special Values:

Let’s say we’ve got a spreadsheet of calculations created for our manager, who has asked for a list of SnackWorld sales numbers by month. We’ve used a series of complicated formulas to calculate these values based off of an external data table, and now we want to send our output to our manager. But, we don’t want to send those complicated formulas — our manager just wants to see our final numbers, not unnecessary information on how we got there.

Pasting As Values is an extremely common practice in the business world. Generally, analysts use Paste As Values on critical data before sending it off to managers for review. This helps ensure that a) the data sent does not unintentionally change if the recipient accidentally modifies source data; and b) the recipient of the spreadsheet doesn’t get distracted by complicated formulas, and instead pays attention to the numbers provided. (Source: Deskbright)

A pretty simple explanation:

There are many reasons for copying formula values. Sometimes you don’t want others to know how the result was derived. Other times, you just want to make it simpler and faster for users. This is particularly true if your spreadsheet calls external resources or spreadsheets. (Source: Productivity Portfolio)

The benefit of Paste Special Values is clear: you can copy and paste some data that doesn’t “bring along” anything else (e.g. the formula and format of the data you are copying). In my experience, here are the practical reasons for using Paste Special Values:

1. Raw data (perhaps exported from a database or ERP application) is badly formatted, and you just want the values to do data analysis
2. You’re building a dashboard and need to quickly format the numbers in way that is aesthetically pleasing to your end audience
3. Need to copy/paste between workbooks, and don’t want the formulas to reference the source workbook because links may be broken when opening up the workbook you pasted into

To handle these scenarios above, we are taught Paste Special Values because it’s the quickest way to get data into a state where you can manipulate the data for further analysis. Today, I still do Paste Special Values for use case #1 to handle the quick and dirty analysis I might need to do in Excel or Google Sheets before copy and pasting the final output — yet again — into a PowerPoint or e-mail.

Paste values in Excel discourages transparency and automation

Based on the explanations for why you would use Paste Special Values, a few themes show up:

1. You don’t want some audience (e.g. your manager) to see the formulas behind the calculations
2. The output should be simple to understand for an end user
3. You don’t want someone who doesn’t understand Excel to fudge with your calculations

This behavior leads to one thing: lack of transparency. You are the “numbers” guy, and if anyone has questions on how the values were calculated, they cannot inspect the source themselves, they have to go to you. Even if the calculations are internal to your organization and never leave the walls of your company, the data and calculations should be transparent for anyone to see so that healthy criticisms and debate can happen about internal processes. This leads to my second takeaway about an unintended consequence of Paste Special Values: repetitive tasks.

As an aside, working at a large publicly-traded company means you have earnings, quarterly reports, and deadlines to hit. In this type of environment, it’s easy to skip to the shortcuts that help you get work done faster to hit deadlines. In this environment, using Paste Special Values is the fastest way to get the data you need in the format you want as I discuss above. The problem with this approach is that you never step back to look at the overall process because you are tied to the month-end close or a quarterly deadline to pump out a deliverable. Is there a way to automate or streamline the common tasks? Probably, but there isn’t enough time or resources to build that solution because you just need to get your job done.

Since Paste Special Values results in hard-coded numbers and text inserted into your spreadsheet, you would have to update them every X minutes/weeks/days/months. But you’re argument is: “It’s so much faster to do Paste Special Values because I know exactly where and how I need to do the Paste Special!”

I’ll stop you right there, because you, again, are the bottleneck. If someone has to inherit the spreadsheet or model, you have to provide detailed explanations on how to do the Paste Special into the format you want. If new columns/rows get added to the data you are copying, this means you are updating the instructions for some analyst to decipher. I can’t tell you how many times I’ve seen a spreadsheet with a text box of 50 instructions that look something like this:

Select all the cells on the “Calculations” page making sure you select all the way to the bottom row and do a Paste Special into the “Cover” sheet where the formatting is preset.

To bring this to a more modern example, imagine you are running a simple website that displays the current weather and temperature. You might get the data from the National Weather Service, but instead of having the data automatically get displayed on your website, you do the following:

1. Read the data in some backend application where the National Weather Service data is displayed
2. Go to the backend CMS of your website
3. Manually update the weather and temperature data and hit publish

This is, in a nutshell, is Paste Special Values. You’ve probably heard this definition of insanity from Einstein: “Doing the same thing over and over again and expecting different results.” I posit this definition of insanity in the context of doing Paste Special Values repetitively:

Doing the same thing over and over again and ignoring ways to scale.

The alternative to paste special values is not what you expect

What is the solution in lieu of Paste Special Values? Tactically, there are alternatives in Excel and Google Sheets that make your data more scalable:

1. Copy Paste Formulas  — An ugly stepchild to Paste Special Values, but at least you are getting formulas that reference some source data
2. GETPIVOTDATA  — A little more advanced, but now you are referencing a PivotTable that can be updated based on some source data. The advantages ofGETPIVOTDATA in Excel is that the inputs can be relative or absolute references so if your column headers change, so can the final output.
3. Tables  — I wrote extensively about tables in Excel and Coda. This solution is probably the most robust but comes with limitations (read the blog post for more).

The underlying solution is not a product feature in Excel or Google Sheets, but rather a shift in thinking about how you reference and display your data. When you step back from your spreadsheet and think about how to make it more scalable so that whoever is viewing your data can 1) learn how it’s made and 2) quickly make updates, you’ll start asking yourself some more important questions about why you’re using spreadsheets in the first place:

1. What systems do I use to get data into my spreadsheet?
2. Is a spreadsheet the best place to store my data?
3. Who needs to see my reports, and what’s the best way give show them the data in an easy to understand visualization?

These are not easy questions to answer, but they hint at the intersection of IT, information security, operations, and good ol’ business users who just need to see data.

Re-Learning Copy and Paste

My dislike of Paste Special Values may be the result of how we, as knowledge workers, learned about copy and paste in the first place. You probably learned about copy and paste from a Word doc where you just had to copy and paste text (for me, it was Corel WordPerfect 7.0). That pattern carried over into spreadsheets due in large part to Microsoft Office, and now we are left with Paste Special to help us move and display critical business data. It’s hard to question the status quo when we’ve inculcated 40 years of experience of copying and pasting from a terminal, to docs, and to spreadsheets.

What if you had to re-learn what it means to do a “copy and paste?” What would you do differently?

Coincidentally, our team at Coda is tackling these big questions because of the implications they have on how to properly structure and scale your data. You would think that something as innocent as copy and paste shouldn’t deserve so much attention, but if we want to make data more transparent and reports more automated, this is a building block that must be taught and re-learned.

Sometimes all you want to do is duplicate a worksheet in Excel or a section in Coda, but what does that mean for the underlying data? When you duplicate a worksheet in Excel, you are creating brand new tables that are not related to the original table of data you duplicated. If the two tables are not related to each other with formulas, then you may have a situation where you have duplicate data, and debugging your file becomes a pain. One potential solution to this problem is duplicating a table doesn’t actually create a brand new table, but rather a table that is connected to the original table so that updates flow all the way through.

How to “teach” tables that are related

Copy and paste is one of the first things you learn when working in a doc. Making the leap from a simple copy and paste to a Paste Special Values in Excel takes time, and when I explain to someone how to do it, I always start with the why and reference one of the use cases I mentioned above. To go from Paste Special to connecting tables to maintain relationships between the data is another big step since now we’re getting into the realm of a relational database. Unless you are a database administrator, this image may look daunting to understand.

The crux of the issue is that this is the proper method, for most organizations, to structure their data and allow for easy manipulation and visualization by an analyst. Perhaps for those of you who are new to spreadsheets and Coda, a regular copy and paste does what you expect it to do (create a duplicate table of data that is unrelated to the source) and you may be prompted with a message on whether you would like to connect your data together. For power users, a copy and paste may automatically connect your tables together. Regardless of who the user is and what their comfort level is with databases, the challenge is teaching what a relational database is in a simple and non-complicated way.

I’m passionate about teaching these concepts and we are actively tackling this challenge at Coda. I hope you come along for the ride!

---