Published January 28, 2019
In a recent LinkedIn survey (as referenced by CNBC) the biggest issues employees faced in 2018 were managing their workload and finding a work life balance. Why are employees so stressed? Companies today are asking their employees to synthesize data and draw conclusions to help drive the business at a more regular and exhaustive rate.
As employees are asked to investigate and then manage more and more everyday issues, wading through mounds of data to understand the details and find a solution becomes a critical and laborious task. While, we have been blessed with a robust tool (i.e. Excel) for such one-off requests, ensuring that the analytics provided are controlled and accurate can be a daunting task.
In the ensuing discussion, I provide insight into the little tips and/or tricks that I have picked up over my career. These thoughts/approaches ensure that the analysis is robust, controlled, and easily presented to the end requester.
- Repeatability: Plan for the work to be completed over and over again. Take the time to “do it right” the first time. Expect “what if” scenarios to be requested and plan for data to be refreshed as required. Plan for things to change in your approach while you design the model and you will have the ability to turn on a dime and provided valuable insight and analysis.
- Variable Set-up: Create a tab that manages all of your high level attributes (e.g. names, dates, attributes, analysis type, etc.). This will ensure all titles and critical variables are changed from solution to solution and reference data is spelled correctly and easily updated.
- Data Validation: Set-up critical variables that will be used as reference data using the data validation functionality. This will ensure critical reference elements are all spelled the same way and that summaries and analysis is holistic.
- Named Ranges: When referencing data, name your ranges. This helps to understand where the data is coming from and what it represents. It really helps in error tracking later on. Avoid one off references where possible and bring back data by referencing the data type as required.
- Look-ups: Always use the Excel look-up approach (VLookup and HLookup) to reference data. This ensures that data will always be referenced properly, particularly as lines/columns are added/deleted to spreadsheets. Many do not understand that the column based process (VLookup) can also be used for row based referencing (HLookup).
- Pivot Tables: When summarizing a large data set, use pivot tables to collect the data. This will ensure that all data is included in the summary. It helps to highlight data that may be “lost in the cracks”. This is a good way to visualize the detailed analysis and “catch” errors in the approach.
- Error Checks: Always use the =If(iserror())) function to deal with conditions that cannot be handled when using the look-up reference. This ensures that the referenced data is actionable and is handled. If you fail to use this approach, you will receive “REF” results that immobilize summaries.
- Record Macro: When you perform a repeatable task, simply turn on the Macro Recorder functionality and have excel record your steps. Once recorded, you can invoke the steps at the call of a command. This will eliminate repeated tasks that take a lot of time and do not stretch the analysis. Use your valuable time to think through the analysis, not repeat clerical tasks.
- Presentation: Think of your output as you create the model. Make sure your work is presentable and collected in a meaningful way. This helps ensure you lay out your thought pattern and you are preparing to explain to others what you did, so it is clear. If you do the formatting as you go, delivering the output at the end is a lot easier.
- Formatting: Use solid lines, double lines, bold lines, and dashed lines to help the reader see the data.
- Bold Lines: Used to separate thoughts/concepts. It creates a collective box around a large concept of focus areas and understand what items drive the thoughts/concepts.
- Solid Lines: Used to separate a focus area within the thought/concept. Helps the reader to see the details that drive focus.
- Double Lines: Used this when you are summarizing data
- Dashed Lines: Used to separate lines within a focus area for ease of reading the information
Bringing structure and planning into your business modeling helps remove your stress and allows you to turn on a dime as required.
If you recognize that you are creating a one off, custom program by completing your analysis, it will help you to recognize the thought and efficiency you will want to design into your spreadsheets. Do not let yourself get lulled into the thought that it is merely a workspace and nobody will ever see your work. If you assume your analysis will be sent to the president of the company, you will take the time, diligence, and thoroughness so the effort delivers over and above the request.
If you take the prescribed approach, your work will deliver insightful results and become critical to the business. It will help you receive the recognition your desire. Just remember, “stars do not beg the world for attention; their beauty forces us to look up”. Make your work stand out like the brightest start in the sky.