The Art of the Error Message

Ever heard of a “wrongologist”? Did you know that there is an entire TED talk playlist about learning from mistakes? Have you read the 2016 Harvard Business Review article about increasing your return on failure ratio? Research has been growing on what can sometimes seem like a common sense notion - that great things come out of learning from our mistakes. The key is to embrace those mistakes and reflect on them.

It is a challenge to put time and energy into thinking about things that went wrong. And when things don’t go as you’d expect in Excel, it is a whole lot easier to think that Excel is broken or just doesn’t work for you. For anything you are trying to accomplish in Excel, there are countless ways for things to go wrong - your data could be set up incorrectly, you could have one letter or number wrong in a formula, or you could have the wrong formatting applied to your spreadsheet. These are all easily correctable, if you’ve got the right mindset for your approach.

There are 4 common mindset errors that you can avoid, and a simple trick that you’ll read about just a little further down to shortcut your way to success. But first let’s dig into these avoidable mindset errors:

Getting technical before getting critical

Using Excel take a lot more critical thinking than most people think it does. Before you can get Excel to do anything that you want, you need to think through the steps that are needed to get you where you want to go. One of the most common mistakes that people make is to jump right in and start manipulating data, writing formulas, and pressing buttons. While I love to encourage experimentation and trying to figure things out, this will only be successful if you have already thought about where you are going and how you are going to try to get there. Avoid this error by taking 5 minutes before you start working in Excel to write or draw or just talk out loud about what your end-goal is, and how you think you are going to get there. Whatever creative way helps you think through problems and solutions, do that!

Not loving the error messages

Does your mind just turn off when you see things in Excel like #DIV/0!, #N/A!, #NAME?, #REF!, and #VALUE!? What do you think when I tell you that these error messages are actually your best friends? OK, let me explain. If things are going wrong in your spreadsheet without error messages, like your formulas aren’t adding up right or your PivotTables aren’t showing you all your data, then there is no message about what is going wrong and how you can correct it. But when you get an error message, you’ve got a key to how to fix the issue. If you get an error message, you can jump for joy, because you already have some really important information about what went wrong and how you can fix it. There are tons of resources on the internet that can tell you exactly what all those error messages are trying to tell you, but before you go looking for them, let’s talk about the next common mindset error.

Getting lost in the internet

Everything you ever needed or wanted to know about Excel is on the internet already, in a few thousand different variations and approaches. It is easy to wile away hours searching for the resource that will help you learn what you need to learn. But for a lot of people, these resources just aren’t as helpful as they could be. Some of them are too technical, most of them use examples that focus on sales and nothing else, and there are just so many of them it can be a big time-suck looking for the one that speaks to you. This is doubly bad, because it can leave you not only without the information you went looking for, but can discourage you from learning more. Getting to know the resources that are most helpful to you and sticking to those can be a first line of defense against getting lost in all the great material that is so readily available, but which can also be a bit overwhelming.

Getting frustrated

Frustration is one of the biggest hurdles to a mindset that will lead to success using Excel. And I get it, there is a lot to get frustrated over. Like when your calculations don’t work out how you expected them to, or when you can’t quite figure out how to write that formula without getting an error message, or when you can’t find the button that you know you used last week or last year but seems to have disappeared. Frustration with Excel can come from thinking that Excel just isn’t working for you, or that you can’t work it, which can turn a lot of people away from using Excel more. The thing to remember here is that Excel always works the way it is supposed to - but a lot of the time we don’t know how it is supposed to work, and how to make it work for us.

One simple trick

Today, you can practice one simple trick to help you overcome these pitfalls: Set a time limit for yourself when you experience an error or a mistake. I like to call this the

I-hit-a-wall-time-limit

When you hit a snag in using Excel, like an error message or incorrect calculations, try to solve that snag for a set period of time. A good rule of thumb is to take only 15 minutes - that includes any google searching - and then STOP. Take a break, make some tea, play with your kids, or search YouTube for 90s hip hop music videos. Whatever you need to do to get away from the potential frustrations of working in Excel while you are still learning to make it work for you. Whenever you feel ready, go back to it, but remember to use your I-hit-a-wall-time-limit whenever you need it.

Like this post? You might also enjoy my FREE 7-day email course:

Mastering the Excel Mindset

4 Excel Organizational Game Changers

If there is something I love as much as I love working in Excel, it is organizing. I love to organize my home, my office, my digital spaces - I even love organizing for other people when they give me the chance! When my physical or digital spaces are a mess, my productivity goes way down. I get distracted by everything lying around and annoyed when piles of stuff start getting too tall and slipping. Eventually I get so overwhelmed by the disorganization that I have to just walk away from it and not see it anymore - or, I have to stop whatever else I want or need to be doing and organize my life first.

Being distracted, overwhelmed, and having to walk away are all things that I’ve also experienced while learning how to use Excel. I eventually found out that a lot of those feelings came from the fact that my data was super disorganized in my spreadsheets. Now when I look at my spreadsheets I do it with the same attention to organization that I do for my home or my office.

The benefits of getting your house organized and getting your data organized in Excel are crazy similar, too:

Know where everything is

Don’t you hate it when you can’t find something in your house? Sometimes it is just annoying, like looking for a rubber band when you can’t remember where you keep them, sometimes it keeps you from doing something else like when you can’t find the remote or your wallet, and of course those times that you can’t stop until you find what you’re looking for, like when you can’t leave the house because you can’t find your keys. And when you put a time-pressure on it - like needing to catch a flight or get a report to funders - then everything gets harder. But when you are organized, this almost never happens. In Excel, having your data organized means knowing where everything is - because you chose where to put everything, and you put it there with a purpose.

De-cluttering for the win

Is there anything better than having wide, clean surfaces? There is space to work, move things around, and you can see everything you are working on. Clean surfaces are really so satisfying. In Excel, clean surfaces means you’ve taken away all the clutter - any data you don’t need in there is gone. It also means that you can see all of the important things that you need to see - everything that you need is cleanly organized in the way that makes sense to you, leaving the rest of your Excel sheet wide open for you to spread out and work.

A sense of calm and tranquility

I’m not trying to get too metaphysical with you here, but there is some real truth to this! Have you ever heard of organization porn? Just seeing things organized can have a positive impact on your mental state. There are tons of physical and mental health benefits to being organized in your life. I’ve definitely found the same to be true for being organized in Excel. A lot of the stress and frustration that is experienced in Excel is a direct result of being unorganized. It can play special havoc on your formulas, PivotTables, and charts (lots of times, they just won’t work unless your data is organized logically). Organizing your data is like feng shui for your spreadsheets.

Time-savings

It is no big surprise that organization can save you time. Sometimes it can feel like it costs time to get organized in the first place and stay that way, but getting organized in Excel is one of the biggest time-savers you will find. So much time can be wasted trying to sift through confusing and disorganized data, looking for things you think should be in your spreadsheet but you can’t seem to find, and generally just feeling lost yourself in a sea of gridlines and numbers. Organizing your Excel spreadsheets up-front means that you won’t have to spend time later cleaning up and making sense of your data.

THE FOUNDATIONS OF EXCEL ORGANIZATION

There are 4 rules of data organization in Excel that will help you save you a ton of time and energy by avoiding some of the most common problems you could experience using Excel:

Always have clear column headers

Column header is simply the term for the words that you put in the first row of your spreadsheet. What you put in the first row should be descriptive of the data that is in each column. Having really clear column headers will help ensure that you don’t spend extra time at any point guessing or trying to figure out what the information in your spreadsheet is. You also want to make sure that you don’t leave the first row of any of your columns blank, which can cause you headaches later on.

Always apply your filters to ALL of your columns and ALL of your rows

This one trips people up a lot. Adding a filter in Excel means you are adding the ability to sort your rows however you’d like, and pick and choose the rows you want visible based on criteria that you get to choose and control. It is really important that you add your filters to ALL of your columns and ALL of your rows. This will ensure that when you start sorting and filtering, all of your data moves together. It happens to everyone at some point in Excel - you don’t add your filter to everything, and then after you do some sorting and filtering you realize that all your rows of data have been mixed up because they weren’t moving together. Making sure that you don’t leave any column headers blank and also that you don’t have blank rows in the middle of data in your spreadsheet will help make sure your filters are applied everywhere. But an easy way to make sure you are applying filters everywhere is to select your whole worksheet before adding the filter.

Don’t mix data types in your columns

It can be tempting to mix data types in columns. One of the most common examples of this is when you have some extra context about the information in your column and you want it to all be together. For instance, if you have a list of people with whom you are planning to meet, and the dates of your upcoming meetings, it can be tempting to add in something like “10/18/16 - Tentative” or “10/22/16 - Confirmed.” The problem with this is that Excel is really bad with mixed data types (like dates and words), and it won’t be able to do some key things, like sort, filter, or do calculations on the mixed information. Data types are things like text, numbers, and dates. Don’t mix these things in a single cell or column. If you find that you want to add context to a number or date, add a column for “notes.” Take a look at incorrect and correct ways to organize your data types:

Get granular with your data

A key tip to remember in Excel is that it is always easier to bring two pieces of data together than it is to pull them apart. As much as possible, you want to keep your data small and granular - meaning break it down to smaller pieces where you can. A really common example here is when you have names in a spreadsheet. You could have both first and last name in one column. But even better, you could have one column for first names and a second column for last names. Another common example of this is addresses. You could put the full address in one column, or you could have a few columns to capture the street, city, state, and zip code of addresses. Why is this important? It makes it A LOT easier to sort, filter, search, and count your data. In our address example, let’s say you want to filter your spreadsheet so you are only looking at people who live in California. You just won’t be able to do that unless the State is in its own column.

(Did you know all of these foundations already? Scroll all the way to the bottom of this email for a bonus - advanced Excel organization with Named Ranges)

If you are creating spreadsheets from scratch, you can apply all of the magic of organization from the beginning. If you are given a spreadsheet to use, whether that is a template that you download or a report at work that someone else created, you might be able to improve on the organization. Just remember the foundations from this lesson and you’ll be on your way to saving time and reducing stress when working in Excel.


Enjoy this post? You may like my FREE 7-Day email course:

MASTERING THE EXCEL MINDSET

BONUS: Advanced organization with Named Ranges

The Named Ranges feature in Excel allows you to name any group of cells, rows, and/or columns with a name that you choose. This comes in particularly handy when you are doing calculations and want to easily and quickly refer to a specific set of data in your spreadsheet. This functionality can be used simply or complexly, but let’s keep it simple for now. Let’s say you want to add up (or get the sum) of all the numbers in Column F. The formula for that would be something like =SUM(F2:F16). With Named Ranges, you can assign a name to the range and then use that name in your formulas. You can name cells F2 through F16 as something else, like “revenue”, and then you can write your formula as =SUM(revenue). Check it out:

6 Steps for Baking Excel Success

THE GO-GETTERS DILEMMA

A lot of people try to solve their problems or do their work in Excel by just going for it - without thinking through too much about the approach or the end-goal, they dive right in. I love the optimism and ambition that people have when they do this - and sometimes it works. Unfortunately, what ends up happening a lot of the time is that you get a few steps in and realize:

  • Excel is not doing what I thought it would be doing

  • I’m not getting the results I expected

  • I thought I wanted one result, but really I need something else

  • I thought I knew what I was doing, but now I’m not so sure

And it is usually right around this time when you start feeling frustrated, tired, bored, and ready to walk away from Excel.

Yeah, this used to happen to me all the time, too. I didn’t know about all the things that Excel could do, and I would start to feel lost in all of the buttons and formulas and functions. And when I couldn’t get it to do what I wanted, I spiraled into thinking that Excel was broken.

LOST IN TRANSLATION

The truth is, Excel is basically never broken. The real problem usually is in the fact that Excel speaks a different language than we do. In my case, I speak English (you might speak French, or Spanish, or Chinese, or any other language), and Excel speaks its own version of a programming language. The good folks at Microsoft have done their best to translate as much as they could to make it easy on us, but when it comes down to actually being able to talk to Excel and tell it to do what you want it to do, things can easily get lost in translation.

It probably would have gone on that way for me - until one day I just had enough and decided to write out all my frustrations and hopes about what I was going. As I got everything I was thinking and feeling down on paper, I realized that what I had started out doing in Excel was never going to get me where I wanted. When I really sat down to consider what I wanted, it became super clear that I had been going down the wrong path, and would have to course-correct before I was going to see any success.

I started to do a lot more writing when I was learning and using Excel, and found that an analogy that a colleague drew for me was completely true:

Working in Excel is Like Making a Casserole.

Let’s say that you are thinking about what to have for dinner. You decide that casserole would be nice, so you heat up the oven and start pulling things out of the pantry. You get a big bowl out and when you look at your ingredients to start mixing you realize that you’ve got a random assortment of things. Some of them might work in a casserole, but other things have nothing to do with it. What went wrong? There wasn’t a clear end-goal in sight with a detailed recipe of how to get there.

RECIPE FOR SUCCESS

With a clear end-goal and a breakdown of the details, anyone can set themselves up to be successful in Excel. All you need to do is pretty much the things you would do in order to make a good casserole:

1. Decide on your end-goal

If you don’t know what it is you want to make for dinner, then you are definitely not going to be able to figure out how to make it. Be specific. Do you want a cheese casserole or meat casserole? Your needs are unique. Go ahead and write them out so you can see them on paper.

2. Make a list of ingredients

What will you need in order to get to your end-goal? If you are making a cheese casserole, you’ll need cheese. But what kind of cheese? Sometimes thinking through what ingredients you need leads you back to re-defining your end-goal. Now you don’t just want a cheese casserole, you want a four-cheese casserole, with 4 specific cheeses of your choosing.

3. Check your pantry

If your end-goal is to make a four-cheese casserole, and you’ve only got 2 kinds of cheeses, then you won’t be getting to that particular end-goal. This is something huge to understand: If you want Excel to display for you the number of volunteers you had last month, but you don’t have any dates in your data about volunteers, then no matter what formulas or tricks you know, you’ll never be able to display volunteers by month - you just don’t have all the ingredients you need! Once you make a list of the ingredients that you need to get to your end-goal, make sure you’ve got them or you can get them. Otherwise, you may have to change your end-goal to be able to match the ingredients that you do have.

4. Break down the steps

This is where people start to try to think in Excel language, but it is still best to keep it to the language that you actually speak. In my case, that is English. At this point, your end-goal might be granular enough that you don’t need to think through too much detail. But in most cases, you’ll want to write out in your own words what you think needs to happen to get to where you want. In the casserole example, you might think of details like “I want the cheese in my casserole to be stringy and melted when it is served, so I’ll need to melt the cheese,” or “the top layer of my casserole should be crunchy, so I’ll put my breadcrumbs on last.”

5. Figure out where you need help

Along the way, if there were any ingredients you needed that you don’t have, or steps you don’t know exactly how to take, you’ll know that these are places where you might need to ask for help - whether that is from a colleague, a friend, the internet, or an Excel Coach like me. This is a great opportunity to learn new skills while getting towards your end goal.

6. Make your casserole!

Now you can really go for it! And remember: it is OK if it doesn’t work out as planned the first time you try. Like cooking, working in Excel can be both an art and a science.

TRY IT OUT FOR YOURSELF

Take 3-5 minutes to think about something that you want to accomplish in Excel - this might be as large as “create a budget for my event” or “gain insights into the activity of my volunteers” or something as straightforward as “add up all my donations” or “organize my participant information in different ways, such as alphabetically or by number of events attended.”

Then take another 3-5 minutes to write down everything else that you can think of about what you want to do - make sure to stick to the time limit, it is totally OK if you don’t get everything written down. Here are some guidelines to help you get out all your thoughts:

  1. What are you trying to accomplish?

  2. Why this is important for you to accomplish?

  3. What information do you have (such as names, dates, numbers, amounts, etc)

  4. Additional details flying around your head about what you want to accomplish - this part is important - try to get down into the details like “know the sum of all my donations AND sort them highest to lowest AND know when they were given AND have it be colorful AND some of my donations have notes I need to include AND I want to know if they are recurring AND the names of my donors are sometimes not spelled right AND my donations are currently in different spreadsheets and I need them all in one.”

This can take some practice, so don’t worry if it feels weird at first. I know - who thought that learning Excel wasn’t just dry technical mumbo jumbo? But once you find your own voice in writing out what you want to be doing in Excel, you’ll find yourself doing it every time you open Excel up.

Enjoy this post? You might also like my FREE 7-day email course:

MASTERING THE EXCEL MINDSET

What a year of learning Excel taught me about life

An unlikely source of inspiration

If you read the title of this post and thought, “what the hell can anyone learn about life from learning Excel,” I don’t blame you. I know that it is an unlikely source of inspiration. But after spending a year throwing myself into learning how to make Excel work for me, I realized that I was learning so much more than just formulas, formatting, and number crunching. I was learning life lessons that were going to shape how I approached my work, my education, my hobbies, my friendships, and even my marriage.

The more I reflect on it, the more lessons I find in learning Excel. Here is just a sampling:

Life lessons From Learning Excel

Life Lesson #1: Confidence comes from small wins - so break down those big goals

My year of learning Excel all began because I had convinced a hiring manager to take a chance on me and give me a job that required a lot of Excel knowledge - even though I had barely ever used the program before (side note: never let Excel requirements hold you back from applying for a job!!). Right away I was given projects involving big spreadsheets to make sense of and work with. Immediately I was faced with the deflating feeling of being overwhelmed by little numbers and letters on the screen. There was so much going on, and I didn’t know where to start. The only thing that I could possibly do was to try to break things down and try to understand piece by piece what I had in front of me, and what I needed to learn. I took a big formula in the spreadsheet and started to copy and paste smaller parts of it into different cells to try to understand the pieces individually. Instead of trying to become an expert in the whole spreadsheet, learning just one piece of one formula became my biggest goal. When I figured out that first little part, I got a shot of adrenaline - I did it! I was able to understand 1/100th of the spreadsheet!! And if I could do that once, couldn’t I do it again? That first small win gave me the energy and confidence I needed to learn the next part, then the next part, and so on. I have used this lesson in so many other areas of my life - it guides my approach to working out, to decorating my home, even to writing this blog post!

Life Lesson #2: The Growth Mindset is for real, people!

I can’t remember how many times in my life I’ve said “I’m just not a math person.” That is the kind of attitude I had when I first started to learn Excel. And the first times that I saw those “#N/A” or “#NAME?” errors pop up when I was trying to write a formula, I felt so discouraged. Obviously I just wasn’t able to learn how to do this Excel stuff, because I had followed all the instructions in the system about how to do it, and clearly it wasn’t working. If I couldn’t get it to work on the first try, then it wasn’t going to happen for me. But since my new job depended on me figuring it out, I got over that initial feeling of wanting to just throw my hands up and walk away, and instead tried again. And then again. Finally, I saw where my mistake was, and corrected it. Voila! The formulas fell into place and the numbers started appearing the way I wanted them. Maybe I wasn’t “just not an Excel person” - maybe I could learn this stuff after all. If you haven’t heard of the Mindset Theory, here’s the summary: If you think that you “just aren’t good at” Excel (or math, or art, or whatever) and that you don’t have what it takes to get good at it, then you are thinking with a Fixed Mindset (and holding back your own potential). If you instead think that you are an evolving and growing person who can apply themselves and learn new skills, then you are thinking with a Growth Mindset, and opening yourself up to your fullest potential. The best part of Dweck’s research is that you aren’t born with one mindset or the other - you get to choose! And you get to make that choice in how you approach anything in your life.

Life Lesson #3: Failure means you are making progress

Speaking of those error messages - who in the world thought those up? When those messages appear in my cells it feels like I’m playing Mario Cart and I just took a turn way too fast, flew off the highway, and my character disappeared into the nothingness. Actually, it is really more like how I played Sonic the Hedgehog back in the day - I would make some progress, collect some coins, but then hit a spike, lose them all, and probably end up hitting another spike to die. Good thing there were those sign posts to hit along the way to save your progress! When I first started to use Excel, I got super frustrated about not getting things right the very first time. At first it seemed like I was stuck exactly where I started and not making any progress. But as kept at it I realized that for every roadblock, mistake, and frustrating moment, I wasn’t really failing - I was learning. As I continued to work, I saw how essential this process was to my gaining a deeper understanding of what I was doing and how Excel works. Now when I start any new project or take on learning a new skill, I both expect and welcome the early failures and struggles. Without them, I wouldn’t get as far as I know I can go.

Life Lesson #4: The second time is always quicker than the first time

Once I understood that failures were part of the process, I started to see an interesting pattern happening. I saw that the first time I would try to do something - like write a vlookup formula, or create a bar graph - it would take me a really long time to get it right. But then the second time I would try to do the same thing, I was able to do it about 10% faster. The third time I tried I was about 30% faster than the first time. The fourth time got up to about 60% faster, and from there it was a quick progression to nearly 100% faster. Yes - I did time myself learning things once I started to notice this happening. I wanted to see if this would hold true in other areas of life, so I decided to test it out on another passion of mine - snowboarding. I have been riding goofy (right-foot first) down the mountain for nearly 20 years, and I was determined to get as good at riding regular (left-foot first). It seems small, but this was a big challenge for me - and totally essential to bringing my snowboarding to the next level. My first trip to the mountain to take on this challenge was....rough. I usually speed past other boarders, but it was almost like being a beginner again, slowly making my way down, stopping numerous times along the way. About halfway through the day I was so tired I gave up and went back to my goofy ways. My second trip started out just as hard as the first - but I was thrilled to find that I was improving and getting down the mountain faster. My third time up gave me confidence that the pattern I had found in Excel was playing out in snowboarding - I was getting measurably faster with every attempt. There are no set percentages for any person about how quickly they will improve. But I’ve seen this play out in Excel and in so many other parts of life: the second time is always faster than the first.

One More Important Thing I Learned

While I was learning these (and more) life lessons from Excel, I was picking up on something about how different people are able to learn how to use and how to love Excel. It might come naturally for some, but for the rest of us it can be frustrating and overwhelming. I found this to be especially true for people who came from a background similar to mine - liberal arts, with no integrated Excel trainings in school or work. I became 100% convinced that if I could take on this challenge and become an Excel guru, then anyone - ANYONE - can do it.

Enjoy this post? You may also like my FREE 7-day email course:

MASTERING THE EXCEL MINDSET