Data Jungler

Most of Excel users even at beginning level make use of Pivot Table to summarize the raw data into table-format report without coding or writing some complex formula. It is easier for Excel beginner to build their own report based on their specific requirement with user-friendly interface and well-defined format. Nevertheless, Pivot table has its limitation of functional design, which Excel formula could make up such deficiency. If you still think that Pivot-table should be the only tools for reporting in the Excel, the function of Excel formula like SUMIF, COUNTIF seems to be under-estimated. Sometimes, mainly in the working, several users abuse the Pivot Table, for instance, to call the data from the pivot table with the method of "copy and paste to values" instead of using the function: GetPivotData. That results in the complicated and less reliable process flow and thus difficulty of automation development and maintenance. In order to design a reliable and automatic r...

Application with Case Study For the example in previous articles, I bet you guys think it is not much challenging. Let's develop more complicated application! I am going to assume that you are competent to take the advantage of worksheet functions in Excel. Imagine that your boss requested you to submit a monthly report for customer analysis. Nevertheless, you now are busy enough but cannot refuse and even lose the job. Come on~poor guy! To prevent from this happen again, I strongly recommend to build a report template for next monthly reports. In this case, a lists of transaction records (Transaction.csv) and members data (Members.xlsx) in your hand. Of course, our target is to generate a complete report with raw data as soon as possible. Process Flow: Preparation First of all, to open a empty workbook and save as macro-enabled workbook (.xlsm) which allows to save the macros. Otherwise, you could not lose all macros after closing the workbook as excel workb...

Nowadays, many tools for data analytic and reporting dashboard have been well developed and taken advantage of by the analysts. Frankly speaking, I am HongKonger and have worked in several companies. The most common analytic tools in the Hong Kong enterprises should be Microsoft Excel. For the data transformation and calculation, it consists of built-in formula with aspects of logical, mathematical, statistical and financial function. Conditional Formatting also saves much time to update the formats in the summary report. I believe many people should know how to use Excel but they seldom hear about Excel VBA. Even heard that, they still think VBA is difficult to learn. In fact, the processing time of repetitive task in Excel still could be eliminated by running macro which consists of visual basic scripts. As you may know, many employees in Hong Kong need to work overtime and under the pressure. Consequently, VBA plays an important role to deal with such heavy workload. I am going ...