Thursday, February 8th, 2007
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!
Helen Bradley
Labels: 16 million, color, Excel 2007, Excel tip, theme
Categories:Uncategorized
posted by Helen Bradley @ 5:38 pmNo Comments links to this post
Wednesday, February 7th, 2007
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.
Helen Bradley
Labels: date functions, Datedif, elapsed time, Excel tip
Categories:Uncategorized
posted by Helen Bradley @ 9:32 pmNo Comments links to this post
Tuesday, February 6th, 2007
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.
Helen Bradley
Labels: color, Excel tip, options, tools, worksheet
Categories:Uncategorized
posted by Helen Bradley @ 9:28 pm3 Comments links to this post
Monday, February 5th, 2007
I write for UK magazines so I often have to use pounds symbols and my mum’s and brother’s surnames have an ‘e’ with an accute accent – unfortunately neither is on my keyboard. So, to make things easier to write, I use a macro to convert a word to a typed character.
Here’s a sample you can use and adapt. It converts the words cents, pounds, degrees and division to their character equivalents and you can use it to replace almost any word or character with another single character, word or phrase of your choosing:
Sub ConvertText()
FindAndReplace “cents”, “¢”
FindAndReplace “pounds”, “£”
FindAndReplace “degrees”, “º”
FindAndReplace “division”, “÷”
End Sub
Sub FindAndReplace(FindThisWord, ReplaceWithWord)
Set EditRange = ActiveDocument.Content
EditRange.Find.ClearFormatting
EditRange.Find.Execute FindText:=FindThisWord, ReplaceWith:=ReplaceWithWord, MatchCase:=0, Replace:=wdReplaceAll
End Sub
Helen Bradley
Labels: Macro, replace character, special symbols., Word
Categories:Uncategorized
posted by Helen Bradley @ 9:25 pmNo Comments links to this post
Sunday, February 4th, 2007
I think we’ve all had that sinking “Oh no!” feeling when we’ve sent an Outlook email. Either you’ve forgotten to include an attachment or you’ve thought twice about what you said and you want to recall the email. In most cases an email can’t be recalled, but you can delay it being sent.
To add a delay to outgoing emails, use a rule:
- Choose Tools, Rules and Alerts and click on New Rule.
- Choose the Start from blank rule option and then select Check messages after sending and click Next.
- Click Next as all messages will be delayed (but you can set an exception shortly) and click Next and Ok again to confirm the rule will apply to all sent messages.
- Choose Defer delivery by a number of minutes and set this to the number to wait – say 2 minutes and click Next.
- Now, set an exception for messages that just have to be sent immediately, for example, select Except if it is marked as importance and set this to High.
- Click Next, type a name for the rule such as Delayed Send, enable the Turn on this rule checkbox and click Finish.
Now, when you click Send, your messages will be held for 2 minutes before being sent except if you make them High importance.
Helen Bradley
Labels: delayed delivery, message, Outlook, rule, sending email
Categories:Uncategorized
posted by Helen Bradley @ 9:08 pmNo Comments links to this post
Saturday, February 3rd, 2007
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:
- Type the list of items to use in a single column in a spare sheet in the workbook.
- Select these cells and choose Insert, Name, Define and type DataForList and click Ok.
- 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.
Helen Bradley
Labels: cell entry, define, Excel tip, insert, list, name, validation
Categories:Uncategorized
posted by Helen Bradley @ 9:02 pm1 Comment links to this post
Saturday, February 3rd, 2007
While it looks like Word has no tool for sorting a list – only for sorting data in a table, this is not the case and the Table sort tool works just fine for sorting data in a list.
To sort a list, such as a list of names, where each name appears on a new line and the last name is separated from the first by a comma and a space, choose Table, Sort. From the Sort by list, choose Field 1 and then, from the Then by list choose Field 2 and click Ok. This sorts the data into order by last name and then by first name where there are two or more people with the same last name.
If the names appear with the first name then a space and then last name you’ll have to tell Word to differentiate between the two names. To do this, select your list and choose Table, Sort, Options and click the Other option and press the Space bar and click Ok. From the Sort by list, choose Word 2 and then, from the Then by list choose Word 1 and click Ok. This sorts the data into order by last name and then by first name where there are two or more people with the same last name.
If you find that Word 2 doesn’t appear as an option in the Sort by list, click Ok to exit the Table Sort dialog and select the option again.
Helen Bradley
Labels: field, list, sort, Table Sort, Word
Categories:Uncategorized
posted by Helen Bradley @ 12:09 amNo Comments links to this post
Friday, February 2nd, 2007
When you need to save a copy of an Outlook message simply hold the Control key as you drag it out of your Inbox and drop it into a folder in an open Windows Explorer window or onto the desktop.
The message is saved as a .msg format file that you can open again at any time by double clicking it in Windows Explorer. When you do this, the email itself opens and can be read, even if Outlook is not open.
To save a message as an .html file, open it and choose File, Save As and type a name for it and choose a location for it.
Helen Bradley
Labels: .html, .msg format, message, Outlook, save
Categories:Uncategorized
posted by Helen Bradley @ 6:07 amNo Comments links to this post
Thursday, February 1st, 2007
When you want to make sure everything on a Word page lines up properly, display the gridlines.
To do this, first display the Drawing toolbar then choose Draw, Grid and choose the Display gridlines on screen checkbox. Set the Use Margins option to start the grid aligned on the left and top margins. Set the value to 1 for small squares and to 2 or 3 for a larger grid.
If you select the Snap Objects to Grid checkbox all objects will automatically line up against the grid.
If you don’t want this to be the case, press the Alt key as you drag an object and it will be freed from snapping to align to the grid.
Helen Bradley
Labels: Drawing, grid, gridlines, snap, Word
Categories:Uncategorized
posted by Helen Bradley @ 6:05 amNo Comments links to this post
Wednesday, January 31st, 2007
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.
Helen Bradley
Labels: Excel tip, Picture, range, Word, workbook
Categories:Uncategorized
posted by Helen Bradley @ 6:01 amNo Comments links to this post
