To effectively visualize your data, you need to arrange them in the correct layout.
Dates, Text and Numbers are treated differently when you visualize data. You can't put them into the same column. For example, price column cannot have text like FOC or NA. Dates column cannot have text like Jan. It needs to be a proper date like 1-Jan 2020.
Pivot Table is a good tool to help you learn the basics of visualization. If you can't visualize data with Pivot Table, you can't do it with others. And most people have not master Pivot Table yet.
Some data needs to be merged while others have to be appended. Knowing the difference between 2 will make sure that you do not corrupt the data in the process of visualization.
DAX is used to summarize data beyond the simple sum and count available in Pivot Table. Learning DAX brings you into the next level of data visualization.
Comparing data across periods requires the use the advanced DAX formula. It will help you compute MTD, QTD, YTD with the previous periods. It can even calculate the numbers for financial year that does not start from 1 Jan.
Visual representations of data, making complex info easier to share and digest. Once you have master the first 6 steps, you will be able to use different tools such as Power BI, Tableau or Qlik Sense for data visualization.
The relationships in data sources are like BGR (Boy Girl Relationship). There are 3 types:
1) One boyfriend to One girlfriend
This is the easiest to manage because one record from one table is linked to another record in a second table.
2) One boyfriend to Many girlfriends OR Many boyfriends to One girlfriend
One record from one table is related to many records in a second table
3) Many boyfriends to Many girlfriends
Many records from one table are related to many records in a second table. The relationship is highly complex and should be avoid at all cost.
1a. Pivot Table can only accept a single source of data.
1b. Power Pivot Table can accept multiple sources of data.
2a. Pivot Table can only do simple summary such as Sum, Count, Max, Min
2b Power Pivot Table can do complex calculations
3a. Pivot table can only accept data from the entire source (worksheet)
3b Power Pivot Table let you pick choose the columns from source to be included in the report
I am conducting our most popular Excel course titled "Hidden Secrets of Data Analysis in Excel" next week on 5 & 6 March 2020. It is not a typical Excel course which just showed you how to use some popular functions and formulas in Excel.
This "Hidden Secrets" course will also impart skills on how to manage your database, a skill that is not available in most if not all Excel courses. To be able to prepare your reports, you need to have at least a basic sense of how databases worked. This will help you manage your data more efficiently, create more dynamic Pivot Table for analysis and reporting.
I will also share with you some computer skills, not those which showed you how to launch a application but at a deeper level on how you should store files and what type of files you should use. Some Dos and Don'ts that more people do not take much notice but will help you improve on your reporting.
If you are interested to take this highly valued course, head over to our Hidden Secrets of Data Analysis in Excel course page to sign up. There is no better time to upgrade yourself and start your journey on Data Analytics than now.
When it comes to creating charts in Excel, most people usually think of only one type of chart, that is, the column chart. It is the default chart in Excel. But you do not have to live with the default charts. There are changes you can make to the chart to make it better looking without you having to spend hours on it. And I am going to show you 3 of them.
1. Changing the background to a picture.
2. Changing the column count to scissors.
3. Highlighting the cause of the drop/increase in sales.
It is better to show you how to do it in the video below than to write. Watch it and let me know if it is going to help you impress your boss with them.
1. You can do Unique Count in Pivot Table if you have Excel 2013 and above
2. You can combined data from multiple worksheets in one from Excel 2016
3. You can do away VLOOKUP, MID, Text to Columns from Excel 2016
1. Unique Count in Pivot Table
The pivot table used to be able to count the number of records in the data source. If the same name appears 10 times in the data source, Pivot Table will count the name 10 times. There are times when you wish to count only the 10 names once and display as one count. If this is the problem you are facing, then you should use at least Excel 2013. A few clicks are what it takes to get the answer.
2. Combined data from multiple worksheets
A common question that popped out in my training is how to do a Pivot Table for multiple worksheets (data sources). The answer is you can't. But what you could do is to combine the records using a function available from Excel 2016. It allows you to do a one time setup to combine all the data sources into one so that you can prepare the Pivot Table like before.
3. No more formulas
Do you dread using VLOOKUP, MID, LEFT, RIGHT formuals to combine and split your data all the time? With Excel 2016, you can do away with these formulas and simple learn how to choose the right function to use. Your data can be combined and cleaned with not a single formula.
Not sure whether of these functions is available to you? Check which version of Excel you are using before you explore the solutions for the above 3 problems. The video below show you how to check your Excel version.
A General Manager of a reputable corporation came for my Hidden Secrets of Data Analysis in Excel Training. I asked him "What do you hope to learn from the course?"
It is a standard feature in my training is to ask them what they hope to achieve in the training so that I can customize it as much as I can to help them.
His reply was "My staff said the reports he wanted cannot be done in Excel. I am here to verify it.
The outcome is "It can be done."
You may think that this general manager didn't trust his staff. But I think that he had probably seen someone doing it and knew that it can be done but he needed confirmation.
So if your boss asked you to do something, you have 3 choices:
1. Tell him/her that it cannot be done and hope that he does find out.
2. Let him find out and tell you how.
3. Get trained, show him you can do it now and earn some points.
Which choice would you choose?
I had an interesting conversation with a friend yesterday. We were discussing on my program called “Making Sense of Business Data” and she made the following candid comments. And my answers to her comments.
Comment: Many are afraid of big data as if they will explode. They think it is hard to sieve out stories too. How can Excel, compared to other newer tools. Help them without them being daunted by the formulae too?
Answer: If you are not able to handle small data, you will have a big problem with big data because you will not be able to handle the sheer volume of numbers coming out of it. That is why I have launched a new program called “Making Sense of Business Data in Excel and Power BI”. If you can handle the data using these 2 power apps, you will have no problem with other tools. I have used many tools over the years and I can safely tell you that Excel is still the best candiate to manage small and big data.
Comment: Aside from addressing their fears, can you address their desire to be better storytellers too that go beyond Excel?
Answer: It is not about Excel. You can become a good story teller when you understand the data you owned. You can’t tell a story if there is not content to tell the story. Big data can become highly complex and requires good technical skills to manage. If the person cannot handle Excel and its formulas, he would have failed to use the sexy tools available to him.
Comment: Excel cannot handle big data.
Answer: Most people have a low opinion of Excel and thought that Excel cannot handle big data. The truth is it can. My personal experience is running reports from a big data pool of $150 million rows of records and it works perfectly well in Excel. The problem is the IT department did not set it up properly to hadle the big data. For many of my bank clients and big corporate clients, they are restricted in the amount of data they could pull at any one time.
1. Reduce information overload
2. Visual representation is better than a long list of text and numbers
3. Choose what they want to see.
The Singapore Government re-activated the PHPC to help to detect and manage the COVID-19 virus spread. It came out with a portal for you to search for a clinic near you. The list showed the clinic nearest to you and the contact details. When I look at the list, I was overwhelmed. The first thought that came to my mind is where are those clinics? I am not familiar with the address and do not know where they are actually. So what did I do? I took the whole list and plotted the clinics on a map.
With the map, I can see where are all the 783 clinics located. I don't have to look at the text and numbers for my first view.
With a map, all I have to do is to zoom in to where I am now, then look for the dots nearest to me. With a mouse over functions, I can then see the clinic name, address and contact number. It helps me identify the clinics near me although I hope I don’t have to use it.Was it easy to plot? It just took me 5 mins. The video is a little longer because I added in explanations. You can watch it below.