Sometimes it’s handy to quickly resize columns or rows in Microsoft Excel. This is simply done by mousing over the right hand edge of the column (or the bottom edge of the row) that you wish to resize, until your cursor becomes a black line with two arrows pointing away from the line at opposite 90 degree angles. With this cursor, you can then left-click and drag, and the column or row will resize accordingly.


However, you can be even more efficient: you can resize several columns or rows at once. Highlight the relevant columns or rows, then go to the right edge of the right-most highlighted column (or the bottom edge of the bottom-most highlighted row) and this will allow you to resize all the highlighted columns or rows simultaneously – simply left-click and drag as before and all the highlighted columns or rows will resize to be the same once you release the left mouse button. (Note that it is not necessary to select columns or rows if you are just resizing a single one at a time, only when you are resizing multiple ones simultaneously.)


Also, you can auto-resize columns or rows. If you want to resize more than one column or row, then you must select them all first. Again, mouse over the right hand edge of the column (or the bottom edge of the row) that you wish to resize until you get the cross cursor with arrows as above . But instead of left-clicking and dragging, double left-click. The column(s) or rows(s) that are highlighted will have resized to be as narrow as possible without obscuring any information – see images below.



The hugely-useful Format Painter tool can be found in Microsoft Excel, Microsoft Word and Microsoft Outlook, and functions more or less the same in all these applications.


Say you’ve assembled a spreadsheet, document or eMail by copying and pasting from a number of sources, and so you’re left with a mishmash of information in different font types, font sizes and, perhaps, different colours.



To use Format Painter:

1. Simply left-click and drag to select the text (or if in Excel, the cell) which contains the type of formatting you wish to apply to something else.

2. Then, with this still selected, left-click the Format Painter icon (in Microsoft Office 2007 this is found by default under the left-most tab, called ‘Home’ in Microsoft Excel and Microsfot Word and ‘Message’ in Microsoft Outlook).

3. Then select, again by left-click/dragging, the text/cells you want to amend.

4. The Format Painter will take the formatting of the originally-selected text/cell and apply it to the second selection.


Quick, easy, efficient, and it gets the job done with a minimum of time, thought and effort…



Following on from our previous post on Pasting from the Web to Microsoft Office, here’s another useful piece of funcitonality, this time only available in Microsoft Excel, which actually allows for ‘live’ communication from the Web to Microsoft Excel.


Microsoft Excel’s little-known but often useful ability of being able to pull in ‘live’ data from websites is perfect for setting up your own price comparisons on a certain product, for example. And it’s simple to do: firstly navigate to the website you want to pull data in from on your browser. Then select the information you want to pull into Excel, Copy it, go to an empty area of an Excel worksheet, and Paste (not Paste Special).


A small Clipboard icon will appear when you Paste. From its dropdown menu, simply select ‘Create Refreshable Web Query‘. You’ll then be shown Microsoft Excel’s ‘Create Web Query‘ box and asked to select the ‘tables’ (i.e. areas of the website you’re pulling data from) that you wish to include in the data being pulled into Microsoft Excel. Tick the relevant ones, and click OK, and they’ll come through into your Microsoft Excel worksheet.

The system isn’t perfect: it doesn’t allow you to fine-tune exactly which information to being into Microsoft Excel – you are only able to select one or more ‘tables’ of data. However, you could pull in the Web data to a dedicated worksheet which exists behind the scenes just as a ‘holding area’ for Web Queries, then use formulas to identify the relevant parts, which then could come through (in a more tidy and fine-tuned fashion) to the worksheets where you’ll actually view and interact with the information.



There are many reasons you might want to Copy-and-Paste from a website into one of the Microsoft Office applications. Perhaps you want to calculate if you can afford several items and want to get their prices directly from the website selling them (Microsoft Excel) or perhaps you want to ‘borrow’ (!) some Wikipedia wisdom for an essay (Microsoft Word) or quote something on a site on an eMail (Microsoft Outlook).


The problem is that, by default, the original formatting from the website comes along with the text content, making the resulting Pasted text look completely different than the surrounding text.


The way around this in Microsoft Office 2007 is to Paste as normal, but then to pay attention to the little Clipboard icon that appears after you’ve Pasted. Left click on this and you’ll be presented with some options:




Match Destination Formatting is the option you want: your Pasted text will assume the formatting of the document, eMail, presentation or spreadsheet that it’s just been pasted into.


If you find that you need to Copy-and-Paste between differently-formatted items often, you can select the bottom option that the Clipboard icon presents you with: Set Default Paste. This will bring up the following box:




This box enables you to set the default behaviour of pasting, removing the need to constantly select Match Destination Formatting. By default the options will be set to Keep Source Formatting – simply use the drop-down boxes to select Match Destination Formatting.


And voilà – hassle-free Copy-and-Pasting from the World Wide Web into your document, eMail, presentation or spreadsheet!


This technique works for Pasting anything, of course – not just text from the Web.



Sometimes you might have a number of formulas on a Microsoft Excel spreadsheet, but might want to remove them and replace them with the values that they calculate (perhaps you’re sending the spreadsheet to someone and don’t want them to accidentally alter or delete a formula, and instead simply want them to see what the actual results of the formula are).


There’s no one menu option to do this, but it’s still simple: select the range of cells that contain the formulas you want to change into simple values, then Copy them (Edit->Copy in Excel 2003, Home->Copy in Excel 2007) and then, without changing the selection of cells at all, Paste Special (Edit->Paste Special in Excel 2003, Home->Paste->Paste Special in Excel 2007).


In the resulting box, choose Paste->Values. The actual results of the formulas will replace the formulas themselves (so for example, a cell containing the formula “=5*3” will now contain the value “15”, which is the result of the formula).


Be careful and think about whether you really want to remove formulas from a spreadsheet – it can be useful in reducing the size of a Microsoft Excel file and can prevent disasters caused by people changing formulas, but only do it if the formulas have done their job and arrived at the calculations you want.