Your Remote Business Partner

Outlook Email Attachment: Winmail.dat

Have you ever received an email with an attachment called Winmail.dat?  Alternatively have you ever sent someone an attachment only to have them tell you it came through as a Winmail.dat file?  If this has happened to you in the past, Microsoft owes you an apology. This didn't happen because of a corrupted file, or a flaky server. It happened because of a bug in the way Outlook interfaces with internet based email. The good news is that we can fix it, and prevent it from happening again.

The behavior occurs when someone is sending an email attachment using Outlook to a webmail account, and the email uses rtf format. The recipient will be unable to open the Winmail.dat attachment, (which appears to be corrupted).  

Interestingly, if the recipient forwarded the message on to someone else, or even sent it back to the sender, it would show up in its native format with the attachment. The problem does not occur if Outlook is used on the transmitting AND receiving end. 

If you use Outlook and want to prevent sending out Winmail.dat attachments, just follow these simple steps:

Go to Tools > Options




Click the Mail Format tab





Click Internet Format




Select either "Convert to Plain Text format" or "Convert to HTML format" under "Outlook Rich Text Options".

Easy!

There are also several winmail.dat decoders out there... here's an example.

Still having issues?  Let me help.

Using AutoCorrect and Find and Replace in Microsoft Word

Old habits die hard... so when you find yourself with changing circumstances it can be difficult to quickly adapt. For example, did you know that the new standard in online media is one space after a period instead of two? For someone who has been typing for 20 years or more that can be a difficult standard to relearn.  

Luckily there are some band-aids embedded in the software that we use. In the example above, which is a simple one, there are several solutions that we could utilize if we are using Microsoft Word. For existing documents that need to be converted to the new standard, a simple find and replace will do it.

CTRL+H brings up the find and replace dialog box in any version, which you will notice has tabs for Find and Goto. These last two are navigation tools that are mostly helpful in larger documents. In 2007 there is a section of the Home ribbon that gives access to all three as well as select.   

In Find what: type a period and two spaces '.  '  (without the single quotes.)

In Replace with: type a period with a single space '. ' 

If you are confident that every situation in the document can be converted without creating problems, then by all means boldly click Replace All and - *poof* - your document has a single space after each period.  

But what about new documents? Of course we can learn to use one space over time. However, we can expect many slips along the way. As a stopgap we can use the AutoCorrect feature to catch our error and fix it in real-time. 

AutoCorrect is one of those things that is wonderful when it works, and maniacal when it works against you. On the plus side, if you type v-e-y-r, Word will recognize that you probably meant v-e-r-y and will swap the letters around for you. There are dozens of pre-programmed items that correct common typing, spelling, and grammatical errors. Also included are some helpful shortcuts. Need a copyright or a trademark symbol? You could Insert > Symbols and scroll through to find it. Or you could type (c) or (tm) respectively and Word will replace it with the appropriate symbol. This is great unless you are actually trying to type (c)!

To find the AutoCorrect (in 2007) click the Office Button > WordOptions > Proofing > AutoCorrect Options.  In 2003 go to Tools > AutoCorrect Options...  

The list that ships with Word is only a starting point. We could easily add the '.  ' to '. ' example to the list. We can also remove items from the list.  A lawyer or architect that often quotes statutes or building codes might be annoyed when typing 1501.21(c) and the (c) switches to a copyright symbol! So if AutoCorrect is being 'too helpful' for you, simply open up the AutoCorrect list and delete this entry. Problem solved. 

A related feature is the exceptions list. Word defaults to correcting words that begin with two capital letters instead of one. So if I entered MIcrosoft, this feature would automatically change it to Microsoft. Since every industry has its own quirks and jargon this can be frustrating if what you are typing is not a mistake. For example an architect, in addition to specifying building codes, may use the abbreviation AB for as-built. If it is common practice in the industry to refer to these as ABs (without an apostrophe) and Word will automatically change it to Abs.  To avoid the problem we need to add an exception to the list. 

From the AutoCorrections dialog box click the Exceptions... button to bring up the AutoCorrect Exceptions dialog box.  Click on the cleverly mis-capitalized INitial CAps tab, and add ABs to the list.  Word ships with IDs as the only default exception.  

If you spend some time exploring all of the correction options you will see that they are pretty extensive.  And between the selectable options and specific lists, there is almost always a way for you to get Word to behave to fit your needs.  

And if you need additional assistance or have questions, please don't hesitate to contact me.

If / Then Statements in Excel: Using Logical Formulas

If you are not using logical formulas in Excel, Then your spreadsheets could be better.  Makes sense, right?  And at the root of it, logic statments are that simple.  The complete syntax would be If something, Then something, Else something different:

If the lightswitch is up, Then the light is on, Else the light is off.

In that example there is a test, and only two possibilities.  In Excel, we would organize the question in this way. 

=if(the lightswitch is up, The light is on, The light is off)

So, if you are not currently using this funtionality in Excel, where could you?  Lots of places once you get comfortable with it.  For example, have you ever seen a spreadsheet that has an entire column of zeros because other columns have not yet been filled in?  Or even worse, a column of errors such as #Div/0! that is displayed when a formula divides by zero, a mathmatical no no. 

Errors and zero values are one of my pet peeves, because with a little more work we can get rid of them.  And since we are dealing with logic, just be logical.  The example above returns an error if numbers have not been filled in for column B and C.  So our statement in words would be:

If B3 and C3 are greater than zero, then calculate D3, otherwise show a blank cell. 

In practice, a company usually figures out sales faster than costs, so we could simplify and just check for a value in C3 as follows:

=if(C3>0,(B3-C3)/B3,"")

Notice that the 'else' in this case has empty quotes.  Any time you want the formula to return text instead of a calculated value, you need to put it in quotations.  If you want to return a blank cell, you still have to explicitly state that with "".

In another example, where we did need to confirm that both B3 and C3 had values, we would just need to change the test statement by adding AND:

=if(and(B2>0,C3>0),...

Taking the text example, we can use a cell to give a statement.  If we know that there is a goal of a profit margin of 35% then in E3 we can add an If statement:

=if(D3>35%,"Goal Reached","Below Goal")

Now if you are following along, or thinking it through in your head, you will have noticed that we have created a new problem.  Until you fill in March's numbers, it will tell you that you haven't reached your goal.  While this is technically true, if it is still February, then it isn't possible for you to have reached your goal.  This is just as bad as the zero values and errors.  So the way to solve it is to nest statments of logic within each other.  This is where a lot of people lose it. 

Think of the old decision flowcharts.  You come to a diamond that asks a question one path if yes, another path if no.  Each one of those paths will have additional questions with different outcomes.  If you need to diagram out the logic of your formula, there is no shame in it.  You are more likely to get it right the first time. 

When you build the formula consider only one section at a time and do not get confused by the commas and parentheses. What we will do is take the entire If statement above and put it in the Then portion of a new statement. 

=if(and(B3>0,C3>0),if(D3>35%,"Goal Reached","Below Goal"),"")

Notice that as you are typing the formula or using the cursor to move in it, that Excel will help you to keep clear on which section you are in by bolding the parentheses and give prompts below the formula bar.  Excel calls it value if true and value if false instead of then and else respectively.  

 

Now I admit that nesting multiple statements can get squirrely in a hurry.  And once you are done, you need to test to make sure everything is working as you expected.

One tip to make sure that your commas and parentheses don't get out of hand is that when you first type If to go ahead and type the framework of the entire statement:

=if(,,)

Once you have typed it out like that, use the cursor to back up to the correct part - if, then , else, and fill it in.  If you follow this method it is not possible to be missing a parentheses when you are done.  And trust me, when you start nesting complicated functions it is pretty easy to miss one otherwise.

There are many uses for this type of logical statement in Excel.  The complete list is:

And, False, If, Iferror, Not, Or, True

A comprehensive description of each of them would take more room than a reasonable post allows.  but I urge you to look them up and practice, because they are all useful and simpler to use than they may seem.  It is these touches which most users never even utilize that make the difference between your spreadsheets looking professional, or just thrown together. 

And if you need further assistance, please don't hesitate to contact me.

Mail Merge in Microsoft Word

As promised, here is the simplest set of instructions on mail merge that I can provide.  These instructions are not materially different for 2007 although the ribbon functions are not menu driven.  Make sure that you have familiarized yourself with Named Ranges before starting.  We will assume that your names and addresses are already setup properly in Excel as Contacts.xls with a named range 'ContactList.'

If you are reading this post in desperation while trying to mail merge under a deadline then use your real data and follow along.  If you are just trying to learn, then you may want to download this Excel file which contains a generic list of fake names and addresses to practice with:

Download Contacts 

We will create labels based on the Avery Label templates #5160 which has 30 labels per sheet.  Start a new blank document.  If not visible, enable the mailmerge toolbar.  Click 'Main Document Setup' and select document type 'labels.'

Under label information choose Avery Standard, Product Number '5160 - Address.'

 

Now we are basically going to go down the line of the toolbar buttons.  Although the order we use is not critical, there is no reason to get creative.  Select Open Data Source.  Navigate to your contact list and click Open.  Under the select table dialog box, select the named table 'ContactList' created previously and click OK.

At this point we have connected the two files but not shown Word how to use the connection.  The Document will show <<Next Record>> for each label. 

Now we want to get specific.  Click 'Mail Merge Recipients.'  This will give us access to the data from Excel contained in our named range.  Here we select only those people we want to include in our list of labels.  For our example we will leave the default (all.)  This is the point that you run into trouble if you haven't constrained your list to a finite range.  You will get thousands of blanks.  

Now we want to build the label.  The label can contain a combination of text and fields.  For example, you could insert an image and type Happy Holidays across the bottom before inserting the name and address fields.  These would show up on each label.  

Select insert merge label and select each field and click insert in the order in which you would like them to appear:  First, Last, Street, City, Zip Code.  If you have named your headers in Excel using non standard expressions that Word cannot reconcile into common field names then you can click 'Match Fields...' to double check or correct translating your Excel columns into standard Word fields.  Then click Close.

We have just inserted each field into one row.  Now we need to go through and add spaces, commas, new lines, etc.  to make the label how we want it to look.  Go ahead and change fonts at this time as well.  Note that only the first label will show the correct fields. The others will still show only <<next record>>.  

Click view merged data to populate the first label with real information.  If you are following along with my example file, then you should see the fictitious Daniel Anderson.  This is the first record (row) in our Excel list.  If it looks right, the click 'Propagate Labels' which looks like a refresh button.  This will take the information from the first master label and copy it to all of the other labels.  If you have toggled View Merged Data on, then it will display the contact information to the other labels on the sheet.  Remember to click the propagate button after any modifications to the Master label.  

Now, if you've struggled with this operation before, you might be excited and ready to print it right now.  Don't get carried away.  Think of the Word document as a set of instructions, not a final output.  All we have done to this point is set up definitions of what to do.  Notice that there is only one page in our list and that the second half of the names are missing.  We need to perform the literal Merge operation next.  If you want to print at this time, feed in your labels and select 'Merge to Printer.'  If, as often happens, you have been doing this in order to help out a less technical colleague then select Merge to New Document or Merge to Email.  This will produce a text version of the merge that would allow someone else to print the labels.  Remember, the document we created is the Master.  The merge is the output and is not linked to the Excel file.  

That's about it for the simple version.  There are many other facets and potential problems, such as what happens if you need to email the Master to someone else instead of the Output.  But you can contact me directly for help with that, because it definitely violates our simple parameter for this post!

Naming Cells and Ranges in Excel

I have had several requests for a simple, step by step post on using mail merge to print labels, envelopes, or letters.  Mail merge is one of those functions which should be simple, but still manages to cause you to pull your hair out!  

As I have stressed, the secret to effective operations such as this is to have the supporting documents properly prepared.  To that end, let's spend some time reviewing names in Excel.  Why Excel and not Word?  Because most of the time, the names and addresses that we want for mail merging are stored in a spreadsheet.    

We typically refer to an individual cell by that cell's Column and Row, such as B1.  But if a spreadsheet regularly references a particular cell in formulas, such as a tax, discount rate, or WACC (for you MBA types) then we can name that cell exactly what it is.  For a single cell or a multi-cell range the process is the same.  Select the cell(s) and then type a name (such as Tax) in the Name Box:

Keep the names concise and descriptive.  Spaces are not allowed so use underscores if required.  Consider one of the primary advantages of such an arrangement before using long names.  Because we have trained Excel that for this workbook, "Tax" = B1, then even if we have 100 worksheets that have formulas using the corporate tax rate, we can always refer to this as 'Tax' instead of 'Sheet1!$B$1'.  So if we are going to type a Name instead of a cell / range then a name like Tax is better. 

When creating, or even just manually entering formulas, it is always possible to make a mistake.  That risk is mitigated by using names.  Which is easier to spot check:

Value = FCF / (1 + WACC)
Value = Formulas!$D$4 / (1 + Formulas!$D$5) 

I mentioned named ranges on the Data Validation post.  Data Validation requires that a list be named if the range is on a different worksheet.  Named ranges are also appropriate when using VLOOKUP.  As with any information technology project, names and lists are things that need to properly maintained.  If the range is a list of active clients and project that will frequently be updated, then there is always a risk that the list will not match the definition saved as the named range.  In such a case try to name entire row or column.  Use Insert > Name > Create.  This will bring up the Create Names dialog box.  

The default assumption is that you will have names in the first row.  A good practice is to leave the second row blank.  This is because of a peculiarity in Excel - call it an undocumented feature.  When you use a range as an incell drop-down list (data validation) the selection will default to the end if there are blank cells in the list.  So imagine a list of the alphabet.  When you drill down on the cell, it will default to the first blank list.  Most people expect to see the beginning of the alphabet not the end.  

There are two ways to handle this.  On a list whose length is fixed, such as the alphabet you can eliminate the blank row 2 if you do not include any blank cells beyond 'Z.'  For a range such as ProductNo that is not fixed, you'll need to use it.  Remember, the problem would not be starting from the bottom of a list instead of the top - the potential problem is inconsistency.  If one drop-down starts at the bottom of the list, and the next starts at the top, it is annoying for the users.  

There are two ways to see a list of named ranges in the workbook.  The quickest way is to drill down on the name box.  Select any of the named ranges and Excel will take you to that location and select the cells that are defined by that name.  This is particularly handy if you notice that there are two names: 'Tax' and 'TaxRate' and you can't remember which is which.  If you find that you need to redefine or delete names, go to Insert > Names > Define which open a dialog box.

Click on a Name and the location will show in the field 'Refers to:.'  You can manually adjust the definition here or you can click on the Range button to select or modify the range in place.  Be consistent and careful with your names to ensure that there are not duplicates in a workbook.  You will not get a warning if you create redundant definitions.  

If you are working with an existing spreadsheet that already has formulas, but would like to apply names instead of references, click Insert > Name > Apply.  For instance, if our worksheet contains a formula that is our net profit * tax rate to determine taxes due then the formula will use B1 as the reference.  

After applying NetProfit and Tax, the cell references are replaced in the formulas with their respective names:

Keyboard Shortcuts in Excel

I know many seasoned professionals that still despise all keyboard shortcuts.  Some will never be convinced, but for those on the fence I want to share with you some of the keyboard shortcuts that I find most useful in Excel.

CTRL+1  This is the same as using the menu Format > Cells...  It brings up the format cells dialog box.  Just select a cell or range and a quick shortcut brings up this useful formatting tool.  While many of the formatting tools of this dialog box are available as tool buttons, there are some reasons that you are better served by going directly to this box.  Here are two:

1. Sometimes columns with headers or multiple formatting types will misbehave.  Have you ever had a column of numbers that you wanted to decrease the number of decimal places shown by one?  Most of the time, you can use the 'decrease decimal' tool button. But when your data contains more than one type, multiple decimal settings, or some types of headers, this will not work.  Occasionally, you can click 'increase decimal' once or twice until all are the same and follow it with decreasing until you get what you want.  Using the Format Cells dialog box will work every time - the first time.  
2. When you have a column of numbers that are formatted as 'general' they will not display comma separated 1000's.  If you use the comma toolbutton then you get more than you bargained for - you get two decimal places as well.  So often you find yourself using the comma, then two 'decrease decimals' to get what you wanted.  As an alternative use CTRL+1, change the type to number, drop the decimals to 0, and check the 'use 1000s separator' box.  Now the more astute may have counted and are now saying "But that means more steps."  Yes, but as mentioned above, this will always work and avoids the buggy problems you may otherwise encounter.

CTRL+;  Enters the current date.  Keep in mind that the format of the date is defined by the formatting category and type.  If you are using a fresh worksheet with 'general' category cells the format will be mm/dd/yyyy.  

CTRL+SHIFT+;  Enters the current time.  As with the date stamp, formatting will ensure that you get what you want.  Perfect for timesheets.

CTRL+SHIFT+"  copies the information from the cell above with formatting.  This is one of the most appropriate shortcuts ever - the ditto mark.  Not including SHIFT will copy the information without formatting which for dates will return a 5 digit number.  

F2 - is the universal edit key.  This is useful particularly if you are moving through a spreadsheet using the arrow keys instead of the mouse and then you need to edit a value in a cell.  Where you would normally click in the cell with the mouse, you can simply hit F2 and make your change.  If this is news to you then you are in for a treat.  Since this is a windows shortcut not exclusive to Excel, it works in all kinds of places.  Looking at files in Windows explorer? Select a file and hit F2 to rename it.  

CTRL+Home moves to the top of the worksheet.  This can be very helpful when you are examining row 57,345 and need to scroll up to the top.  

CTRL+ARROWDOWN selects the first blank cell in a column.  This works whether there are 10 cells in the column or 10,000.  Remember back before you knew this shortcut and you would scroll 3,000 rows past where you intended?  Well, Office 2007 solved that problem, but this shortcut is still faster and more reliable.  Although statistically most data is stored in columns, the same concept works for rows.  Just use ARROW RIGHT or LEFT.  

CTRL+SHIFT+ARROWDOWN similar to above but this shortcut is much more useful.  It will select all of the cells between the current active cell and the first blank.  

CTRL+SPACEBAR  selects an entire column.  Now it is as simple as CTRL+SPACEBAR, CTRL+C, CTRL+V.  Who knew you could copy and paste a column so easily?

CTRL+B,I,U  These are pretty basic and have been around since the beginning but as I was bolding the previous shortcut I decided I would be remiss if I didn't mention it.  Use B for Bold, I for italics, and U for underline.  If you have something selected then that will be changed.  If nothing is selected, then the formatting you selected will be toggled on.  

OK, there are lots more but if you memorize and master each of these you'll have a great foundation.  Just two more that are ,again, Windows shortcuts which you can use in any program. 

CTRL+TAB  this will cycle between tabs.  In Excel this will cycle between SHEET1, 2, and 3.  

And finally, ALT+TAB.  This goes back to the beginning of Windows.  It toggles between the active program and the previously active window.  Entering information in Excel, but need to verify something in another program?  ALT+TAB to switch to the last program, then ALT+TAB to switch back.  Need to switch to a different program?  Keep ALT held down and all open programs will be available.  Hit TAB as many times as necessary to select the program you want. 

Cleaning Up Your Data in Excel

A future post is going to cover comparing two lists of data.  But before tackling that problem, it is important that we understand the tools we may need to get our data ready to compare.  

It is likely that we will need to either break up values, or combine them.  In Excel and database speak these are actions are parse and concatenate respectively. 

 

An example of concatentate would be if first and last names were in separate columns.  It is easier to compare the names if they are combined into one value such as First Last or Last, First.  Both of these are simple to accomplish.  The formula method is to use =concatentate(A2,B2) which yields "Jack Smith" with a space between the names.  See below: 

  

Obviously if we wanted the last name first, we would have reversed the two entries.  To accomplish the result 'Smith, Jack' We need to tell Excel to add a comma and a space. Add a third argument to the formula ", " quotations included. 

You should know that there is a bit of a shortcut for concatenating values.  You can simply use the & symbol as in =A2&B2.  Note that in order to have spaces or commas these will require addtional arguments:  =B2&", "&A2.  

The opposite condition would be if you needed to parse a field into separate values.  Say for example one list is stored as Last, First and a second list is First MI Last.  In order to compare these two lists as we will in our next post, we need to make sure we are comparing apples to apples.  This is easier than you might think.  Using separate formulas, or a series of formulas, it is possible to break up even very complicated values.  Then you can put them back together however you want by concatenating.  Another example would be a list of driver license numbers that start with a two letter state designation such as NY-555-55-5555.  

The formulas we need to know about are Len, Find, Left, Mid, and Right.  These can be found under the text category.  For all of these functions think of the characters in the cell as being numbered from left to right.   Use Left to grab the first two characters of the driver's license: =left(A2,2).   This would return NY.  To truly parse the data into separate fields, we would want a third column that gave us the DL# without the leading state letters or the hyphen.  To accomplish this, we can use the function Len to determine how many characters are in the field (this many vary down the list) and then grab everything to the right of a particular character.  We can do this in two steps in separate columns, or combine these steps into one expression.  If you find this process confusing, use separate columns for each step.  The first step is to determine how many characters are in the field: =Len(A2) which returns 14.  We want the number without the first 3 characters, two for the state, and the hyphen.  So we could use =right(A2,11) to extract the number.  But think it through first.  We know that different states use different number formats and lengths.  So, in order to have the formula apply to all conditions we should let the number of characters needed be calculated instead of assumed to be 11.  The following modification will take care of it in one step: =right(A2,len(A2)-3).  Again, if you are uncomfortable nesting functions then simply break these out in different columns and reference the other columns to get your desired result.  This function right starts, not surprisingly, from the right side.  If we wanted the last four digits off a social security number for example, we could use right(A2,4).  

The names are a little more complicated to parse.  But if you think about how you would do it you would find the comma, figure out what digit it is, and then go left for the last name or right for the first.  Step one is to use Find:  =find(",",A2) which returns 6 for 'Smith, Jack.'  To parse the last name we would use this result with function left.  Combined into one step it would be =left(A2,len(A2)-find(",",A2)).  Once we have successfully parsed the last name, we know how many characters we DON'T want in the first name.  So we can reference the results of this previous formula.  Remembering that the first name would be the length of the combined name minus the characters of the last name, minus the two characters of the comma and the space.  So to parse the first name we use =right(A2,len(A2)-B2-1) to get the value 'Jack.'  Notice that since the value in B2 = 6 we reference the cell directly instead of recalculating this value.  This is a good example of when it is conceptually easier to break it between two columns.  You may have expected that we would be subtracting 2 characters for the comma and the space, but since the find function returned the position of the comma, the length of the first name would be one less character.  

The only function we have not yet used is MID.  We used the Right function since the name lengths vary.  If for example we had a product code that was made up of Vendor#-Category- Part#, and each part was 4 digits such as KLaA-0123-4567, then we could parse the category using =mid(A2,6,4).  

I should mention that there are many different ways to accomplish each task.  For the relatively simple examples above, you could use mid instead of right.  Then, instead of determining length by subtracting the partial characters from the total characters, you could instead use a number that is larger than you would need, like 25. Conveniently, Excel does not add spaces if this number exceeds the number of characters present.  I generally use the slightly more complicated method above because I respond to its logical and precise methodology, conceptually.  

If all of this seems like way too much work just split out some names or numbers, then you are probably not thinking of a list of 50,000 names.  Obviously if you only had 15 names in the list, it would probably be just as quick to copy and paste.  Next time when we compare data lists, you will need the skills we have developed here.  Although we will use lists that are already in the same format, in real projects most lists require some reformatting.  

Use Appear and Dim to Keep Your Audience Focused

Have you ever been unfortunate enough to sit through a Powerpoint presentation where each slide is just packed with tons of information?  You end up reading 5 minutes ahead of the speaker's presentation instead of listening to what is being said.  

The whole reason to use Powerpoint in a presentation is to better focus the audience, not to distract them.  So let's look at a simple animation tool that can make a big difference - Appear and Dim.  This animation scheme is easy to apply and a useful effect.  Here's how it works:

• On a typical slide with a title and four bullet points, The presentation will intially show only the title of the slide.  
• The first point appears with a click from the pointer.  
• The second click brings up the next bullet point along with any subpoints, and half-tones the first point to de-emphasize it and keep the audience focused on what the speaker is currently discussing.

 

It is very simple to apply this animation scheme to the entire presentation.  From the menus, navigate to Slide Show > Animation Schemes... This will bring up the Slide Design sidebar where you can change the Design Template, Color Scheme, and Animation Scheme.  

Under the Subtle heading, select Appear and Dim.  Then at the bottom of the sidebar click the Apply to All Slides button.  Notice the other two buttons:  Play and Slide Show.  Play will show the animation scheme currently selected within the normal view.  Slide Show will switch to the presentation view from the current slide (equivalent to using the Shift+F5 shortcut)

Keep Those Documents Secure!

Internet and networking security is a rightfully popular topic.  It really cannot be overstated how important it is to protect access to your data whether at work or at home.  One thing that is often overlooked is file level security.  What would happen if your laptop were lost or stolen?  Do you keep your account information in a file named Banking, or AccountInfo?  That would be pretty easy to find, wouldn't it?  Surely you don't keep a list of passwords in a cleverly named Passwords.xls do you?  

How tight you keep your security should be a function of your exposure.  At highest risk are business travelers.  A couple of recommendations are to avoid keeping critical files or links to them on the desktop, and consider naming files something innocuous.  

One of the easiest things you can do is to add password security to the individual files. PDFs are easy to secure when creating by going into the properties when saving or printing to PDF and selecting the password to open option.  Most of the files we use for sensitive information are Microsoft Office based; either Word, Excel, or Access.  Access users are usually a bit more familiar with password security.  In Office 2003 files, go to tools > options and select the security tab.  

The advanced option allows you to select the encryption type for the file.  For most users this is a non-issue.  If a real hacker has your file and intends to break it, you have bigger problems than the one file you added a password to.  Consider password protection like locking your car.  A professional car thief is barely slowed down by a locked door or a car alarm, but it does a good job of stopping a crime of opportunity.  

Enter a password in the first field and click OK.  Office will ask you to reenter the password to make sure there is no mistake.  As always, ensure that this is a password that you can remember.  Unlike when you forget your password for logging in to a company website, there is no option for emailing a password to you if you forget it.  

Now I should mention that through VBA it IS possible to add a challenge question that would remind you of your password, but since this blog is for users of applications more than programmers, I won't go there.  And one of the great things about Microsoft is that the necessary tools are right there in the software - you really don't need to know how to program to use their apps to the max.  Now go and secure those important files!

Pivot Tables Will Change Your Life (For the Better)

Last time we talked about how to get excel to format cells automatically.  This time we are going to talk about how to analyze data without messing with the original information.  So what are pivot tables?  Pivot tables are like a report that shows you only the information you want to see.  If you have a lot of information, imagine 50 columns by 1000 rows in a worksheet how can you determine what is going on in that large a spreadsheet?  Pivot tables are the solution. 

There are great examples from different industries, but they might be difficult for everyone to relate to.  For something universal, let’s look at customer orders.  To keep it simple, we’ll say that the order list has a couple of simple columns:  Customer, State, Product, Date, Month, and Amount.  Obviously this is a gross simplification, but once we know how easy pivot tables are, we can easily expand it.  So below we have our fictitious orders.  To download a copy of this spreadsheet to play along click here: Download PivotTablesExample

Once we have a more than a couple of screens of data its difficult to see where the money is coming from.  To get started, all we need is a worksheet that has labeled columns.  In order for excel to recognize a header row, all we need to do is have the labels formatted a little differently.  For our example data I used Bold text and a larger font than the table information.  If we have a properly formatted worksheet, then excel is smart enough to select everything in it.  We can just select one cell in the table and even if there are 5000 rows, and 5000 columns excel will select the full extents of table.  So, assuming we’re all using the same data, click a cell in the middle of the table, such as C10, then click Data > PivotTable and PivotChart Report…

We're going to accept the default options.  Click Next.  Verify that the selection box grabbed the entire table then click Next.  Click Finish.  

This will create a new worksheet with a blank pivot table.  Although it is possible to do most of arranging and  formatting within the pivot table wizard, I find it just as easy to do after the fact.  It is so useful to change pivot tables around that we need to get good at it.  

So from the Pivot Table Field List select Month and drag it into the area labeled 'Drop Row Fields Here.'  Now we'll use a different method.  Select State, verify Row Area is selected, then click Add To.  Then using either method add Customer.  If you drag and drop, be aware of exactly where you drop it into the row area.  A vertical gray indicator line will appear where the field will be placed when you let go of the mouse.  Make sure you drop it to the right of State.  

You see that the pivot table automatically sorts the rows from left to right.  So all of the August (shown as 8) data are grouped together.  Same for States.  Notice that there are automatic totals for the Months and States.  If you don't want these, right-click in any total, such as CA Total and select Hide.  

Now let's add some actual content.  Add the Amount field to the data area.  This will give us the total revenue broken out by the finest category (right-hand field in row area) which in this case is by Customer. To change the format of a pivot table item, avoid the temptation to just select the cells and format as usual.  Since Pivot tables are dynamic in nature, we would lose this formatting if we changed or updated the table.  So instead, right click on the grey header that reads 'Sum of Amount' and select field settings.

Here you can change the display name of the field, and the formatting.  Click the number button bring up the familiar list of data types.  Select accounting and change the number of decimal places from 2 to 0.  Click OK, then OK again to close the field setting dialog box.

Now a bit of advice.  Don't worry too much about messing anything up in a pivot table.  With a little practice, you'll be able to recreate any pivot table in about 60 seconds.  Additionally, even if you can't seem to get it to do what you want just know that there are ways to do almost anything that you want.  Give it time and practice, practice.  If you get stuck - ask for help.  So let's get right on in there and mess with it!

You show the pivot table to your boss, and she points out 2 problems:  

1. There was only one sale from July listed here, and September isn't over yet.  So, in other words - we only want to see items from complete months - August.  
2. She doesn't like using numbers, like 8 for months.  She wants the text 'August' instead.     

No problem.  We'll fix the labels first.  Select A5 (click on the 7) and type 'July.'  Do the same for August and September.  Excel will remember that for the field Month 7 = July. This is an important concept in pivot tables.  We don't have to change the original data which we may or may not have control over.  Think of output generated from another software program such as your ERP system.  We change the pivot table, and leave the data alone. 

Now for the second request.  Notice that the grey header of Month has a drop down arrow similar to when you filter columns.  Drill down on Month, uncheck (show all), and check 8.  Click OK and now we are pivoting only the August sales.  

Get the idea?  Now for some major changes that are very easy.  To change the pivot table to show only the data from Florida, we can grab the grey State header and move it to the left of Month.  Then we drill down on State, deselect (Show All) and select Florida. 

Now get rid of month.  Simply select and hold the Month header and drag it out of the pivot table.  When you are in a safe place to drop it, the mouse pointer will change to an X.  

To show the information in another way, and trust me, you'll get plenty of special requests when you start showing your team your pivot tables, add Product to the column fields area.  Remember that you can drag and drop or use the Pivot Table Field List to accomplish this.  Now add month to the column fields.  Since nothing was sold in Florida in July, add the State of Maine to the list.  Deselect month August just to make the table smaller.  Did you notice that Excel remembered that we wanted to see July instead of 7 for the months?  Pretty slick.  

The only basic area of the pivot table that we have not used is the page fields.  Page fields are global.  You use this when you want to isolate just one item from a field.  When we moved State to be the leftmost row and selected only Florida to show, we could have instead put the month field into the page field area and selected Florida.  The page field is not designed to multi-select.  If you wanted to see customer 256 and customer 199, then put customers in the row area.  If you only wanted to see customer 256, page fields work great.  A very common use for page fields is pivot out the information by month to generate monthly reports.  

Here are some keys to keep in mind when creating and maintaining Pivot tables:

• Although you are used to formulas recalculating as soon as the precedent cells are changed, pivot tables do not automatically update.  To update a pivot table, click refresh on the pivot table toolbar.  It is possible to make pivot tables automatically refresh but it requires VBA.  Contact me if this the type of support you need.
• The pivot table toolbar has important tools that you need to know about.  Particularly handy are the built in formatting and the button to re-display the field list if you inadvertantly close it.  
• Most of the spreadsheets I create for clients have at least one pivot table.  Once you start regularly using pivot tables you will find that you change the way you build your spreadsheets.  What I find is that as soon as someone wraps their head around how to best incorporate this tool, they think about using Excel much more like a database than an accountant's ledger.  
•