Loading component...
At a glance
The 7 Habits of Highly Effective People by Stephen R Covey is a classic twentieth century business and self-help book. In 2011, Time magazine included it in its list of 25 Most Influential Business Management Books.
The habits taught within can be applied to creating a new or updating an existing Excel file, and at different levels of detail.
This article is a bit different from usual, and looks at applying these habits to a new Excel project. Consider using artificial intelligence (AI) in the process.
Understand the task
This habit requires gaining a good understanding of the required outcomes. Some useful questions to ask at this stage are:
- Who is the Excel file for?
- What is it for?
- How often is it required?
- Are comparisons required? If so, compared to what?
- Is the output a report or a dashboard or a combination of the two?
- How much flexibility or interactivity is required?
Prioritise the outcomes
The required outcomes then need to be prioritised. What is the most important? What must be included and what are the nice-to-haves?
This habit requires some pushback to focus on the most important outcomes. It may also require a few iterations between this and the prior listed habit to arrive at the final requirements.
Some outcomes may need to be delayed or scrapped. Further evolutions may need to be planned for in future to handle lower-priority requirements.
Once complete, the user can create a high-level specification of the requirements. Review the specifications with an AI system to illicit other ideas or things to consider.
Focus on the end goal
Once the outcomes are determined, the source data needs to be identified and analysed. Is the source data fit for purpose?
Excel’s Power Query can be used to fix any data sets that are not fit for purpose. Most AI systems can assist in writing more complex Power Query code.
At this point, it may become apparent that Excel may not be the final solution. Power BI may be more suited to providing the report or dashboard. Excel has Power Query and Power Pivot which are both part of Power BI.
Even if Power BI is the final solution, Excel may still play a part. It could be used to prototype and test solutions that will later be built with Power BI.
If there is doubt about whether the outcomes can be achieved, Excel could also be used to develop a “proof of concept” model.
Excel’s flexibility and fast turnaround times can make it useful in iterating towards a solution.
Plan ahead
Once the data is ready, this habit will determine how to provide the solution. This could involve combining Power Query, formatted tables, PivotTables, the data model, dynamic arrays, formulas and functions, custom functions, charts and conditional formatting.
Visual Basic for Applications (VBA) macros may also be used, but some organisations restrict them. Note that VBA macros only work on the desktop version of Excel. Macros can automate and speed up tasks while reducing manual processes and errors.
This habit requires anticipating and avoiding errors, which means making sure data entry and selections are as easy as possible. This may involve using drop-down selections.
Error handling in formula and macros is also required. For those errors that occur, there need to be validation checks and notifications to alert users of any issues. This habit also requires:
- testing the solution
- creating instructions to help users
- documenting the solution for future developers.
AI could be used to assist in these tasks.
Synergise
Many of Excel’s features and functions are synergistic. Using formatted tables to hold data and options is best practice. Features like PivotTables and dynamic arrays, and many other Excel functions work seamlessly with formatted tables. Power Query produces formatted tables and can also directly populate the data model which drives PowerPivot.
Be aware that the data, VBA macros and custom functions may be reuseable in other models, saving development time for other solutions.
Stay sharp
This is about lifelong learning and applying those new skills.
Excel has introduced many useful tools in the last 10 years that change how spreadsheets are built. Unfortunately, many users are still using Excel as they were in 2010.
Using the correct tool for the job is important. The more tools you have in your Excel toolbox, the more options you have when building solutions.
AI is a useful learning assistant. It can review and summarise learning materials and even ask questions to test your knowledge.
Make sure everyone wins
This last habit is more about building relationships and negotiation strategies. You may need to apply its principles if there are competing or limited resources. It does not apply directly to building spreadsheets.
Applying the seven habits to your spreadsheets can help create better, more robust and flexible user-friendly solutions.
