Guest blog by Mynda Treacy of My Online Training Hub.
If you spend hours updating Excel reports each week or month then I’m sorry, but you’re doing it wrong. It shouldn’t take that long.
All you need are a few key rules for setting up your workbooks and in most cases you should be able to simply import your new data and the report will update. Job done!
Here are 5 rules that will free you from the drudgery hours before your peers.
Rule 1: Lay your data out right first time
The ideal format is tabular. That’s where each column houses one type of data, and each row houses one record like this:
This is your database. From here you can create any report quickly and easily. Also avoid blank rows or cells in your data.
For bonus points format the data as an Excel Table and use the structured references to build formulas quickly and easily. Excel Tables automatically incorporate new data into the table and auto fill any existing formulas.
Rule 2: Named ranges
Naming ranges allows you to build formulas quickly and makes it easy to understand the formula when you come back to it months later.
Use the OFFSET function to create dynamic named ranges, or simply format your data in an Excel Table as suggested in Rule 1.
Rule 3: Dynamic, dynamic, dynamic
Reports should be dynamic. By dynamic I mean they should automatically incorporate any new data without the need to update formulas, or PivotTable source data ranges and the like.
Use Data Validation to allow you and the report recipient to quickly change the filter for the report. If you produce a monthly report then have a data validation list that allows you to select the next month. This data validation list is linked to your formulas so they dynamically update.
If you use PivotTables to analyse your data then set them up so they automatically refresh when you add new data to the source. Don’t do this if you have loads of PivotTables in the file as it may kill it!
Rule 4: The 3 Stages
Be disciplined about building your reports. You should have 3 types of worksheets in your file:
Stage 1: Your database – lay it out in a tabular format as per rule 1. Don’t start manipulating your database. If you change the layout of your database then you make it difficult to do any other analysis later on. Leave it as is (in a tabular format) and do your analysis on another sheet.
Stage 2: Your analysis – keep it together and document well. Trying to understand the web of formulas months after the report was created can take longer than creating the report again from scratch.
- Create a document map, showing where the data comes from, how it feeds into the analysis
- Shade cells that contain formulas
- Use comments to document the logic.
Stage 3: Your report or Dashboard.
These 3 stages are exclusive, don’t cross contaminate data with analysis and reports with data and vice versa.
Sometimes you might need more than 3 worksheets for the analysis, and you might have more than one database, but keep your report on one page with nothing else.
Rule 5: KISS (keep it simple stupid!)
This is more for the person receiving your report than you. Keeping it simple means don’t go getting fancy with formatting and detract from your message.
Understand the objective for the report and the audience, and use placement to emphasise the most important data instead of overpowering formatting.
Big design no-no’s are:
- 3D charts
- Garish colours or too many colours
- Too many conditional formats used together
Mynda Treacy is co-founder of My Online Training Hub. Her Excel Dashboard course will transform the way you build your Excel reports and teach you techniques that will take your Excel skills to the next level.
Her previous classes have received rave reviews:
“Without question, this course has caused me to re-evaluate how I am building my reports, charts and graphs.”
“Your Dashboard course has already saved me a lot of time! I haven’t done my own dashboard yet but I have used some of your tips and presented them during meetings and people were really positive (some even impressed ).”
“I have taken a whole new approach to producing my monthly reporting pack for the Executive Management team using the techniques and tips learned in your course so far.”
If you want to take your Excel skills to the next level to get your dream job, that elusive promotion, or just get your work done faster, do yourself a favor and click here check out Mynda’s course.
Trackbacks/Pingbacks