Transcription
So let’s say I have three bookshelves at home that are packed with movies, and I need to keep an Excel workbook just to know which bookshelf has which movie. So if you look here, I have movie titles and format, I also have three sheets at the bottom: bookshelf 1, bookshelf 2, and bookshelf 3. They’re all identical in format, the only difference is they have a different list of movies. Let’s say I wanted to add a background color to the header, just to make things a little prettier, in this sheet. To do that, I would select row one, select the paintbucker tool here, select the color I wanted, and release. And there you go, I have a gray header – I could do the exact same change to bookshelf 2, select the first row, apply the color, same thing to bookshelf 3. Now, that works, but it’s pretty manual – and I wanted to show you a better way. I’m going to press undo three times… okay.
Now I’m back to the first sheet – instead of doing it the way I just showed you, try this – go to the first sheet, hold SHIFT, and then left-right the last sheet. You’ll notice that they’ve all been highlighted, that’s because they’re now all selected, and when I select the first row, and then the select the color – as the background color, the same change will apply to all selected sheets. Now that’s cool – but you know what’s more impressive? The same thing works for formulas… so look at this. I’m gonna go back to bookshelf 1, I’m going to hold SHIFT, left-click the last sheet, and then, let’s make something a little better.
Let’s say I wanted to know the number of movies in my shelf. So let’s type here “Number of movies” and then we’re going to put it right there. So here I’m going to put COUNTA, and all that does is it actually counts all the cells that have values in a given range, so the range I’m going to give it is the movie titles, I’m going to go column B, starting at 2, as you can see on the left here, all the way to B, ending at 999. Why 999? Because I know I have less than that many movies per bookshelf. So I’m going to press ENTER, and here you’ll see okay, it has 300, nice. And if I go to bookshelf 2, 169, bookshelf 3, 342. I only had to change it in one place and all the cells were configured.
Now if you know your workbook is going to have many sheets that are identical in layout, you can leverage this to really save some time. And just so you know – CTRL works too. Let’s say I wanted to select bookshelf 2 and 3, and make their header color, you know, yellow, for whatever reason, and then I go to bookshelf 1 – that one’s still gray. So SHIFT and CTRL are super useful if you want to modify multiple sheets that are similar in format, at the same time.