For giving me a chance to show you that I can prepare a special chart for you in seconds
You have sent me for upgrades and I have acquired new skills so that I can help you meet the new demands of your job.
When your peers say bad things about me, you always stand up for me and tried to prove them wrong and you did it!
I don’t have to work long hours because you know what I am capable of and made use of my capability
If things get a little more complicated, you bite the bullet and pressed on. You discovered my new skills and I am glad that I can assist you.
Whenever I upgrade myself, you take the time to discover my new skills and made use of them.
I am glad that you didn’t fire me when my competitors offered their services for free. Because you know how much more capable I am than them.
From: MS Excel
Whenever I give you the right report, you said that is expected of me.
Whenever something goes wrong, you blame me. I am not to be blamed for the mistake. I am just following your instructions.
Do you know that I am capable of many things? I bet you don’t know because you always asked me to do the same old thing.
Do you know that I am capable of many things? Just because your peers told you otherwise and you believed them.
Why do you make me work more than 8 hours a day, when you can take short cuts and finish it off in 15 minutes?
Whenever I asked to upgrade myself, you always said that it is a waste of money. Do you know that I can make your life easier after upgrading?
You always threatened to replace me, telling me that someone else do my job cheaper, even for free. Do you know that they are not compatible with other working partners?
Whenever things get slightly more complicated, you choose the easy way out.
by Voice of Excel
Whenever I give you the right report, you said that is expected of me. --> Pivot Table can produce the report the way you want it because the data source is setup in the right format.
Whenever something goes wrong, you blame me. I am not to be blamed for the mistake. I am just following your instructions. --> If your data source do not have the relevant headers and data is not arranged correctly, your Pivot Table cannot produce what you wanted.
Do you know that I am capable of many things? I bet you don’t know because you always asked me to do the same old thing. --> Most people only know how to put data into the worksheets and do simple addition and filtering. Excel has a lot of functions not know to many users.
Do you know that I am capable of many things? Just because your peers told you otherwise and you believed them. A lot of users only know the basics of Excel and do not explore beyond the basics if nobody tell them about it.
Why do you make me work more than 8 hours a day, when you can take short cuts and finish it off in 15 minutes? --> A lot of people resort to simple and paste simply because they do not how to use beyond the basic functions to save time.
Whenever I asked to upgrade myself, you always said that it is a waste of money. Do you know that I can make your life easier after upgrading? --> New functions of Excel have been added into the latest version. For example, Excel now as Flash Fill which can guess what you want to do when you give it a simple example.
You always threatened to replace me, telling me that someone else do my job cheaper, even for free. Do you know that they are not compatible with other working partners? --> Open Office and Google Spreadsheets formatting may not be compatible with Excel because of the way data is stored in each applications. If you save your data as an Excel workbook, it may not work probably. Excel in Mac is also not completely compatible with Windows version.
Whenever things get slightly more complicated, you choose the easy way out. --> A lot of users resort to simple copy and paste instead of using formulas to help them re-organize the data to the correct layout and format.
1. You have one worksheet for one year of data.
Your data for all the different years should be stored in one worksheet. If you break them into multiple worksheets, you are storing your data wrongly. What is the implication when you store them in multiple worksheets? You cannot analyze your data across different years and you will find them you are spending hours trying to comparing the seasonal patterns in a year.
2. Your header contains descriptions Jan, Feb, Mar ... Dec
Data should be stored vertically. The moment you store your data in this format, you will notice that your Pivot Table looks ugly and cumbersome. In fact, your report can be too complex for presentation. The report wouldn't look pretty and you will struggle when you need to create different views for your report
3. You have a column called day for each calendar day 1 to 31.
You have a column and that column only showed the day of the month. If you store your day of the month this way, you will not be able to make instant references to lots of information that you might need in future, such as the day of the week, week no, etc.
So what would be the correct way to present the data? You should store each row of data with a date that is presented in this format, 1 Jan 2020. When you have a column to store the date, you will be able to make reference to the day, month and year of each row of data. Pivot Table recognizes the dates and will be able to organize the data according to day, month and year for you instantly with little effort from you.
Not all the data comes nicely in an Excel worksheet. Some come in csv format, text or linked to some data sources. If you are lucky and there is IT support, you can get the IT guys to organize the data nicely for you. If not, there is a bit of moving around to get the data in the right layout. Splitting and grouping of the data is usually done within Excel and you probably have to
know some Excel formulas or functions to get this done efficiently. If not, good
luck to your copying and pasting.
Once you have gotten the data in the right format, you are ready to analyze it. Pivot Table is usually the tool for you to perform your analysis. If it is not good enough because there are some
calculations to do, then you have to use PowerPivot for this.
Analyzing also means organizing the data and find meaningful patterns that help you understand the data better. For example, you can identify the top 5 sellers, top 5 products, top 5 categories, etc. Analysis involves knowing the finer details of the top 5.
Your audience, usually the management, is not interested to know everything about the data. They are more interested in your findings and the key points of the data. Presenting interesting charts to catch your audience attention and the key statistics so that they could remember the numbers easily is the main focus of this section. If you can achieve this, the time you spent analyzing the numbers will not be wasted.
We have always positioned ourselves as an Excel course provider, although we do not offer basic, intermediate and advanced Excel courses. Business professionals like our courses because we showed them shortcuts and how to save time working with Excel. They learnt how to use a Pivot Table, VLOOKUP and charting.
It is always our aim to help business professionals who attend our courses solve their problem by showing them solution that they could take and implement immediately after completing the course. Most of them love us and we are glad that they did.
At the beginning of this year, we started review our courses, the feedback, the objectives of what our learners hope to achieve and what they learnt at the end. We realised that the root cause of their problem is not about understanding Pivot Table or VLOOKUP. It is caused data source setup, not being able to organize their data in an optimized manner that allow them to perform their analysis fast and presenting a simple report for meeting and review.
With that understanding, we are re-calibrating our courses and focus on 3 core competencies which are the foundation to use business data collected to optimize their business performance. The 3 core competencies are building a scalable data source, setting up a report for fast analysis and generating tables and charts for simple reporting.
And to better reflect what we do all these while, we are changing our training courses to group coaching program in 2020. We want our learners to ask questions and get answers when they come for our programs.
Hidden Secrets of Data Analysis in Excel continue to be our core program which offers business professionals a good solid foundation in building a scalable data source, setting up a report for fast analysis and generating tables and charts for simple reporting. Using this framework, they will be able to analyze any business data they have.
Learning the Power of PowerPivot will be focussing more on reporting so that they are able to update their reports almost instantly.
Learning the Magic of Macros is focussed on building a scalable data source so that it can be used for Fast Analysis and Simple Reporting.
It doesn't matter whether you are from small company or a big multi-national corporation (MNC). If your team is spending hours preparing their reports, it is a sign that your team’s performance is not optimized. Generating a report should not take more than 5 mins. The hours they spent on reporting right now is probably taken from the time they should be using to review performance and execute their plans for growth. If you find that your team is displaying such symptoms, they probably lack one or more of these 3 key competencies (designing a scalable data source, developing a dynamic analytical report, summarizing their findings)