Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you are looking at a large database sometimes it becomes difficult to read, particularly if it's got a lot of dense scientific data. This one might be not so bad, but how about this one here, this particular worksheet which has a lot of scientific data? And one thing that would help would be to somehow be able to add color to every tenth row, every sixth row, whatever seems to fit your needs. One approach could be to turn this into a table, and in Excel if you click the Insert tab and choose Table, Excel quickly figures out the range of your data, click OK, and you can get this effect. That would certainly make it more readable, and there are quite a few variations in here. But maybe what you would like to be able to do is to control this with your own sets of colors and possibly your own groupings.
Maybe it makes sense to look at this data and think of it as pieces five and ten, for example, five rows, every ten rows, something like that. So, although this might be appropriate for some situations, let's do some undos here. I am going to press Ctrl+Z a few times to remove this table definition and point in another direction, and you could do this for any worksheet of course. I am going to click in the upper-left corner to select the entire worksheet and then on the Home tab choose Conditional Formatting. There is no built-in feature here that actually does this for us, but we can create a rule here, a new rule, and this is a little obscure at first, but once you see that, I think you could see how might use it and apply it to your own example.
Now I am typing in a formula here, =mod. What does that mean? It comes from the word modular, it's a function in Excel, and what we are doing here in fact, is saying if the cell in question if divided by five has no remainder, if it's equal to zero, then that row we want to select and we want to apply format to. What cell do we use here? The active cell in this worksheet right now is A1. Now just by putting in a1 because we have selected the entire worksheet, this applies to every single cell. It's like a surrogate or substitute here.
=mod(a1 and actually what we are looking for here is on the row number of a1, so let me put the word row in here. Now Row is another function in Excel. When we divide this by five and the remainder is zero, and so any cell that's in row 5, row 10, row 15, etcetera will fit this particular formula. Apply a format. The most obvious format would be to fill it with some kind of a color. Pick any color that seems appropriate for you. I just pick yellow here. Click OK, click OK and every fifth row is yellow.
Now if we delete rows and add rows, it's still going to keep the scheme no matter what. It's always there and if we print this it's going to be much easier to read. If we just keep it here as we view it, its much easier o read. You can easily change your mind about the scope of this. We could click in the upper left-hand corner, go back to Conditional Formatting, possibly manage the rules, edit the rule. If we want this to be every four rows, change the five to a four, every three rows change it to a three, every ten rows change it to a ten and so on.
Similarly you could do this with columns. Now I would not suggest doing both at the same time, but if you somehow thought this was useful to do it by columns, same idea. Obviously the word row would be replaced by column and then the number here would be the one you choose. So it's an easy feature to set-up by way of Conditional Formatting. Again, the rule is a little strange if you have never used the mod function before. You do have to keep in mind that even though this only refers to cell a1, by inference it refers to the entire selected data and since we selected the whole worksheet it applies to the entire worksheet.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91106 Viewers
80 Video lessons · 138156 Viewers
59 Video lessons · 56972 Viewers
52 Video lessons · 70605 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.