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

Data’s got a brand new bag: PowerPivot for Excel (video)

I liked the heads up but was expecting a little more from the video. For instance a quick example how you linked up your sheets in the way you showed.

Still, thanks for the heads up but my data amounts are hardly enough to expand on it like this. But having said that, a small side-step if I may...  Since beginning this week I started using Outlook 2010 (was using SeaMonkey's mail). Part of this decision was triggered by the 'Business Contact Management' extension; that critter is outrageous for small businesses like I represent.

I'm slowly, but steadily, starting to become -very- impressed with the way you can inter-exchange data between Office applications. A bit comparable like you showed here; one sheet easily connected to the other. In my case; accessing Outlook data (contacts) from Word (using VBA obviously).

Alas, thanks for sharing but..  - no offense - but I am looking forward to one of your "casual" example video's. Those really rock, I'm even actively (carefully) spreading them with some of my customers :-)

View the original article here

Remembering Nathan (Nate) Oliver

thank you for your tribute to Nate

I am so sad that I will not see Nate or talk to him again.  He was gifted in many ways.  His brilliant mind and physical skills were tempered by his passion for people and being helpful.  I love what everybody has written, even those who are just starting to learn who Nate was through his legacy of posts, blogs, pictures, and more.  I took many pictures of Nate over the years.  They are here:

skydrive.live.com

Goodbye my friend and colleague.  We had great times and I always learned something from you.  Even though you are gone, your code lives on,  thank you

To Nate's family and friends, I give my most sincere condolences

Warm Regards,

Crystal

Microsoft MVP, Access

www.accessmvp.com/strive4peace

View the original article here

Tracking small projects in Excel

Hi Anneliese,

If you ask any project manager what's the worst thing that he can do to manage his project's, he'll tell you "using Excel to schedule and manage the project".

MS Project is way better at that and Excel is really not made to manage projects. Problems with Excel will start one you have change requests, how can you handle them?

I'm a contributor to PM Hut and one of the common things that project managers tell us is how Excel "ruined" the project (of course, you might think that they're blaming the tool, but when you start hearing the same thing over and over again, you will start wondering).

PS: Glenna's work is perfect, and I'm not criticizing it in any way (as she tried her best to make Excel work as a PM software), but again, and in my opinion (and the opinion of many other project managers), Excel is just not made for project management.

View the original article here

Introduction to Spreadsheet Risk Management

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit

Part 1: An Introduction to managing spreadsheet riskPart 2: How companies can manage their spreadsheet riskPart 3: Excel’s auditing functionsPart 4: Using external software packages to manage your spreadsheet risk

The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive.

See the full news article here: http://www.express.co.uk/posts/view/276053/Mouchel-profits-blow

Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.

Because we all love Excel, right? True certainly, but the main reason is that it is intuitive, flexible, cost effective and provides quick solutions to high priority day to day problems.

And what is the alternative? The IT department. The simple fact is that end user developed spreadsheets often fill the gap between the current business requirements and formally managed IT systems.

Unfortunately this reliance on spreadsheets, rather than robust, well governed IT solutions can add significant risk to an organisation if it is not properly managed.

Spreadsheet risk is the risk that a business could lose revenue and profit, fail to comply with regulators or find its reputation damaged as a result of spreadsheet error (be it fraudulent or unintentional).

Poorly structured spreadsheets can also lead to a loss of productivity and increased audit costs, further damaging the bottom line.

A recent study of typical enterprise spreadsheets by the Tuck School of Business at Dartmouth found that 94% of spreadsheets and 5% of all formulae within spreadsheets contain errors.

The European Spreadsheet Risk Interest Group (EuSPrIG) are the voice of best practice spreadsheet development and the management of spreadsheet risk.

Below are a couple of examples of what can go wrong from the EuSPrIG website:

C&C Group admit to mistake in revenue results

Shares in C&C fell 15 per cent after it said total revenue in the four months to end-June had not risen 3 per cent as reported, but had dropped 5 per cent. C&C said cider revenues in the UK had fallen 12 per cent, not 1 per cent, while cider revenues in Ireland were flat instead of up 7 per cent as reported last week.

C&C’s group finance director and COO said the error in last week’s announcement occurred after data were incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement. “It was basically human error… there’s nothing wrong with our accounting systems,”

FSA fines Credit Suisse £5.6m

The FSA decided to impose a financial penalty of £5.6 million on the UK operations of Credit Suisse in respect of a breach of Principles 2 and 3 of the FSA’s Principles for Business:

Principle 2 states that “A firm must conduct its business with due skill, care and diligence.”Whilst Principle 3 requires that “A firm must take reasonable care to organise and control its affairs responsibly and effectively, with adequate risk management systems.”

More specifically, section “2.33.3. The booking structure relied upon by the UK operations of Credit Suisse for the CDO trading business was complex and overly reliant on large spreadsheets with multiple entries. This resulted in a lack of transparency and inhibited the effective supervision, risk management and control of the SCG {Structured Credit Group}”

Excel is, and is likely to remain, the first choice for businesses when developing financial models and analysing data. The risk that this introduces to businesses if unmanaged is real and potentially material.

In the next article we will look at ways that companies can manage their spreadsheet risk.

In my brief usage of Excel, I have experienced several risky situations. Sometimes it just a mild data loss, other times, there was a potential of revenue loss or customer annoyance. Due to the economic slowdown many large and small corporations are employing spreadsheet based solutions. And if you do not understand the risk & manage it, then your risk being featured on EuSPrIG’s horror stories page.

Do you know (or experienced) a spreadsheet horror story? Please share your ideas and best practices with us using comments.

Many thanks to Myles for writing this series. Your experience in this area is invaluable. I am really keen to learn about the best practices and adopt them in my business. If you enjoy this series, drop a note of thanks to Myles thru comments. You can also reach him at Excel Audit or his linkedin profile.

RSS feed for comments on this post. TrackBack URI

View the original article here

Formula Forensics No. 005 – Zebras and Checker-Boards

This week in Formula Forensics we’ll look at, Zebra Stripes and Checker-board Conditional Formatting.

This idea is inspired by a number of posts over the past few years asking about zebra stripes but specifically BobR who in in June 2011, also asked about Checkerboards in the post: Want to be an excel conditional-formatting Rock Star, Comment No. 154.

I got the conditional format for alternating row and column colors,

Is there a conditional format to make it a checkerboard whereas the cell A2 will remove either the conditional for the row or column and then alternately to A4, B1, B3 etc?

Chandoo responded fairly quickly with this Conditional Formatting formula:

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

Unbeknownst to Chandoo I posted this about a minute later:

=ISODD(ROW()+COLUMN())

Both formula correctly answer BobR’s question.

So today we’re going to pull apart Zebra Stripes and Checker Boards and see what makes them tick.

As always you can follow along in a download file here: Download File.

Zebra Stripes as Conditional Formatting is simply applied using a simple formula within Conditional Formatting.

=MOD(ROW(),2)=0

Conditional Formatting requires a formula that returns a boolean “True” to apply a format or a Boolean “False” to not Apply a format.

So the formula is better read as: If MOD(ROW(),2)=0

And  If MOD(ROW(),2)=0, the formula will evaluate as True

This is best evaluated as 3 columns on a worksheet.

In cells

B5:B10 The formula =Row() returns the Row Number

C5:C10 The formula =Mod(Row() ,2) returns the Mod of Row Number, divided by 2

The Mod function returns the remainder of the division of the Row Number divided by 2,

So in Row 5, Mod(Row(),2) = Mod(5, 2) = 5/2 = 2 Remainder 1 = 1

and in Row 6, Mod(Row(),2) = Mod(6, 2) = 6/2 = 3 Remainder 0 = 0

D5:D10 The formula =Mod(Row() ,2)=0 checks the remainder against the value 0

This is what evaluates to either True or False depending on the Row number.

Where the Values are True the Format will be applied (Even Rows)

The Conditional Formatting can be applied to Odd Rows If the Formula is slightly altered

=Mod(Row() ,2)=1

Similarly the formatting can be applied to Columns using

=MOD(COLUMN(),2)=0/1

RobR received two responses to his Checker-Board Conditional Formatting request.

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

and

=ISODD(ROW()+COLUMN())

Lest see what’s inside these two formula.

This is a simple If Formula with 3 components

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

If Condition        MOD(ROW(),2)=1

Value if True:     MOD((ROW()-1)*8+COLUMN(),2)=0

Value if False:    MOD((ROW()-1)*8+COLUMN(),2)=1

The If Condition is already known to us, as it’s the same formula used in the Zebra Stripes above.

It evaluates to True when it is on an Odd Row.

So when it is an Odd numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=0

And when it is an Even numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=1

We can notice that these are the same formulas which have a different ending of =0 and =1

MOD((ROW()-1)*8+COLUMN(),2)=0

This section Takes each Row subtracts 1 and then multiplies this number by 8. This can be expressed as simply as saying multiply the Row * 8.

This will always return an Even Number and could have been simplified to Row()*2

MOD((ROW()-1)*8+COLUMN(),2)=0

The next bit adds the column number to the previous Even Number.

So now this part will be Odd when the column is Odd and Even when the column is Even.

MOD((ROW()-1)*8+COLUMN(),2)=0

The remainder of the formula is the same as the Zebra Stripes formula.

An Odd Number (Odd Columns) in the section above will return a 1 as the result of =Mod(Odd,2)

An Even Number (Even Columns) in the section above will return a 0 as the result of =Mod(Odd,2)

When evaluated against 0 will return True for Even Columns and False for Odd Columns.

Now the exact same happens in the False section of the If formula except that it is evaluated against 1.

I tackled this problem from a different direction to Chandoo.

Knowing that Even + Even = Even and Even + Odd = Odd and that the row and Column Numbers increase in each direction by 1 each Row/Column, it was simply a matter of adding the Row and Column numbers together and checking if it was Odd or Even

The Excel function IsOdd() and IsEven() both return a Boolean “True” if the contents are Odd or “Even” respectively. This negates an external truth check as described above.

This is easily shown by adding a formula to the Checker area

=Row()+Column()

Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=1

If the alternate shading is required a switch to

=ISEVEN(ROW()+COLUMN())

Does the trick.

Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=0

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

and

http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

and

http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

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

Formula Forensics 001 – Tarun’s Problem

Formula Forensics 002 – Joyce’s Question

Formula Forensics 003 – Lukes Reward

Formula Forensics 004 – Freds Problem

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 this post.

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

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: Check-Board, column(), if(), iseven(), IsOdd(), Learn Excel, Microsoft Excel Conditional Formatting, Microsoft Excel Formulas, MOD(), row(), Zebra Stripes

RSS feed for comments on this post. TrackBack URI

View the original article here

Excel mashup tutorial

One of our Excel MVPs—Jan Karel Pieterse—emailed us earlier this week suggesting we take a look at the online tutorial he created about Excel Mashups. We did, and now we want to share it with you. 

In case you missed it, we recently blogged about ExcelMashup.com, a new site for developers which includes demo apps and code snippets for building Excel “mashups.”  If you don’t know exactly what a mashup is, you’re not alone—it’s just a web page that takes data from existing sources and combines it into something new. For example, a web guide to restaurants that consists of search results, Bing maps, and customer reviews.

You create an Excel mashup by uploading a workbook to SkyDrive and then embedding it on a web page. Then, you use JavaScript to programmatically interact with that workbook.

Jan Karel's tutorial:

Shows you how to embed a workbook on a web page.Provides JavaScript code snippets.Walks you through creating a web control.Provides a demo that pulls it all together.

And, you can read his instructions in Dutch or English! Have fun!

View the original article here

What is one area of Excel you want to learn more? [Survey]

AppId is over the quota
AppId is over the quota
Posted on December 2nd, 2011 in Learn Excel - 190 comments

It is almost weekend. Today we (Jo and I) are going to watch a cricket match being played in Vizag. We are pretty excited as this is the first time we are watching a match in stadium.

So, let keep this light and fun. I want to know What is one area of Excel you want to learn more?

I will go first. I want to learn more about Data Tables & Simulation.

What about you? Go ahead and tell us using comments.

Note: Here are a few choices if do not know what else is out there.

FormulasArray FormulasFormattingConditional FormattingChartingAdvanced ChartingPivot Tables & ChartsTablesData TablesValidationFilters & SortingVBA (Macros)Linking to Databases etc.SolverStatistical Analysis (regression, time series etc.)Scenarios, What if analysisDashboards

So go ahead and tell us.

RSS feed for comments on this post. TrackBack URI

View the original article here

Create smashing mashups with Excel

Today's post is brought to you by Larry Waldman, a program manager on the Excel team. You may remember Larry from his previous posts on embedding Excel files on web sites and designing Excel solutions for the Web.

Hello, web developers! Today, I want to point you to ExcelMashup.com, a new site from the Excel team that is designed to help you get started creating spreadsheet-based mashups. To create an Excel mashup, you use Excel Web App to create an embedded workbook that is stored on SkyDrive, and then displayed in a host web page. Once you've embedded the workbook, you use the JavaScript API to programmatically interact with it. 

To get started, do the following three things:

Sign up for a free SkyDrive account, if you don't already have one.Upload your Excel file to SkyDrive.Grab the JavaScript embed code snippet and start coding!

We've created some example mashups to help you see what's possible, including a Daily Calories Calculator and a Destination Explorer.

In this example, you search for a specific food, choose a serving size, and then see dietary information appear inside cells in an embedded workbook. This interactivity is accomplished by using the JavaScript API. This example also shows how to use the calculation and charting features of Excel Web App to render a chart that compares daily calorie intake to recommended dietary guidelines.

 

To learn how to recreate this example, click the text in the upper-right corner of the page. Then, to find sample code and instructions, click a specific area on the page, such as the search box or meal input worksheet. 

In this example, select a place to visit, and you'll see information about the area's average temperature and precipitation, along with monthly visitor patterns. (Fair warning: This information is intended only for illustrative purposes, so please don't rely on it for actual trip-planning activities!) You'll also see related information about your destination appear in Bing Maps.

 

To find out how to we created the Destination Explorer mashup, refer to this how-to guide.

We hope you're as excited as we are about interactive data mashups, and that you'll find ExcelMashup.com helpful as you create with your own. Feel free to ask questions in the Excel Web App forums or use the interactive code explorer, where you can experiment with our API right there on the site. Developers and JavaScript experts should feel right at home!

If you have comments, questions, or suggestions, please let us know by leaving a comment here or on ExcelMashup.com.

View the original article here