As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports. Reports are essential to know the level of the service:
- Bad results mean that somethingis not correct and quickly actions have to be taken.
- Good results means all OK but it can be improved. There is always something to improve, even if it is small thing as an ant so I always look into it.
Depending of the time I am spending doing reports, I am dedicating less time to do improvements so the optimization is an essential part when I work in my reports.
Microsoft Office provides a good tool for that, I am sure that all of you are using excel but how many of you are using the correct formula or a way to automatic everything with a macro ??? Everyone can put formula but the correct formula to optimize the best result, how much ??? How many people are using the macro function to create the report in one single click ??? How many people are using the “name manager” option to update automatically all charts ???
Of course, every option has its pros and cons, it is just to find the right balance. When I started to use excel, I was using formula but with the time, trying to find a way to optimize the report for a better performance and to save time to dedicate for other things, I learnt that using the correct one is essential and especially, using the macro so in one click, everything will be done in an automatic way.
I spent a lot of times to create my macro or formulas but I have to see it as an investment in long term and not in short term. For instance, you spend 5 minutes every month to do copy and paste manually but with a macro, it will take you 5 seconds to do those 2 actions automatically.
The advantage of a macro, as you can guess, is the speed. The other advantage is that everything is correct because if you are doing a manual copy, you can miss one cell but not with a macro.
The disadvantage of a macro, as said earlier, is that you have to spend time to make it at the beginning. The other disadvantage is the maintenance, each time that you change something in the sheet, you have to update the macro manually.
It is the contrary of a formula, a formula will update automatically if you do a modification in the sheet. Of course, you can put formula in the macro but every modification in the sheet should be updated manually in the macro.
Depending of the report, I use either the formula or the macro or both. If the report is always the same, I am using macro. For instance:
- A SLA report, there is always the SLA and the same team
- A CMDB report, there is always the same title but the data can be less or more depending if new CIs have been added or old CIs deleted.
If the report will change every month, use formula or both. For instance:
- A quality report, the number of titles will change every month so formula
- A problem report, main categories always present and other categories will change every month so macro for main categories and formula for the others.
Whatever the kind of reports, the priority is to optimize the time to create them so I can dedicate more time for other managements.
If you are new in excel to create formula and/or macro, it will hard at the beginning and you will feel frustrated to not get the right result you want (I know this feeling because I went through it) but with obstinacy, you will get it. Just used any help that you can find. In my website, I share some formulas and macro that I used for my reports, if you can’t find the ones that you need, search in internet, I am sure that you will find it.