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!

113 thoughts on “Seven Habits for Highly Effective Excel Planning”

  1. You want me to think about spreadsheet habits the day after new years eve?!

    OK, you make apoint. I am always surprised at what I did last year. We move onwards and upwards. Hopefully.

    Happy new year to all

  2. Hello Kien
    Thanks for your help on making our planning tool.
    I can now proud to say that I use all these habits and it makes our lives many times easier! The template is great and runs nicely with the tool and others we build with it.

  3. Pingback: Excel Planning Systems: Exploding Tables – a Method For All Planners

  4. Tip 1 is too obvious, or so I thought. So I took a look at several spreadsheets I developed recently. They are for the same purpose but they are all different. Thanks for the tip.

  5. Pingback: Seven Habits With Effective Do Well Planning | Sourcing

  6. Pingback: What Aspects Of Production Planning Should You Consider? | Wardrobe Ink

  7. Pingback: The Aspects Of Production Planning | The Patriots For Truth

  8. Pingback: What Is Considered In Production Planning? | Tugce Makina

  9. Pingback: The Aspects Of Production Planning | Promocion 90

  10. Pingback: How You can Employ Better Planning in your Production Business | Maqui Magic

  11. Pingback: How Does Production Planning Work? | Concours Online

  12. Pingback: Enterprise Automation by using Excel | Cheap Degrees Online

  13. Pingback: The Aspects Of Production Planning | Traveling Queen

  14. Pingback: Excel as a Business Automation Program | Dream Center Peoria

  15. Pingback: Small business Mechanization using Excel | Jones Base Ball

  16. Pingback: Enterprise Mechanization through Excel | 01 Times

  17. Pingback: What Aspects Of Production Planning Should You Consider? | Healertom

  18. Pingback: The Aspects Of Production Planning | Remove Av Security Suite

  19. Pingback: What Aspects Of Production Planning Should You Consider? | Banner Ads Guide

  20. Pingback: Automating the Business with Excel | Government Job Matrix

  21. Pingback: What You Should Understand About Production Planning | Montgum

  22. Pingback: Automating the Small business with Excel | I Heart Violence

  23. Pingback: Automate your Enterprise with Excel spreadsheets | Smooth Jazz Mag

  24. Pingback: The Aspects Of Production Planning | Creative Youth Theatre

  25. Pingback: The Aspects Of Production Planning | Dellen Bach Rudolf

  26. Pingback: The Benefits from Production Planning Excellence | Iphone Vt

  27. Pingback: The Aspects Of Production Planning | My Daughters Voice

  28. Pingback: Systemize your Enterprise with Spreadsheets | Voice For The Unheard

  29. Pingback: What You Should Understand About Production Planning | Baird Jones

  30. Pingback: Automating the Business with Excel | Walsh Web Design

  31. Pingback: Systemize a Business enterprise with Excel spreadsheets | The International Playmates

  32. Pingback: Benefits of Production Planning for Small Production Enterprise | Virtual Worlds Jobs

  33. Pingback: What Aspects Of Production Planning Should You Consider? | Avr Video

  34. Pingback: Excel for a Business Automation Program | My Daughters Voice

  35. Pingback: What Is Considered In Production Planning? | Zilisch Transport

  36. Pingback: Business and Production Planning – An Introduction | West Life Persian

  37. Pingback: How Does Production Planning Work? | Hops And Gnarly

  38. Pingback: Automating the Business enterprise with Excel | Rede Explicadores

  39. Pingback: Enterprise Automation using Excel | Ry Wire Mesh

  40. Pingback: The Aspects Of Production Planning | Rumbas VIP

  41. Pingback: The Aspects Of Production Planning | Casamia Homes

  42. Pingback: Enterprise Mechanization by using Excel | Bleuciel Creations

  43. Pingback: Automate a Business with Excel spreadsheets | Pasta Degohan

  44. Pingback: The Aspects Of Production Planning | Elliot Must Go

  45. Pingback: Excel as a Business Automation System | Zhang Changlin

  46. Pingback: Automating the Business with Excel | Brighton Adventist Academy

  47. Pingback: Advantages of Production Planning for Small Manufacturing Firm | All Sun Glasses

  48. Pingback: Automate a Company with Excel spreadsheets | 0769r

  49. Pingback: Automating the Business with Excel | Walla Walla Brewers

  50. Pingback: Automating the Business with Excel | Alloccasions Child Care

  51. Pingback: What Is Considered In Production Planning? | Youtube Music Free

  52. Pingback: The Aspects Of Production Planning | Find Me A Wife

  53. Pingback: Automating the Business with Excel | Wine Cue

  54. Pingback: The Aspects Of Production Planning | MadBugSEO

  55. Pingback: The Aspects Of Production Planning | listabusiness.info

  56. Pingback: How Does Production Planning Work? | Internet Marketing Home Study Program

  57. Pingback: The Aspects Of Production Planning « Business Accounting Payroll

  58. Pingback: What Aspects Of Production Planning Should You Consider? | Youtube Converter

  59. Pingback: What You Should Understand About Production Planning | atv yamaha information

  60. Pingback: What Aspects Of Production Planning Should You Consider? | Accounting Help Online

  61. Pingback: What You Should Understand About Production Planning | Software

  62. Pingback: What You Should Understand About Production Planning | Wealth and Happiness for Ever

  63. Pingback: What Is Considered In Production Planning? | Iwebpreneur

  64. Pingback: What You Should Understand About Production Planning | Youtube Converter

  65. Pingback: What You Should Understand About Production Planning | How Do I Find A Movie

  66. Pingback: What Aspects Of Production Planning Should You Consider? | freeComputer Game Downloads Full Vversion

  67. Pingback: What Is Considered In Production Planning? | Video Tours

  68. Pingback: How Does Production Planning Work? | Accounting in MYOC

  69. Pingback: What Aspects Of Production Planning Should You Consider? | Elections 2012

  70. Pingback: Smart System 2020 » Blog Archive » The Aspects Of Production Planning

  71. Pingback: What You Should Understand About Production Planning | C Compiler Download

  72. Pingback: How Does Production Planning Work? | Wealth and Asset Management

  73. Pingback: What Aspects Of Production Planning Should You Consider? | Youtube Youtube Download

  74. Pingback: What You Should Understand About Production Planning | How Do You Download From Youtube

  75. Pingback: How Does Production Planning Work? | LICS

  76. Pingback: What Aspects Of Production Planning Should You Consider? | How To Make A Free Web

  77. Pingback: What You Should Understand About Production Planning | Debt Management

  78. Pingback: What Is Considered In Production Planning? | Financial Planning Management.org

  79. Pingback: How Does Production Planning Work? | Games For Kids And Adults

  80. Pingback: The Aspects Of Production Planning | Youtube Youtube Download

  81. Pingback: The Aspects Of Production Planning | Business Models

  82. Pingback: The Aspects Of Production Planning | Youtube

  83. Pingback: What Is Considered In Production Planning? | Financial Investment Management

  84. Pingback: What Is Considered In Production Planning? | Youtube Videos

  85. Pingback: What Is Considered In Production Planning? | Utube

  86. Pingback: What Aspects Of Production Planning Should You Consider? | Youtube Youtub

  87. Pingback: What Is Considered In Production Planning? | Making Money Online With Ease

  88. Pingback: What You Should Understand About Production Planning | Youtube Video Songs

  89. Pingback: What Aspects Of Production Planning Should You Consider? | Youtube To Be With You

  90. Pingback: What Is Considered In Production Planning? | Computer Games Free Online

  91. Pingback: Upside Cafe » What Is Considered In Production Planning?

  92. Pingback: What Is Considered In Production Planning? « Altitude Right On The Money

  93. Pingback: What You Should Understand About Production Planning | Youtube Music Free

  94. Pingback: How Does Production Planning Work? | | The I.T Managers BlogThe I.T Managers Blog

  95. Pingback: What You Should Understand About Production Planning | California Commercial Loans

  96. Pingback: What You Should Understand About Production Planning | Youtube Converter

  97. Pingback: How Does Production Planning Work? | Detroit Local

  98. Pingback: What Aspects Of Production Planning Should You Consider? « My Blog

  99. Pingback: How Does Production Planning Work? | D 20

  100. Pingback: The Aspects Of Production Planning | Download Power Point Free

  101. Pingback: What Is Considered In Production Planning? » Introduction Ads Business Opportunities Blog

  102. Pingback: What Is Considered In Production Planning? | Business to Business

  103. Pingback: The Aspects Of Production Planning | Antispy

  104. Pingback: The Aspects Of Production Planning | Investment Wealth Management

  105. Pingback: What Aspects Of Production Planning Should You Consider? | Download Turboc

  106. Pingback: What Is Considered In Production Planning? | iCNSQ Tech Blogs

  107. Pingback: What Aspects Of Production Planning Should You Consider? | Visual Studio 2005 Download

Leave a Comment

Scroll to Top