Your Remote Business Partner

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:

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.  

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.  
•   

Data Validation in Excel Enforces Consistency

Have you ever sorted or filtered a spreadsheet only to find that some of the entries have the state of Florida listed as FL whereas others used FLA and some even typed the whole F-L-O-R-I-D-A?  Whether it is a project name, dates, or simply an inconsistent labeling, these types of poor practice happen all the time, especially when multiple people use the same spreadsheet. 

Part of the problem is that Excel really is not a great database and does not by default ask the spreadsheet user to declare what type of data each cell will contain.  Also, people tend to use the office suite only to the extent that they are comfortable with each application - have you ever received a letter from someone in Excel instead of Word?!!  We really should be asking ourselves if the document we are working on is in the appropriate format.  A good rule of thumb is to ask whether any calculations are being performed.  So in other words, are we adding column D + E and then dividing by column F and storing the results in column G?  Are we using any formulas at all?  If not, then we probably should be using Access instead of Excel.  If it is a contact list - that's an Access project, not Excel.

Assuming that we must use Excel for the task, let's look at ways to make sure we have everyone in lock-step.  For our example, we're going to think of a project-based firm that has customer names and project numbers.  First, we should make sure that we are conceptually thinking about how we set up the spreadsheet.  Think of a painter with a palette full of different colors of paint in one hand, and a canvas that is going to have the paint applied to it in the other.  Instead of colors, we're dealing with information, and instead of palette and canvas we have different worksheets.  Our palette will be named "Names+Formulas" and the canvas will be named "Data."  

 (Note I have colored the "Names" tab grey)

In the Names worksheet create two lists in columns A.  Label the column in Row 1 as Customer_Name.  Never use Spaces in headers.  For this example add fictitious customers: Zoe, Blackberry, Trump, and Newcastle.  Now that we have our current customer list we can name this range.  Let's assume that we expect to have some more customers in the near future so we will include a couple of blanks in the range. 

 

1. Select A1:A11 making sure to include the header name as well as the blanks for future names. 
2. On the insert menu select Names > Create
3. Ensure that the default Top Row is selected and click OK
   

This has trained Excel that in this worksheet, the list Customer_Name = worksheet (names+formulas) range A2:A11.  Now switch over to the Data sheet.  Lets assume we have a nicely formatted document set up that we are using to track our customer orders.  Column B will have the customer name.  To add a pulldown:

1. Select column B then off the Data menu select Data > Validation...
2. In the settings tab under Allow, select List
3. In the source field type: =Customer_Name
4. To prevent people from entering anything not on the customer list, uncheck the box beside Ignore Blank.

If you click OK and click in cell B3, a drop down arrow will appear in the corner of the cell.  Click this arrow to drill down and show the list of customer names to choose from.  A user can either select from this list or type in the name directly.  If you have unchecked Ignore Blank, then only the names from the list can be entered.  If you have checked Ignore Blank then the drop down is only a convenient reference instead of an absolute restriction.

The other two tabs in the Data Validation dialog box allow you set an error and input message.  If you are not ignoring blanks then it is good practice to set an error message explaining what to do if the user needs to add a name to the list.  Much like when you are using a program and a message pops up saying 'you do not have access to this location, contact your system admistrator' we can inform our users about what the proper method to add a new customer will be.  If everyone who has access to the spreadsheet should be allowed to add to the customer list it is simple.  Just reference the list on 'names+formulas' something like below:

Simple enough?  Great.  Repeat the same process for project numbers or other important items that can no longer be entered willy nilly.  In a future post I'll show you how to set it so that if you select customer A, then under project number only the projects (or order numbers) of that particular customer are available.  Now that's using data validation!!!

Now for some caveats:

• Don't overuse your new favorite feature.  Remember that simpler is usually better, and overconstraining anything is annoying for the users.
• Think hard about whether you need to use the input message or not. Most of the time it will be pretty obvious what needs to go where. And remember what is helpful the first time can rapidly become uneccesary and annoying if it is frequently used.
• Constrain only as far as is necessary and consider all your options. In the introduction I mentioned the example of state names. Instead of creating a list in Names+Formulas of all 50 states or their abbreviations consider some of the other validation options like text length instead of list. If you set the textlength | equal to | 2 it will have the same effect as having a long list in a dropdown of abbreviations.

• Remember to update your named ranges. If you add a customer name beyond A11 it will not appear in the drop down, and will trigger the error message until the named range is updated. Be proactive and maintain your spreadsheets when other people have to use them. Most people are not going to care why it doesn't work, they just want it fixed!
• Consider who should have access to setting up standards and maintaining lists. I typically will set up the names+formulas sheet, and then either hide it or protect it with a password. Your situation and both the number and skills of your users will inform this decision for you.
  • Test, test, and then test some more before you release your fancy new workbooks. Instead of assuming that you have all the angles, try actually inputting 20 or 30 records and see:
    
  • Have I overconstrained to where it is impossible to enter a record?  
  • Is it annoying or unintuitive to use?  
• Get help when you need it. 

Conditional Formatting in Excel

The bigger your spreadsheet gets, the harder it is to highlight the really important information or, indeed, to even find it.  For example you might have a long column of percentages and maybe they change frequently as you update the supporting information.  Wouldn't it be great to be able to track these changes at a glance?

Or, maybe you've even gone down a list of information and highlighted positive numbers with a green background and maybe negative numbers with red.  How would you feel if Excel would do that for you, and update it instantly if the numbers changed?  It can using a tool called conditional formatting... (and it’s easier to use than you might think!) 

Let’s get started with an example and then expand on some of the possible uses.  Look at the simple table that compares fiscal year 2007 to 2008 below:

 

Here we're interested in comparing this year’s results to the previous year.  Again, keeping it simple, our results can be up, down, or level. 

1. Select cells D2:D6
2. Clicking Format > Conditional Formatting… brings up the following dialog box:

Any changes we make will apply to all of the selected cells. 
For the first condition make the following changes:

Cell value is, greater than, 0

Note that when ‘greater than’ is selected in the second drop down Excel deletes the 4th field. 

Now that we’ve identified the first condition (regions that improved) we need to tell Excel how we want these to look.  Click the button to bring up the familiar formatting choices that we typically use for formatting cells.  Click the patterns tab and select a green color of your preference, then click OK.  Back in the Conditional Formatting dialog notice that the preview of condition 1 now shows a green background. 

Click the Add>> button in the Conditional Formatting box to add a second condition.  Following the same logic, change the values to:

Cell value is, less than, 0 and format the pattern to be red.  This time click on the fonts tab and change the font color to white.  Click OK and review your preview to make sure it looks right.

Click OK and Excel evaluates whether the cells meet either condition 1 or condition 2. 

Notice that the Mid-Atlantic region did not change year over year so D3 is unaffected by conditional formatting because it does not meet condition 1 or 2.  You may be tempted to add an ‘equal to’ 0 as a third condition but this is unnecessary.  You’ll quickly find that there are only 3 available conditions in conditional formatting which can limit some of your fancier ideas.  In another post I'll demonstrate some really advanced examples and even how to edge out a 4th condition. 

For now, we’ll add just one more level of complexity.  You notice that the first pulldown in the condition definition gives you the options of ‘cell value is’ and ‘formula is.’  If we want to highlight the entire row of the region that had the biggest increase then we use ‘formula is’.  And that’s the way to think about it.  If you want to format the cell that you are evaluating like we did in column D then use ‘cell value is.’  Any time that you want a cell to change based on the information contained in a different cell, use a formula.

I have to admit though, as nice as this is, it really does get a bit more complicated, and usually requires a couple of tries to get perfect.  And that’s why we’ll leave this example for our next post...