Excel Pasting column widths

Copy and paste a range in Excel and you get everything except the column widths. They get left behind and sometimes that's a big nuisance. When you need to paste column widths too, paste your cells and don't move. Choose Edit, Paste Special and click Column widths and the column widths of the new cells adjust to match the source.

Easy when you know how...

Quick Question - what is 2 + 3 x 4?

Answer: 14

Surprised? Did you think it was 20? It's not. Try it in Excel if you need proof. Type =2+3*4 and the answer is 14.

Excel calculates according to an order of precedence which isn't necessarily left to right. In this calculation it performs multiplication first and then the addition, hence the answer: 3 times 4 is 12 add 2 is 14.

To learn more about it, look up Order of Precedence in Excel Help.

In the meantime, the short explanation is that it performs things in brackets first, then percents, exponents (as in squared and cubed), multiplication and division, then addition and subtraction. If you have two of the same such as an addition and a subtraction they're done left to right. So, to force the calculation to perform your way, put things in brackets when you want them done first.

In our case, to get an answer of 20, write the formula =(2+3)*4

Display an Excel Workbook's path

Sometimes it's handy to show the path of an Excel workbook on the screen. Here's a workaround using a toolbar to do this.

1. Right click on the menu bar and choose Customize and then the Commands tab.
   
2. From the Categories list choose Web and, from the Commands list drag the Address option onto a toolbar or into the menu bar.
   
3. Click Close and voila! the file's full path will appear in the box.
   

Excel 2007 goes total color!

It's always been confusing to me and my readers why you have an unlimited number of colors to choose from in Word but only a handful of really ugly colors to use in Excel. It doesn't make sense - do Word users have better design skills than Excel uses - do they think that those of us who create worksheets somehow won't be blindsided by how ugly the color combinations we're being served up are?

Well, if you've been hankering after a rich color palette for your worksheets, you need do so no more. Excel 2007 gives you access to a full range of 16 million colors from the colors dialog so that you can use these colors, for example, for formatting text, charts or drawn objects. You can also select a Theme which gives you instant access to colors that are selected from the same monochromatic group and guaranteed to look great together. The new and very colorful options in Excel 2007 are guaranteed to wow you!

Calculating elapsed time in Excel

It's a common enough scenario - you want to know how old you are in months or days or the number of days left in the year. There appears to be no Excel function for calculating the difference between two dates. Not so! there is a function and it's called Datedif.

The Excel Datedif function isn’t well known because it is largely undocumented in the Excel help files. Its purpose is to calculate the difference between two dates and it can do this by measure the distance using one of these attributes: "m" for months, "d" for days, "y" for years.

So this formula calculates the difference in months between FirstDate and SecondDate:

=Datedif(FirstDate,SecondDate,"m")

Replace "m" with "y" to get the result in years etc..

Always ensure that the first date occurs earlier than the second or you'll get an error.

More colours in Excel 2003 and earlier

I don't know why, but there are heaps of colors available in Word for formatting text and lines but only a small subset of colors are available in Excel. Does Microsoft think we don't like color in Excel?

Unfortunately we can’t specify additional colours for use in Excel workbooks either but we can change the colours that are displayed in the color palette to a mix of what we do like. So, if pink isn't your thing, you can replace all the pinks with colors you do like and will use.

To do this, in Excel, choose Tools, Options, Color tab and select a color to change. Click Modify to choose a new color in its place. Repeat as desired.

Automatic Excel cell entries

We all love to save time and here's a great tip to make repetitive cell entries in Excel just so much easier to complete.

You do this by making a drop-down list in a cell so you can select your entry from it rather than having to type it fresh each time.

To do this:

1. Type the list of items to use in a single column in a spare sheet in the workbook.
   
2. Select these cells and choose Insert, Name, Define and type DataForList and click Ok.
   
3. Move to the sheet where the data goes, select the cells for the drop-down list and choose Data, Validation, Settings tab. From the Allow list choose List and, in the Source area, type =DataForList and click Ok.

Now, whenever you click a cell in this range you’ll see a list box indicator appear and you can choose the cell entry from the list.

Snap an Excel range

Take a picture of a range in Excel to insert into Word as a picture or to place as an image in another area on a workbook. To do this, first select the area to snap and hold Shift as you open the Edit menu. Choose Copy Picture, select As shown on screen or As shown when printed and click Ok.

You can now paste the image wherever you desire. This Shift + Edit menu option also works for copying a clip art or other type of image inserted into an Excel workbook.

Random numbers in Excel

When you’re testing a workbook it is handy to be able to fill a set of cells with a sample value. The Randbetween function included in the Analysis Tookpak does this for you.

To enable this, choose Tools, Add-Ins and enable the checkbox for the Analysis Toolpak. Now write the function, for example, this provides a random number between 1 and 100 (including both numbers):

=randbetween(1,100)

Copy it to all the cells to fill. To fix the values so they don’t change each time something in the worksheet changes, select the cells with the formula, choose Edit, Copy then Edit, Paste Special, Values.

Snap a range
It's possible to take a picture of a range in Excel to insert into Word as a picture or to place as an image in another area on a workbook. To do this, first select the area to snap and hold Shift as you open the Edit menu. Choose Copy Picture, select As shown on screen or As shown when printed and click Ok.

You can now paste the image wherever you desire. This Shift + Edit menu option also works for copying a clip art or other type of image inserted into an Excel workbook.

Use this technique to print data from two Excel sheets on the one sheet of paper, something that's notoriously difficult to do otherwise - regardless of the fact that it's a feature we'd all love to have!