Custom formats, relative addressing, and conditional formatting

- Imagine you're going to be taking a trip in a few months and you've got your departure dates lined up. You might consider any one of these different days as a departure date and you're thinking of returning on one of these three days and you've got some flexibility so you put in those different dates and you've gone to the airline website perhaps, or called them and got these prices for the different amounts here. Also, you've checked out rental car prices and the unusual one of these days here is a lot pricier for some reason. That's not the same day with the charges over here are pricy but never the less, you're curious about some of the combinations here.

Now, this is kind of a setup based on a real life experience of just using different features in Excel to make this more readable and more usable, and you could easily imagine expanding it into a model that has a lot more departure dates, a lot more return dates and consider possibly even another set of modules here relating to hotel expenses possibly but as we see these different techniques, they all are more or less shortcuts, except for one of them, and it'll make you a more efficient Excel user if you've got these techniques mastered. Let's begin with the idea of a simple little variation on how the titles are put in.

I'm going to double click in cell A2, right in front of the D in Depart, and type Return, press Alt, Enter to introduce a line break. I can leave it like that simply by pressing Enter but I'm also going to click in front of the R in Return put in some spaces. What I'd like to have the word Return above Depart but off to the right and the Depart below that off to the left, looking like that. Now, in order for this is to more or less line up with the data to the right, we no longer need column B so I'll right click column B and Delete it. Take this data here and simply make it align in the top of the cell, that looks a little bit better.

Possibly I could have put an arrow or something like that behind Return and one other minor suggestion here. By clicking cell A2 and then on the Home tab going to the drop arrow for Borders, go to the very bottom, More Borders and we can choose a different line type, I'll just use the relatively thin one right here, but use the angle here, the angle line. It's going to look like this, sometimes this is referred to as elbow formatting so then it's pretty clear where Return means the date to the right, Depart means the day below.

The way these are displayed, not really a problem but since we're talking about data all within the same year, it's more or less across the same two week time frame, why not display these with day of the week and the month and the day. Highlight all these first and then go to Format Cells, and a quick way to get there is Control, 1. Number tab, date category, pick one of the simple ones here and immediately cut over to Custom. M's and D's and Y's. If we put in three Ds, that will put in the abbreviation of the day of the week, like S-U-N, four Ds would spell it out.

So supposing we want to see something like S-U-N, comma, we'll put in the comma now, a space, the month, three letters for an abbreviation like M-A-R or A-P-R. If we want to spell it out we use four M's. Space D, that covers all data entries whether they're single day or double day, we don't need the year on this one, and we'll simply click OK. The date value is still there whether we show the year or not, but these are going to be more readable now and probably the way most people would probably want them. We know which day of the week it is, we know the month, we know the day, clear enough.

Now, we've seen our airfare totals here, we checked out our rental car totals here, let's show the total right here and rather than writing a formula here that adds up those two and then copying it rightward and then downward, nothing wrong with that, why don't we high all these cells together. Then i'm going to type Equal and I'll be adding B3 and F3, makes no difference which one I do first so I'll click on B3 + F3. If I were to press Enter, I'd only have the answer for one of these cells, I'm going to press Control, Enter and all those formulas are in place, and we see the relative totals.

Now the rental car per day calculations is going to be a little bit trickier. So, for this day only, if we want to know the per day charge first of all, there's the total charge 940, but we to divide that by the number of days for the trip length and that's going to be divided by April 3rd minus March 17th. But we want to take care of the others and we can do this in the same way that setup the previous formula simply by highlighting these cells but it's going to be a little bit trickier this time because we need to calculate those date differences so we start with Equal, clicking on the cell that has the 940, divided by and now using left parenthesis to get the date difference we'll first click on the return date right here, Sunday April 3rd and the other cells that we'll be using today will always get the dates out of row 2.

So, after putting the F2 here we'll use the Function key F4, I'll press it repeatedly. I want the dollar sign in front of the 2 but not in front of the F, just like that. That means if we copy this, the return date is always going to come out of row 2. It'll come out of different columns, columns G and H. From this we want to subtract the starting date, the departure date. Right now it's A3 and here all of our departure dates are always going to be in column A. So we'll press the Function key F4 here to make sure the dollar sign is in front of A but not in front of the 3.

We certainly could have typed the dollar signs, too. Right parenthesis, once again, I will not press Enter, but Control, Enter, there we are, and just to check out one of these I'll take this one right here and double click, you see what it's doing is taking the 1333 and then for the date difference, it's taking the April 5th date minus the March 19th date. So you see how that's playing out. By this way, this assumes that when you rented the cars on all these days and returned them, it was within the 24 hour timeframe so you don't have an additional date charge.

So we've got these all in place, looking pretty good and for some of us, we look at the numbers, we know exactly what's going on. We're going to make our decision maybe based on the lowest price combination, something like that but if we want another visual to enhance this, we could use Conditional Formatting. And the temptation might be, well why don't we just do all these at once? In other wards, highlight these and using the Control key highlight these and then these and apply Conditional Formatting, the problem is there it would treat all these numbers as if they were in the same pot so to speak, and we would have colors where all the low colors, all the low numbers would be a light color, for example, all the high numbers these here would be pretty much the same color.

So we have to do these individually but we can copy it pretty easily. I could start with any of the fours clusters of numbers. I've got these highlighted, Home tab, conditional formatting, I'm going to use Color Scales. This Color Scale here, the greens are the high numbers, the reds are the low, I prefer it to be the other way. Similar now, I guess, to the traffic light idea. Greens are good, reds are not so good, red means stop. But in this case, green means yes we're more likely to purchase so we'll use that layout. So we see the color scheme there so, the yellows are in the middle so to speak, the greens are cheaper, the reds are more expensive.

We don't have to do this all over again for the three other clusters, we simply can copy the format. Now temporarily here it will hide the pennies for our rental car charge per a day but that's not too important. We can come back anyway and correct that. With the right mouse button, after highlighting this data, I'm going to drag this downward. As I release the right mouse button, Copy Here as Formats Only. Pennies disappeared for the moment although they're still there in the background. This data is highlighted, with the right mouse button I'll drag it leftward, let go of the right mouse button Copy Here as Formats Only and I'll do the same thing up here drag upward, right mouse button, Formats Only, there we are.

And then, if it's important to put the pennies in, we'll just come back here and increase the decimals, there we go. So, that wasn't truly necessary to have the conditional formatting but on the other hand, it makes the decision easier to make. And we could easily imagine putting in more dates over in column A and more crossroad to and expand this model it just gives us a good quick visual as to what the various charges are, what the highs are and what the lows are. So you've just seen a number of techniques here. From making displays easier, more readable and also calculating different totals using the technique Control, Enter, pulling together our formulas quickly and easily.