Messy Sales Pipeline CLEANED UP
In this video, learn how to transform a messy sales pipeline spreadsheet into a visually appealing and functional dashboard using checkboxes and conditional formatting, making it easier for sales reps to track their progress.
So I was working with a client and this came up where they had a sheet. It was leads and contacts and they were working on their pipeline and I went through a really cool data design. So the problem was hey this sheet one looks like a mess. It looks like a lot of stuff to do. It's not really that much stuff to do. And also we can gain a lot of information from the data in this sheet if our sales reps were actually filling this out. what can we do to make it easier for them to actually fill it out and so it doesn't look so technical it doesn't look like they have to enter numbers much and also how do we review the sheet can we make this into a dashboard and that's what I hear a lot of is can I make this into a dashboard can I take take the
dashboard but really what we could do is take the data and turn it into visuals and I'm going to show you a few steps that I showed I thought it was really creative and really cool kind dealing with this thing instead of just taking the data and moving it somewhere else. I want to turn this data into a story. So what they had and what was unique was they wanted to sum up all of the things that were happening and they could have said yes or no. So here's the pipeline. They have a value of the pipeline that they they're going to pitch the customer on something. They're going to schedule a calendar some event. They're going to confirm, hey, are you going to come to this call? They're going to show up. And then at some point during the call or after the call,
they're going to close. So, these are the main four steps. They want to say, how many people are we getting through this? Uh who which of our sales reps are actually doing this? And can we give our sales reps a nice viewable thing into their own pipeline, but also can we share this with our leadership and say, "Here, help the people who are trying needing help." So when we first look at this, I see something very clear is the zeros and the ones all are fading into each other. And yes, the idea could be change this to a drop down. Totally possible, but I have some issues here. And the issue is let's just change this to yes or no. And let's copy and paste this all the way down. And let's put
some nos. This is exactly the same problem. we end up in the exact same spot we were before where it just all looks the same. We can't visually tell a yes or a no. And yes, of course, we can go in here and we can say yes is green, no is red. Done. Apply to all. Yes. And okay, we have color. But again, it all looks the same. even if we can see the color, it just looks like a mess. There's a few things we want to do with this data . And also, we want to be able to keep this sum up here. And and that was one clear thing that I wanted to accomplish is how do I show visually something
and keep this sum? Of course, we could change this if this is a yes, yes, no, we could change this to equals count. If range is E col E criteria yes and totally possible. Okay, so that's not really the issue is just changing this to a yes or no, changing this to count if you want it to be visually easy and also easy to fill out. Okay. If I have to fill out this one or or change this zero to a one, I double click in here, change it to the zero to a one, hit enter. Cool. Done. If I want to change the yes to a no. I click it,
click no, and done. I can make it twice as simple. I can make this so much more faster for you. And I'm going to show you how. And I'm going to show you how I solved this uh client's problem and how it turned into something way cooler than I ever imagined. So E3 to H3 and down. We want to change all of these. So there's data here. There's a zero or a one. And honestly, I think there's actually a third thing which but we don't care about it, which is no information. In this particular case, this person did not need to have nothing filled out and then either a yes or a no. So that also
clued me in to the fact that like a yes or no is not going to work here. A yes or blank might work. Not yes or no. But one or zero worked here. It was binary. This is happening or not. And if it's not, we're working on it. So what we'll do is here's one of the first parts of the solution and there are many steps in this because I'm I'm really excited about this. So take this E3 to H3 and actually if you don't want to do it all at once, you don't have to. We're going to insert a checkbox . We're going to make sure that that was correct. This is I think up to here. Um, if you look over here in the value, a checkbox is basically just
a visual representation of either true or false. However, we want zeros or ones. So, what we're going to do is rightclick. We're going to go to view more cell actions. We're going to go to data validation . We're going to click here in this data validation rules, which we already have. It already started our data validation rules, which are a checkbox . Click there. Use custom cell values . And now checked is one, unchecked is zero. Done. Now, now you look in here in the where the formula is. It's a visual representation of one or zero. And we do not have to change this sum. So this formula up here stays the same. Okay, I'm going to copy and paste this all the way down. But I want to remain the data. I want the data
to remain. Right click, paste special data validation only . Now I have not changed the data . However, there is a huge visual difference between a checked box and an unchecked box. It is like full or not full. It is not one color or another color. It is color or not even color. It's it's it's full or not full. This is more visual than even yes or no and a color. This is really cool. I think this is really cool. But we are not done. I'm going to show you an even better thing. Okay. So, in talking with
this client, we figured out the basically the thing we want to work on is not the ones that are working. So, if a if a salesperson goes works this lead, gets them a calendar, gets them confirmed, gets them to show up, and gets a close, that's great. We don't have to work on that. We don't have we don't we care less about that than the ones that are not working. Okay, why didn't we get this calendar? Why didn't we get this confirmation? Why didn't we get them to show up? Why didn't they close? And it's in actuality the ones that we are like even in the word yes if it's yes or no yes yes is bigger no is smaller but really it's the opposite we want we care more about
what's missing than what we have done and yes we have these numbers up here and we care yes we care we care close rate show rate confirm rate calendar rate and we can get those pieces of data up here we can say okay how much percentage went from calendar to confirm. We do this, we do this, divided by this, make a percentage here, and then we copy and paste this all over. And so we see, okay, 67% of everyone we've shown to closes. We can also do a different number here. Basically say it's how many closes out of calendars. And and we could do this forever, right? We can do all cool um pieces of information. That's 18% of
everyone who got on a calendar has gotten to a close and we can each know the other way I wanted to put dollar signs in front of the E3. There we go. And now I can copy paste and we see okay yeah 45% of the calendars have confirmed 27% of the calendars have shown 18% of everyone who shows who we book a calendar with has closed. Great great little snapshots, right? Wow. But what we want to see is the thing we haven't done. This is showing us what we have done. I know this is a lot of a lot of talk. I want to show some actions. First off, let's make this nice by centering it
vertically and horizontally. And now we add a little bit of magic. Go up to format . Go up to conditional formatting . Format rules and we want to say is equal to one. We're not going to highlight it. In fact, we are going to do the opposite. We're going to lowlight it. Let's say we're going to change the fill color to none. We're going to change the text color to the lowest one that we can actually see. So, this light gray three might be too tiny. We're going to go to light gray one. Right. Click done. And look at that. we now see what we need to work on. So, it's still possible to see and it and
it'll turn automatically. If we click on a check box, it checks it off, but it's highlighting. And we can also highlight the boxes in another way with some color a bit. Let's do conditional formatting . Add a new rule. when it is equal to zero. We don't want none or we don't want a background and our color let's say it is a dark red. Let's make it slightly uh yeah done there. So if we uncheck it it's red and yeah here we get a little burst of color something to see.
I'm going to Minimize these text up here down to eight. That's good. I want to view I also want to unview the grid lines and just put some grid lines here. Let's do light shade of gray there. Cool. So what started as a wall of data of numbers is now a visual pipeline. We can see clearly. Okay, we have confirmed. Okay, we are cutting through. We are only seeing what we have to work on. That's really cool, right? One other a couple of other things, two other things I would say is I want to get rid of zeros. A lot of clutter is from data that we
have to ignore. We should be ignoring. We can do this with conditional formatting as well. D column format conditional formatting if is equal to zero. We do not want the background and we want the text to be just gray gray that do. So there is a number there that might be a little too light. Maybe just a little bit. Okay. See our our value is zero. And now we're working on the when we have value can see it's full. Great. A revenue as well could be automatic. It could say equals if this checkbox is equal to one
value if true is going to be the value here in D column. If it's false I'm going to say nothing. And there our I column is automatic. So the moment we close we're like oh yeah we've got here we got them to show up. We got them to close. Boom. We got revenue. We don't have to do anything there. And it is missing. And so there's this pull this pull. Okay, we have no re not no not zero dollars of revenue. We have no revenue and again I want to stress but there is sort of three pieces of information. There is some number there's a number zero and there's nothing. And so here on this revenue side we are really like we need to fill this in right okay we we filled in our value we've got them to on a calendar
we've got them to confirm or not. We got them to show or not and we are closing or not. And visually, this I think is a really cool way to visualize a pipeline of data and design data in the sheet without having to create a dashboard to show this information visually. Hope you enjoyed it. Hope you enjoyed this fun video. You're watching Better Sheets here on YouTube. You have two options here for your next video. Before you go, comment down below if you have any question whatsoever, and I'll make future videos from those questions. But for right here, right now, one of these videos could be your next Google Sheet.