Excel: Merge Cells and Multiple Lines in Cells

A couple of related formatting tricks that you can use within a spreadsheet this time.<br /><br />Let's start with a look at a standard bit of data.<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMpovAdyvI/AAAAAAAAE1g/vQq2CAOq50Q/s1600-h/20090707a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355670161663576818" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMpovAdyvI/AAAAAAAAE1g/vQq2CAOq50Q/s400/20090707a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 231px;" /></a>Column A holds the collective heading - the people in Column B in the first four rows belong to Team A and then Team B personnel are held in rows 5 to 8.<br /><br />We could just repeat the descriptive header by selecting cell A1 and copying it down to the next three rows. But it then starts to become less clear because there is too much confusing text.<br /><br />So instead we'll merge cells A1 to A4 together as a single cell. In Excel a range of cells is written using the syntax A1:A4 - the colon means "<em>through to</em>".<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMqnR32oCI/AAAAAAAAE1o/-45tQRRR_us/s1600-h/20090707b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355671236174585890" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMqnR32oCI/AAAAAAAAE1o/-45tQRRR_us/s400/20090707b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 255px;" /></a>So we need to select the four cells by clicking into A1 and then <a href="http://computerbasicsforfree.blogspot.com/2008/11/click-and-drag.html">click and dragging</a> until the range A1:A4 is selected.<br /><br />Then we select the <em>Merge & Center</em> whilst tutting slightly at the American spelling and hey presto, the four cells become one whilst at the same time the text has been centred in the horizontal plane. The text though has moved to the bottom of the new cell, to the spot previously occupied by cell A4.<br /><br />This is because <em>Merge and Center</em> (tut!) is more often used to merge cells next to each other horizontally - grouping a number of column headings for instance.<br /><br />We can do a couple of things now to format that and they include centring (darn it - the American spelling would look better there...) the text vertically and twizzing it round (Lancashire/technical term for aligning it vertically).<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMtSR8cW8I/AAAAAAAAE1w/FHGYrVxFtls/s1600-h/20090707c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355674173951466434" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMtSR8cW8I/AAAAAAAAE1w/FHGYrVxFtls/s400/20090707c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 313px;" /></a>Select cell A1 (the merged cell still has that designation) and <a href="http://computerbasicsforfree.blogspot.com/2008/11/just-right-click.html">right-click</a> the mouse to bring up the dialogue box shown.<br /><br />Now select <em>Format Cells</em>.<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMuPhrHqOI/AAAAAAAAE14/mOE_SsjrXqs/s1600-h/20090707d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355675226145794274" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMuPhrHqOI/AAAAAAAAE14/mOE_SsjrXqs/s400/20090707d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 287px;" /></a>The resulting dialogue window allows us to format lots of things but for now we are interested in the Alignment tab (pointy hand 1).<br /><br />We can alter the text alignment to centre the text both horizontally and vertically (pointy hand 2) and we can twiz (or is it twizz?) the text (pointy hand 3).<br /><br />Having clicked the <em>Ok</em> button and resized the cell by double-clicking the right cell wall to get the <em>best fit</em> the result is as shown below. <br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMxMEZR48I/AAAAAAAAE2I/BmlQJ8uAp3M/s1600-h/20090707e.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355678465281614786" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMxMEZR48I/AAAAAAAAE2I/BmlQJ8uAp3M/s400/20090707e.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 314px;" /></a>The pointy hand shows the simple way of aligning text left, right or centred in both horiziontal and vertical planes. The <em>AB</em> button with the little arrow to the right of the pointy hand can be used to twiz text - it opens the same dialogue box we have just used.<br /><br />That's one way of doing it. Here's the other - which may appear simpler, but may not meet your needs. It's always good to have an alternative!<br /><br />If whilst typing the names of the team members we used the ALT-Enter key combination we can force cell B1 to accept multiple lines within the single cell.<br /><br />So we type <em>John Tharmy</em> then hold down the <em>ALT</em> key and whilst it's held down, tap the <em>Enter</em> key. Now without moving from the cell type <em>Sally Vaite</em> and use ALT-Enter again until we read the last name. Now tap the <em>Enter</em> alone as you would normally do and the list of names each occupy a line within the single cell.<br /><br /><a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SlMw8Hv8ThI/AAAAAAAAE2A/6QICIljwGT4/s1600-h/20090707f.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355678191304068626" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SlMw8Hv8ThI/AAAAAAAAE2A/6QICIljwGT4/s400/20090707f.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 249px;" /></a>Job done!

Diposting oleh Abdullah Syafii

Excel: Keep Headings in View Whilst Scrolling

A question I've been asked a few times is how to keep headings in view when you scroll down or across a large spreadsheet.<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtWUF4T08I/AAAAAAAAEt8/PGuFYZ3YIAQ/s1600-h/20090619a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5348963885608326082" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtWUF4T08I/AAAAAAAAEt8/PGuFYZ3YIAQ/s400/20090619a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 251px;" /></a>Here I've got a spreadsheet that records the number of views my photos get daily at my <a href="http://www.flickr.com/photos/bispham2/">Flickr account</a>. Every day I look at the stats on my account and in the spreadsheet I enter the number of views per day and the number of photographs I have stored there. From these two pieces of information the spreadsheet can work out the total number of views by adding the total from the previous day to the daily number of views and the average views per photo by dividing the total views by the number of photos.<br /><br />However over the course of a year or two there are so many entries (days) that the column headings would normally disappear off the top of the screen when you scroll down.<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SjtXlevVcXI/AAAAAAAAEuE/znGhpeXcGIg/s1600-h/20090619b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5348965283850973554" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SjtXlevVcXI/AAAAAAAAEuE/znGhpeXcGIg/s400/20090619b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 251px;" /></a>To stop this happening I've used the <em>Freeze Panes</em> function of Excel to stop the top two rows from scrolling. Those two rows contain the headings and remain on screen whilst the rest of the rows scroll.<br /><br />To start, select the cell underneath and to the right of the rows and columns you want to keep in view. In my example I only want to freeze the top two rows, not any columns at the left, so the cell I've selected is A3.<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtYaVyCBrI/AAAAAAAAEuM/V3x7Q0iok2c/s1600-h/20090619c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5348966191979431602" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtYaVyCBrI/AAAAAAAAEuM/V3x7Q0iok2c/s400/20090619c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 251px;" /></a>Then I select the <em>View</em> tab from the menus at the top.<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtYruk1jII/AAAAAAAAEuU/5PmDXltGF6A/s1600-h/20090619d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5348966490692750466" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SjtYruk1jII/AAAAAAAAEuU/5PmDXltGF6A/s400/20090619d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 251px;" /></a>Finally I select one of the options from the <em>Freeze Panes</em> icon.<br /><br />Note that once you have frozen some headings, the options change to include an <em>Unfreeze</em> option.<br /><br />Now I can scroll down as far as I need to and can still see the column headings.

Diposting oleh Abdullah Syafii