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…
Labels: column widths., Excel tip
Categories:Uncategorized
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
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
Labels: Excel tip, Order of Precedence
Categories:Uncategorized
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!
Labels: 16 million, color, Excel 2007, Excel tip, theme
Categories:Uncategorized
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.
Labels: date functions, Datedif, elapsed time, Excel tip
Categories:Uncategorized
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.
Labels: Excel tip, Picture, range, Word, workbook
Categories:Uncategorized
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.
Labels: analysis toolpak, Excel tip, Randbetween, random numbers
Categories:Uncategorized
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!
Labels: camera, Excel tip, snap
Categories:Uncategorized
