I’ve been trying to form a new habit for the past few weeks.
I want to be a better and more consistent writer, and I don’t want it to feel like pulling teeth every time I sit down at the computer with a blank document in front of me. In my ideal world, writing would be something that I can do without even thinking about it - something that I could almost absent-mindedly sit down to do and find myself with a perfectly formed blog post after 30 minutes of joyful and care-free writing.
I’m also trying to be realistic. I don’t know if I’ll ever be able to realize that ideal world, but I do know that the more intentional practice that I can put towards building any skill, the more improvement I will see. That is why I have started nearly everyday for the last few weeks writing at least 500 words. By 8am, I’ll sit down with my coffee and breakfast, open a blank google doc, name the doc “FREEWRITE” (so that there is no pressure to come away with a finished product), think about what is on my mind or what I observe around me, and just start typing. The first few days went pretty slowly, and I had to push myself to get to 500 words. But the more I do it, the more routine and habitual it becomes. And after a few days I also got a bit more comfort and familiarity with the whole writing experience. One way this helps is that I can eyeball how many words I’ve written on a page, which can drive me to write more or to be OK with stopping and moving on to a different task.
This is essentially the same way that I formed good Excel habits.How can you recognize good and bad Excel habits? Good Excel habits save you time and make your life easier. Bad Excel habits waste your time, frustrate you, and sometimes keep you from learning more skills.
Whatever level of Excel user you are, you most likely have some good and bad Excel habits that you do without even thinking about doing them. By identifying and getting rid of those bad habits and building up your favorite good ones, you will be able to approach any work in Excel with more confidence and comfort, since you’ll be doing some basic things that you’ll do without even thinking about them. And no matter what your habits are, the way you make or break them is by seeing the results. When you have a good result from breaking a bad habit or building a good one, you can get the motivation to keep doing those habits, until they are just second-nature.
Here are a few of my favorite GOOD Excel habits. These have definitely shown me really positive results, including saved time, more accurate work, fewer mistakes, and the confidence to get even better.
Add filters (and add them again when you need to)
Most of the time when you are working in Excel, you are going to want to sort and filter your data. That means adding that little down-arrow to the first column on your spreadsheet. If I have even two columns of information, I’ll go ahead and add a filter to both columns. If I end up adding more columns, almost immediately I make sure to re-apply filters to include the new columns. When you don’t have filters on all your columns, you run the risk of sorting some of your data, but not all of it.
If you have more than 5 workbooks open at one time, you start running a lot of risk - you can forget which workbooks you are actively working on, which workbooks have which information in them, and Excel starts running more slowly and might even crash. If I have two different workbooks of information to work from, I will combine them by adding a new sheet to one workbook and copying all of the other workbook data into this new sheet (or you can use the “move and copy” action that can be found by right-clicking on the tab). Then I close the second workbook. Now I have only one workbook open with two sheets of relevant information in it. Do this as many times as you can.
For presentations, trade gridlines for borders
Gridlines make it much, much easier to read a whole bunch of numbers and words in Excel. But when you are trying to draw attention to certain information, particularly just a few cells of information on a worksheet, gridlines can be noisy and distracting. When I make presentations and want to highlight a smaller table of information on the screen, I will remove the gridlines from the worksheet and instead add borders around my cells with the important information. Or even better, use the “format table” action to add in some nice colors and borders.
Use keyboard shortcuts for navigation
It is an indisputable fact - using your mouse in Excel will slow you down. There is just no competition. It takes way more time to move your hand from your keyboard to your mouse and back again, moving the cursor around with the mouse, and scrolling with the mouse, and way less time to navigate an Excel worksheet and workbook with your keyboard. Learning the keyboard shortcuts takes commitment and practice - a lot of times it will feel easier to just use your mouse. But the more you use the shortcuts, the quicker you become at them, and the better results you will be able to recognize.
Wherever possible, take the simple route
In Excel, there is always more than one way to get to the end-goal that you are working towards. Sometimes it is a science, but a lot of times it is an art. When faced with choices about how to approach your work in Excel, often times the simplest path is the best one (or a few simple paths combined). For example, let’s say you want to know if the data in one cell is the same as in another cell. You could write a complicated if/then statement. Or, you could just use equal signs: =A1=A2. This simple formula will return a TRUE if cell A1 is the same as A2. If it isn’t the same, the formula returns a FALSE. Couldn’t really be simpler than that.
Use color coding sparingly and with rules
Color coding is tempting - it is an easy way to mark things that you want to know or remember, and breaks up the monotony of all those lines and boxes. But color coding can get really messy also. Sorting and filtering can screw around with it. Adding and removing rows and columns can mean you need to apply the colors again. It can be a big struggle to change colors if you want to make them different at any point. And importantly, you can forget what the color coding meant, or someone else using your sheet will have no idea what the colors signify. Use colors for presentations, but not so much for organizing and working with your data. But if you really love color coding, the best way to do it is with conditional formatting rules - that way there is at least some record of what the colors mean.
Speaking of keeping a record of what something means, one of the absolute best Excel habits is to take notes about what you’ve done in your spreadsheets. When you’ve got some documentation about what is going on, you can leave your spreadsheet for long periods at a time and know what you are coming back to. You can pass your spreadsheet to someone else and have them understand things without needing to explain it. Even though it takes some time to enter the notes as you go, it will save you tons of time later on. There are a lot of ways you can take notes in Excel - right in the cells, using comments, or starting a new tab called “notes” where you type all your notes.
It isn’t sexy, but the best way to form a new habit is to practice, practice, practice. I want to hear from you! How do you form new habits? And what Excel habits do you want to have?
I’ve got a story to tell you, and it’s all about what kept me up working in Excel long into the night and early morning:
The Set Up
My wife works with someone who gives her data in Google Sheets. And by Sheets I do mean Sheets, plural. She gets her data scattered across many different Google Sheets, which she needs to then make sense of, aggregate, and even add to. Like the supportive partner that she is, my wife suggested to her colleague that maybe there was a better way to do things, and that she knew just the person to help out. Of course, my wife was trying to kill two birds with one stone - to get me some work, and also to have me make her life easier by getting the data exchange process more streamlined.
Helping my wife’s colleague streamline her work was pretty straight forward - I took all of her manual processes and built a few sheets and formulas that became a tool to automate her work. Now all she had to do was copy & paste in new data when it came in and….poof! All her manual tasks were instead taken care of by formulas and functions. Hours of her work shrank to minutes. It was wonderful! But then I got to the part that would help my wife. Making it possible for her to receive and enter data in one place, without having to search through multiple sheets, turned out to be a lot trickier. I had to do a lot of design work and learn some new formulas. It all turned out to be way more complex and time consuming than I could have predicted. And this is work that my wife does only once a year - so all my work was only going to benefit one person, one time a year.
I did eventually build processes for her that allowed her to seamlessly receive and enter her data, but did she really need the experience to be so much easier and more streamlined, when it was only something she was doing once a year? I asked myself, was all the time and energy that I put into what I built really worth cutting down the few hours that she took sifting through Sheets and data for this work, to a few minutes?
When I answer this question for myself, I know the answer for sure:
YES!! And I’ll tell you why:
- The first reason is obvious - this was for my wife, and I would do anything and spend any time to make her life even a little bit easier.
- It turned into a great sample of a need and a solution, which can become a case study for me to use in the future. If a client comes to me with a similar need, I’ll have one more tool in my tool belt to offer them as a solution.
- I learned new things! Learning is one of my core values, and also one of the things I find most fun. In this case, I learned new approaches to working in Excel and even some new formulas. I was reminded that there is always more than one way to get to an end-goal in Excel, and the context of this work really pushed me to think about what would be best in this case.
- I got to practice walking away from Excel. At moments when I got stuck and frustrated and things seemed like they were too hard to figure out, I stopped working and took a step back. I tell my clients to do this all the time, but I got a great reminder of why it is so important. It helped keep me calm and let me come back to the work with fresh perspective.
The last reason is also pretty obvious - it was all worth it for me because I love this stuff!! It was literally fun and exciting for me to get this thing working for my wife. I’m telling you, I had flow doing this. And in the end I got a huge amount of satisfaction making it work.
The More Complicated Question
So, should you do what I did? Invest a significant amount of time to automate a process that happens only once a year and benefits only one person’s time?
I mean, unless this is your happy place like it is mine. And really, unless the time and energy you are going to invest in automating your processes is going to at least double the amount of time you are going to save every year. If not, why go through the potential frustration and disappointment if it doesn’t work out in the end?
There is one other reason that I wouldn’t necessarily recommend that you do what I did. Unless you have already mastered some aspects of the Excel mindset, you may not even know how to get started or how to state your end-goal and break it down into more manageable tasks. This is totally possible for everyone to master and to accomplish, but I’ve seen it done most successfully by the people who already have a strong sense of the approaches that are learned as part of mastering the Excel mindset.
Can you get there? Of course you can!! If you are interested in learning more, I would love to speak with you about it! Schedule some time with me to talk about your Excel needs. And if you don’t have time to learn it all, send me an email and let me know what manual processes you are struggling so we can see if there is a way to automate them!
Do you ever wish for more time?
I have yet to meet a person working in the non-profit or education sector who feels like they have just the right amount of time to do everything that they need to do. In mission-driven work in particular, the urgency to do our work quickly and efficiently carries a lot of weight, because we are working towards impacts that can change the trajectory of people’s lives. So whenever we have tasks that feel like time-wasters, it isn’t just annoying because our own personal time was wasted - it can have a negative impact on our part in the missions we are serving. That’s not good. Plus, it is super annoying.
The surprising benefits of keyboard shortcuts
If you want to save time using Excel, then you want to use keyboard shortcuts. Using keyboard shortcuts just means that instead of navigating and choosing options with your mouse or your touchpad, you are doing it with keys on the keyboard. And yes, it actually does save you time. You might only see it saving a little bit of time here and there, but that all adds up in a big way.
How much time do you save in a simple copy & paste when you use keyboard shortcuts instead of your mouse? Probably between 1 and 5 seconds. It doesn’t sound like that much, does it? On the other end of the spectrum, this guy showed that it would take 9 hours to scroll to the bottom of your Excel spreadsheet, if you really wanted to spend your time doing that. If you don’t want to spend 9 hours wasting time in Excel, you could use a keyboard shortcut to save you 8 hours and 59 seconds. These might be examples on the extreme ends of time savings and wasting, but they illustrate a good point.
I estimate that keyboard shortcuts save me between 2 and 4 dozen hours each year.
That is a day or two. I save about a day or two of time that would otherwise have been spent in Excel getting to do something else, like play with my daughter, watch football, go snowboarding, get more tattoos, read the New Yorker - the things that I like to do. For people working in non-profits and education, that could mean hours of time spent on instruction, or community building, advocacy, fundraising, relationship building, or whatever it is that you do so well and adds so much value to your missions - instead of wasting time in Excel.
But there is even more time that is harder to estimate.
Keyboard shortcuts aren’t just about whizzing around your spreadsheet and not worrying if you forgot your mouse at home. For me, the benefits were a lot more than just that, and have definitely saved me even more time than I can count. Learning and using the keyboard shortcuts actually taught me about the way Excel is built and how it works. Getting a sense of how the shortcuts worked also let me see how Excel “expects” you to set up information inside of it. The shortcuts were created assuming a certain way of information being put in Excel, and knowing that can help you choose better ways to enter information and keep it organized. For me, keyboard shortcuts gave me a sense of ownership, confidence, and skill that I didn’t have before. Once I started to use them more frequently, I started learning other technical skills more quickly. I found that using keyboard shortcuts helped me build good habits around using Excel, and having good habits are a huge part of making Excel work for you.
Now that you want them, how can you get them?
I used to think that the best resource for keyboard shortcuts was a downloadable and printable sheet of 200+ shortcuts for PCs and Macs. I mean, it had everything on it, what’s better than having everything? If I would recommend any of the MANY resources like this out there, it would be this one from ExcelJet - and I do reference this resource sometimes myself. But, I don’t think I ever used this resource to really “learn” keyboard shortcuts. I could look up new ones, but the knowledge never really stuck, so I would have to look them up over and over again. And honestly, I couldn’t really think of a better approach. But then I heard about Hot Key Excellence.
It’s a game! A game to learn keyboard shortcuts!
Alright, maybe I’m a little too excited about this. It is still in its beginning stages, and I imagine it will continue to get better and better. I’ll admit, I don’t really need this game. I find working in Excel to be as exciting as a game already, and I practice just by doing my work. But I can imagine that this will be a game-changer (no pun intended) for anyone who wants a little more guidance, interaction, and fun for learning their keyboard shortcuts. I’ve gone through a few rounds of the free version, and I was impressed at the approach and the potential.
I am all about anything that is going to make using Excel a better experience for you, and help save you time working in a spreadsheet and maximize your time out in the world, serving your mission. Let me know if you try it out and what you think of it!
I incorporate keyboard shortcuts into all of my coaching sessions and trainings, so if you are looking for more customized support, let’s set up some time to chat about your Excel needs.
I need to ask you a big favor:
if you ever see me using too much technical Excel-speak and not explaining enough in plain English, then PLEASE call me out on it. When I’m talking to a software developer or data scientist or database nerd, then sure I’ll try to show-off with big technical words. But when it is you and me, or anyone else who has better things to do than become fluent in “Excelese,” let’s keep it simple and straightforward.
Check out the first sentence of an email I got from one of my favorite Excel sources on the web: “Data labels are a flexible system in Excel's charting engine made to display source data directly in a chart.”
If you got it right away, that’s awesome!! But maybe, like I did when I first read it, you felt confused by the language and couldn’t picture what it was trying to explain. Data labels are a system? Excel has a charting engine? What is source data? What is a chart?
As you already know, everything you need to know about the technical parts of Excel is already on the internet (and don’t forget, it’s free!). But the way that it is presented and talked about can be a whole other layer of challenging, and sometimes requires you to know more “Excelese” then we should really be expecting from someone who has to use Excel, but it isn’t the main part of their job.
The problem that a lot of my friends, family and clients have with this kind of technical explanation from an Excel guru is that it is doing its best to speak English about Excel, but it is still mired in Excel language.
So what was this sentence really saying? Here it is in English: In Excel, when you make a graph (also called a chart) it can sometimes be hard to know what the exact information is being presented - whether it is the bars in a bar chart or the slices in a pie chart. For example, if you have a pie chart with 4 slices, all different sizes, what percentage of the pie is each? What actual number does that percentage come from? You can have that information on the chart itself, and Excel calls that Data Labels. Without these little labels in the picture, you’d see bars and slices, but you wouldn’t know exactly what numbers they are supposed to be telling you:
Now, did that make any more sense?
One of the first mindset approaches that I teach for Excel is all about “visioning.” This is basically the process of thinking about, speaking out loud, writing down, and getting specific and detailed about the thing that you want to do or accomplish in your work. You are probably already be doing this in some capacity either for your work in Excel or elsewhere in your life, and you already speak your first language (mine is English). So learning and practicing how to do visioning for Excel work can be a quick and easy win that can yield incredible results.
Nobody sits down to a spreadsheet and says “I want to change the data labels system in the Excel charting engine.” No, you sit down and you say - Hey, I need to see the actual numbers of my information in my graph. And while we are at it, can it be in the font, size, and color that brings me to my happy place?
Nearly every time you start working in Excel or google sheets, the first thing you should do is…Stop! Don’t work in the sheet just yet. Open up Word, or Docs, or break out the pen and paper, and start envisioning and capturing what it is you want to do or have in the end. Write a little story about it in English. The translation from your vision to functions, formulas, and formatting in Excel/sheets will go so much faster! Here are some sample questions you should ask yourself when you start:
- What is my vision / end-goal?
- Can I break it down into smaller, specific pieces?
- Do I have everything I need in one place?
- What do I know how to do?
- What am I unsure of?
You might find yourself starting out pretty big, with something like “I want to be able to be a guru in Excel and use a lot of cool formulas.” OK, that is great! But, it isn’t specific enough to help guide you to success. Start big, but break it down and get as specific as possible. Get down to something like “I need the information that is in Column F in my first worksheet and Column B in my second worksheet to be in one place, and in alphabetical order.” Once you get to that point, then you can break it down into smaller pieces.
Here is my challenge to you: Open up a google doc, call it “Excel Vision Freewrite” and just start typing. Don’t delete anything, don’t slow down, just get down everything that you are thinking about related to your Excel project. And if you want any advice or suggestions from me about how to accomplish what you want to do - hit Reply and share the link to your document!
Want to learn even more about writing your vision / end-goal statement and getting from there to learning the technical skills to make Excel work for you? Head over to my contact page and let’s find a time to connect about how I can help out!
What does is mean to be living in the information age?
There are a lot of different ways to breakdown and categorize the times we have lived in as humans. If we look really broadly, we see that the names we have given to different time periods throughout history are pretty well descriptive of what was really important at that time: The Stone Age (stone), The Bronze Age (bronze), The Iron Age (iron), The Middle or Dark Ages (nothing?!), The Machine Age (machines), The Atomic and Nuclear Ages (you get the point).
The most highly valued thing in today’s world is invisible.
Like the atomic and nuclear ages, the most valuable thing in the world starts out basically invisible. You can’t really see or put a shape to nuclear energy until you walk into the gigantic facilities made to produce it, or imagine the shape of a bomb used to transport it. Information similarly has no true shape or form - it is all inside our heads at first. But then we built tools to make information visible. Stone tablets, papyrus scrolls, the printing press, typewriters, computers, and data storage hardware have all been means of showing information. So what makes the information age different than all the rest of history, in which it has been a cornerstone of the human experience to collect and make information visible?
The information age is about what you do with information, and how fast.
How fast can information be recorded on a stone tablet? How fast can it be shared? How fast can it be updated? Yeah, not fast at all. In fact, really slowly if at all. In the information age, value is found not only in the quantity and quality of the information you have - in fact, just having a ton of information won’t do you much good if you don’t also have the tools, skills, and capacity to understand it, manipulate it, present and use it. Once you’ve got information, what will you use it for? Mission-driven work in this age uses information to gain insights, gain support (financial or otherwise), tell stories, compel change, and make impact. The value of information to mission-driven work is in how it can increase the quality, quantity, and speed of making impacts and change.
Let’s be real, using information isn’t a new thing.
It is second-nature to us as humans to bring in the inputs of information all around us - the weather, the distance between places, the number of things you have - anything we see and do is information that we use in some way. We do this really really efficiently for some critical human tasks, like the ones that contribute to keeping us alive. Fight or flight is an extremely quick reaction to a set of inputs that trigger these responses in us - we take in the information as it comes to us, and we respond to it very quickly by engaging or running in the other direction. It keeps us alive and sane. But try to do that with 100-times the number of inputs, or as a group of people instead of as just one person. That’s when you can encounter overwhelm and paralysis or people running into each other. In the information age, we have the tools to collect and review so much more data than we’ve ever had access to in the past, and in the blink of an eye.
Real-Talk: You CAN NOT opt-out of the information age
I am very passionate about this fact. There is no turning back and no hiding under a rock. You are part of the information age no matter what. But that doesn’t have to be a big and scary commitment - in fact, the awesome thing about living in the information age is that you get to join all the skills you’ve already got (community building, development, social work, relationship building, problem-solving, etc.) with information technology, all of which can lead to more meaningful, larger-scale, and faster impacts wherever you are working and whatever you are doing.
The gateway to the information age
You need to be comfortable using technology and data to thrive in the information age, and to use information with speed and at scale to drive and enhance your impact. That doesn’t mean that you all of a sudden need to know how to write code or build an app for Salesforce or become a data architect. But you do need the mindsets for using technology and data that will get you started. One way of the most practical and useful approaches to learning those mindsets is through learning Excel. Excel is a gateway for you into any other technology that you may need and use. The mindsets that lead to learning and using Excel in a way that works for you are exactly the same ones that give you entry into the world of all the rest of the technology tools managing the information age.
What is it about rivalries that makes them so fun and compelling to watch?
Sports are an obvious example of rivalries. As a Yankees fan, I can never see a Boston Red Sox hat without wanting to get into a heated debate about the state of the two teams and why mine is the best. The funny thing about rivalries is that the two rivals have so much in common. Rival teams play the same sport with the same rules, at different times they can have the exact same players and coaches traded on and off their rosters, they do the same press conferences, and they use the same gear. To us regular fans or non-sport watchers, the only differences between the teams may look like the color of their uniforms.But there is no denying that the culture of a team can be very unique, and extreme fans will be able to rattle off the differences and distinctions between teams easily. It is both the differences AND the similarities that fuel an epic rivalry and create fans (and haters) for life.
Excel vs. Google Sheets
It might not have as many years as the Yankees - Red Sox rivalry, but the Excel vs. Google Sheets competition is heating up, and the fans are starting to claim their seats on either side of the bench. If it is a good rivalry, then you already know there are going to be a lot of similarities, and likely some deep cultural differences. So what is the same and what is different - and more importantly, which is going to support and help you in the important work that you are doing?
Almost everything is the same.
Doesn’t seeing that just make you so happy? I know it makes me happy. At this point, the kind of work you can do and the value you can get out of each system is pretty much the same. In fact, you can almost say “Excel” and “Google Sheets” interchangeably, that is how similar your work and outcomes can be in each platform. Both Excel and Sheets were built for basically the same purpose - to organize data, help you arrange that data into information, provide built-in tools to help you turn that information into knowledge and wisdom, and to help you do all this as efficiently as possible, over and over again. Plus, they look really similar. You open either one up and you see rows and columns, white cells and grey lines.
The differences are few, but HUGE.
There are just a few differences between Excel and Google Sheets that are worth mentioning, but they are REALLY worth mentioning. Here is a quick overview:
- Sharing and collaborating. First and most important, Google Sheets was built on the same dream of collaboration as all other Google web products. It is connected to the rest of your Google account, and sharing and collaboration were there from the beginning. Excel does have collaboration options, but they are clunky, ugly and archaic compared to what Sheets can do. Microsoft has upped their game in this area with the introduction of Office 365, but it still just doesn’t hold a flame to what Google Sheets has always done best.
- Speed and power. Sorry Google, I love you, but Excel is still faster and stronger than you. The speed comes from two places - first, the processing power of a program installed on your computer has an edge over the web-based systems that rely on internet connections and other behind-the-scenes technical stuff. Second, the keyboard shortcuts on both systems are mostly the same, but Excel does have the edge over Sheets. The shortcuts are better and more plentiful. And then you talk about power - that has to do with the extra stuff you can build on top of the basic functions the systems can do. Google is adding more and more functionality every year, but I don’t think they really have aspirations to equal or beat Excel in all the fun and cool things it can do. But do you really need all that additional stuff anyway? Most people don’t.
- Ease of use / user experience. There are some clear differences in how easy and intuitive each system is, but the “better” one is going to be dependent on the person using it. There is less to look at and choose from in Google Sheets, so it can seem more streamlined and straightforward. On the other hand, Excel has some great drag-and-drop capabilities, especially with PivotTables and charts. Google Sheets give you some of the most important functions right up front for you to quickly and easily access (this is especially true when you right-click on a cell), but Excel just looks nicer and more colorful, and allows you to customize it to have your most important and frequently used functions exactly where you want them to be. Sheets saves automatically every few seconds and works the same across Macs and PCs. Excel has great version history if you need to look back at previous versions of your work, and the automatic formatting is plentiful and visually more fun than in Sheets.
Which team are you rooting for in this rivalry?
Having a favorite in this rivalry can depend on why you are using spreadsheets in the first place, and honestly both are good choices for being a fan. Which one should you use, Excel or Sheets? The answer to this is pretty easy - use both! There are great reasons to use each platform for different purposes. I use both every day, and sometimes I try one first and then move into the other.
So, are you a superfan of Excel or Google Sheets? Drop some love in the comments to let us know what you like or dislike about each system, and why you might use one over the other.
Have you ever asked yourself, what is the best exercise to do that will help me lose weight, get stronger, or tone up?
I honestly can’t remember where I first heard the answer to this question when I asked it for myself. So if you’ve heard this answer before somewhere, let me know the origin story! But there is actually an answer to this that has worked miracles for me. It has saved me hours of time I would have wasted doing the wrong exercise and helped me work out more, get more adrenaline and happiness from my workouts, and develop better habits for exercise and healthy living in general. What is this super secret exercise with magical powers?
The best exercise in the world is the one that you will actually do.
I know it sounds like a simple answer, but it really has changed my life. Runners look really healthy and committed and I want to be one - but I hate running. Every time I tried to do it as an exercise I ended up hating the time I spent doing it and inevitably abandoned it. But when I found something I loved doing, something that I am happy to find the time in my schedule to do, something that inspires me to be better and stronger - that is when I started to hit my weight goals, get more energy, and feel generally great. For me, that was SoulCycle. Whether you love it or hate it really doesn’t matter. I love it, and more importantly I actually DO it, so it is the right exercise for me.
(Side note: Just because I hate running now doesn’t mean I always will. People change all the time, so it is great to try things more than once).
Have you ever asked Google what is the best way to do something in Excel?
It is pretty common for anyone using Excel (myself included) to use Google to learn how to do something in the program. And when you do, you can be bombarded with different kinds of resources - websites with hundreds of articles about how to do things in Excel, YouTube channels filled with videos of people teaching Excel skills, forums with people exchanging information about Excel, and even Pinterest accounts (mine included) with images related to your Excel question. So, what is the best resource out there for learning Excel? Because let’s be clear:
Everything you need to learn how to do technically in Excel is available on the internet, for free.
So which resources should you be using? What will give you the best learning and get you using Excel like a tech pro? I’m sure you’re not surprised when I say:
The best Excel resource in the world is the one that you will actually use.
Let me be honest, I have a really hard time sitting through YouTube videos about Excel. I can’t for the life of me pay attention long enough to learn anything. I’m constantly switching to a different tab while a video is open, fast-forwarding to try to find what I really wanted to learn, and just tuning out when I’m staring at the screen. YouTube videos are not the way for me to learn anything about Excel. Instead of forcing myself to watch them, I go to the resources that work for me, which are the more techie blogs and forums like StackExchange. I also go to some really great Excel-focused websites like Chandoo.org, ExcelJet, and ExcelZoom. All of these resources offer basically all of the information you could ever want about the technical aspects of Excel, for free.
If anything you need to learn about Excel is already out there for free, why isn’t everyone an Excel pro?
The information might be out there, but it might not be presented in the way that works best for your learning. I might be missing out on tons of amazing videos about Excel tips & tricks, but I can get basically the same information in a different format and in a way that works best for me.
But I’ve learned that for a lot of people, especially anyone working in education or non-profit work, the resources out there just don’t speak to them. It is all there, but it isn’t very helpful when you are trying to learn how to accomplish something in Excel. I’ve also learned that searching through a ton of Excel resources can be frustrating and tiring, and leaves people with a sense of having wasted their time.
Everyone has the abilities to be an Excel pro - they just need the mindsets to be able to learn the technical skills.
When I was first learning Excel, I found that I had to change my perspective about myself and how I learn to be able to master the technical skills that are explained in those free resources I mentioned. No matter what your skill level or comfort level is in Excel, if you have the right mindsets for learning, you can make Excel work for you.
If you’ve ever felt like there are an overwhelming number of resources out there about Excel, and you haven’t found most of them very helpful in your learning - let me know! Hit reply and tell me about what you’ve tried to do, what was confusing, what you wished would have been different. And if you are ready to learn new mindsets and get a fresh perspective on learning and using Excel, let’s set up time to chat!
Google fails more often than you think
I read an article recently that made me want to predict the future. The products that Google releases have failed or been cancelled both at a rate of approximately 36%. I predict that an “enhancement” to Google Sheets recently announced and installed by Google will join that graveyard. The new feature added to Google Sheets appears when you create a PivotTable, and in plain English it is a few suggestions about how you might want to build your PivotTable (what should go in the rows and columns, and what should be counted up). If you aren’t familiar with what a PivotTable is, it is functionality in Excel and Google Sheets that is most commonly used to help you group and count different information in your spreadsheet - also known as aggregating your data. And I predict that the supposed “upgrade” to PivotTables will fall on its face as a big failure.
The future is totally unpredictable - so why am I so confident about this particular failure from Google?
I am going to make my case in 3 simple points, all of which are a response to an article about the new feature that I am going to brutally break down. No disrespect meant to the author of this article, but I am going to rip apart a bunch of what they have written.
Point #1: The term “Power User” is dumb, and this new feature won’t even help you learn how to use Sheets to gain insights.
What is a “Power User”? This article seems to think that the difference between us regular folks and Power Users is the ability to use PivotTables. Get ready to be de-throned, Power Users, because the word is out that anyone can learn PivotTables!! But we all have an idea of what it means to be a Power User - someone who seems to know a lot and can do a lot of different things in Sheets or Excel. But it isn’t really the technical skills that give Power Users their, well, superpowers. Power Users can do what they do because they have the mindsets and some focused learning needed to be able to take an idea of what they want to see or do with their data and to use Sheets or Excel to make that happen. The new feature in Sheets skips the learning and the mindsets, and simply tries to make suggestions about how to aggregate or count your rows and columns of data. It doesn’t teach you how you might come to those suggestions on your own, and will leave people cycling through all of the suggestions trying to understand why they were chosen when they don’t make much sense.
Point #2: Human beings are necessary to make sense of real (i.e. messy) data.
Real data is messy and comes with a lot of asterisks. Only a human will know and understand the real-world context surrounding the data and be able to address it. There is no way (at least right now) for a computer to make suggestions for your data that really are aimed at garnering greater insights (unless it gets lucky in its suggestions, which is just random happenstance if it works). This is especially true for people working in non-profits and education. The example given in the article is simple and irrelevant to much mission-driven work: “For example, someone could create a pivot table that takes a spreadsheet full of sales transactions and outputs how much revenue is attributable to each salesperson.” First of all, mission-driven work isn’t about transactions, outputs, and revenue. It is about transformations, services, and impact. Can I see an example of how a PivotTable can answer questions about that? Sheets might look for words like profit, revenue, sales, items, salesperson, or anything similar when making suggestions for how to roll up information in PivotTables. But if your column headers are not in this same genre and your data isn’t set up cleanly, if won’t be able to make any meaningful suggestions at all - it just doesn’t know how. A human being will still need to tell Sheets what to do, not the other way around!
Point #3: Everyone can learn how to use PivotTables, and it is insulting to suggest otherwise.
This new feature (and the article) FAR underestimate your abilities to learn the mindsets and foundational skills that are needed to make the most of PivotTables and Sheets in general. Many people are already doing the aggregation of data that PivotTables makes easier - they are just doing it manually with lots of counting, sorting, summing up formulas, and other workarounds. With the right mindsets and approaches, such as sketching out your questions and the insights you want to gain, it is really only a short jump to learn the technical skills for how to use PivotTables. Consider this statement from the article: “Google hopes that these features will help make it more accessible for people who don’t have PhDs in spreadsheet manipulation to use the same features that their power user friends are already familiar with.” Are you kidding me? First of all, a PhD in spreadsheet manipulation? That is absolutely ridiculous. Excel and Sheets ARE accessible to anyone. A big problem is that most of the resources that are available to teach people how to use Excel and Sheets just teach the technical skills, and mostly in technical language. But these technical skills come much easier to learn when the right mindsets are in place.
Because real data is messy, and using it to gain insights requires real human intervention, and because using technical functionality before you understand why you are using it often leads to frustration and wasted time, I predict that this new feature in Google Sheets will not help most people and will not teach anyone how to become more skilled at aggregating data and using it for insights, and will therefore be put in the graveyard of unhelpful and unpopular Google products. And if you take away only one thing from anything I’ve said here, make it this:
Don’t underestimate yourself - there is no definition of a “Power User” and no PhD in spreadsheet manipulation. You are 100% capable of learning and using Excel or Sheets in your context, and making it work for you.
Want to up your game even more? Need more of these tips and tricks? Let’s get some time on the calendar to chat about your Excel needs! Contact me about individual trainings, team trainings, custom Excel/Google Sheets projects, or anything else with Excel/Google Sheets.
Do you know who Emmitt Smith is?
If not, go wild on the googling. What you’ll learn is that Emmitt Smith is one of the most celebrated pro-football players in history. But what you might not learn is the incredible way in which Emmitt Smith went about achieving all of his football goals, including becoming the all-time leading rusher in the game. In case you don’t know, that basically means he ran with the football the most of any player in the history of the NFL.
Until you write them down, they are just dreams.
When Emmitt Smith was in high school, his coach taught him and his teammates to write their goals on a piece of paper and put it in their lockers so they could see it every day and know what they were working towards each day on the field. The coach told them that until you write them down, they are just dreams. Once you write down your dreams, they become goals. Every year, Emmitt Smith wrote down his goals and worked towards them relentlessly. And he achieved greatness in his sport, and in many other ways in his life. Before Carol Dweck’s work on the growth mindset gave scientific backing to the idea that your mental outlook on your goals and challenges has a strong influence on your ability to achieve your goals and overcome your challenges. If you envision your goals and believe that you can achieve them, you are more likely to succeed. Our mindsets can make all the difference.What do goals look like?
Goals can take a lot of different forms - they can be a number (I want to be able to do 100 push-ups at a time), an output (I want to write a book), a skill (I want to be able to play piano), an award (I want to have a superbowl ring), or any other incarnation of your dreams. You have to do what is right for you. But I would strongly suggest that if you are making some goals around technology, don’t make them tech-focused. Instead, set goals around the impacts and outcomes that can come from using technology. Here is a personal example:
When I started learning Excel, I didn’t know what I didn’t know, so I didn’t know what my goals should have been. I had two colleagues who were really really really good at it - they moved so fast I could hardly understand what they were doing and talking about. They worked like magicians with the keyboard, the numbers, the colors. They seemed unstoppable. I wanted to be just like them. In a way, my goals were living, breathing people in front of me each day. But I had no idea how I could become unstoppable, too. They would throw terms around that I could have written down in goals, but honestly I wasn’t even going to understand what I was writing down: PivotTables, Vlookups, Absolute References, Sumproduct, Automated Processes, Linked Spreadsheets - my colleagues were talking about and doing these things all the time like it was nothing, and I didn’t even understand what the words meant! So I couldn’t make my goals around those technical things. I had to make them more about the impact that would come out of picking those things up along the way. Here is what my goals for Excel looked like in 2011:
- Be more willing to ask questions until I really understand what I am doing.
- Increase my speed of using Excel to get requests done for my colleagues faster.
- Don’t waste time doing manual processes when they can be automated.
I put these goals up at my desk and looked at them every day. It reminded me that I wanted to be an expert who could quickly provide quality support to my colleagues. If I started to do something repetitive that wasted time in Excel, that was taking away from my goals, and so I had to switch gears and learn how to do it better and faster, or how to automate it. I have had somewhat similar goals every year since then, which is how I eventually got to starting my business. Now everything I do in Excel I look through the lens of my business mission: To help people learn, use, and love the technology that supports their work.
Now I want to hear from you! What does your technology goals worksheet look like? How can achieving these goals have an impact on your work and your life?
A few years ago, I took a storytelling workshop meant to help participants craft and share a personal story in order to make deep connections with listeners. We got a basic prompt (“tell a story about something important that has happened in your life") had about 10 minutes to think through the entirety of our lives up until that point, pinpoint one important story to share, craft some of the structure and themes of our story, and then get ourselves psyched up to share our most personal moments with a group of strangers.
Have you ever been asked to be vulnerable and share a story about yourself? How about a story that showcases the most important aspects of your work?
Our stories about ourselves and our work are based in both quantitative and qualitative information, and both can be surprisingly challenging to shape into a clear and impactful message. The workshop that I attended was helpful in preparing me emotionally to talk about myself and my work, but I was still needing more storytelling training in other areas, like thinking about the perspective of my audience, clearly articulating the message I wanted people to walk away with from hearing my story, choosing the right details to articulate the critical information, and creating visual representations of my story to support and enhance its impact.
Believe it or not, the way I actually developed these storytelling skills was through learning to use Excel. But because Excel starts off filled with bland gridlines, unending rows and columns, and a black-on-white display that can make anyone quickly lose interest, how is it possible to keep your audience engaged, captivated, and understanding the message you want them to get? It is surprisingly similar to what you might do if you were writing a short story, with just a touch of technical skill.
Here are the top three ways to become a master storyteller in Excel and - surprise, surprise - only one of them HAS to happen in Excel!:
- Write out your message in English first.
- Design for your audience.
- Learn to use dynamically shifting text.
This might seem like an obvious tip, but this one can really trip people up when they are getting started using data and Excel. There are so many other things you can start with - getting your numbers lined up, creating graphs and fun visuals, throwing in colors, making all different kinds of PivotTables of information. But when you start with these things it is far too easy to get caught up in the details and the technical stuff, and to move off-message. Before doing any of that, open a new tab in Excel, or a blank Google doc, or grab a piece of paper and a pen, and start writing out what message you want your audience to take with them from your spreadsheet and/or presentation. When you do start going back to your data and functions, you can always check back in with what you wrote down for your message to make sure you are sticking to it with anything that you present.
Some people like to read long, flowing descriptions of scenes, some want to know every little detail their characters are thinking and saying, and some want short sentences that are direct to the point and get them the information they need. The same is true for looking at data and information in Excel. As you are putting together your message and story in Excel, there are many different ways you can present it. The best way to get the message to your audience is to do it the way that they will best receive it. If your audience only wants the high-level information and would get bogged down in details, you’ll want a simple and clear message clearly displayed, without a lot of other words and numbers that could get distracting. On the other hand, if your audience tends to asks tons of questions and will want every last detail, make sure those details are clearly organized so that you can pre-empt those questions where possible. There are a ton of other configurations this could take, depending on who is looking at your presentation.
Excel formulas can do a lot more than math - they can help you keep your story and message up-to-date as your data and information gets updated as well. By just using an ampersand (&) and some quotation marks, you can make sure your message always is in line with your data. Let’s say you want to always present on the number of volunteers and the hours they put in for your organization each month. The sentence you always want at the top of your sheet is: This month ([MONTH]), we engaged [NUMBER VOLUNTEERS] for a total of [TOTAL HOURS] hours. But of course, the month, number of volunteers, and volunteer hours change each month. So you can have a formula of both text and numbers that looks like this:
Want to up your game even more? Need more of these tips and tricks? Let’s get some time on the calendar to chat about your Excel needs! Contact me about individual trainings, team trainings, custom Excel/Google Sheets projects, or anything else with Excel/Google Sheets.
It’s football season, and this year I am absolutely obsessed.
When I say obsessed, I mean that I am watching the games live, re-watching them later, watching the shows and documentaries on NFL network, listening to podcasts about the NFL, and you can’t really have a conversation with me without getting pulled into some football talk. So when you are obsessed like this and filling up your head with everything possible about one subject, you start to hear the same commentary about the games and the players over and over again. And this year I’ve been hearing about two themes in football that are super well-aligned to technology and Excel: Time and Vision.
Quarterbacks trying to move the ball down the field have very little time to think and make decisions. Most football plays have about 25 seconds to set up and then once the play starts, most end after about 10 seconds. In those 35 seconds, quarterbacks need to review how the defense on the other team is set up to attack, possibly adjust their own play based on that, get their players in the right places, know how much time is on the play clock. Once the ball is snapped, they need to watch their receivers running all around the field, keep an eye on those defensive players from the other team and the players on his own team trying to protect him, and do all of that while blocking out the sight of thousands and thousands of fans screaming for or against his success. To be successful, quarterbacks need to have a keen sense of time and a wide field of vision.
Time and vision - two things that are also incredibly important for learning and using any technology, and especially Excel.
Even though you may technically have more than 35 seconds to set up and run your play in Excel, it doesn’t always feel like it is worth doing any more than that. How does it feel after struggling with technology for about, say, 15 seconds, or 15 minutes? It is frustrating and annoying, and everything that keeps coming after that can seem like a total waste of time. And I know that your job isn’t to be the quarterback of Excel - you are teaching, or planning, or selling, or volunteering, or managing, or whatever your real job is to do. So you can only spend so much time in Excel when you’ve got other important work to be doing. In the time that you do have, you are trying to keep a lot in your field of vision. Just think about a typical blank Excel document - you’ve got hundreds of rows and columns, thousands of cells, buttons everywhere, and not to mention all the information that is sitting behind the scenes that you are trying to keep in your vision. In Excel, there is always more than one way to do everything and there are unending options for how to format your information to make it look attractive - That is a lot to keep in mind and trying to envision for whatever you are doing with your data and technology.
Quarterbacks are paid millions of dollars to get as good as they are - but what about us regular people trying to get good at Excel? Here are a few tricks of the trade that can help you up your game:
- Use keyboard shortcuts. Just like quarterbacks have to throw the same pass over and over and over again to perfect it, using keyboard shortcuts can take some practice and repetition. It might feel hard to use them at first when you are just getting used to them, but you will see exponential results. The more you use them, the more natural and second-nature they become, and you will see exactly how much time they will save you. There are dozens of good shortcuts, but start out with your basic navigation. Ctrl+arrow keys on a PC and Command+arrow keys on a Mac. Try them out, see how they feel, and if you catch yourself reaching for your mouse or touchpad to do any scrolling, stop yourself and use your keyboard instead. The more you do it, the better it will feel.
- Separate your data. There are times when you want to put a lot of things in just one little Excel cell, and times when you really don’t want to do that. For the most part when you are organizing your information before presenting it, you want to make sure you’ve got only one concise piece of information in a single cell - and every cell in that same column should also keep it small. A key example of this is that you don’t want to mix words and numbers - that makes Excel get a little headache and complain when you try later to search, sort, or filter for something specific. So you don’t really want this:
Nov 13 - webinar Nov 18 - class Dec 1 - Study group
- When presenting information, ditch the gridlines. Football and Excel both have really important gridlines that help you see where everything is in your data. But sometimes they can get overwhelming and messy. It would be hard to erase the lines on a football field for a clearer view, but in Excel you can easily ditch your gridlines, and use borders around your presentations instead. Just head over to the View tab, and uncheck the Gridlines options:
Instead, you want:
Want to up your game even more? Need more of these tips and tricks? Let’s get some time on the calendar to chat about your Excel needs! Contact me about individual trainings, team trainings, custom Excel/Google Sheets projects, or anything else with Excel/Google Sheets.
Excel users are people from all kinds of backgrounds, who are at all different levels of comfort and confidence using technology, working in tons of different sectors, and who want to do vastly different things in Excel. But all these different people inevitably have the same question over and over again when working in Excel, whether they are asking their coach or themselves:
“Am I doing this right?”
With my coaching clients, I hear this question after we go over doing something new in Excel, and it’s time for the client to try it on their own. They take a few tentative steps, and stop just before finishing to ask me - “is that right?”
It is a totally natural question to ask - before you take that final step, you want to validate that you’ve done everything right. If not, why take the time on the last step? Of course, the answer everyone wants to hear is “yes you did it right” or “no, you messed up, here is how you fix it.” But neither of these answers is the right one - in fact, no answer is right to this question. The reason for this is because there is actually a really important reason to take this last step before getting any validation. Creating anything in Excel is a series of steps that are getting you from point A (the idea of what you want to do or make) to point Z (successfully creating or doing it). Every single step taken along the way is a critical learning moment, and none more so than those steps that you are unsure of. And that is why I always answer the question the same way:
“Try it out!”
My coaching clients tend to give a nervous giggle at this point, and then they go for it. About 50% of the time, everything has worked out and they feel excited and validated in their work. The other 50% of the time, they get an unexpected result and we have to go backwards over our steps to figure out what went awry. There is a built in safety net when you have a coach watching you work and helping you troubleshoot. What happens when you are on your own?
The best thing that I can teach as an Excel coach is how to do it without me there as a safety net. That takes a lot more than just technical knowledge. It takes the confidence to try things out, to get things wrong. You need the approaches and the mindsets that will help you to stick with your work and figure things out on your own. These things are what will support you in taking the technical Excel skills that anyone can learn and applying them to your unique needs and the outcomes you want to achieve.
Ready to learn the mindsets you need and how to apply these with technical skills? Join my FREE email course:
Mastering the Excel Mindset
I’ve been reading Thomas Friedman’s book Thank You For Being Late: An Optimist's Guide to Thriving in the Age of Accelerations, and it has got me thinking a lot about what it means to be living in the so-called “information age.” Technology is part of nearly every single aspect of our lives, and becoming more all-encompassing every single day. Everyone is managing the incorporation of advanced technologies as part of our everyday lives in different ways. Some people - especially those young enough to have grown up with technology (I’m looking at you, 90’s babies!) - seem to just pick it up, run with it, use it, even improve upon it without any apparent hesitation or fear. Then there are others (of all ages and backgrounds) who look at technology and everything seems to go haywire. But no matter who we are or what we do, learning and using technology is baked into the fabric of the world we live in, and we all need to find the ways that we can make it work for us - and not just work us over.
Excel is one of those technologies that is expected to be used in nearly any job sector and function, similar to how Microsoft Word and PowerPoint are ubiquitous across nearly all roles in all businesses, government agencies, and non-profits - large or small. But what is the right level of comfort and use for these programs? You don’t need to be a best-selling author to use Word, and you don’t need to be a graphic designer to use PowerPoint. Similarly, you don’t need to be a data nerd or a techie to use Excel. In fact, a lot of data nerds and techies use even more complex tools and programming for their work. Excel (and it’s equivalent in Google Sheets) is actually the best program for people who are not technology or data experts to use for so many useful and vital parts of their work.
So how can you tell if you need more Excel in your life? Here are a few tell-tale signs that you should be learning and using Excel more or in a different way to add value to your work and life:
- You want to de-clutter
- You are looking for more time
- You want to move up at work
- You need to keep track of a lot of things
You want to de-clutter
Digital clutter and information overload got you down? Collecting information and resources digitally is part of everyday life for most people in the information age. But with the amount of information that is available and constantly being updated, it can become overwhelming to collect so much digital data, and it can actually block our brains from being able to use technology for the benefits it can provide. If you find yourself not learning existing technologies and trying out new ones because of a block like this, that is actually a big red clue pointing to the need for more Excel in your life. Excel is the ultimate organizer and recaller of information. Budget software like Quicken can handle everything about your money, Event planning software can keep event information as organized as possible, and the calculator on your computer can do a wide range of complex mathematical functions. But NONE of these programs can do all of these things at once, because they are designed to do one thing and do it in a standardized way. Excel can do all of these things (really anything you can imagine), and it can actually do it better than a lot of programs focusing on one thing only. If you are feeling a bit overwhelmed with your digital clutter and information overload, Excel is your new best friend. It is designed to force you to be clear about your data and to make sure it is “clean” (usable for gathering insights), searchable, and accessible.
You are looking for more time
Using technology in all parts of our lives came with the promise to do things more efficiently and save us time - so then why do so many people feel like they are busier than ever and would love to get a few extra hours into each day? Like in a story about magic or superpowers, technology has the potential to be used for good (time-savings) but can also be used for evil (time sucking). Which way you use it is all up to you. If you are a person looking to gain a little more time back in your life, then learning and using Excel can go a long way towards that! Excel is built to automate anything that you would do over and over again, day after day or week after week. If you find yourself doing a ton of manual work in Excel that is taking up your time, then you are missing out on a huge opportunity to automate your processes and save time - so you actually need more Excel in your life to learn how to make things happen automatically. It can be really hard to do, but putting in more time upfront to learn a bit more will save you innumerable hours in the future!
You want to move up at work
This could mean that you want more responsibility in your current role, or you want a promotion, or you want to switch jobs entirely and move up the ladder or move to a job that will make you happier. For any of these things, I can guarantee that getting more Excel in your life can help in multiple ways. First, as was just mentioned, time-savings. With more time you can get more accomplished, and automating processes in Excel lets you focus long-term on what you are best at in your job. Second, nearly every job now works with Excel in some way, and the better you can do it, the better your prospects for job advancement. People are seriously impressed with and envious of Excel skills, and you can be the one that they look to for answers and good work with Excel. Third, Excel is a driver of insights - when you know how to use it to organize your data into information, then you are primed to learn insights and knowledge from that information. The ability to gain insights from data and information is at the top of the list of amazing qualities for so many employers and in so many roles. Plus, you get the added bonus of feeling more confident about any decisions you are making, since they will have the data and information to back them up. Finally (but really there are still so many more reasons), knowing how to use Excel is like a gateway towards knowing how to use all sorts of technologies and systems. The methods and mindsets needed to learn Excel are the same that you’ll need for learning nearly any technology, and because Excel covers so much ground, it is the perfect classroom for getting into the technology space.
You need to keep track of a lot of things
From both a personal or professional lens, there is always more information that we can and should keep track of and know about ourselves and our work. From number of steps to calorie intake to heart rate to money spent to volunteer hours to productivity rates to anything that you can imagine wanting to know more about. If you have a lot to keep track of, then you need more Excel in your life! Many organizations use “trackers” of some sort, and though not all of them are as good as they could be, their intentions are in the right place - it is helpful to know what is going on in your life and work so that you can use that information to make things even better. The more comfortable you are using Excel to track data, and the more knowledgeable you are about how Excel can help you format and present that data, the more you will be able to quickly and easily keep track of anything in your work or life.
Need more Excel in your life but not sure where to start? Check out my free email course:
Mastering the Excel Mindset
Have you heard the saying “Change is the only constant”? It is often attributed to the Greek philosopher Heraclitus, who wrote Panta Rhei or Life is Flux. Basically, everything is always in motion, always changing, always in flux, making “change” the only thing that is constant in the world. Sometimes change is awesome - get a new job, move to a new city, meet a new friend, or get surprised by good news like your rent is going down, or you are getting a raise, or you win the lottery. Any of these things could change our lives for the better a lot or a little. But other times change hits us with the unexpected that throws us off our path - an unexpected tax bill, a shift at your job, a hurricane.
Whether it is good, bad, or even neutral change - it can be disruptive. Even if your entire life isn’t up-ended by some big change, making any re-arrangement to our view of the world takes time and energy. This often times feels particularly taxing when it comes to our data and spreadsheets. You set up your spreadsheets to handle one set of data, and then another set comes along to throw everything out of whack. Or you did a bunch of calculations to get to a final number, but then you get some new information and the parameters change, making all your calculations wrong since they were based on a different set of rules or assumptions.
If change is constant - in life and in spreadsheets - then what can you do to deal with it? You may be surprised to learn that the same things that drive people to successfully deal with change in life are the same things that can be done in Excel to deal with change in your data. It is all about setting yourself up to:
Thinking about the future
This one never came naturally to me. I live mostly in the present, and I don’t think about what the future looks like. But too many times I ran into situations where I didn’t think about the future and when it hit me, I wasn’t ready for it. If you are like me, you don’t need to make any big changes to your personality or have a crystal ball to see into the future - you just have to stop for a moment or two to think about what could be. There are innumerable ways that life could twist and turn, and we can’t imagine and hold all of them in our heads - there are just too many variables. So why worry about trying to hold on to all of that? Just take a minute or two to sit back and think about what changes might be possible to come your way in the future. When you are working with Excel, before you start building your spreadsheets or adding your data, think for a minute or two about how your data or needs could change in the future.
Being flexible and adaptable
A natural and very common human reaction to change are feelings of disruption, confusion, frustration, and fatigue. It can be mentally taxing to think things are going one way and then you are hit with information that changes your path. Research suggests that the natural reactions to the stress of change have some evolutionary origins, but that chronic stress - from chronic change - can contribute to a number of physical health problems. Luckily, research has also been done to identify the ways to copy with change, and finding ways to be flexible and adaptable top the list. It may seem comfortable to find a specific path and try to stick to it, but we know that change will take us off that path. It won’t look the same for everyone, but however you can find to “go with the flow” will help you in life, and of course in Excel.
Get prepared for change
What does it mean to get prepared for change when you can’t possibly know when or what change will come? Sometimes you can arrange the plans you lay and the tools you use so that they can set you up to accept changes. In Excel, there is a built-in approach for dealing with changes to your data: it is called Dynamic Referencing. This is actually a fairly simple, yet powerful, concept. The basic idea is that if you are going to be calculating anything, you want to be prepared for the fact that your data could change. For example - let’s say that you are calculating the average score of grades in your class. You’ve got 10 students, and you enter their test scores next to their names in your spreadsheet. Seems pretty simple, right? Since Excel is just a big calculator, you can just add all those numbers up in a cell, like this: =98+87+70+96+80+98+87+70+96+80 etc etc. Then you know that to get an average you divide that total by the number of students, so in another cell you put the total you got over 10, like so:
And Excel spits out your average - 86.2%. That would all be fine and good...if change wasn’t inevitable. So you go back and realize that you entered one of the student grades incorrectly, and then two students unexpectedly transfer into your class and quickly take the test, so now you’ve got to add in their scores. Now not only do you have changes in your data, but you would have to make those changes in your calculations as well. On this small scale, that may simply be annoying and time-consuming. But imagine this on a large scale, and it can become impossible.
Instead of writing your calculations out the way you did, you can instead “reference” the cells that have the information you need, so that when that information gets updated, your calculations are prepared to take those changes in stride, and give you the correct information you need in under 1/1000th of a second. This is done with what is called “Dynamic References.” The “dynamic” part is all about you know what - change! When something is dynamic it means it is updating and changing. And “reference” means pointing to a cell or range of cells, or “referencing” those cells. There are many different ways to use dynamic references, from the very simplest to the most complex. Let’s start simple. You can add 2 and 2 together in one cell using Excel as a calculator, and it looks like:
Of course you will get 4. Or instead, you can have the number 2 in two different cells, and in a third cell you will reference whatever values are in these first two cells, like so:
You get the same answer either way, but with dynamic references you’ve unlocked the potential and the plan for dealing with change. This simple example can be taken up to any level of complexity. But the thing to remember is this: If you find yourself typing numbers into your formulas (or “hard-coding” them into your formulas), then you know to step back and prepare for change by using dynamic references instead. It will save you time and energy in the future, since nothing is constant but change.
Like this post? You might also enjoy my FREE 7-day email course:
If there is one thing that technology is NOT supposed to be, it is a waste of time.
In fact, isn’t the point of technology to help us do everything smarter, better, and most importantly, faster? This is especially true of Excel. Before we had spreadsheets, we had to write lists out on paper, scan down them with our fingers hoping not to miss any details, count things up manually, and generally spending thousands upon thousands of extra hours looking at data and doing calculations that Excel can complete in mere seconds. When Excel came along, time-savings and efficiency skyrocketed - but it seems like mostly for the people who really know and understand the system inside and out. But what about everybody else that has to use it? Sometimes it can seem like instead of saving time, Excel is costing more time in all the unknowns, the mistakes, the error messages, the weird results, and the seemingly endless amount of Google searching that can be done to try to find the right answer to an Excel question.
No one goes from 0 to 60 their first time.
Even those people who seem to know how to do everything and anything in Excel didn’t start out that way. Everyone has to learn how to use Excel at their own pace. For some people that will be slower than others. A slow start can sometimes be a big roadblock for learning and practicing Excel. And that is understandable - it is counter-intuitive to consider putting in a ton of time just learning how to use a system that is supposed to save you all that time in just being able to “do it for you.” It’s true, there is no getting around this. However long you need to learn Excel is how long it takes. But luckily there is one thing that you can depend on from learning Excel:
However much time you put into learning Excel now, you will save yourself double that time or more in the future.
This is always the hardest part - and in some cases it can seem like a leap of faith. It can be especially hard to remember and believe when there is a time-crunch or a deadline that has to be met. And at those moments, it might not be the right time to sit down to learn and practice all of the potential time-saving functions that Excel has to offer. But whenever it is possible, taking the time to learn and practice will pay off dividends in future time-savings.
To get you started on your path towards being faster and more efficient in Excel, here are XX tips that you can use right now that I guarantee will save you time as soon as you start using them:
1. Highlight rows and columns with keyboard shortcuts
When you need to highlight an entire row or column, a simple keystroke can do this for you with speed and accuracy. Just hit Ctrl+spacebar to highlight an entire column or Shift+spacebar to highlight an entire row. Happily, this is one of the few keyboard shortcuts that are exactly the same on both a PC and a Mac.
2. Find (and Replace) anything in your spreadsheet
There are times when you just want to find something - a word or a number - quickly in your spreadsheet. You can use the keyboard shortcut Ctrl+F / Command+F to open the Find function, or hit the Find & Select button on the Home tab.
This is also where you can switch over to the Replace function, if you need to replace all instances of a word or set of characters with something else.
3. Check out two spreadsheets at the same time (View side-by-side)
Do you have the same or similar data on two spreadsheets, and you need to look at them both at once? It is so easy in Excel! While both spreadsheets are open, go to the View tab on either one and hit “View Side by Side.”
4. Copy your formatting from one cell to the next
Have you figured out the most beautiful display of information in one place in your spreadsheet, and wish that you could copy all of that work from one place to the next? Copy-and-paste won’t cut it because you’ll write-over whatever text is already in your other cells. So instead, you can use the Format Painter. Select the format you want to copy, click on the little paintbrush in the Home tab, and then select where you want that formatting to go!
5. Remove duplicate values
There are so many reasons why you might need to get rid of duplicate values. Just one of those could be if you want to get a list of unique values from a column in your spreadsheet. Using the Remove Duplicates function in Excel will get rid of the duplicate values, so make sure that you either want those duplicates deleted OR isolate the data you are manipulating. Other than that, it is so simple - highlight the cells that you want to remove duplicates from, and on the Data tab click on the Remove Duplicates button:
Want even more time savings in Excel?
Sign up for my newsletter and get a
Top Ten “In The Know” Excel Tips & Tricks:
Subscribe and get a free download: What you need to be "in the know" with Excel
What is it about Excel that can make people pull their hair out?
Whether it is numbers not adding up right or forgetting how to do that one thing you want to do - it can be overwhelming and frustrating to the point that you just give up.
Excel is a tool that almost everyone is using or trying to use, but that most people don’t know how to use it, don’t like using it, or spend too much time not getting enough benefit out of it. This is especially true for people who aren’t “techies” or whose backgrounds and strengths are in areas other than technology. For a lot of people, just learning the technical parts of Excel just doesn’t cut it. Some of it will sink in, but most of it will be forgotten. Inevitably, they will hit snags, get unexpected results, and be super frustrated. In the end, they would keep on doing things manually - like counting things up by hand or doing a ton of copy-and-pasting - that would take forever and still end up with lots of time-consuming errors and mistakes.
After years of teaching people of all backgrounds and skills how to use Excel, I’ve heard all of the reasons why Excel is hard and frustrating to work with. But I’ve also seen even the most techno-phobic people turn the corner and see the benefits of overcoming their frustrations and fears. So if learning just the technical pieces doesn’t cut it, how can non-tech people learn how to use Excel to its fullest benefit? I’ve worked with hundreds of people on getting over their roadblocks by teaching them the mindsets that can lead anyone to success in using Excel. These mindsets are all about Making Excel Work For You. Not making you work for Excel, and not making Excel work for anyone else’s needs. Making it work FOR YOU. Now you can get the same coaching in an online course that lets you go at their own pace.
This course isn’t for everyone.
If you are someone who knows that you need to use a vlookup formula, can search on the internet for instructions on how to do it, follow the instructions, and figure out how to make it work for you, then you are doing great and you don’t need this course! You do need this course if you have tons of other strengths but have struggled to learn and use Excel. After taking this course, you will have the key mindset approaches to Excel that will let you:
Be confident doing anything in Excel
Work through any errors you might encounter
Break down your big ideas into attainable steps
Work faster and more efficiently in your spreadsheets
Never give up on finding a solution
Celebrate the progress you make in your learning
Turn the boring parts of Excel into fun challenges
Build trust in your data
But the number one thing that you’ll get out of this course is that you will be able to be your own teacher in the future - you will be in control of your learning and continuing to make Excel work for you.
And of course, you will learn the important technical pieces, too, including:
Simple and advanced sorting and filtering
Freezing rows and columns from the scroll
Keyboard shortcuts to save you tons of time
The most frequently used AND the most useful formulas
Absolute and relative references (those little $ in formulas)
How to create charts and graphs
Tips & Tricks that will make you love Excel
With the mindsets and technical skills you will learn from this course, you will be able to smash your Excel fears and take control of your spreadsheets.
Are you ready to get everything you need to Make Excel Work For You? Sign up below to get updates on when this course is released and let me know what YOU want to learn to do in Excel:
Sign up for updates now - and get a FREE BONUS download
Have you ever taken something apart to see how it works or to fix it?
Before my daughter was born, my wife and I got ready for her arrival by decking out her room - crib, rocker, tons of toys, and an adorable mobile. That's the contraption that hangs over the crib and plays a cute little tune while hanging giraffes go around and around. The baby loves it. So one day I was turning the crank to entertain the baby and pop! - something inside clicked, and that was it. No music, no spinning giraffes. I could crank it all I wanted but I could tell something had gone wrong, because it wasn’t winding up or working anymore. That was NOT okay, since the baby loves watching it go around and around. I had to do something.
Normally, I’m not a tinkerer. I don’t even usually read the instructions for assembly, much to my wife’s chagrin. But as they say, everything changes when you have a baby, and something changed in me. I decided to take apart that mobile and fix it. First thing I did was to take out a screwdriver and open up the back. I started to lose a little bit of patience when I realized that there were two sizes of screws on the back, and one required a smaller screwdriver than I had picked out to use. I had to get back up off the couch, search around for another screwdriver, then take out the second set of screws. Now I had 3 small screws and 3 even smaller screws that I needed to keep track of. OK, back to taking things apart. I lifted off the back and saw a lot going on. One thing I recognized was a music box - you know those little cylinders with bumps on them that sound the right notes as it spins and hits the bars. There were three or four wheels with spokes that spun each other, and another little box inside that must have had even more mechanisms inside it. I was pretty nervous that I was just going to break the whole thing apart in my hands. So I took stock of everything in there and looked at each piece individually. I looked at the connections between the pieces and tried to figure out what the problem was. Honestly, it was basically by luck that I moved my finger across a little piece that I guess was stuck somehow and unstuck it, making the whole thing start working again. It was pretty darn cool - the wheels started to spin, the music started to play, and I could see a happy baby in my future. I was intrigued by my tinkering - I stopped the wheels and looked around a bit. I held down a piece, touched something else, wound the whole thing up, and tried to break it and fix it again. I admit, I didn’t really understand all the mechanics of this simple little device, but I got closer to understanding it, and when I screwed those different sized screws back in and hung it up again for my daughter to watch it twirl and listen to the music, I felt a swelling amount of pride that I’d even attempted to figure it out. I now have this secret hope that it will break again and I will be forced to take apart more pieces so I can really learn how the thing works.
One of the most important aspects of learning how to make Excel work for you is tinkering. Taking things apart, breaking them down into smaller pieces, understanding how each simpler piece works and is put together to make the whole.
Two good examples of how tinkering is the best way to make Excel work for you come from two different situations. The first, you are working with an Excel template or a spreadsheet that somebody else has made and has given to you to work with. The second is that you have an idea or vision about what you want to do in Excel.
In the first example, you are likely to run across some things you don’t understand. Let’s look at the example of a complicated formula. You might see something like this:
Now is the time to start freaking out, right?
There is a lot going on here, but that doesn’t mean that you can’t tinker around with it to try to understand what is going on. Take it apart, break it down into simpler, smaller pieces, and see what you can understand. In this example, you might have seen that there are a few different formulas happening all at once. You could take them apart and try to see what they all do. For example, take the MATCH formula - MATCH(C2,$A$1:$A$6,0) - throw an equals sign in front of it, put it into a new cell, and you can see what the result would be. Maybe you didn’t realize that there were a few formulas in here that you could break out. No problem! Take a look at the cells that are referenced - like B2, C2, A1 through A6 and K1 through K9 - at least you can see that these are cells that are important to the calculation. Don’t know what those dollar signs mean? Type into Google “what do dollar signs in Excel mean” and start to get an idea of what is going on with those cells. Tinker with it, and you’ll start figuring out how each of the pieces fit into the whole.
In the second example, you have an idea of what you want your spreadsheet to do, but you may not be sure how to get there. You have a vision of all the information that you will have in your spreadsheet and you know what you want the result to be. For example, you’ve got a spreadsheet of all your donors and volunteers and the hours they’ve put in, and you want Excel to get you the total of all volunteer hours for each person and also to show how many repeat opportunities a volunteer has done, and to mark if they have also donated money or not. As you might guess, there is not one formula or one special function that will do all of that in a single move. In fact, you might find yourself needing at some point to write a formula similar to the one in the first example. But before you can even try to learn how to write the technical piece, you have to write out your vision in plain English. Break it down into smaller pieces. Take just the first part of your needs (total volunteer hours) - there is no Excel function for giving you “Total Volunteer Hours” so you’ll need to take a look at your spreadsheet. See what you’ve got, and write out what you want to do. If you aren’t sure how to do it, break it down into smaller pieces and go looking for the answers. It can be as simple as Googling “how do I add up two different cells in Excel?” and you’ll find an answer out there. The more complex your vision, the more pieces you’ll need to break down and then add up together again to get to your goal. But each smaller piece is something that you probably already know in Excel, and all you need to do is put those pieces together.
Tinkering - the key to making Excel work for you. Once you understand the smallest actions in Excel, you can build on top of those to get to the more complex stuff. You don’t need any special background for this. In fact, some of the people who are extremely technically knowledgeable about Excel don’t come in with the tinkering mindset, and they will never get Excel to work for them the way that you would be able to. Take it apart, look all around, and try to put it back together. You can totally do it!
Like this post? You might also enjoy my FREE 7-day email course:
Mastering the Excel Mindset
Excel templates are Excel files that come with pre-made formatting and calculations. Using Excel templates has its advantages - they are set up to do things that you don’t know how to make Excel do yet. So you get the chance to just put in the data that is relevant to you, and then everything pre-built should do the math, add the colors, and hopefully get you to some new information that you wouldn’t have been able to get on your own.
There are uncountable Excel templates out there for anything you might need to do: budgeting, fundraising, project management, event planning, calendaring, gradebooks, attendance tracking. Basically if you have a need, there is a high probability that someone has made an Excel template for it, and you can go find it on the internet. There are some pretty amazing ones here: https://www.smartsheet.com/top-excel-budget-templates
Excel templates can be really great - they can get you started working in Excel, give you ideas about how things can be set up in Excel, show you what is possible in Excel, and if everything is working out just how you need it, then it can save you the time and the energy it would have taken you to learn how to build something like it yourself. Excel templates tend to take advantage of some of the best and most important benefits that Excel offers: automation, data validation, dynamic updates, beautiful formatting, etc.
But Excel templates can also hold you back. Only the most simple of templates are perfectly easy to use. A lot of templates come with shiny features and lots to take advantage of - but that also means you have to figure out if everything in there is relevant to you. Because things are set up in ways that you might not understand, it is super difficult to change anything to fit your specific needs and it is super easy to break something in the template. Plus, you cheat yourself of trying to figure out on your own how to do all these cool Excel things. You can get frustrated when things don’t work out, because you don’t understand what is going on in the spreadsheet to make it all work.
So basically, the top 3 reasons to use Excel templates are sort of the same reasons why you shouldn’t use them:
|Why you should use Excel templates||Why you should NOT use Excel templates|
|1. Everything is built already, so you don’t have the frustration of trying to make it work yourself.||1. Everything is built already, so you can get easily frustrated when it doesn’t fit your specific needs and you aren’t able to customize it.|
|2. Templates usually have the best that Excel can offer: automation, data validation, great formatting, dynamic updates, and more. You get to take advantage of them all.||2. Templates usually have the best that Excel can offer, but you aren’t learning how any of it works or how to do it yourself. The templates tend to lead you away from a growth mindset and keep you away from learning - templates might be of some help in the short-term, but in the long-term you won’t be able to do anything on your own outside of the template.|
|3. Everybody loves shiny things, right? Excel templates look so good and can have so many cool features.||3. Shiny things can be super distracting. If you don’t need everything that is in a template, it can be easy to get lost in the mix of everything in there, and end up not using most or all of the template.|
If you are working in Excel but don’t want to use a generic Excel template, what else can you do? One option is to work with me! I love working with people to get them exactly what they need in Excel. Let me know what you need and we’ll work together to get you an Excel spreadsheet that is tailored to your needs.
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.
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
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
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.
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: