Microsoft Excel Productivity Tips
7 the type of report that is killing you slowly, inside out!
Before we begin this article, take a look at the report as shown in the figure below. If you have had create such report by merging cells and manually format every single cell, I bet you have no life. I guess you have been spending most of your time VLOOKUP’ing, format painter’ing everything, which is not productive at all. This report is also static and dead, which means that whenever there is a new set of data for upcoming months and years, this report does not automatically update. Instead, you will have to create more merge cells to the right.
Realize that the report shows above contains many merged cells? Check out Deadly sins Part4, I talked about why merge cells are something you should not be doing any more in Excel.
If you’ve been creating such report, guilty for that, continue reading. Check out what are the troubles you’re gonna get if this goes on and on.
Whenever I’m teaching or coaching PivotTable related module or courses, I always like to pull out this report and ask my student, “is this how you create report?” 11 out of 10 people would admit to this sin. To create this report, you will spend most of the time highlighting cells, merging cells auto-filling months, dates and formulas. Do you know that you usually spent a few hours just to create such report? Slowly this has becoming a routine, perhaps monthly. In fact, many people think this is how a report should be done. What I always believe is productivity and routine can be automated. Spending time on routine is just not productive at all. So, you must be curious now, how can this report be automated?
Some must be saying, “using VBA”. Well, it really depends. In this article, I’m not going to touch about VBA as I assume not everyone are tech savvy. So, I will show you some simple steps on how this report can be automatically updated without any lines of codes.
I have once been asked a question, how do we filter sideway? If you understand why this question was being ask in the first place, you know it is referring to the report shows above. So, what if you’re being asked to create a report showing only Q1 from Year 2010 to 2012. Majority of the time the answer will be cut and paste the columns for Q1 and compile them into a new sheet. Some may say I will choose to hide those columns which are not Q1. Well, both solution sounds pretty fair to me because at the moment you only have to deal with 3 years. Give it a good thought, what about you are asked to prepare a report showing only the Q1 for 10 years period of time? What about Q2? Which means there will be endless cut and paste or hiding unhiding columns? Do you see the problem now? Unproductive!
Such report should be created using Pivot Table tools. Of course, in order to use Pivot Table, your data has to be arrange in Table form. Every data should stay in its own column. A sample of table shows as below:
This is how your data should be arranged. All data are being separated in each column and properly labelled by its header. You will then create/ Insert Pivot Table using this table as data source.
After the Pivot Table has created, now you have the blank canvas to play around with. Drag the fields into the Row/Column/Value fields as needed, your report should finally come alive.
Whenever there is new data, you simply have to append the new sets of data to the table. The refresh the pivot report to update the report with the latest dataset.
Talk about filtering sideway, you can now do it by simply inserting a slicer. So, slicer is a tool which allow user to slice the data base on any criterion being selected, just like how filter works but to the whole new different level. You will be able to locate the Slicer command from the Pivot Table Tools: Analyze Tab.
Select the item that you would like to filter, and the report will instantly change its look and data displayed based on what’s selected in the slicer. To select multiple items in a row, hold on to SHIFT key; To select multiple items that are not in the row, hold CTRL key while you’re making the selection.
In case you missed out : Part 1, Part 2, Part 3, Part 4, Part 5, Part 6
