Wrapping Text

For multiple line entries of labels, comments, notes, or descriptions in Microsoft Excel, select the cells (or an entire row or column), choose Format > Cells, pick the Alignment Tab, and check the option for Wrap Text, finish with OK. The column width will determine how many lines are needed to display the text. Choose this over multiple rows for lengthy text entries for greater readability and for any lists of data.

Once text is formatted to enable wrapping, you can further control the layout by pressing [Alt] + [Enter] to force a line break within the same cell.

No Formula, Please

Formulas entered in Excel are relative to the cells they are entered in.

Now you want copy the “result” of a formula to another cell.  So you go ahead and copy the cell where the result is and “paste” it it into the new cell.

Whoops! What hapened? The reult is not the same as what you wanted to copy….formulae are relative and the new cell will refer to different cells… damn frustrating!

Solution: When you want to paste the result of a formula into a new cell choose “Paste Special” from the Edit menu and choose Values from the dropdown menu… Voila… your reult is in the new cell without the formula.

If you’re frustrated with you Paper Work and need an experienced Paper Work expert to help you Call Peter

PAS Administrative Support Services

Peter 613-347-9893

pas-admin@sympatico.ca

Entering Phone #’s – What a Pain!

These days to make a call you have to dial the 10 digit number…which means dialing the area code as well as the local phone #.

Formating the # for each entry is a PAIN! For example to enter my phone number you would have to type “(613) 347-9893″. Thats a total of 14 keystrokes. Save yourself some time and format your phone # column as a phone #.

It’s Simple…

Select the whole column

Choose Format from the top menu bar

Select Cells and from the Drop-Down menu choose Special and select Phone Number

Now when you enter 6133479893 your entry will automatically be formatted to read (613) 347-9893… it saves a lot of time – believe me!

Hey… Wait a Minute…I’ve got an even better tip for inputting phone #’s and other information!

Call and I can Work with You!

Smart Data Entry

Here’s the scenario:

 

You are maintaining a customer list in Excel. Your column names and widths are as follows:

 

Note: To set column widths Select Format from the top menu then choose Column and select Width. Enter the names and widths shown below.

 

Last Name            = Lname                   Column width = 15

First Name           = Fname                    Column width = 10

Address 1             = Add 1                      Column width = 20

Address 2            = Add 2                      Column width = 15

City                      =City                           Column width =  15

Province            = Prov                          Column width =  4

Postal Code       = PC                             Column width =  7

Phone               = Phone                        Column width =  12

Cell                  = Cell                              Column width =  12

Email              = Email                           Column width =  25

Notes              = Notes                          Column width =  25

 

This may seem to be a tedious exercise, but what you learn here can be applied to any Excel list you want to design.

 

Now you’re ready to start entering your info. 

The total width of your columns is 160 spaces.  With a spreadsheet that wide, entering data is going to be cumbersome. Moving from cell to cell you lose site of which record you’re working on.

Let’s work SMART!

Place your cursor in the first cell under the Lname column.  Select Data from the top menu line. Choose Form and WOW!  There’s your whole data entry form with all kinds of options to move around your “database”.  Too Cool – Too Smart!

Excel Can Find It!

Geez! I have a big file of customers with all their information including phone #’s & addresses .  I want to talk to Jim Smith but I can’t find him in my Excel spreadsheet. I also know that I have a lot of “Jim”s in my spreadsheet.  So let’s find the one I want. Go to cell A1 to make sure we start at the beginning of your data.

Go to the Edit menu and choose “Find”. Choose the “Find” tab in the next screen presented.

Enter Jim  in the “Find What:” Leave the defaults and check boxes as is (play with them later) and click “Find Next” at the bottom of the Find window… Voila! there’s the first “Jim″.   Maybe he’s not the Jim you’re looking for… click “Find Next” and there’s your next Jim!

Remember if you enter anything in the “Find What” field Excel will find it within any cell… it will find “Jim” in a cell containing “Jim anyone”.

My advice: when doing a Find  in any spreadsheet allways start in cell A1… it will give you the peace of mind knowing that Excel has looked everywhere.
Visit pasadmin.wordpress.com often… we add tips to help you Work Smart – Not Hard  every week!

If you’re frustrated with you Paper Work and need an experienced Paper Work expert to help you Call Peter

PAS Administrative Support Services

Peter 613-347-9893

pas-admin@sympatico.ca

The Power of “Find”

The Power of the “Find” function in Word’s Edit menu will lower your frustration level.  Place your cursor (Insertion Point) at the very top of your document… now let’s get started.

Scenario: You have a form letter that you have addressed to a customer and you want to call the customer but you’re not sure about his phone #.  You do know that his # is somewhere in your doc.  You also know he is in the “613″ area code.

Let’s find the number! Go to the Edit menu and choose “Find”.

Choose the “Find” tab at that screen.

Enter 613 in the “Find What:” field and click “Find Next” at the bottom of the Find window… Voila! there’s the first “613″. I think you’ve found your customer’s phone #.  Does he have a cell phone in the same area code that may be in your document… click “Find Next” and there it is!

Visit pasadmin.wordpress.com often… we add tips to help you Work Smart – Not Hard  every week!

If you’re frustrated with you Paper Work and need an experienced Paper Work expert to help you Call Peter

PAS Administrative Support Services

Peter 613-347-9893

pas-admin@sympatico.ca

Doesn’t Look Like a Fraction!

Go to a blank cell in a worksheet. Enter the fraction 3/4. Hey! Wait a minute! I didn’t say March 4^th …. I entered three quarters. Simple… I’ll just format the cell as a number or fraction (Format Menu/Cells) the result is 39145. Where did my fraction go?

Here’s the answer. Excel automatically interprets your cell as a “date” entry. 39145 is the serial number for March 4, 2007. This example was done in 2007… another year, a different serial number…we’ll talk about date functions in another tip.

What about displaying my fraction? Here’s the Trick! Go to a blank cell. Enter 0 space 3/4 (read as zero space 3 forward slash 4). Voila! Your cell now shows your fraction and it has that fraction’s mathematical value. Multiply that cell by 4 and the answer will be 3 and not 156580 (39145 x 4).

The Zero Dilema

Often you want enter a number that begins with a Zero (Invoices, Zip codes). How frustrating it can be when you want the preceding zero(s) to appear in the cell.  In an Excel cell enter 0012345 and press enter.  OOPS! No Zeros! – only 12345 appears.  Go back to the cell and check the edit bar above – no zeros there either – like you never entered them.

STOP the Frustration! Precede 0012345 with an apostrophe usually located to the immediate left of the Enter key on most keyboards.

Now try it again as follows: ‘0012345

Problem solved!

Call Peter at PAS Administrative Support Services

We can Solve your Office Headaches

613-347-9893          pas-admin@sympatico.ca

Separate Pages When Printing – Page Breaks

You can spend a lot of time inserting and deleting rows in your worksheet so that when you print it your information stops and restarts on the next page where you want it to. This can be very frustrating and cost you a lot of ink and paper when testing each adjustment.

Help is on the way! Simply design your worksheet without concern about printing it. When you’re done, now you can define how it will look on paper.

Now, go to one row below where you would like the printer to restart on a new page and do the following:

1. Go to the Insert drop down on the main menu at the top of the page
2. Choose Rows and press Enter… a blank row will be inserted into your worksheet
3. Make sure the first cell in the new row is the active cell and return to Insert drop down on the main menu at the top of the page
4. Choose “Insert Page Break” … you’ll see a dotted lin appear in that row… This is cool because when you print your worksheet a new page will start at this “break”.

Now you have “Design Power”. Use Print Preview from the File menu…oops! don’t like what you see? Let’s change it.

1. Go to the row where you inserted a Page Break
2. Go to the Insert drop down on the main menu at the top of the page
3. Choose “Remove Page Break”
4. The page break is gone and you can repeat the steps above to define a new row where you would like a new Page Break to be inserted.

Any problem with this procedure?

Call Peter at PAS Administration Support Services…

We Can Help!

613-347-9893

Page Borders in Word

Page Borders often present a problem that is easily solved, but is frustrating until you know about one small adjustment. The problem is that when you print your masterpiece, the page you have worked on so carefully, the bottom of the border does not print.

The border looks fine, it is exactly as you wanted it to look. However, when you go to Print Preview, you can see that the bottom border will be missing if you print the document.

To solve that, you can make one change from the default setting.  Follow these steps:

1.      Go to the Format menu and select Borders and Shading.

2.      In the Borders and Shading window, select Page Border.

3.      At the bottom right portion of this window there is a button labeled Options.

4.      Click on the Options button. In the

5.      Borders and Shading Options window you should see a Measure from box which, by default, is set to Edge of page.

6.      Click on the down pointing arrow and select Text.

7.      Click on OK to get back to the Borders and Shading window where you will click OK again.

Check it Out… All should be well now!

 

Any problems … Call PAS Administration Support Services… we can Help!

Does It Read Smoothly?

Sometimes you want to move a paragraph or two around in your document. For instance, you might want to move a paragraph from its current location to before the preceding paragraph. You can quickly move paragraphs by following these steps:

1. Position the insertion point in the paragraph you want to move.
2. While holding down the Shift and Alt keys, press the Up Arrow or Down Arrow to move the paragraph.

This will help you shape (edit) your document – “No Pain – No Strain”  Once you get all your ideas on paper (Word) you become the editor to define the flow of how it will be read.

Call Peter for Help!

Getting around Your Document

I was working on a document with a student one day when we noticed a change needed to be made at the beginning of the line.  I mentioned it and the student immediately held down the left arrow key to get to the error and fix it. 

I became impatient… using the arrow key takes forever to get where you want to be. It can be a painfully slow process to move through a document a single character at a time.

Depending on your system settings, to speed things up considerably, just hold down the CTRL key and press either the left or right arrow keys. This causes the insertion point to jump from word to word, rather than character to character. The increase in speed is dramatic.

More keyboard Tricks & Tips coming Soon!

Word Formatting Tip

Suppose your writing a multi-paragraph document but you would like to separate each with a formatted line.

Try this … it will give your document a more professional look.

Enter some text in a new Word document… don’t waste time typing… just copy any junk from an existing document.

At the end of the text, press Enter twice:

At the insertion point of the new line try the following:

• Type three dashes and press Enter; you get a single line
• Type thee underlines and press Enter; you get a bold single line.
• Type three equal signs and press Enter; you get a double line.
• Type three asterisks and press Enter; you get a heavy dotted line.
• Type three tildes (~) and press Enter; you get a wavy single line.
• Type three pound signs (#) and press Enter; you get a heavy line bordered by two thin lines.

You find your text followed by a blank line and then by the line separator of your choice. Hit enter twice and start your next text paragraph and repeat the steps above.

You’ll find your document will have an organized look without unrelated paragraphs running into each other.

What Day of the Week Is It?

When Did I do This?

What if you’re using Excel to track the hours you are working for a customer. Naturally, you will use Excel’s date formatting in column A , # of hours worked column, a rate/hour column, a formula to calculate that day’s billing amount and a total billing to date column.

Hey! Wait a Minute… What Day of the Week Did I Do That? My electricity bill doesn’t tell which Day of the Week I spent my Money.

Tell your customer precisely when you were there for them… tell them the Day of the Week you did the work for them… it will help trigger their memory and will result in less invoicing contraversaries.

Impress your customer by inserting an additional column after the Date column… call it “Day”. The formula in column B2 will be =TEXT(A2,”DDDD”).

You and your Customer will love your Record Keeping .

What Is A WorkBook ? – What is a WorkSheet ?

What Is A WorkBook ? – What is a WorkSheet ?

The terms WorkBook and WorkSheet mystify new users of Excel. Let’s have a look through “The WayBack Machine”.

I just bought a beautiful bookkeeper’s ledger book. Each page has 14 columns. On each page of the bookkeeper’s ledger book I keep information about a different company – a separate Income or Expense account for each column… very tedious to do every month… for each company.

Now Let’s Have Some EXCEL Fun!

Open Excel and you will see a blank WorkBook.

The top left of the screen shows you the Book Number you are working with…This will change after you save this WorkBook under a new name. More about saving your WorkBook in another Tip.

At the left bottom of the screen you will see some directional arrows and then the indicators for Sheet1, Sheet2 and Sheet3. These sheets are the separate pages of your 14 column bookkeeper’s ledger book.

The Power of Excel will allow you to rename each Sheet, Insert many more Sheets and re-order them at will.

Of course The Power of Excel allows you to have many more columns than a 14-column ledger book and over 65,000 lines in each WorkSheet.

See my “How To Manage WorkSheets” Tip for more.