By now you know spreadsheets can do some a whole lot, but today is where it gets really fun, because today we are talking about a whole list of super cool things you probably had NO idea a spreadsheet could do.
That’s right! Those bells and whistles I promised you spreadsheets have? We’re finally digging into them!
Prefer to listen instead of read? Scroll to the bottom for the audio version of this post!
Before we get to the good stuff, let me allay any concerns by letting you know the vast majority of these spreadsheet hacks are not that complicated.
It may take a couple of tries to get them right or figure out exactly how you like to use them, but most of these do not involve super complex or complicated formulas and craziness that can get jacked up and make a spreadsheet start yelling at you with error codes and stuff. Sound good?
COOL THINGS YOU DIDN’T KNOW SPREADSHEETS COULD DO
With those fears laid to rest, let’s dive into the fun stuff!
CONDITIONAL FORMATTING
With the exception of your basics like text formatting, and the slightly more advanced data validation, conditional formatting is hands down my favorite and most-used spreadsheet feature.
Conditional formatting is when you put together a set of conditions — like being equal to an amount of money or before a specific date — and tell the cells to do different things based on those conditions.
For example, on a budget spreadsheet (or any other numbers related one) you could set cells to be green if the amount in them is under a certain dollar amount, and red if it’s over. (Or purple or magenta or whatever other color you feel like using!)
If you’re using a spreadsheet to track to dos or plan an event, you could set cells to change color based on how close you are to a specific date.
This is a conditional formatting set up I used a lot to keep track of all the deadlines for planning my wedding! If it was before the due date, the cell would be green. If the task was due tomorrow, it would turn yellow. If it was due today, it would turn orange. And if it was overdue, it would turn red.
For visual processors, conditional formatting is a simple way to set things up so it’s easy to quickly intake information and figure out what to prioritize!
RELATIVE DATES
If you’re an event or wedding planner, maybe you host one retreat each year for your business, or you just have your client process mapped out to a tee, relative dates can be your best friend!
Chances are, if any of the above applies to you, you know exactly how far before or after an event you need to get certain tasks done. There are things you always do immediately after you book someone, things you always do six months or three months or two weeks out, and follow up tasks you take care of one day, five days, or three weeks after an event. (Or you’d like to streamline your process to get it to that point! If so… let’s talk!)
The timeline doesn’t change, just the date. Well, you can use relative dates in a spreadsheet to map out due dates for an entire project or event timeline simply by inputting the event date!
All you have to do is designate the cell your event date is in (say B2) and add or subtract the number of days to or from that date.
Need to complete a task three weeks before your client’s wedding date? Input the formula =B2-21 into your cell and it will automatically set that date for you every time!
Need to send a follow-up to your conference attendees one week after the conference concludes? Input the formula =B2+7 into your cell, and you’re good to go!
Add this together with conditional formatting and it makes tracking deadlines and to dos easy peasy!
AUTO-FILL IN OTHER SHEETS
Whether you’re working with budgets, event information, or guests for podcasts, the last thing any of us want to do is keep retyping the same information into different locations over and over again, right? It’s a pain in the neck and a waste of your precious time!
Fortunately, spreadsheets can take care of that legwork for you by using the auto-fill feature!
In a spreadsheet, every single cell is unique — there is only ever a single B5 or C12 on each individual sheet within your spreadsheet. And all you have to do to pull information from a cell in one sheet into a cell in another is use that cell information as a record locator of sorts.
Let’s say you use a spreadsheet to plan out your content and then you have another sheet that maps out your content management process. You’re definitely not going to want to retype the name of each blog post or email subject line into every single step of the process for managing that piece of content.
You can use auto-fill in your content management sheet to pull the title of your planned blog post straight from your content planning sheet… no manual updating required!
You can also use auto-fill to do summations or even more complex formulas like COUNTIFS and SUMIFS if you want to input data related to your finances or clients in one sheet, and have a summary of that data on another sheet.
COPY EXISTING SHEETS TO NEW ONES
In the same way that manually typing the same information into different sheets within the same spreadsheet over and over again can be a drag, having to manually transfer information from an existing spreadsheet to a new one is also a drag.
Fortunately, you don’t have to worry about that anymore!
You can always copy an entire spreadsheet, of course, but you also have the option to copy any individual sheet to a new or an existing spreadsheet.
A practical example of how this could be helpful?
When I was planning my wedding, I had a 15-tab spreadsheet I used to plan literally everything. When it came to working with vendors, some of that information applied to them and some of it didn’t.
Most of them needed to see our timeline and wedding contacts, but our ceremony coordinator didn’t need details on the reception and our reception coordinator didn’t need the shot list for our photographer. Because of the copy-to feature, I was quickly and easily able to create individual spreadsheets for different vendors with just the information they needed and nothing else!
REMOVE GRIDLINES
This might seem super basic and simple, but those sometimes irritating gridlines you automatically see in the spreadsheets? You can get rid of them if you want!
This is a fantastic way to really customize the look of your spreadsheets and do so much more with them than just have a bunch of cells in rows and columns with little to no additional organization.
With cell formatting, you’re always able to manually add borders around an individual cell or group of cells, which means you get to choose which cells have those gridlines and which ones don’t… as well as the color of them!
This allows you to take this…
And turn it into this…
Pretty amazing, huh? In Google Sheets, all you have to do is click “VIEW” and uncheck the “Gridlines” options and poof… they’re gone!
LOCK CELLS OR ENTIRE SHEETS
So you’re finally getting the hang of this spreadsheet thing and no longer getting all sorts of crazy errors and the like when you input formulas into cells, which is super exciting!
But you’ve also reached a point in your business where you’re bringing other people into the mix, which means you have to start sharing these spreadsheets with them and even giving them the task of editing and updating them!
Giving access to a spreadsheet — especially one that has a bunch of complex formulas — can be super scary, but you don’t have to worry! With any spreadsheet, you have the ability to lock editing features on the entire spreadsheet or on individual cells.
The former is great if you need someone to be able to view the information in the spreadsheet but not edit or update it, and the latter is great if you need someone to update information in certain cells, but you really need them to leave others alone so they don’t mess things up!
TRANSLATE IN APP
This last spreadsheet hack is just for Google Sheets users, but I find it super cool!
Did you know Google Sheets can harness the capability of Google Translate and translate copy for you right in the spreadsheet?
That’s right! All you have to do is type the formula =GOOGLETRANSLATE(CELL, “source language”, “target language”) into another cell and it will automatically translate for you!
I’ll be honest, I can’t think of a lot of businesses where this would be a necessary feature, but it’s still pretty nifty, if you ask me!
See? Spreadsheets might look all simple and innocent, but these babies have plenty of fancy bells and whistles to make them super fun, super powerful, and super beautiful organizational tools for your life and business! And this is just the beginning!
start the conversation