Jan asks if it is possible to shade half of a cell. Episode 905 shows one cumbersome way to do it . This podcast is the video companion to the book, Learn Ex...
Transcript of the video:
Welcome back to MrExcel netcast, I'm Bill Jelen. Today we have a question sent in by Jan. Jan has a cell where she's drawn a diagonal line through the cell using the border tool, and wants to know if she can color just the top half of that cell. You can do it, but it's rather cumbersome.
I'm going to come down here to the drawing toolbar, and under Auto Shapes, use Basic Shapes and choose the right triangle. Now, when I draw this right triangle, I want to drag, but I'm going to hold down the Alt key while I drag. So I can start to drag and then hold down the Alt key, which basically will force the triangle to fit the shape.
Now, I'm going to take that triangle and I want to flip it over, so that way it's at the top half. I'll hold down the Shift key while I click on the green handle and drag around. Basically, we'll now get a nice triangle that fills in the top half. I want to format that using Ctrl+1, and we're going to choose any color in here-- I guess I'll choose a green.
And new in Excel 2003, we can change the transparency. So I'm going to go up to about 60% so that we have some color, but we can still see through it. And then, importantly, we want to turn off the line-- say "No Line", click OK. And what we've done now, is we've formated the top half of that cell. Now, if we use Format Cells, and go into the Border, and set a diagonal line, click OK, we now have the illusion that we've colored just the top half of that cell. So that might be cool.
Now, Jan didn't ask this, but I'm guessing that they actually want to have something on top and on the bottom of that line. And the cool way to do this is to type a few spaces and then type the number that should go above the line, and then Alt+Enter-- hold down Alt+Enter-- and without typing any spaces, type the number that goes below the line. And basically, what we do is we turn on Wrap text, and you see that we have a number that appears above the line and below the line. So in that case, I can see, you know... Again, we would do pretty much the same trick as before. I'm actually going to copy it up here and see if I can just resize it to create a triangle that will color just above the line. Now, of course, every time that we change the column width, or change the row height, we're going to have to go through and adjust those triangles.
So, very, very cumbersome, but if you just simply need to make it work, there's certainly a way to have it work.
Alright, well I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Keywords for this video: accounting, autoshapes, business, excel, format, microsoft, mrexcel, spreadsheets, technology, tutorial
This video is current as of December 16, 2008