Thursday, March 22nd, 2007

Pictures inside Excel comments

In a previous tip of the day, I showed you how to create shaped comments in Excel but today I’m going to go one step further and create pictures inside the comment.

As you might expect, start off in Excel and add a comment to a cell. Right-click the cell and choose Show comment and then click the border of the comment to select it. Choose Format, Comment and, from the Colors & Lines tab’s Color dropdown list choose Fill Effects and then the Picture tab and click Select Picture.

Find a picture to add to your comment from those in your My Pictures folder, enable the Lock Picture Aspect Ratio checkbox and click OK twice. You’ll now have the image inside your comment.

Depending on the image that you have used you may want to change the format of the text, for example coloring it a different color and sizing it large enough so that it can be easily seen.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 6:27 pmNo Comments links to this post

Tuesday, March 20th, 2007

Naming Ranges in Excel

When you’re working with different areas on an Excel worksheet it sometimes helps to name the area or range as Excel calls it.

You might do this so that you can easily select a print area from a number of different printing areas on the worksheet or where you want to move very quickly to a named area which is in an out of the way place on the worksheet.

To name a range, select the cell or range of cells to name and choose Insert, Name, Define and give the cell or range a name. You can use whatever name you like, it just must be a single word name with no spaces and it can’t start with a number. When you’re done, click OK.

Now look up to the top left corner of the screen to the left of the formula bar you will see a small Name dropdown list. You can dropdown the list and select the named cell from the list and you will automatically go to it and, if it is a range, it will be automatically selected ready, for example, for printing.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: ,

Categories:Uncategorized

posted by Helen Bradley @ 5:40 pm4 Comments links to this post

Monday, March 19th, 2007

Shrink to size Excel Printouts

When you’re working on a very big worksheet in Excel sometimes it happens that when you print it out you get a stray row or column on a single sheet of paper. It’s not only a nuisance but it’s also a waste of paper.

To ensure that this doesn’t happen choose File, Page Setup and click the Page tab. The Fit To option lets you specify the number of pages wide or tall that a worksheet is. Select the measurements that you want to fit, for example fitting to one page wide by one page tall will print everything on a single page.

What if you don’t know both measurements? Don’t worry, if you know you want it one page wide but don’t know how tall that will be, select Fit to 1 Page Wide and leave the Tall box empty – that means delete anything that’s in it.

This works the same if you know how tall you want it to be but not how wide. It’s a smart way of saving trees and frustration and it looks much neater than handing someone something with a skerrick of info on the last page.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 5:20 pm1 Comment links to this post

Saturday, March 17th, 2007

Any shape Excel Comments

I like to use comments in my Excel worksheets but sometimes the square yellow boxes just don’t do it for me.

Luckily with a little bit of imagination and skill, I can change the shape of my comment boxes.

Here’s how to do it yourself:

Add a comment to your worksheet and click the cell that contains the comment, right-click and choose Edit Comment and then click on the border of the comment so you have it selected.

Now, from the Drawing toolbar click the Draw button and choose the Change AutoShape option in the menu. You can select from all sorts of shapes including stars and banners and flowchart symbols.

When you’re done just click outside the comment and the change will be created for you. Neat stuff? I think so.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 4:36 pmNo Comments links to this post

Sunday, March 11th, 2007

Keeping watch in Excel

Big worksheets are a bit of a nuisance to work with. I typically find myself working in one area but wanting to see what is happening in another.

To do this, I set up a watch value that tracks the figure I’m most interested in and which is off the screen. To do this, right click the cell you’re interested in seeing and choose Watch to set the cell as one to watch. Now, regardless of where you are in your worksheet, you can see the value in the ‘watched’ cell in the Watch window – double clicking its title bar anchors it on the screen.

In earlier versions of Excel you could create your own watch window using a text box. Draw it using the Text Box button on the Drawing toolbar, click inside it, press F2 and type the cell to watch, eg =Z100 – simple and very effective.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: ,

Categories:Uncategorized

posted by Helen Bradley @ 4:45 pmNo Comments links to this post

Friday, February 23rd, 2007

Excel – trapping invalid data

There’s a term that I love called GIGO – garbage in garbage out. When you’re designing an Excle worksheet to solve a business problem you need to make sure that your data is correct or your worksheets won’t give valid results.

Luckily, Excel’s Data Validation tool helps you prevent incorrect data being added into your worksheet.

To set up a validation rule and to configure what will happen if incorrect data is entered, start by selecting the cells to which the rule should apply. Choose Data, Validation, Settings tab. Now, for example, to limit the data being entered to a whole number less than 200, select whole number from the Allow drop down list. When the Data area appears, choose Less Than and then in the Maximum area type 200.

To configure a user message to tell the user what is required, click the Input Message tab and type a title and a piece of explanatory text telling the user the limits to the type of Data you’re expecting them to enter. This appears as a tooltip entry when any of the cells configured with the data validation rule is selected.

You can also specify what should happen if a user enters an incorrect value. To do this, click the Error Alert tab and select the type of indicator from the Style list. If the user enters incorrect data and if you have chosen the Stop style they will have two choices – Retry and Cancel and the invalid data cannot be entered into the cell. The Warning and Information styles both warn about the incorrect data but still allow it to be entered.

Once you’ve chosen the style, enter the Title and the Error Message which the user will see if the data in not correct. Click Ok and you can now test the rule.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , , ,

Categories:Uncategorized

posted by Helen Bradley @ 8:16 pmNo Comments links to this post

Thursday, February 22nd, 2007

Excel – finding duplicates

Excel’s conditional formatting option has lots of handy uses including highlighting cells that contain duplicate data.

To do this, highlight the range to investigate, for example, a series of entries in column C starting with cell C1. Now choose Format, Conditional Formatting, select Formula Is and enter this in the formula area:

=COUNTIF($C:$C,$C1)>1

Click Format and then select a pattern and color to use and click Ok.

Now duplicate entries in the column will be indicated with the shading you’ve provided. You can copy and paste this formatting down to other cells in column C to include them.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 8:14 pmNo Comments links to this post

Friday, February 16th, 2007

Excel 2003 lists

Excel 2003 offered a cool new tool for managing data that was in a list format. It made Excel the place of choice for small lists and it simplified the process of charting list data – Excel lists expanded automatically to allow for more data to be entered and charts based on the list data automatically expanded to include the new data – wonderful!

Here’s how to work with lists in Excel:

  1. Turn an existing table of data into a list by clicking on a cell in the range and choose Data, List, Create List. If your list has a heading row, enable the My list has headers checkbox and click Ok. Notice the border around the list.

  2. To add a row, click in the list area and click in the last (Insert) row which has an asterisk in its first cell. It is also possible to add a row in the middle of the list by clicking where the row should appear and choose List, Insert, Row.

  3. When data is created as a list, the AutoFilter feature is enabled. To sort data in the list, click the dropdown arrow to the right of the column (field) to sort on and choose Sort Ascending or Sort Descending as required. To sort on multiple columns, use the Data, Sort dialog.

  4. To create a complex filter for your list, click the Custom option from the dropdown list for the field that you want to create the query on. Set the tests to use and select And or Or depending on what information you need to extract. Click Ok to view the results. To display all records again, choose Data, Filter, Show All.

  5. When you create a chart based on list data it will be automatically updated when you add a new item to the list. To create your chart, click in the list and click the Chart Wizard button on the new List toolbar and proceed through the Wizard as you would for any other chart.

  6. To perform calculations on list data use the Toggle Total Row button. This adds a total row to the list and totals the right most column. To disable this total or create another one, click the down pointing arrow to the right of the total and choose None or a different calculation. Each column has its own down pointing arrow from which you can select the calculation to be made on that column’s data.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 7:22 pmNo Comments links to this post

Page 5 of 512345