Excel PRO TIPS Part 2: Formatting

1. Formatting Tips Intro: Everyone in this section will talk about formatting tips, which are all about customizing the look and feel of your Excel workbooks. We'll talk about freezing pains, designing polished reports and dashboards and working with tricky data types like dates, zip codes and phone numbers. Finally, we'll dive into some expert level tips like formula based formatting rules and custom number types. Let's get started. 2. Formatting Shortcuts: all right. Next pro tip. Using control shortcuts to apply self formatting. So pretty simple one. This is a one star tip. Very straightforward. Point is, you can access some of the common Excel number formats using control shortcuts with shift so pressing control Shift one or control exclamation point is gonna format your selection as a common number format control shift to would format as time three would format as date fours currency. Five. His percentage and six is scientific, so I tend to the X power That's a scientific type of number format. And any time you want to revert your formats back to kind of unformed, matted general style, you could use control shift. Tilda, which is the key that believe on most keyboards, lives to the left of the number one. So here's a quick demo were in practice this in Excel. Let's say you've got four columns like this release date. Facebook likes gross revenue and R A y all different types of formats in there, and you want to turn it into something like this at least is the first pass is a great way to use the control shift shortcuts you could select column a use control shift. Three. To turn those rod date values into properly formatted dates, you could use control. Shift one on column B to turn those Facebook likes into standard number format control. Shift four would turn those gross revenue values into currency and finally, control Shift five would turn those raw decimals into actual percentage values. So the obvious use case here is really just to quickly apply those common number formats without using your mouse about clicking and without working through the format cells dialog box. So let's take two minutes and quickly practice applying some of these formatting shortcuts . All right, so if you've been following along, open up your Excel pro tip Excel file. Head to a table of contents, and this is the first formatting tip formatting shortcuts. Go ahead and click. The link will take you to the first kind of orange tab here formatting shortcuts and you'll see five columns very similar to the ones that I just described. Release date movie likes gross revenue budget and our ally, and as you can see, these air all kind of unformed, matted, general style formats at this point. So all we need to do is remember those shortcut keys to actually convert these into formance that make more sense. So to start, let's go ahead and select all of column A and use the control shift. Three. Short cut. And that's gonna turn those raw date values into dates that are actually a bit more readable. And you may or may not like the default date format that it selected for you. I'm so from here you can either right click and go in your format options. I like the control one shortcut to get to the same place, and you can go ahead and choose date and maybe format it something like this, which personally, I prefer a bit more. Now let's go to Facebook likes right? Standard number format. Um, all we need to do here is control Shift one. And there you go. It's added the decimal with two placeholders that comma between thousands separators again . Maybe it's not your favorite format, but you can go ahead in your home menu here. You know this case maybe want to get rid of those decimal places, and there you have it. Gross revenue and budget. These air both currency. Let's go ahead and hold shift Grab column D. So I've got both at once do a little bit of a two for one special here. Control shift four, which is the dollar sign, and that applies the currency format. Again. I don't really have this data at the sense level. It's just rounded to the dollar so I can go ahead and remove those decimals. Last but not least, but select column E control shift five, which you guessed it is the percentage symbol. And there you go. Now we've got a properly formatted percentage. Sharon column Eat. So remember, if you want to go back and revert these two kind of a general un formatted style, we could just select all of this data a through E and use the control shift till the option to take us right back to where we started. Or we could control Z undo that change. And there you go. We've got a proper formats. So quick primer on how to use those control shortcuts to quickly format yourselves 3. Snap to Grid: all right. A next pro tip is a really simple one. This is a one star formatting tip, very, very basic, but also very helpful. Going. Talk about aligning workbook objects using snap to grid, and it sounds a little bit fancy, but it's extremely simple. All you need to do is hold the altar key as you drag to resize and object to snap them to the cell borders. So here's an example. Let's say you've got a chart on a worksheet, which is an example of an object, and that shark is just kind of floating there, freeform on top your grid. If you grab the entire chart or one specific edge of the chart and hold Ault, you can essentially snap it right to those column and row borders. This is a great tool for spacing and alignment and consistency to make sure that all of your objects airline to that same comment grid. So a couple bonus steps here as well. If instead of holding Ault, you hold control while you dragon object. What that's gonna do is actually create a duplicate copy of that object at the same time. So really quick, way to kind of create duplicate versions of the same object instead of copying and pasting on. And then second tip here. Another alignment related tip. If you hold shift as you drag an object, it's gonna limit that movement to a single plane, either horizontal or vertical. So the obvious use cases here again, making sure that your worksheet objects are all aligned to that common grid and also design and clean and polished user facing tools or dashboards. You know, sometimes these little things these little formatting tips, like spacing in alignment can go a long way as far as producing professional quality work. So let's jump into our pro tip workbook and practice snapping some objects to our grid. All right, so if you've been following along with the course, you go ahead and open up Your Excel pro tip workbook had your table contents were gonna jump to the snap to grid demo in our formatting tip category and press link. And when you land on that snap to grid tab, you'll see three charts that are kind of just floating here on the sheet. And if you select one of them and note, you gotta be careful, kind of where you click and you want to click right by the edge where you see that four pointed arrow that's gonna allow you to move the entire object at once. Notice how it kind of move smoothly over the sheet, whereas when I hold, Ault starts to get a little bit snappier right and snapping to the actual worksheet grid behind it. Let's go ahead and align the upper left corner right there to be three. And that was an example of moving the whole object. But we can also just grab you know, the bottom edge, for instance, old Ault and snap it to Row 13. Grab the right edge. You're gonna look for the two headed arrow in this case. Hold out and let's snap it right to call him F. So now we've got a chart that's exactly five columns wide and about 10 rows tall. Just gonna make it very easy to follow the same process with these other two charts. Let's go ahead and grab this blue chart. It's move it kind of close to the green one. We're gonna hold all but the upper left corner in H three and then go through that exact same process grabbed the bottom hold all to row 13 from the right edge Hold. Ault applied to column l And there you go. Now we have to consistently formatted charts. All we've got to do now is applied that same process to this orange guy here, and we're gonna drop it into n three scroll all the way over. We'll also bring it out to comb are and bring the bottom edge up to Row 13. And there you have it. We've just created consistently sized charts using that snap to grid option. Also, keep in mind you've got other tools in the page layout tab to align different objects. For instance, if you were to select and hold control to grab all three could use tools like a line top or distribute horizontally to accomplish the same effect. And these tools can be really, really helpful, especially if your column widths aren't exactly the same as they are right here. So, like most things in excel, many different approaches that you can use to solve the same task. So there you have it using Ault to snap objects to the grid 4. Hiding Workbook Elements: the next pro tip that I want covers pretty quick. One talk about hiding individual workbook elements to help reduce clutter. So in the view tab of your ribbon, you'll see a group of check boxes under the show category. Looks something like this in that group of check boxes allows you to hide individual elements from view, including grid lines, Formula bar or the headings themselves. The Roman column headings. You can also collapse the ribbon itself from view. There's a little carrot icon in the lower right corner and then pin it back in place by selecting a tab and then pinning where that carrot icon WAAS. I'll show you exactly what that looks like in just a second, but basically the idea here is to take something that looks like this, which you know is obviously designed to be polished user facing report dashboard. But it still feels very much like a spreadsheet. You've got the formula bar got your row and column headers. You're looking at your grid lines so it feels very much like a spreadsheet. I really want to make this appear to be a cleaner, more polished reports something like this. So by simply turning off the grid lines, hiding the formula bar and headings and collapsing the ribbon. You end up with something like this that feels more like something you'd see coming out of a B I tool, like a tableau or power bi I even though we're still here within the Excel environment. So easy tip, but also very helpful one, especially if you work in analytics and you build reports and dashboards like this now one quick thing to note some of those settings grid lines and heading specifically on Lee impact the sheet that you're on. But others, like the formula bar and the ribbon collapsing on the ribbon settings. Those impact every sheet in the entire workbook. So I just keep that in mind. And then again, common use cases creating those clean and polished kind of like dashboard style views and then also hiding those grid lines and headers helped to prevent users from actually modifying rows, columns or formulas in your tool. So with that, let's jump into excel and I'll show you how to hide some of these workbook elements. Okay, so here we are in the pro tip workbook gonna go to hidden workbook elements in our formatting tip section PressLink. You'll jump to that hidden workbook element tab and this is just like we describe. You've got this user facing report charts and, ah, numbers and figures and insights, but it still feels like a spreadsheet. You know, I can still see the cells and the headers and formula bar. So what we're gonna do is head to our view tab, we're gonna de select the grid lines. You know, that alone makes a huge difference. De select those headings because we don't want to be adding or modifying rows and columns at this point. And those two changes that have just made grid lines and headings you'll notice if you click on other tabs does not impact those other worksheets, right? So I still see the grid lines and the headings here in my snap to grid tab, but not in my hidden workbook element tab. By contrast, if I uncheck the formula bar, that's a change that impacts every single worksheets. And I don't have access that formula bar anywhere else in this file. So keep that in mind. Same thing goes for collapsing the ribbon. It can go all the way over to this little carrot icon. Press it to collapse that ribbon, and now I'm left with kind of this blank, clean canvas. And to get my ribbon back, all I need to do is navigate into any one of these tabs, and that will kind of temporarily pulled my ribbon back. And where that carrot icon was, it's now a little pin. All you gotta do is click that pin, and now it's fixed back in my worksheet. So there you have it. It's a simple is that that's how you can hide individual worksheet elements to create these kinds of Polish reports or reduce clutter in your worksheets. 5. Format Painter: next pro tip is one of my favorites, replicating formats with the format painter. Now this is a one star tip, very basic, very simple, but also very powerful once you know that it exists. So essentially, the format painter. All it does is it copies all of the formatting related settings from a cell or selected range of cells that includes the fill color, the fonts, that number format that borders, the styles, the alignment. All of that information gets copied and then applied from one selected range to another. So, for instance, we're gonna be looking at a demo where we have a number of columns, each with 0 to 5 rating scores. And in this case, we've applied a simple color scale that column E. And we want to apply that same color scale at same conditional formatting logic two columns , F, G and H. So to get started, all we need to do is select the cells that contain the formatting we want, which is Values and Colin E. And go into her home tab and click that format painter button that looks kind of like a paintbrush Now. One thing to note is that if you click that button once it's gonna grab and copy those formatting settings one time, so the next place that you click is where those formats would be applied and then the tool be deactivated. If you have cases like this where you want to take that formatting and apply it multiple times like two columns F, G and H, you can double click that format painter button to kind of lock it in place, which allows you to continuously apply that format over and over and over until you click the button again to deactivate it. So really helpful tool that's gonna allow us to do something like this and apply that color scale from Colin E toe, F, G and H by simply clicking three times. Now, common use cases again quickly applying formats across multiple columns without having to manually go in and adjust any of those formatting settings yourself and to ensuring that sell for mints are exactly the same, including all of those different criteria or settings like Borders, Phil's number, formats and so on and so forth. So let's jump to our pro tip workbook and give this a shot. All right, so once you've opened up your pro tip workbook, head to your table contents first tab, and you should see a format painter demo right here in the formatting tips section. Go ahead and press link. It's gonna take you to the format painter tab. And what we're looking at here are burrito ratings from individual reviewers and these reviewers who must be, ah, burrito fanatics. They're out in San Diego. They've reviewed all of these different types of burritos on given dates that are coming from different locations or restaurants. And the key here, the columns that we really care about in this case, um, are the ratings themselves, which live in columns e through l. And basically what each reviewer has done is scored each burrito on a 0 to 5 scale in terms of a number of different criteria. So tortilla, the burrito temperature fillings, the meat volume, uniformity, salsa synergy, which not sure what that means and the rap quality. So we've got looks like about eight different scoring criteria, and this is a great opportunity to use something like conditional formatting to really visualize some of the patterns and trends that might exist here. You know that are tough to see when we're just looking at the raw data itself. So what we're gonna do here is actually just select Oliver Tortilla Data. I'm gonna click the first row and then use the control shift arrow down shortcut that's going to select the entire column there and scroll back up. And now, instead of just using a basic color scale, I want to actually use some of the highlight cell rules, and I'm gonna use the between rule. So the first rule that we're gonna set is one of format cells that are between a score of zero and two with a red fill with dark red text. Actually, let's change that to 2.5. So that's pretty bad. Those heir burritos that got very, very low scores for this particular criteria. That tortilla I'm press OK, no, back into her conditional formatting menu. We're gonna simply add another between rule. This won't overwrite the existing one. This will add a 2nd 1 and now we want to format scores between 2.5 and 3.5 as kind of average there, like in the middle. So let's do a yellow fill with dark yellow text press OK, and then same process. Let's add one more conditional formatting rule for between 3.5 and five, which are very high scores. I recall those green filled with dark green text and press. OK, so here we go we can see that it's populating, as we'd expect got a good range of high, medium and low scores here for the tortilla criteria. And now we want to apply that same logic, that same conditional format to the temp fillings, meet volume and so on and so forth, all the way out to column elk. Now there are a couple of ways we could do that. We could select the values in column F and work through that same exact process we just did , which is obviously a little bit inefficient. We could go to our conditional formatting rules, could manage these rules and change the references themselves, which is also a little bit messy. The easiest way to do it is gonna be to grab that whole column e going to home menu format painter and watch this about click it once and then select column F. It's going to apply that format, but now my format. Painters deactivated, see, but because I want to apply it to six more columns now, a better approach is to double click that format painter, click G, click each click I cook J, K and L. And there you go. Note how it also changed the column with because, technically, that's part of the formatting that I've copied here from call math. So now to deactivate, all I do is click that format painter button again, and there you have it. I've taken my custom conditional formatting rules from column E, and I've applied it to seven additional columns in a matter of seconds. So really great use case for how to use that format painter and the double click tool to very quickly apply formatting from one range of cells to another. 6. Color & Border Design: all right. Hey, everyone. Next pro tip that I want to cover is how to use color and borders in a thoughtful way to create polished Excel reports. Now it's important to keep in mind that Phil colors and border settings they're not just stylistic tools. When you use them carefully and strategically, it can actually go a long way to help improve the readability of your reports. That can help to create important spacing between cells and, most importantly, draw attention to key metrics or trends or patterns that may have otherwise just been lost in the noise. And one particular tip that I use all the time is hiding the worksheet grid lines and then adding white borders to create a nice, clean and polished look. Let me show you what that would look like here. Let's say you've got a range of cells. You got things spaced kind of nicely. I've got some self ill. They're looking at some traffic metrics in some lead metrics. If you select that entire range going to your hometown drilling to those border options down where you see that drop border tools, you'll see the line color option when you select white. You can use that tool and apply those white borders to create effect that looks something like this. And note. I've also added some darker shading here to draw attention to some columns. But basically what we've done is used borders in kind of a non traditional way. We're not using borders, too. Add lines between cells. We're using borders to essentially create the illusion of spacing between cells that can really help with readability and with creating professional quality, really nice and clean reports. So the use cases here designing tools and dashboards that don't have that spreadsheet look and feel, and then again creating separation between cells or columns or values without having to manually add extra redundant rows or columns. Let's go ahead and give this a shot in her pro tips workbook. All right, so go ahead and head to your table contents tab the beginning of the workbook and look for the color and border demo in the formatting tips section and go ahead and press link to jump straight to the tab. And what you're looking at here is basically just a raw, unformed, matted chunk of data, and we've got some traffic metrics here like impressions and clicks. That's an lead. Metrics got transactions. And this kind of follows. Ah, Purchase funnel. Right, So maybe we're driving traffic to a website here and we're looking at data week over week, and some of that traffic will convert toe leads, and some of those leads will ultimately convert into what we care about, which are actual transactions. So in its current format, this is very, very hard to interpret. As a user, you don't really derive any value from it because the human brain just isn't designed to see patterns when data is formatted in a raw, tabular style like this s So what we're gonna do is use basic formatting tools, weenies, colors and borders to really help polish this a little bit more and help the user digest the information, draw their attention to the metrics and the columns that we care most about. So starting with color, Um, one thing that I like to do is format my most important columns with a nice green. It's a subtle way to tell the user Hey, these air cells that I care most about this case, those would be our transactions. Let's go ahead in our home tab. Give those transactions a light shade of green, and then we can give the header a green shade as well for the sake of consistency. But to make it pop a little bit more. Let's go one shade darker. So there you go. We got a transaction. Metrics in green leads are also important, but they're not quite as important. They're kind of like a mid midway stop to a transaction. So let's go ahead and maybe use something like on orange or yellow. Here, after leads, same thing with the hetero make it pop a little bit more with a slightly darker shade. And then traffic is something that we don't necessarily optimize, too. So we can use more of a natural, a neutral shade here, like maybe a like a light grayish blue, and then format that header in line. So even just those simple steps have helped to create some separation between these kind of categories of metrics, which has really started to go a long way already. And now what if there's some columns here that we care more about? Certainly care about the volume of impressions and clicks or Leeds or transactions. But what's more important to us and perhaps to our clients, are the rates that cost per click in the clicker. Eight Cosper lied and lied rate and the cost per transaction and transaction read. That's the case. One little tip that we can use here continuing to just use colors and sell fills is to highlight thes slightly darker shade, just like we did with the header that basically tells the user these columns are still related to our other traffic metrics. But hey, pay a little bit closer attention to these because these are the ones who really care about . We're gonna do the same thing with our lead numbers here, that darker yellow and with our transaction numbers with a darker green. So again, even just a few simple steps and we're starting to really bring this data toe life using very fundamental tools. Next up, I want to show you how to use that border tool, that white border approach to turn this from kind of a set of spreadsheet numbers into something that looks like more of a polished report and first step to do that is actually to hide the grid lines here, which you can do in your view tab just de select the grid line box. And now it looks kind of like we're working on a blank white canvas. Then you're gonna go back into home, gonna select all of the cells where you'd like to apply these borders and then drill into the border settings right here in the font category. And what we want to do is go down to line color and select white. And then before you do anything, drill back in your border settings. We're gonna go in tow line style and select slightly thicker border option here in the middle. And then again, one more time back into borders and click the all borders button. And there you have it. We've applied those borders to the entire selection of cells, and this has created that effect of generating spaces almost between those cells and creating a nice separation to make these values a little bit more readable. So you could certainly apply your own types of styles to this, or you could take it from here and go even further. But that's a nice way to use some of those simplest tools like Phil and Borders, to create nice, clean, polished looking reports 7. Freezing Panes: all right. Next pro tip. Want Talk about freezing pains to keep certain rows and columns in view. Now you're freeze panes Options, which you'll find in your view tab, are commonly used to you guessed it freeze or fix the first rower column in place while allowing the rest of your worksheet to scroll. So if you've got a worksheet selected, you can head to your view tab and you'll see three different freeze panes options. Now two of these air Very straightforward. You can freeze just row one the top row, which is almost always your header room. Or you can freeze your first column column A. Now, the third option is a little bit less intuitive. What it will do is essentially freeze all cells above and to the left of the selected cell . So that's the only one that actually cares about where your cursor is or which sell you have selected when you choose that option. So if we were to click that first option freeze panes in this case where we have Selby to selected, we'd end up with something like this where Row one and column A are always visible in the window no matter where the user scrolls. So in this case, we're actually looking at data from Colin P. 32 to sell s 48 but we're still seeing the titles in Column A and the headers in Row one. So that freeze panes that generic option at the top of the list. That's a great tool to use when you want to fix both rows and columns. So if you use cases here, the most common one is really just keeping that hetero, visible special. If you have a very large data set with many rows and one thing to note, this is one of the default features of formatting arrange as a table, and then the second use case is breezing important or key columns when your tables might be very wide or contain a large number of columns or fields. So let's jump into our pro tip workbook and practice freezing some of these rows and columns. All right, so in a table of contents, look for the Freezing Pains demo and her formatting tips section. Again. This is a two star difficulty, not too bad press link. Now jump you straight to the town and what we're looking at here. This is movie data from the I M D B movie database. Now, anyone who has taken my data analysis with Excel Pivot tables course I'm sure you are. Ah, very, very familiar with this data set because this is the same same data, same information that we used throughout most of that course. For those of you who haven't seen this yet. Essentially, what we're looking at here is movie titles on each row as well as a whole bunch of attributes and metrics related to that movie s o the release date. Whether it's color, black and white genre language, country rating information about the actors and directors and then some metrics like Facebook likes. I am DB score reviews, duration, a revenue budget, etcetera. The details don't really matter. In this case. The point is, we've got a lot of columns to work with all the way out to Columns E. And we've got about 1000 rows of data here as well. And what you'll notice is that as I scroll down like this all of a sudden, these metrics lose their context because at first glance, I have no idea what that number 1000 and sell s 98 even means I have to scroll up kind of map it to the column header up in row one to say, OK, that represents movie Facebook likes. And now which title is that associated with? Well, same story. I've got a scroll all the way to the left and say, All right, that was from Can't stop the music Now what would be really helpful? What would be ideal is if I could keep the movie titles in view all of the time as well as my headers and Row One. So freeze panes is gonna allow us to do that. Now let's head to our view tab. You can drill into the freeze pain options to see those three choices that we talked about . And the 1st 1 just for the sake of demonstration is just freezing the top row. And when you select that option as you scroll up or down, row one never leaves the view. It stays fixed or frozen in place. But if you scroll laughter right, that column a is hidden as you move to the right, so it gets kind of half the job done, but not the whole thing. Same story. If we go back to freeze panes and just freeze the first column, it's gonna keep column a and view as you scroll left or right, but you'll lose those headers as you scroll up or down. So again it gets the other half of the task done, but not the whole thing. Let's go ahead back into freeze panes. What's unfreeze? And now remember, because we want to freeze both rows and columns and use that generic freeze panes option. This is where our cell selection is important. And because we're gonna freeze the cells above and to the left of the selected cell, that means I need to select cell be to if I want all rose above meaning Row one and all columns to the left column A to remain frozen, invisible in place. Essentially, what I'm doing here is I'm defining the top left corner of the window of my worksheet that I want Teoh remain Scroll herbal, if that makes sense, so with be to selected. Let's go back to freeze panes and use this first option here and now. Watch this. Check it out when we scroll down. We still get our header. We scroll right. We still get her title and look how much easier it is to interpret. You know some of these values deeper into our data. Set 12,000. That's director Facebook likes for Star Trek. Three 1 38 That's the total reviews for a nightmare on Elm Street. Eso really? What we've done is use a simple tool like freeze pains to make this worksheet much, much more user friendly. 8. Center Across Selection: I want to share a pro tip that I came across a few months ago that I thought was really interesting. And it's about centering text across multiple cells, but not in the way that you might normally do it. So, generally speaking, most users when you want toe center, text across multiple cells, we'll start by merging those cells and then aligning the text to the center of that emerged cell. And there's nothing wrong with that. It's totally fine. It gets the job done. Only down side is that occasionally that can cause some issues and headaches. If you do need to modify other cells in that worksheet, so an alternative approach here is instead of merging. You could do something like this where you have your text in one cell, in this case itself D to, and you can select the cell range that you would have normally merged. But instead of actually merging, go ahead and use the control one shortcut or navigate to the format cells dialog box and right there in the second tab, the alignment tab, you'll see some horizontal options, and one of those options is center across selection. Now, when you click that option, where you're going to see is the exact same effect as merging and centering without actually merging the underlying cells. So you end up with the same outcome with more flexibility. Now you can edit all of those cells individually, like you normally would. So really nice, helpful tip for doing things like formatting your text headers within reports and dashboards were eating to edit other cells within the sheet or customizing your alignment in cases where, maybe for whatever reason, you can't use merge cells. So let's jump over to excel. I'm gonna show you one quick example of how this could be used. All right, So head to your table of contents. Look for the center across selection demo. Go ahead and press linked to jump to the tab. And here we've got a basic performance. Dashboard. Um what? We're paying attention to our the header cells, right? We've got this main header here as well. A sub headers for different metric or column categories, traffic and leads, and finally, another header here. That's kind of indicator segment breakdown portion the report, and as I'm selecting these cells, you may notice that these are in fact merged cells, and the problem with these merge cells is that they look nice. But if I wanted to, let's say, rearrange the order of some of these columns like Swap cost per click and click rate, for instance, a right click column each could cut, and I get an error that says, We can't do that to emerge. So same story. If I want to clear some of the contents of these cells, right click clear contents. Same error. And it's because of these merge cells that were getting those errors. So let's go ahead and try to use that formatting tip at center across selection option to accomplish this same visual result while allowing all of the cells in your dashboard to remain edit herbal. So to do that we're gonna do is navigate to our home tab with this merge Sell selected, Which gonna unclip Emergence center that's gonna move the text to the left. Most cell. Right now it's D to and then do the same thing for the other three headers. Here you can check the button or use F four to repeat the last action, and there we go and now to accomplish that center cross selection effect. We're going to select that same range of cells that we would have selected to merge them and either right click or use control one tow launch the format cells dialog box. Gonna jump into the second tab alignment and right here in the horizontal options down near the bottom, you're gonna see center across selection press OK, and note how that text still lives in cell D two. But now it's displayed, centred across the selected cells, which is exactly what we want. Let's go ahead and do the same thing here. Gonna select the traffic options. Here's a great way to use that F four shortcut. Don't even have to go back to my format cells dialog box. I can repeat that same action that I just did for the other three headers and there we have it. So now if I wanted to swap the order of these two columns, right, click, cut, insert, cut. And we're good. Same thing. I can clear contents if I want. I can edit each of these cells individually. So there you have it. Nice little tip centering text across a selection of cells 9. Invisible Text Formatting: all right. Time to share one of my favorite pro tips. It's a really simple one, but actually use it quite a bit. It's about using custom formatting rules to make your text invisible. So what we're gonna do is basically use a custom number format and set a type defined by three consecutive semi colons to essentially tell Excel not to display the text in a cell. So you may be wondering, Well, what the heck is the point of completely hiding your text or your values? Well, in the demo, I'm about to show you we're gonna look at a heat map like this, right? We're looking at monthly average temperatures across a number of different cities and basically reviews to conditional formatting rule to generate this heat map style visual right where we've got these low temperatures and dark blue and high temperatures in red. And that looks great. But if we wanted to actually keep the format, keep the actual heat map effect, but not show the numbers, this is a perfect chance to do that. So what you could do is press control. One. Launch the format cells dialog box. You're gonna head to that last category that custom category on the left and simply type in those three semi colons in the type bar. When you do that, you're gonna get in effect like this. Now it's important to note you're not actually deleting the values themselves. They're still there. You'll still see them in the formula bar, but this formatting rule essentially prevents those values from displaying in the cells themselves. So that's one very common use cases. Creating these heat maps to visualize data and patterns and trends well, actually obscuring the underlying values. Another common use cases to transform columns of values into things like data bars or icon sets to visualize patterns again without showing the numbers themselves. So let's jump in or pro Tips workbook and give this a shot. All right, so here we are, on the table of contents were gonna go to our invisible text demo and formatting tips. PressLink. And here we see that temperature heat map, just like I showed you. We've got data for think about 10 different cities from around the world, with these average temperatures month by month and again, the idea is to continue to show this visual pattern this trend, but without having the distraction of the data points of the values themselves. So we can't just delete the values right, because the formatting was conditional based on those values. So let's undo that. We can't just shrink our values away. That's kind of trying to cheat. Doesn't really work. And we can't really format the color of our font any particular color because it's always gonna be visible somewhere, right? So those are some options that might get you close, but not quite all the way there. Also, if we were working with a tool or formatting option like data bar or a Nikon set, you do have options that air basically check boxes that say, Just show the bar or just show the icon, not the value. Unfortunately, we don't have that option here with our color scale, which is based on the self fill, but no worries. All we have to do is select these values control one toe launch that format cells dialog box. I had to custom and where says General here, just delete the general and type three consecutive semi colons press OK, and there you go. Now we just have the heat map effect. The conditional formatting continues to work because the values air still there. You could see them in the formula bar. We've just hidden them from view. So there you have it, one of my favorite kind of quick tips and excel using that custom number format to make your text invisible. 10. Zip Codes & Phone Numbers: there are handful of data types that are notoriously difficult to work with in Excel. One of them is dates, and the others are zip codes and phone numbers. And the reason why these types of data are challenging that should work with an Excel is that they look like numbers. They look like quantitative value fields, and because of that, Excel often converts them into numerical values the same way that it would convert sales data or Leeds or clicks or dollar spent. And the fact is, really don't want to treat things like zip codes or phone numbers that way, because we'll never want to apply any sort of statistical operation or aggregation or summer ization to those values. In other words, it will never in a 1,000,000 years makes sense to add to phone numbers together, even though their numbers and they look like values. We'd like to treat them more like text in this case. So here's an example of what zip codes and telephone numbers often look like when you load them into excel. The challenge with the ZIP code field is that you may have a leading zero in a five digit ZIP code that's been dropped off because Excel is saying, Hey, this is a value you don't need that leading zero It means nothing. But realistically, we know that as part of a zip code, that zero is meaningful and is important and significant. A similar case with the phone number. Generally speaking, you won't want your numbers formatted like continuous string of values like this. You'd more likely want to add dashes or spacing between the area code and the rest of the number, depending on exactly how you'd want to format it. So the good news is that most versions of Excel actually have a special formatting category in the format cells dialogue box, and it's right above the custom category at the bottom of the list. And within that special category, you'll find ZIP codes will find phone numbers and Social Security numbers. And the common thread between all of these different data types is that their data types that look like values but shouldn't be treated numerically. So if we were to take these two columns, apply those special formats for zip code and phone number, we'd end up with something like this, so those leading zeros would be restored for the ZIP codes, and the telephone number would be formatted with those parentheses around the area code, some extra spacing and those dashes before the last four digits. Now, one thing to note. If you don't have access to these special formatting options, or if you want to format things in a little bit of a different style your own way, you have a few different alternatives. You could write your own custom formatting rules, or you could use cell formulas. In this case, we could use a text function to correctly format, though zip codes and formulas like left mid or right to form at the phone number. So to summarize common use cases here again reformatting zips and phone numbers that were automatically converted into numbers and adding those leading zeros to force your ZIP codes back into a consistent five digit format. So let's practice getting her hands dirty with some really zip code and phone number data. All right, so head to your table contents. Look for that zip code and phone number demo and go ahead and click link. This will take you to basic sample of customer data. We've got first names column, a got last names, street addresses and then those classic zip code and telephone number fields as well, and you'll notice that our ZIP code has lost its leading zero. That's why we see some four digit Zip codes here and some five digit and our telephone numbers air just being treated as a long kind of string of values treated like numbers. So all we need to do here again, it's apply that special formatting rule. So let's start with ZIP code. Select all of Colin D use control. One is the shortcut to launch that format cells dialog box, and all we need to do is head down a special and zip code. So we're dealing with simple five Zip goats. We don't have the extra four digits at the ends. This first option will work just fine. Press OK, and there you go. All it's done is restored those leading zeros in cases where it had been kind of stripped down to four digits. So that all looks good. Let's go ahead and follow a similar approach with the phone number column calling e so control one to launch format cells. Special phone number. OK, And there you go. It's added the parentheses, added some spacing and a dash, so this should get the job done, you know, 99% of the time. But again, if you wanted something a little bit more custom, remember that you could always use functions like text left mid or right to customize exactly how the ZIP codes and phone numbers look and how they're displayed in the cells. If you'd like to learn more about those functions, check out my advanced formulas and functions. Course otherwise, using this special cell formatting option should get the job done. 11. Grouping Columns & Rows: all right for our next pro tip. Let's talk about grouping columns or rows to help you simplify your Excel reports. And to do that, we're going to use the group tools from our data tab to hide selected rows or columns from view. And a common reason you might want to do this is if you have a report that looks something like this, it's just overwhelming. It's dense. There's way too much information packed in there, and we've got to simplify things for our end users. So in a case like this, one thing that we could do is select groups or chunks of rows or columns and use those grouping options from the date attempt to turn it into something like this. Now, essentially, what Excel has done here is consolidate and collapse. Those columns arose but automatically generated toggles those plus and minus signs that allow the user to show or hide those individual groups. And then also in the upper left corner of that screen shot, you'll see buttons labeled one and two, and what those will do is actually hide and un hide all of the road groupings or all of the column groupings with one click of the mouse. So common use cases here. I use these tools quite a bit because I designed a lot of reports that by necessity, contain a very large amount of data for my clients. But the fact is, I only want to display the most critical, the most relevant information up front. So what I do is use thes grouping tools to kind of collapse or consolidate those extra columns behind the scenes so that they're still there for users to access. If they'd like to drill deeper and then second use case here is again, it gives these users away too quickly. Toggle between views you know, from a detail view to a summary view without having to manually right click Hide a row, right click on hided again. Thes toggles produced by the grouping Tool. Make that interaction very easy and very smooth. So let's jump into our pro tips workbook and give this a shot. All right, so in your table of contents, go ahead and look for the group column and row demo in our formatting tips section who had link out to that tab? And what you'll find is an absolute beast of a report here got something like 30 different columns of data. As we scroll through, you can see that we're breaking things down by weeks by segments by regions. It's just way, way too much information, you know, Look at the metrics. We've got five different component lead types got transactions for something like 10 different products. When realistically, the most important information is that total lead number on that total transaction number. So got a lot of information here. That's really just creating noise and making it more difficult for the end user to derive any sort of value from this report. And listen, I've worked in analytics for a long time. I've built tons of these reports for clients and customers and colleagues, and I understand the feeling of wanting to include metrics just because you're tracking them. Now here's the thing. There is a middle ground between excluding data from the report and showing it in one monster view like this. And that middle ground solution is using these group columns and rows to consolidate and hide some of that data from view. But the key is that we're not deleting it. We're gonna keep it there. It's still gonna be accessible if and when the end user wants to drill into that level of detail. So it's a really great option in cases like this now, to make this happen, let's go ahead and hide or consolidate some of the extra data. That's maybe not as critical. So, for instance, looking at our segment breakdown, maybe we just want to compare a versus B for the current week. And it's less important to understand how each individual segment has trended over the last four. So, for example, I can grab Row 21 hold Shift Select 23. These are the three rows that I want to hide or collapse. Using the grouping Tool can navigate to my data TEM and hit group from the outline options and press the group tool. And there you go. It's created this little toggle, this handle that now allows me to hide or display those three group rose with the click of a button. Now go through that same process. Highlight Rose 26 through 28 and one of my favorite shortcuts. The F four key is just gonna repeat that last action. So just gonna group Those Rose. Same thing with my regions Select Impress at four. Select the three rows at four, just repeating the process for each of these grouped sections of Rose. And then, once I've gone through all of the rows that I want a group I can scroll up and take a look at this one and to label appear in the upper left corner. But that's gonna allow me to do by pressing the one that collapse all of those groups of Rose with one single click. And as you might expect if I click to, it's gonna expand all of those groups of Rose with one click. It's a really helpful tool there. Let's go ahead and set it to one and keep these rows collapsed and go through that same process with these extra metrics columns. So, for instance, we can select column through em, which contains our individual component lead types here Group group. There we go last, but not least, que through Z, with all of our individual product transactions, group those as well and there you have it. So now take a look at this kind of before and after shot. We have essentially trimmed down a huge amount of information to really draw attention to the metrics that matter. You know, the current weak performance for Segment A versus B or Regions A versus B, C, D and E. We're looking at the total leads and the total transactions. But here's the beauty of it. If and when our clients says that's great, that total leads are up week over week. But which individual leads drove that game? Well, we've got the option here. We've simply consolidated it into those group columns. So there you have it. That's your primer on grouping rows and columns. Really great way to simplify your reports for your end users. 12. Formatting Errors with IFERROR: all right. And this next pro tip want to talk about reformatting errors using the if error function. And in this demo, I'm gonna show you how to wrap formulas with an if error statement to essentially customize how those error messages will be displayed in the cells. So, for instance, instead of showing hashed of zero or numb or reference error, you can customize how those errors will be displayed. Like with a simple dash, for instance. Now really, really important note here is that you've got to use this tip carefully and on Lee when you understand and expect the source of the error. So in this particular use case we're using if error as a cosmetic tool, we're not using it as a Band Aid to try to fix broken formulas or mislead users in any way . So let's take a quick look at an example where, using if error might be appropriate Now, if you've been following along with the course, this should look pretty familiar. We've got our interactive baseball report here. We're showing metrics for it given selected player, and this is a case where we do expect and understand that there will be did zero errors. We're gonna have players who didn't play in a given year. We're gonna have players who didn't attempt a stolen base, in which case we will have equations that have a denominator of zero, which will in turn evaluate to a div zero error message. And that's OK. It doesn't mean there's anything wrong with our logic or calculations. So in a case like this, what we could do. Let's take a column like stolen base percentage and essentially wrapped that function, which is dividing the stolen bases by the total attempts. Wrap that in an if error statement so essentially telling excel. If you get an error when you evaluate this formula, then return a dash. That's the dash and two quotation marks. Instead of returning your default error message, and when you apply this change to the rest of the formulas in the report, you end up with something like this, which just looks a lot cleaner and a lot more polished for end users. So common use cases this is really one of the most common ones cleaning up these years are facing reports, or dashboards, where occasional errors are expected and understood and then. Also, it's important to keep in mind that if error can also be used for other reasons that are not simply cosmetic, like, for instance, if you want to produce a completely different output based on whether or not a conditional test yields an error, you can use this in much more complex ways as well. So let's jump into our pro tip workbook and practice wrapping some functions in an affair statement. All right, so from your table of contents tab, go ahead and find that reformatting error demo in our formatting tips section and press link that's gonna take us to our baseball dashboard. Here and again, we're looking at six years of baseball performance data, and we have the ability to drop down this menu and select any player we choose from the list. And in many cases, like Adam Jones here, everything looks just fine. This is a player who played all six years in the sample. There's no diff zero no zeros and denominators, so we don't see any errors, and this looks perfectly OK. But what if we change the selection to someone like A J. Pierzynski? For instance, here's a guy who's not much of a base runner doesn't steal a lot of bases. So as a result of formula like stolen base percentage, which has a denominator calculated as columns A B plus A C is going to yield it. Div zero error in cases where this player didn't attempt a single stolen base all season. And then we'll have similar cases for players like Victor Martinez all the way down here at the bottom who didn't play an entire season in this case. In 2012 he tore a ligament in his knee, didn't play a single game. So we get these div zero errors for three of our rate metrics strikeouts, two walks, stolen base percentage and batting average. So let's go ahead and apply this if error statement to those three columns and reform at how those errors air being displayed. So we're gonna go ahead and start with the average, basically just taking the hits divided by the at bats. All we need to do is place are cursor right after the equal sign type if error and open a parenthesis, you're saying if you get an error when you make this calculation, comment to the value if errors. This is what we want the cell to display instead of the default error message. So something like quote a simple kind of dash surrounded by two quotes. Close the parenthesis press OK? And then we can go ahead and grab that first formula and drag and apply it all the way down to Road 10. And there you go. You see that? DIV zero marriages completely disappear. Same story with stolen base percentage if error open parenthesis, comma, little dash and quotes Close it off. Press enter and drag it down. We should see those to our messages Turn into dashes. There we go And then last but not least, K two b b, which is strikeout to walk ratio. If you get her comma returned a dash close the parenthesis press OK and we are good to go. So now, no matter what player, I use their select here whether they play it every year in the sample or not, they won't get those ugly kind of harsh error messages. They'll just get a simple dash instead. So there you go quick little primer on how to use the if error statement. Clean up your error messages 13. Converting Text to Date Values: all right. This next pro tip is one that I'm really excited about because it's one of the most valuable tips in the entire course, going to talk about how to convert text strings into date values. In other words, we're gonna take fields that excel recognized as straight up text and turned them into proper date values. Now this should come as no surprise. But dates are one of the most challenging, if not the most challenging data type toe work within Excel. And the reason why it's so challenging is because date formats tend to very considerably not only across different regions, but even within individual regions or locales. So in the US, we might use month slash day slash year in Europe, it might be days slash month slash year, and in other cases, you may have data coming in in totally random formats, like day, day, month, month, year, year, your year or some variation of that. Now, the good news is that Excel was built to understand a pretty wide range of date formats specific to your regional settings. The not so good news is that you won't always get data with dates in your regional settings , You may have to sometimes reformat a restructure dates so that Excel will understand them. Other times you might get data that's just in a totally wacky format that Excel has no choice but to format as text. Like this data, for instance, here's a quick snippet of the demo we're gonna look at in just a second. Take a look AT column A. These dates are formatted your year, your year, month, month, day day. And unfortunately, that's not a format that Excel is designed to understand. So as a result, all of those values in column A are being treated as text, just like the type column or the country column in B or C. But here's the thing. Not all is lost. If you have a case like this, what we're gonna do and what I'm gonna show you how to do is used text functions like left mid right. And can Katyn eight toe actually physically rearrange the pieces of that text string into a format that my version of Excel will understand. And then finally, I'm gonna wrap that function in something called a date value toe, actually convert the result into a proper date. And once I do that, I'll be able to use date and time functions and time intelligence to treat that date just like a would any of. So, in other words, you want to take what we have here in column A and turn it into something like this in column B, which we've labeled new date. And to do that, I'm gonna show you how to write a function like this. So it looks like a bit of a beast. But trust me, it's not that bad, and we break it down again. We're just grabbing pieces of that text string, rearranging them and then telling Excel, Hey, this is a date. Treat it as such. So to recap, common use cases reformatting fields that Excel wasn't able to recognize as one of those common date types or converting dates from text to date values so that you can use them for time, intelligence, date, time or time series analysis. So let's jump into our Excel workbook and see if we can actually use thes functions to convert a text string into a proper date. All right, so open up that excel pro tip workbook in the table of contents. You'll see the text to date conversion demo towards the bottom of the formatting tip list. This is a four star difficulty. So we're getting towards the challenging side of the spectrum. Go ahead and click link to get out to the text to date conversion tab. And what we're looking at here is shark attack data. And for those who have taken my data analysis with pivot Table course, he'll recognize that this was used in one of our case studies for the course. Basically, we've got records of every shark attack dating back to about 1950 whether it was provoked or not. And yes, apparently some people choose to provoke sharks for some reason. What country and area took place, what the victim was doing name, gender, description of the injury and a little bit morbid, whether or not it was a fatal attack. But we don't really care about these different columns right now for the sake of this demo , really just want to look at column A, which is labeled as a date column. But I have my doubts. So what we want to do first is confirm whether or not excel is treating these values in column A as dates or not, and so we have a few different ways we can do that. One option is to select one of the date values and use the control one shortcut to pop up or format cells dialog box. And we have two different options here. First, we can go into the general category, and if this were a proper date, what we would see here in the sample box is a date value, which would be a five digit number in this case. That indicates the number of days passed since January 1st 1900 which in the Excel world is the start of time. In this case, that's not what I'm seeing. I'm just seeing the exact string in the cell returned here, So that's a pretty good clue that this is not being treated as a proper date and another option. If we try to just select one of the standard date types here, press OK, nothing happens now. Another way to check is to insert a new column and try to use a date function like year, for instance, and reference that field. So the year of a two equals a number error because a two is not a proper date. That kind of seals the deal. We know now for sure that these values in column A are not proper dates these air text strings disguising themselves as dates. But not to worry we can fix this we're gonna do is label this new column B something like New Date. And again, what we're gonna do here is actually physically grab pieces of this text string the month piece that day, Peace the year piece. We're gonna force them into a format that Excel recognizes as a proper date. In my particular case, I'm going to use the two digit month the slash the two digit day slash and the four digit year. And to do that, I'm going to use thes text functions like left mid right and ampersand. Or can captain eight. So let's give this a shot and kind of work through it piece by piece. Start with the equal sign, and the first component that I need is the month, and I know that the month lives in the middle of my text string in column A. So when you use a mid function here and my text lives right here in eight to and it starts in the fifth character because the 1st 4 are the year and the number of characters that I want to return are, too, because I want a two digit month here. Let's close it out, stop right there and just press enter. Play that down. This will be a good way to kind of spot. Check that it's working properly. So this should be a one for January 2345 all the way up to 12. That looks good. So I got my month in there. Now the next piece of this formatted date that I need is the slash followed by the day. So I'm gonna manually insert that slash using, like in Katyn eight symbol and then a slash surrounded by quotes. And now I need my day. So one more and percent and the day is basically represented as the last two characters or the right most two characters from that stringing column A. So you guessed it. I'm going to use a right function there going to say, give me some text from the right of a to I want to characters. Close it off, press enter. And as you can see, we're starting to get a little bit closer. January 12 January 16th February 18th July 21st. So we're getting there Next up, we need the year. So again we need a second slash first surrounding quotes and percent. And that year is represented by the left most four characters. So I left Have a to and we want four characters this time. Close it off. Press center, quiet down. And now this is starting to look like a riel. Properly formatted us style date. 1 12 1956 25 1950 But we're not quite there yet. The last thing we need to do is take this text string because it still really is just a text string and wrap the whole thing in a function called date value. And we do that. We say, Alright, Excel. Take a look at this date text that I've created and convert it into a value that you can understand right when we apply that all the way down. Take a look at that that's looking pretty darn promising at this point. So now all that's left to do is confirm whether or not we've gotten there. We can use those same tools that I just showed you a minute ago. Can right click format These cells take a look at the general format, and that is a proper date value. So this is telling me that January 12th 1950 occurred 18,275 days after January 1st 1900 which again is the start of excels. Timeline. So that's looking good. Press OK, and we could just undo. We don't actually want it. Display as Thea Date Value one to display a more appropriate, a readable format. And let's just check one more time by inserting a new column. And let's try to use function like year and reference this new field and press OK, looks a little bit funky here. Might be wondering why it says 1905 That's just because this new column is formatted as a month, day, year date style. We don't want that type of format. In this case. We just want general or number format, and that will turn it into something that makes a little bit more. Since like 1950 1951 all the way down to 2015. So there you go. That's just confirmed that we have indeed successfully converted this text string and column A into a properly formatted Excel date in column B. Let's go ahead and delete that year field, and there you have it really great tool that could be an absolute lifesaver when you need it. 14. Formula-Based Formats: All right, This next pro tip is one of my favorites. I'm gonna talk about defining our own formula driven formats. Now, we're up in the five star expert level category now, so this is not gonna be easy, but it's a really powerful tool once you learn how to harness it. And what we're gonna do in this demo is used formula rules to format cells, using complex logic that are standard formatting and conditional formatting tools simply can't handle. So, for example, we're gonna apply formatting to entire rows based on values and other columns. We're gonna apply formats to cells that meet multiple formula based criteria, using things like conditional statements and logical operators. And in this example, we were going to do is take a look at an example to do list, right? We've got a list of tasks, categories, priorities and due dates. And in order to apply these formula based formatting rules, what we're gonna do is select the range of cells that we want to apply formatting to. We're gonna drill into our conditional formatting menu from the home tab and click New Rule and then the bottom of the list. There's an option that says use a formula to determine which sells to format now. Rule of thumb here, a little tip in Tell a sense, which is basically that formula helper box that appears when you're writing a formula in the standard formula bar. Uh, that doesn't exist here in the conditional formatting dialog box, so it's kind of like the Wild West out there. Make sure you know your formula syntax before you start writing your formatting rule. And what this option is going to do is allows to create complex rules like this one. In this case, we're highlighting entire rows with red fill in cases where a the due date has passed and be the status and column E is zero or incomplete. And that's something that you just can't do with your standard conditional formatting tools . So to summarize, some common use cases again applying those custom or complex formats beyond the standard options or highlighting cells that meet a specific set of multiple criteria. So let's go ahead and open up, excel and see if we can write one of our own formula driven formatting rules. Okay, so if you've been following along, go ahead and Open up your Excel Pro Tips workbook. We're gonna jump into our formula driven formats lecture down here in our formatting tips section press link that will take us right to our to do list. And here we've got a list of about 20 different tasks. They're categorised as worker personal. We've got priority where Oneness High three is low. Got a list of due dates here, and then an icon set for status zero is incomplete and a one is complete. Now, one thing to notice that this is based on a today function which is volatile, and that means that you're not going to see the exact same dates that I'm showing in my screen. But you should see the same trends and relationships between your today and your due dates in your workbook. Now the first thing that I want to do is highlight or format this due date column to draw attention to important dates, and let's start with two different rules. First rule is to highlight any date that has passed, meaning the due date is behind us. So to do that, let's go ahead and select the entire range of cells that you want to apply formatting to gonna go into her home town conditional formatting. And as a rule of thumb, you should always check to see you know if a standard tool exists to serve your purposes. In fact, there are highlights cell rules that use dates specifically, and there are a number of options that you can use here. So formatting cells that contain a date occurring yesterday or today or tomorrow or the last seven days. But nothing. That's quite what we need, because the first thing that we want to do is highlight any cell that's before today any cell or date that's in the past. And then after that, we're going to do one more set of formatting rules to highlight dates that are almost do so dates where the due date is within three days of today, and neither those options are available here, which tells us that we're going to need to use formula based rules. So let's cancel out. We'll keep this selection active, go back to conditional formatting. Now we're gonna go into new rule, use a formula to determine which sells to format. So our 1st 1 where the date is earlier than today the date is passed just like any formula . We're gonna start with an equal sign, and we need to select one of the cells in our range containing a date. Let's select the first cell here D three and say if deep three is less than today, open closed forensic, then format that in a certain way. So if the due date has passed, let's go ahead and fill these cells with something like a light reddish orange tone could pick whatever you want here. Press OK, press okay? Again, You'll notice that the entire column formatted right. That doesn't seem right, because today is 10 15 and we've got 10 16 10 21. Those really shouldn't be highlighted. So to diagnose what's going on, let's jump back into manage rules. Select that ruling clique at it, and what's happening here is that when we click d three, it automatically applied fixed references. So what this is doing is for every cell in that selection. It's checking. This condition is D three earlier than today, and because D three is September 15th and today is October 15th this will return true every single time, meaning that this format will be applied to every row, every cell in this selection. So all we need to do to combat that is changed the Roe reference to relative by removing that dollar sign before the three and that will allow us to check the dates not just in row three, but also 45678 all the way down to Road 24 and now pressing OK and applying that updated rule. Now we see what we want. Any date before 10 15 is now highlighted. Any date on 10 15 or after is not highlighted, so that's working exactly as we'd expect. Now let's work on that next rule. So now we want to highlight dates that are almost do so due within the next three days. So again we want to highlight that same range of cells back in a conditional formatting at another new rule using a formula. This is gonna be similar but a little bit different. So we're going to say, if the date in this column and remember, we've got to remove that fixed row reference minus today. So the difference between that date and the current date That difference is less than or equal to three days. Then we know the due date is coming up. It's approaching. So let's format that with something like no yellow fillets, like a warning sign. Right Press OK. And we press Okay, it's gonna happen. Is that that new rule is gonna take precedent and essentially overwrite the red fill that we had created in our first rule. All we need to do is manage our rules again and simply change the order so that our red rule dates in the past again. Take priority. So press apply and OK, and there you go. So this looks exactly like we'd expect these air. The three tasks that are coming up got process. Those expense reports contact I t. And then we've got to restock that goldfish food, right? So those are two examples of how to use relatively simple at date rules and apply them to one column in this case are due dates in column D. Now let's do one more example and get a little bit fancier. Let's flag entire rose where three conditions are true First, where the deadline has passed. We know how to do that already second, where the status zero, meaning that task did not get done. And three, where the priority is one. So we're looking for high priority tasks where the due date has passed and they were not completed. Now, to do that, we need to select the entire range of cells where we want the formatting to be applied. And because we want formatting to be applied across an entire row, we're gonna grab this entire range from a three d 24. We're gonna add a new formula based rule. And because we want to test for three different criteria all being met, we're going to use an an statement. So start with that and open up a parenthesis and then right are three conditions. So condition number one we've seen before the due date with a relative row needs to be less than today. It's past that's condition. One condition, too, is that the status is incomplete. So sell E three with a relative row reference equals zero and are third condition. Is that the priority cell C three with a relative row reference should equal one. So those are three conditions here. Let's close the parenthesis, set a preview, determine the format And this time, instead of a Phil, let's go ahead and just do a bold font. And let's make the font color bright, bright red and press. OK, so and D three is before today, E three equals zero and C three equals one press. OK, and take a look. So two of the Rose and our to do list have been highlighted, and both of these rows were not complete. The due date has passed and their priority one. So we should have coordinated those interviews back on September 26th which we did not do. And, uh, we did not send invites for Billy's birthday. They were supposed to send him out in October 11. So if only we had created these formula driven, conditional formatting rules and our Excel to do list earlier, we might have caught this and Billy would have had the birthday that he deserved. Obviously, these air just a couple examples of how you can use formula based formatting rules to do really interesting things like this in Excel. Hopefully that gets your gears turning and inspires you to create some interesting conditional formatting rules of your own 15. Advanced Number Formats: All right, So this next tip is a five star, expert level formatting tip. We're gonna talk about some of excels advanced custom number formats. Now, if you've been following along with the course, we have used some of these custom formatting options in the past. For example, when we use the triple semi colon custom formatting type to make our text invisible. Technically, we were using one of thes custom number formats, but realistically we were barely scratching the surface of what these tools are capable of . And in fact, there's an entirely different language or syntax behind the scenes that drives much more complex custom number formats. And what users don't realize is that you have the ability to format numbers based on four different conditions positives, negatives, zeros or text. And that's what will allow us to create custom rules that we may Onley want to apply to positives or negatives or any other type of value that we've selected. Now, in this demo, I'm gonna cover things very quickly, keep things very high level. If you want to learn more and really dig into the syntax, follow this link here. It will take you to the Microsoft Office support site, where they have some really deep, really comprehensive documentation where you can learn a lot more about the capabilities of these custom formats. So in this demo, we're gonna take a look at a few different columns of data, look at gross revenue, budget and profit. And basically, we want to turn these rock unformed added values into something like this. And basically, what we're doing is rounding down these very large numbers and adding labels like an M to signify millions. But the key is that we're not actually dividing the underlying value. It's still three million were just using a custom number format to change the way that numbers displayed so that it's 3.0 with an M label. Now. One thing to note. As we work through this demo, keep in mind that your numbers may look a little bit different based on your regional settings. In places where I'm using a comma to signify thousands, you may use a period or vice versa. Just keep that in mind. So we're gonna walk through each of these examples step by step. But essentially, this is what we're gonna be doing. We're gonna select column of Valleys. We're gonna launch the number format Dialog box can use that control One shortcut. We're gonna go into a custom category and apply a rule like this one here for profit in order to form at those values in a way that we couldn't do with standard tools. So same story with revenue and budget. We're gonna go into that custom category, right? Our own custom rule, and transform it into something like what you see here. So common use cases. Maybe you want to apply custom formats to Onley positives versus only negatives to draw attention to certain patterns or trends. Or perhaps the most important or most common use case here is like I've shown to abbreviate very large values with those K or M labels to signify thousands or millions without using a formula and without modifying the actual underlying value. So with that, let's jump into excel and see how this actually works. All right, so in your pro tip workbook head your table of contents, you look for the advanced custom formats demo in our formatting tips section. Go ahead and click the link and what we're looking at here is a pretty simple data set. We've got about 50 rows of data. Look at movie titles with gross revenue, budget and profit currently unformed, matted I would like to do first is play with this gross revenue column and show a version of it that's rounded down with that millions label. So that's a little bit more readable, that we can compare numbers a little bit more clearly side by side. So the first thing I'm gonna do is actually created duplicate. So when you right click and copy Colin, be and insert it right next to it, I just want to be able to spot check against my original column and make sure that these custom rules are operating as expected. So we go and select that entire column C the the right click choose format cells or used the control one shortcut and navigate all the way down to our custom category. First thing I'd recommend doing before you just start typing things into the box here is to actually look at the list of pre populated custom options here because in most cases you have something that's gonna get you close to what you need and then you can tweak or modify from there. So to get a sense for how this syntax really works, let's look at what some of these examples actually do. So the 0.0 will essentially force to decimal points whether or not they're significant values. So I can show you what that would look like. Simply adds the decimal point. Let's control one to get back there. The hash with a comma essentially forces a comma separator for thousands, and that's something that we will definitely want. So let's go ahead and test that one. There you go. It's gotten rid of the decimals, but it's added those comma separators. Let's jump back in. This format would apply both the decimals and the comma separators, and now we're getting into some more complex cases. Were retreating positives differently from negatives? We've got labels that apply it red color to the funt. If you're curious, this little underscore open parenthesis. What that's actually doing is it's adding a space equal to the width of a parenthesis for all positive values, so that both positive and negatives, which are always surrounded by parentheses, will be in alignment. So just a little teaser, You know, for how complex this syntax can really get. But in this case, let's stick with our thousands separator for now. And all we're gonna do is add a dollar sign to symbolize currency at the start press. OK, and there you go. So we're on our way, but nothing really groundbreaking. Yet This is a format that we could have applied very, very simply. So if we want to get a little bit more complex here, let's go ahead and add to this a little bit. We were to just add a space and say, Put a K there in parentheses. All that would do is just at a K label at the end, right? And that doesn't make much sense at this point because we're still looking at the full number. We're not rounding down at all. If we want to tell Excel to round down the last three digits, we can do that by simply adding a comma after the zero. So watch these numbers 82,300,000 press OK, that becomes 82,300 with decayed symbolized thousands. So technically, this number is correct, but it's still too large to be really readable. So instead of using the K label, let's jump back into our custom dialogue box. What we're gonna do here is round down another three digits using a second comma. We're gonna change that K to an M for millions and press OK, now we're getting somewhere. So we've got 82,000,016 million, 17 million. The last adjustment that I want to make here is to actually bring back those decimal points because now we're rounded off a little bit too much. So let's go back in one more time. All we need to do after that zero it's at the period 00 that's gonna force to decimal points. Press OK and Walla. We've got our beautifully formatted, rounded down gross revenue values with that millions m indicator and we haven't actually changed the underlying value. That's the key here. We've preserved the value itself. We've just displayed it in a really interesting way. And this formatting rule applies and can be copied just like any other. So we could select all of column C head to our home tab, use the format painter and apply that same rule. Two column B the budget, Kahlan and all of those conditions carry over. So now. One last example. Let's play with this profit column a little bit. Calling e right click had to format cells going to custom. And in this case, I want to draw attention to any films that may have possibly drawn a loss. So, in other words, a negative profit. This is a good place to use these custom formats and another good example of when I don't want to reinvent the wheel when I know their options here, that will get me very close to what I need. So, for instance, this one right here, it's a currency format. It doesn't have decimal points, which is good cause. I want this rounded to whole numbers, and it already has this red format with parentheses for negative values. I know it's negative because it's the second condition after the semi colon. So let's go ahead and start with this one and press OK and that gets me really close to what I want. It's showing me all of the positive profits as a traditional currency format and any negatives. This case there's only two as red with parentheses and from here, you could kind of tweak this as you see fit. You know, if we wanted to maybe go back into custom and make our positive numbers green, you could literally add brackets and type. There were green and press. OK, and there you go. It worked. It's not pretty. In fact, it's really hard to read, and it's pretty ugly. But that applied a green format to the positive values and let's go back. And because that green was a little bit harsh, maybe instead of the green, we just want a plus sign for positives. And then, by contrast, we can add a negative sign before the red negative numbers press. OK, And there you go, so you can start to see all of the flexibility here for creating her own very dynamic, very complex number formats using this custom syntax. So again, if you want to learn more, make sure to head to that office support link. They've got some great documentation there. Otherwise, that's your quick primer on advanced custom number formats.