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: