Found this really cool Excel tricks you'll surely love!

Found this article about 10 obscure and really cool Excel tricks that can easily speed up your spreadsheet chores. Practice it and it will surely come in handy and this is a good arsenal to have when dealing with repetitive and time consuming spreadsheet work.

Here is an excerpt of the articles. Visit the link below for the complete list of cool Excel tricks.

#1: Select All with one click

The next time you need to select an entire worksheet, click the little gray box in the top-left corner of the sheet. As shown in Figure A, it's the space above the row numbers and to the left of the column letters.

Figure A

Select the entire worksheet by clicking on the gray square above the row numbers (and to the left of the column letters).

Why would you want to select the entire worksheet? Let's count some of the ways:

• With the entire worksheet selected, you can copy it from one workbook (XLS file) and then paste it into a worksheet in a different workbook. Selecting the whole worksheet ensures you won't accidentally miss something. Note: If you want to make a copy of a worksheet within the same book, just right-click on the worksheet tab, choose Move or Copy, then select the Create A Copy check box.
• There are, of course, other ways to select all the cells in a worksheet. If you're a keyboard person, press [Ctrl]A. If you're a menu person, go to Edit | Select All. 

#5: Generate a unique list of entries in a column

When you support or teach Excel users, one of the most common questions you'll hear is, "I've got a list with a thousand entries in a column, and many of those are duplicates. How do I generate a list of the unique entries in that column?"

There are at least two good answers to that question. The first answer is to refer back to #3 above: Go to Data | AutoFilter and then click the drop-down list for the column in question. Doing so lets you see the list of unique entries onscreen. If seeing the list satisfies your need, you're finished.

The second answer is the one to use if you want to have a list of the unique entries you can copy and paste elsewhere. To generate such a list, you'll use Data | Filter | Advanced Filter. To demonstrate how it works, we'll use the data in Column B from the sample sheet we introduced in Figure B.

1. Click on the column letter to select the entire column that contains your data and then copy it by pressing [Ctrl]C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you'll need the column header.)
2. Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed.Note: You don't have to select all the data or sort it first for this tip to work.
3. Go to Data | Filter | Advanced Filter.
4. By default, Excel will suggest filtering the list "in-place." There's nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
5. As shown in Figure I, select the Copy To Another Location option, select the Unique Records Only check box, and type B1 in the Copy To field.
6. Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure J.

Figure I

Use the Advanced Filter options to tell Excel whether to filter in-place or to copy the unique records to another location.

Figure J

The Advanced Filter feature copied a sorted list of the unique entries from the source data in Column A. You can check out the rest of the Excel tricks here TechRepublic.com - Excel Tricks. Till the next Excel coolest tricks!

Shortcut for Long Models

Have you created models which run into 20 – 30 years? You might have noticed that navigating to the last year (the last column) is probably the most boring part (and also the most time consuming part). Excel does provide you a shortcut (Ctrl + end), but that hardly works!

It’s been a while since we spoke and in this tutorial, I would like to make up for our lack of interaction by introducing a clever trick to cut down your time and effort in creating such models.

In most of the financial projections that we create for Project Finance, Project Management (Especially for long gestation projects), month on month projections, navigating to the last year/ month in such a sheet is a slow process. Typically you would have 100s of years/ months and you have the following choices with you:

· If you use Shift + Right key, you can easily take a quick nap by the time you reach the right cell.

· If you use Ctrl + Shift + Right, Excel will take you to the end. If you are planning to come back with Shift + Left Key, I suggest you have a comfortable pillow to sleep!

· I earlier used to resort to Ctrl + End shortcut key, but that does not work if your sheet has end characters placed at random places in your sheet.

The basic techniques do not work here!

The trick is to use a combination of Excel shortcuts and use the modeling process more intelligently. The first step is a manual process and can take the usual time – Creating a guiding row.

For example, in my model, I have created a row for Construction counter flag. For this row, I typically just write the formula and use Shift + Right key to navigate to the end of the model and use one of the following:

· Ctrl + R (Copy to the full row)

· F2 (to Edit), followed by Ctrl + Enter (Please note that it is not Ctrl + Shift + Enter)

· Copy (Ctrl + C) in the beginning and then press Enter (I avoid using Ctrl V to make sure that my clipboard is always empty)

Once we have the guiding row ready, we can use a combination of the excel shortcuts that we already know of. Let me show you the sequence:

1. Copy the formula

2. Navigate to the guiding row

3. Use Ctrl + Right Arrow to navigate to the end of the guiding row

4. Go Down one row (From the guiding row)

5. Use Ctrl + Shift + Left key to select and reach the beginning

6. Press Enter (or Ctrl + R Key) to fill all the cells

7. Final Shortcut Usage

Just like in this case we are using a row as a guiding row, I also use a guiding column to navigate quickly to the last column. What I would do is simple – Put a cross after the last column and then use Shift + Ctrl + Right key to navigate to the end.

I will speak about this trick in another tutorial!

Shortcuts are cool! They help you concentrate on the modeling process rather than waste time fiddling with the Excel. Which shortcuts do you use in your long models? Share and learn!

I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com

Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.

RSS feed for comments on this post. TrackBack URI

View the original article here

Join Excel School & Become Awesome in Excel Today!

Some of you know that I run an online Excel training program – Excel School. This program has 24 hours of detailed, step-by-step, fun & very useful Excel training, all available online so that you can view & learn at your own pace.

Creating this program has been the best thing that happened in my life. This program has been received very well by Excel users all over the world. Since we launched in Jan 2010, More than 2,500 people have joined Excel School and have become awesome in Excel. Personally, I have learned so much more about Excel, teaching & running business by conducting this program in last 2 years.

You too can become awesome in Excel by joining us. Please click here.

I have asked our students & recognized Excel personalities to review & rate our program. You can read a few of those reviews here:

Here is what David says,

Chandoo brings out innovative ways of using Excel formulas.  Some are functions I’ve never heard of, and some are formulas I thought I knew.  Chandoo shows new ways to use the functions.  The lessons are very informative and it is great to have the spreadsheet examples.  Being able to download the lessons is great since I will no doubt forget a few things along the way.

Here is what Jesse says,

The downloadable materials are VERY helpful, but even better combined with the very excellent instruction–it’s all the best!

Reghunath says,

Fabulous teaching technique. Your ability to teach basics with simple language is truly appreciable.

Here is what Daniel Ferry from Excelhero.com says,

If you want to develop an amazingly strong skill set in Excel, Excel School is the right place. The online school is first rate, as are the downloadable workbooks and videos. It is obvious from the moment you first log on that Chandoo has worked endlessly for over a year now, designing the perfect curriculum and developing lessons, with the business user in mind.

Read Daniel’s full review.

This holidays, I want to spread some love. So we are giving 20% discount on course fees. Please use the discount code LETSGOEXCEL to claim it during checkout.

Note: this discount is valid until 5th of Jan, 2012 only. So hurry up.

Just like everything else here, we have a 5 step tutorial on this too

Visit Excel School page.Know about the course & what you get.Decide which option to go for & Click on the green sign-up button.Pay course fees (using your credit card, eCheck, PayPal accounts)
For our Indian students, we have credit, debit cards, net banking, check, bank transfer options. Click here. Start learning & Become Awesome in Excel

That is all.

PS: If you want to learn Excel, but not pay, check out these 80 links. Tons of information, examples & awesome tricks to be learned.

PPS: Go ahead and enjoy the discount. Because you want to become awesome in Excel. Click here.

RSS feed for comments on this post. TrackBack URI

View the original article here

8 Tips to Make you a Formatting Pro

We  can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

Introduced in Excel 2007, Excel Tables are an incredibly powerful way to handle a bunch of related data. Just select any cell with in the data and press CTRL+T and then Enter. And bingo, your data looks slick in no time.

Learn more about Excel Tables.

So you have made a spreadsheet model or dashboard. And you want to change colors to something fresh. Just go to Page Layout ribbon and choose a color scheme from Colors box on top left. Microsoft has defined some great color schemes. These are well contrasted and look great on your screen. You can also define your own color schemes (to match corporate style). What more, you can even define schemes for fonts or combine both and create a new theme.

Consistency is an important aspect of formatting. By using cell styles, you can ensure that all similar information in your workbook is formatted in the same way. For example, you can color all input cells in orange color, all notes in light gray etc.

To apply cell styles, just select all the cells you want to have same style and from Home ribbon, select the style you want (from styles area).

Learn how to use cell styles in Excel.

Format painter is a beautiful tool part of all Office programs. You can use this to copy formatting from one area to another. See below demo to understand how this works. You can locate format painter in the Home ribbon, top left.

Sometimes, you just want to start with a clean slate. May be it is that colleague down the aisle who made an ugly mess of the quarterly budget spreadsheet. (Hey, its a good idea to tell him about Chandoo.org) So where would you start?

Simple, just select all the cells, and go to Home > Clear > Clear Formats. And you will have only values left, so that you can format everything the way you want.

Formatting is an everyday activity. We do it while writing an email, making a workbook, preparing a report, putting together a deck of slides or drawing something. Even as I am writing this post, I am formatting it. So knowing a couple of formatting shortcuts can improve your productivity. I use these almost every time I work in Excel.

CTRL + 1: Opens format dialog for anything you have selected (cells, charts, drawing shapes etc.)CTRL + B, I, U: To Bold, Italicize or Underline any given text.ALT+Enter: While editing a cell, you can use this to add a new line. If you want a new line as part of formula outcome, use CHAR(10), and make sure you have enabled word-wrap.ALT+EST: Used to paste formats. Works like format painter (#4)CTRL+T: Applies table formatting to current region of cellsCTRL+5: To strike thru.F4: Repeat last action. For example, you could apply bold formatting to a cell, select another and hit F4 to do the same.

What looks great on your screen might look messed up, if you do not set correct print options. That is why, make sure that you know how to use these print settings. All of these can be accessed from Page Layout ribbon. For more, you can also use print preview and then “page settings” button.

Formatting your workbook is much like garnishing your food. No amount of plating & garnishing is going to make your food taste good. I personally spend 80% of time making the spreadsheet and 20% of time formatting it. By learning how to use various formatting features in Excel & relying on productive ideas like tables, cell styles, format painter & keyboard shortcuts, you can save a lot of time. Time you can use to make better, more awesome spreadsheets.

Formatting (or making something look good) helps you get great first impression. I am always looking for ways to improve my formatting skills. While a great deal of formatting skill is art (and personal taste), there are several ground rules to follow as well. Applying ideas like consistency, alignment, simplicity and vibrancy goes a long way.

What formatting tips & ideas you follow? Please share them with us using comments.

In my Excel School program, we focus not just on teaching Excel, but also teaching you how to make awesome Excel workbooks. You can see how I format my data, charts, dashboards & reports and learn hundreds of tips on formatting.

Even the lesson workbooks are beautifully formatted & packed with fresh ideas for you to try.

Consider joining our Excel School program, because you want to be awesome in Excel.

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: cell styles, Excel 101, excel tables, format painter, formatting, keyboard shortcuts, Learn Excel, printing, screencasts, spreadsheets, using excel

RSS feed for comments on this post. TrackBack URI

View the original article here

Learn Any Area of Excel using these 80 Links

Last week I asked, What is one area of Excel you want to learn more?

More than 250 of you responded to this question. Many of you shared your areas of interest thru comments, quite a few of you also emailed me personally.

You told us what you want to learn, the next step is logical. We share some of the best tutorials & examples with you so that you can learn. In this post, we have presented more than 75 links, to help you learn your area of focus.

I have divided this in to 16 areas. In each area, we have identified (upto) 5 best links for you to learn more. I have also recommended 1 or 2 training programs that make you awesome in that area. Plus, if we found any excellent external resources, we have highlighted them as well.

So go ahead and learn Excel.

If you come across any good resource for learning Excel, please share it with us. I am always looking for ways to learn more. So go ahead and drop a comment.

Special thanks to Hui, for compiling the survey results & some of the links.

RSS feed for comments on this post. TrackBack URI

View the original article here

Last-minute budget tips for those last-minute gifts

At this point in the holiday season if you’re like me, you’re over budget. Yesterday I started worrying about that cashmere sweater I bought for one sister-in-law and the sweatshirt I bought for the other. It’s back to shopping this evening to try to even it out.

This time when I’m wandering the aisles, I’ll check my Excel holiday budget on my phone. I saved it on SkyDrive—Microsoft's free cloud service—so that I can take a look to remind me what I bought for whom and for how much. My budget is a customized version of the holiday budget template we made available in early November. 

Here’s how you can customize that budget and access it on your phone:

Click to open the Excel holiday budget from our SkyDrive, and it will open in Excel Web App.

Download it by clicking the Download button in the upper-left corner of Excel Web App.

After you’ve downloaded and opened it, make the spreadsheet yours by replacing the example information with your own.

Save it to your SkyDrive, choosing Save & Send on the File tab, click Save to Web, and then click the Sign In button.

If you don’t have a SkyDrive account, sign up for one using the Sign up for Windows Live SkyDrive link.

After you sign up, use the Sign In button on the File tab to save the spreadsheet on SkyDrive.

Now, on your smartphone, go to http://skydrive.live.com, and sign in. Find the spreadsheet you uploaded, and open it.

To make the spreadsheet appropriate for year-round use, open it in Excel, remove the holiday picture, and change up the color scheme. Here’s how:

Replace holiday-related text with your own, such as, “Vacation Budget” and categories such as “Flight,” “Lodging,” and “Meals."

In the first row, click the picture and press your DELETE key to remove it.

Now let’s change up the color scheme.

Go to the Page Layout tab, and click Colors, and then choose a color scheme, such as Adjacency.

Right-click Cell A1, click Format Cells, and then on the Fill tab, set Pattern Color to Automatic, and Pattern Style to Solid.

For the Background Color, choose a color from the color scheme.

Repeat the previous step for rows 2 and 4. Select them both: After selecting Row 2, hold down the CTRL key and select Row 4. When they’re both selected, right-click and change the Fill settings as you did for Cell A1.

Select Row 3 and repeat the Fill settings, only choose a darker background color.

Right-click Row 5 and change its background color to one of the colors in the color scheme.

Select the other category rows (for example, the rows labeled, “Lodging” and “Meals”), and press CTRL+Y to repeat the color formatting you applied to Row 5.

Lastly, modify the text and budget amounts, and save the spreadsheet. Remember: Use Save & Send to store your budget on SkyDrive, where you can get it on the go.

View the original article here

Formula Forensics 006. Palindromes

Chandoo wrote a post in August 2011 where he looked at determining if a cell contained a palindrome.

Chandoo presented a formula for determining if a cell; C1; contains a palindrome:

=IF(SUMPRODUCT((MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)=MID(C1,LEN(C1)-ROW( OFFSET($A$1,,, LEN(C1) )) +1, 1))+0)=LEN(C1),"It’s a Palindrome","Nah!")

And then Chandoo challenged everyone:

How does this formula work?

Well, that is your weekend homework.

So today we’re going to complete our homework and pull apart the above formula and see what makes it tick.

Download the example file so you can follow along with a worked example, Excel 97-2010.

In a blank worksheet enter

C1: “Chandoo” without the brackets

D1: =IF(SUMPRODUCT((MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)=MID(C1,LEN(C1)-ROW( OFFSET($A$1 ,,, LEN(C1)))+1,1))+0)=LEN(C1),"It’s a Palindrome","Nah!")

In the example below we will work on two words:

Firstly, “Chandoo” which is clearly not a Palindrome

and Secondly, “Radar” which is a Palindrome

The main structure of this formula is that it is a simple If () function.

The Excel If() function is defined by 3 parts

=If(Condition, Value if True, Value if False)

Our Formula is

=IF( SUMPRODUCT(( MID(C1, ROW( OFFSET( $A$1,,,LEN(C1))), 1) = MID(C1, LEN(C1) - ROW( OFFSET( $A$1,,,LEN(C1))) + 1, 1)) + 0) = LEN(C1), "It’s a Palindrome", "Nah!")

Condition:                 SUMPRODUCT((MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1) = MID(C1, LEN(C1) - ROW(OFFSET($A$1,,,LEN(C1)))+1,1))+0)=LEN(C1)

Value if True: "It’s a Palindrome"

Value if False: "Nah!”

Lets not waste time on the two Values if True/False as they are purely a message to the user, the real work happens in the Condition part of the If() function.

The Condition does all the work: SUMPRODUCT((MID(C1,ROW( OFFSET($A$1,,, LEN(C1))),1) = MID(C1, LEN(C1)-ROW( OFFSET($A$1,,,LEN(C1)))+1,1))+0)=LEN(C1)

Is a Sumproduct and a Len

That is the formula is doing a calculation of the sumproduct of some inner calculations and comparing the answer to the length of the contents of cell: C1 in the example of "Chandoo" = Len(C1) = 7

SUMPRODUCT(( MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)= MID(C1, LEN(C1)-ROW( OFFSET($A$1,,, LEN(C1)))+1,1))+0)=LEN(C1)

Lets now look at the two inner calculations:

MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)

Copy this calculation into E7

= MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1) Don’t press Enter, press F9

(If using the example file goto cell E9, Press F2 and then F9)

Excel will return {"C";"h";"a";"n";"d";"o";"o"} Don’t press Enter, press Esc when ready

It is an Array of the letters in the cell C1

Now do the same for the second part of the equation:

Copy this calculation into E8

= MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1) Don’t press Enter, press F9

Excel will return {“o”;”o”;”d”;”n”;”a”;”h”;”C”}

You will notice that this array is the reverse of the word in C1

We will come back to how these two equations work in a minute or two

But note that we now have

Sumproduct(({"C";"h";"a";"n";"d";"o";"o"}={“o”;”o”;”d”;”n”;”a”;”h”;”C”})+0)

We can evaluate the inner part of this to see what happens

Copy this calculation into E10

={"C";"h";"a";"n";"d";"o";"o"}={“o”;”o”;”d”;”n”;”a”;”h”;”C”} Don’t press Enter, press F9

Excel will return an Array {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

This means that only the 4th letter or “n” in Chandoo is the same forward and backwards.

Looking at the next bit

Copy this calculation into E12

=({"C";"h";"a";"n";"d";"o";"o"}={“o”;”o”;”d”;”n”;”a”;”h”;”C”})+0 Don’t press Enter, press F9

Excel will return {0;0;0;1;0;0;0}

Excel has converted the false/True array above into an array of 0's and 1's

Finally in E14 evaluate:

=Sumproduct({0;0;0;1;0;0;0})

Is evaluated and Excel adds up all the numbers returning a 1 as the answer.

So the original equation :

=IF(SUMPRODUCT((MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)=MID(C1,LEN(C1)- ROW( OFFSET($A$1,,, LEN(C1))) +1,1)) +0 )=LEN(C1),"It’s a Palindrome","Nah!")

Is simplified as

=IF( 1 = LEN(C1),"It’s a Palindrome","Nah!")

Now C1 has the Word “Chandoo “ in it which is 7 letters long

=IF( 1 = 7,"It’s a Palindrome","Nah!")

So the If() function returns the False answer of “Nah!”

If we place a Palindrome such as “Radar” in C1 and skip backwards to the

SUMPRODUCT(( MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)= MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1))+0)=LEN(C1)

Section , Evaluating each part again we see

E21: MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)

Evaluates to {"R";"a";"d";"a";"r"}

And

MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1)

Evaluates to: {"r";"a";"d";"a";"R"}

And

( MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)= MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1))

Evaluates to: {TRUE;TRUE;TRUE;TRUE;TRUE}

With

SUMPRODUCT(( MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)= MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1))+0)

Evaluating to: 5

Which is clearly equal to the length of the word “Radar” and so the If() function returns “It’s a Palindrome”

But how do the middle bits

MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)

and

MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1)

Work?

The two equations are effectively the same

The first works left to right and extracts each letter one at a time

The second works right to left and extracts each letter one at a time

How Does

=MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1) F9

Evaluate to {"C";"h";"a";"n";"d";"o";"o"}

=MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1) F9

Is a simple Mid() function which takes the 1 Character at position ROW(OFFSET($A$1,,,LEN(C1))) from the contents of C1

What is ROW(OFFSET($A$1,,,LEN(C1)))

Is used to return an array of numbers from 1 to Len(C1) in this case 7

eg: {1;2;3;4;5;6;7}

So in E16 enter =ROW(OFFSET($A$1,,,LEN(C1))) and press F9

Excel evaluates it to {1;2;3;4;5;6;7}

So the function =MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1)

Returns the 1st, 2nd. 3rd, 4th, 5th, 6th & 7th characters from C1 as an Array

=ROW(OFFSET($A$1,,,LEN(C1)))

Takes the Row of the range defined by the Offset Function

Note that OFFSET($A$1,,,LEN(C1)) is a simple Offset that sets up a range

The excel Offset Function is defined as

=Offset(Reference, Rows, Columns, [Height], [Width])

In our example

=OFFSET($A$1,,,LEN(C1))

Will return a Range which is referenced to A1, has no Row or Column offset and is the length of cell the contents of Cell C1

Effectively returning a range A1:A7

Because this is all in an Sumproduct formula, Excel evaluates this formula for each value in the Range

And so

=ROW(OFFSET($A$1,,,LEN(C1)))

evaluates it to

{1;2;3;4;5;6;7}

Which is then used extract the characters from the word in C1 into an Array as {"C";"h";"a";"n";"d";"o";"o"}

A similar process applies to the second half of the two equations

MID(C1, LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1)

Except that it is evaluated from the Right to Left of the Word in C1 by use of the

LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1

In the Mid Equation

So in summary we use Sumproduct to compare two Arrays, which contain the word and the word reversed, to each other. The Sumproduct counts the number of common matches and then this is compared to the length of the word.

If the two match the word is a Palindrome.

You can download a copy of the above file and follow along, Download Here.

You can learn more about how to pull Excel Formulas apart in the following posts which are all included in the Formula Forensic Series:

I am running out of ideas for Formula Forensics and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003 or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.

RSS feed for comments on this post. TrackBack URI

View the original article here

The Excel part of mail merge

Hey, the holidays are fast approaching, which means you've got to get your cards signed, sealed, and delivered! This post goes out to those of you who keep your address list in Excel and need to figure out how to use it to create mailing labels in Word.

Creating labels can be intimidating, mainly because there are a number of intricate steps to follow and you're typically working with different programs—in this case, Excel and Word. And if you create labels infrequently, it's hard to remember what to do and what to watch out for.

Learning how to make your Excel address list magically show up on your sheets of labels boils down to five basic steps:

This post is about that first step—the one where you prepare your address list in Excel so that you can use it in Word. If you get this part of the process right, things will run more smoothly when you're setting up your labels in Word.

The key thing to understand is that your column headers, or categories, in Excel will become merge fields (placeholders) in Word. Each merge field corresponds to a piece of the address on the label—first name, last name, street address, and so on. Word pulls out the information in your Excel columns and plugs it into the corresponding merge fields, with an end result that looks something like this:

When setting up your address list in Excel, consider the following tips:

Use "friendly" column headers such as First Name, Last Name, Address, and City instead of Column 1, Column 2, Column 3, and Column 4.Set up your address list so that each column represents the smallest possible piece of information. For example, use separate columns for First Name and Last Name rather than just a Name column. This practice gives you more flexibility if you end up creating cards or letters in addition to labels.Avoid blank rows and columns in your address list. During the mail merge, these blanks can trick Word into thinking that it has reached the end of the address list, when in fact there is more information after the blanks. To make it easier to pick the address list you want Word to use, give it an easily recognized name in Excel. To do this, select the range of cells that make up your address list. Then, in the Name box next to the formula bar, type a name like Holiday_Cards and click OK.Postal codes can be tricky. If you have a column containing postal codes, make sure you format that column as Text. Otherwise, Excel will strip out any zeros from the front of the postal code. If you're creating a new address list from scratch, be sure to format your column as text before you type the postal codes. If you're importing addreses into Excel from a .txt file, use the Text Import Wizard to format the appropriate columns as Text (as opposed to leaving them in the General format or in another number format that might mess up the mail merge).

By the way, if you want to create labels from your Outlook contacts, you can do that without first importing the contacts into Excel. Word can access your contacts directly from Outlook during the mail merge process. For the details, download this Mail Merge Made Easy guide. It will tell you exactly what to do, based on where you keep your contacts (Excel, Outlook, or another email program) and where you plan to print your labels (Word or Publisher).

Once everything is set up in Excel, you'll need to open Word and start your mail merge (Mailings tab | Start Mail Merge group | Start Mail Merge). The following articles do a good job of walking you through that process.

I know darn well you do! Please feel free to leave them in a comment.

View the original article here