Seven Habits for Highly Effective Excel Planning

Happy New Year!  A time for planning and New Year resolutions.

Ouch.  The word “Resolutions” doesn’t give you good feelings, does it?  It implies that we have to act with resolve and discipline right through the year.  Following resolutions requires effort and determination.  Almost seems like they are made to be broken.

I prefer habits.  Develop the right habits and it takes an effort to break them.  We are all creatures of habit and human beings tend to do what comes naturally.  I borrow the headline from Steven Covey’s book because this difference has sold over fifteen million copies and changed the lives of many people.

Forming good Excel planning habits takes a little conscious effort in the beginning.  After a short while, the subconcious mind takes over and you cannot imagine doing things any other way.

So, in the spirit of planning and preparation for 2011, here are the seven habits for highly effective Excel planning.  Start the new year with these simple Excel techniques and you will be well on your way towards planning excellence.

1.  Organise Your Worksheets with a Consistent Layout

This sounds like dull housekeeping, yet it is the simplest one and quickest to get benefit straight away.  In the Fast Excel Development Template we always place table headings in row 10, formulas in row 8 and markers in row 7.  This way, we always know where to look and link.  You often need to put settings and formula above a table, and this method gives you plenty of rows for this.  Any more, and you probably need a table on another worksheet.

Automating Excel planning with macros is made much, much easier.  Debugging, testing and adjusting is a breeze.  And when we create a file, it is easily recognisable for others to follow what is going on.

2. Import and Connect Your Data

The alternative to importing is manual keyboard punching or cut-and-paste.  If we could cut half the hours that Excel users spend in punching, cut/copy-pasting data we would boost global GDP by whole percentage points.

The world is full of data.  Let’s keep that data in a single database and pull it in, not replicate it.  Even if you are creating the data in Excel, you can manage it by text file tables.  Force users to pull from a single source and not create multiple versions by adjusting it manually in an Excel worksheet.  Of course, connecting directly to a database with ODBC or OLE drivers can be the most direct and dynamic way.  However, text files offer a simple and quick way to create a data store that multiple users can access on a network drive.

Importing data in tables encourages you to think in terms of that “single version of the truth”.  Applying this mantra to Excel business tools will deflect much of the fear and doubt that IT types have with Excel-based systems.  Just because your Excel tool is not packaged software, doesn’t mean you cannot use software-grade data management!

3. Calculate with Tables

Humans like to see data in a matrix:  Products down rows and dates across columns.  This is the most common way that planners arrange their planning spreadsheets.  Calculations apply to multiple columns.  Cells and worksheets connect to each other with manual references.  Making changes to this takes much work and mistakes abound.

The biggest production planning mistake with Excel happens when people mix up data, calculation and reporting.

Perform calculations in a table for any planning tool or system.  You can use matrices to display data.  Just create the report in a pivot table.  How do you know if you are using tables to calculate?  Easy.  Ask yourself if the same formula is being used in more than one column.  If the answer is yes, you can probably benefit from normalized tables.

4. Place Formulas Above the Headings

This way you only need to define the formula once and apply it to a whole column of data.  The calculations are stable and you do not need to go looking for multiple versions of the same formula. Using simple macros to automate planning is much easier when the formulas are always in row 8 for any worksheet.

You can use the Fast Excel Development Template to build planning and scheduling systems.  This shows how to set formulas once in row 8 and apply automatically using dynamic named ranges.  See this Excel Development Template tutorial for an overview on dynamic named ranges and how to create them automatically with the Development Template.

5. Copy/Paste-as-Values

If you copy a formula and paste it onto another cell, you get another live formula.  Do this for a thousand rows and you have one thousand live formulas.  Repeat across many columns and sheets.  You now have a large, unwieldy workbook that is slow to calculate and prone to crash.

Here is an alternative.  Copy the formula and paste it in the target cells.  Then copy the results and paste special-as-values.  You now have calculated results that are stored as values.  The difference in workbook size and calculating speed is huge.  And you control when the calculation is made, so it can be set as part of a routine.  Again, the free Development Template gives you a way to do this and there is the dynamic named ranges tutorial to help you.

6. Keep Formatting to Reports Only

Cell formatting is often used in Excel planning tools to highlight results.  Many planners use cell borders, fill and font to group products together and indicate importance.

There is a place for visual aids to readability.  This is usually a report and not a calculation sheet.  If you leave the calculation worksheet free of clutter then it will be faster to build, manage and run.  This habit encourages you to be mindful of the difference between a report and a calculation sheet.  If users will be reading information from it, then the sheet is a report.  There should be no formula on this sheet.  If there are formula, then it should not need any formatting.

There are some exceptions that prove this rule.  However, form a habit that questions any use of formatting and you will end up with faster planning tools that are far easier to use.

7.  Automate Repetitive Tasks

Time spent typing, copying and pasting into a spreadsheet is time not spent with the customer or adding value to a product.  At Production-Scheduling.com we love Excel, but we hate repetitive Excel tasks.  This means that macros are our friend and the macro recorder is a very special buddy to anyone unfamiliar with VBA.

With a few simple tricks and templates, you can create an automated routine.  It takes little more time than it does to perform the process itself.  The beauty of Excel is that you do not need to write code to be a programmer.  A good foundation of VBA knowledge certainly helps.  However, you can get going with the macro recorder and download our development template.  I keep mentioning this because it works.  Production-Scheduling.com consultants save hundreds of hours every year using this tool.  And it is totally open and free, so there is no commitment or lock-in to a product.

We have prepared some free video tutorials to help you to get started.  The template will not make much sense to you without them, so be sure to spend a few minutes to get familiar and some time to play around with it and practice.

So, here is to a successful year in 2011.  Better habits lead to better planning.  Better planning creates more value in your business and career.  Good luck!