Armed with an obsessive love of color-coding and all things related to organization, I’m on a mission to help you maximize efficiency in your business and life so you can spend time on what matters most to you.
Equally right and left-brained, I’m that unique business owner who can think like a creative, but act like an administrator. This blog is where you’ll see me bring that dual-brained magic to life, taking the left-brain side of life and breaking it down for your right-brained self.
By now, you’ve hopefully started to understand why I love spreadsheets as much as I do. They really are super amazing tools that you can do so much with, even if you’re just using the basic options they have for formatting and functions!
But today, friend, oh, today, we are diving into the spreadsheet stuff that makes my heart sing. We’re digging deeper into the formatting and functions within spreadsheets and your life will never be the same.
Prefer to listen instead of read? Scroll to the bottom for the audio version of this post!
That may sound a little bit dramatic, but seriously. You can do a whole lot with the basics, but some of the more advanced options for formatting and the like can take your entire spreadsheet up a notch and open up a world of possibilities for managing and organizing things within your business!
Have I hyped this up enough? Let’s get down to it.
Ever seen a spreadsheet with dropdown menus that provide you different options to choose from, instead of having type in things yourself?
This is just one form of data validation available in a spreadsheet!
Any form of data validation will set up a cell so it basically yells at you (in the form of a little angry triangle in the upper righthand corner of the cell) if the data you’ve put in the cell is invalid.
The dropdown menu is the one I use the most, and you have the option to select from a predefined range or write your own list of dropdown menu options.
In addition to dropdown menus, you can also set your data validation to several other settings related to dates, numbers, text, or formulas.
For dates, you can validate cells that have any valid date, dates before or after another date, dates within a range (i.e. dates before one date but after another), or dates that aren’t within a range.
For numbers, you can validate data within a range of numbers, data that isn’t within a range of numbers, data that’s less than, more than, equal to, or not equal to a specific number.
For text, you can validate cells with specific text, text containing a specific value, text that doesn’t contain a specific value, or text that is an email or a URL.
Other than the super basic stuff like text formatting, alignment, and the like, this is probably my most used spreadsheet feature, bar none.
What is conditional formatting? Well, it’s formatting a cell (or group of cells) based on a condition.
For example, if you have a to do list or you’re using a spreadsheet to track a project, you can set up conditional formatting so the cell is red when a task is listed as Not Started (one of those options from the dropdown you can set up with data validation), it turns yellow if the task is In Progress, and it turns green if the task is Complete.
Or you could set up conditional formatting so a cell is green if the date in a particular cell is after today, yellow if the date is today, and red if the date is before today. This is a fantastic way to keep an eye on any tasks that may be overdue or have upcoming deadlines.
I get it. Even with borders and highlighting cells and conditional formatting and stuff, spreadsheets can still feel like a lot of cells and numbers and information that’s difficult to process.
And that, my friend, is where charts come in.
Yes! Charts! Every spreadsheet program uses charts and allows you to take all that information you’ve put into the spreadsheet — whether it’s text, numbers, dates, or a combination of them — and transform all that text into a chart that allows you to visually track your data.
You can create all your typical charts — lines, columns, bars, areas, and pies — and in some spreadsheet programs you even have the option to create maps, radars, timelines, and more.
All you have to do is select the range you’re pulling data from and make it pretty!
Finally… did you know some spreadsheet platforms allow you to add drawings and images directly into your spreadsheets?
Similar to the charts, this is a fantastic feature for anyone who’s more visual and opens up such a range of possibilities when it comes to what you can use spreadsheets for in your life and business!
Note: To my knowledge, this feature is only available in Google Sheets, but hopefully it will be an option in Excel and Numbers soon!
Your mind may already be blown a little bit by some of the advanced formatting features available in spreadsheets, but now it’s time to dig even deeper into the functions!
We already talked about the basic SUM function, which will add up the value of cells within the designated range.
The SUMIF and SUMIFS functions take addition to a whole new level and are fantastic for analyzing and manipulating data within a business.
As you might be able to guess from the function names, SUMIF will sum the values of a group of cells if they meet a certain condition. For example, you could tell your spreadsheet to sum the values of a range of cells if they are above a certain price point, or the payments took place between certain dates.
SUMIFS just adds additional functionality and allows you to set multiple conditions or variables for your sums. So you could tell your spreadsheet to sum the values of a range of cells if they are above a certain price point and if the payments took place between certain dates.
To use the SUMIF function, type =SUMIF into your function box. To use the SUMIFS function, type =SUMIFS into your function box.
As SUMIF and SUMIFS are to the SUM function, so COUNTIF and COUNTIFS are to the COUNT function.
Just like COUNT will count all the cells in a range, the COUNTIF and COUNTIFS functions will count the cells in a range if they meet a certain criteria.
This is a fantastic function to track a lot of client or customer and sales related data like the number of referrals you got from different sources, the types of packages you’re booking or products you’re selling most often, and more.
To use the COUNTIF function, type =COUNTIF into your function box. To use the COUNTIFS FUNCTION, TYPE =COUNTIFS into your function box.
If you’re using your spreadsheet to track or manage any kind of to do list or project, the DATE and related functions can be super helpful.
DATE is the simplest of these functions and literally just turns the information in a cell into a date, which is something you can easily do just by formatting the cells accordingly.
But beyond that, you can use several other date related functions to automatically update and track date related information.
The TODAY function will set a cell to always update with the current date and the WEEKDAY function will tell you the day of a week for any given date. The WORKDAY function will tell you the number of days before a particular start date and DATEDIF will tell you the difference between two dates. The WEEKNUM function tells you the week you’re at in the year and the NOW function will give you the current date and time.
There are even more date related functions than these and they open up so many options when it comes to setting up your spreadsheets to help you easily track and manage projects and tasks within your business!
We’ve already touched on a couple of these, but in addition to conditional formatting, spreadsheets can also use logic within their functions!
The SUMIF/S and COUNTIF/S functions are examples of using the IF logical function, which will work if certain criteria is met.
Two of the other most common (and helpful!) logical functions are the AND and OR functions. The AND function will return a true value if all of the arguments in the cell are logical and false if any of them are not logical. The OR function will return a true value if any of the arguments in the cell are logical and a false value if all the arguments are logically false.
Your brain may feel like it’s exploding a bit right now, and that’s okay!
Truthfully, even with these more advanced functions and formatting options, we’ve still only begun to scratch the surface of what spreadsheets are capable of doing in your business, but I hope you’re beginning to see the immense power they have and all the options available to help you in your business!
Love & Spreadsheets is part of several affiliate advertising programs. If you make a purchase from certain links here or on social media platforms, I may make a commission. But don't worry, I only share about products I love and think you will too!
Get 10% off your first order
Get 10% off your first Love & Spreadsheets purchase! Pop your name and email below to instantly get your code.
By joining, you'll get 10% off and stay in the know!