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

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

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.

Centering Across Columns

Excel has a nifty feature that allows you to format your headings to look better on the printed page. You can center the contents of a cell so that it is spread across several columns. For instance, if the body of your worksheet is six columns wide, you can center your headings across all six columns. Applying this type of formatting is very easy:

1. In the left-most cell of the range in which you want the text centered, enter the text.

2. Select the range of cells across which you want the text centered.

3. Click on the Center Across Columns tool on the toolbar.

An EXCEL Customer List

A well designed name and address database would define a separate cells for the First Name and Last Name. Reasons to separate the name fields are for the purposes of Sorting the list and Finding a Customer  in your list using Excel functions. Below are instructions to combine the First and Last Names into a single cell… these new cells can then be used for  envelopes or mailing labels.  Don’t forget… your Customer Excel database can hold everything you need to know about your Customer.

Give us a call and we show you how we help you “Work Smart – Not Hard”

loadTOCNode(2, ‘moreinformation’);You can concatenate or adjoin text in multiple columns by using the & operator or the CONCATENATE function–for example, if you type the following data in cells A1:C2:

A1: First	B1: Middle	C1: Last
A2: Tom	B2: Edward	C2: Smith

To put the full name, in cell D2, type one of the following formulas:

$D$2: =CONCATENATE(A2,” “,B2,” “,C2)

$D$2: =A1&” “&B2&” “&C2

Note A space (” “) between the cells is used to insert a space between the displayed text.