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.
The Public Health Preparedness Clinics (PHPCs), as well as polyclinics, will provide special subsidies for Singapore Citizens and Permanent Residents diagnosed with respiratory illnesses (e.g. common cold).
Please bring along your NRIC or birth certificate, along with your PG/MG/PA and CHAS card, where applicable.
To find one near your place, use the interactive chart given below.
If you are using Mobile,
Double tab to zoom in.
Press and hold one of the green dot to show clinic name and contact details.
If you are using Desktop,
Use the mouse scroll wheel to zoom in.
Mouse over one of the green dot to see more details.
Full screen chart can be accessed thru this link.
This map is created with Power BI.
Data is a collection of numbers and text in rows and columns. Business data is a collection of numbers and text in row and columns in Sales, Marketing, Human Resources, Logistics, Accounting, Budgeting, Training, Stock Inventory, and other relevant business related areas.
For the purpose of explanation, I shall use your phone records as an example. Phone records is a collection of phone numbers and names in rows and columns. Each person is in one row and the contact number is a column.
Business data is a random set of numbers and text until it is grouped into different categories.
Using phone records as the example again, phone records are random set of names and phone numbers. Other than those numbers that you call often, the rest are usually numbers you have not touched for at least a few months. We call the former active numbers and the latter inactive numbers.
There is also a high chance that you group them into different categories such as relatives, school friends, colleagues, old neighbours, new neighbours, etc. If you have just classify them as active and inactive numbers, you might have a problem search for them when you need to call someone whom you have not called for a long time. And you might not have remembered the name. By classifying them into different categories, it will be easier to find the number by looking at a smaller set of numbers.
Business Data works the same way. You have to group the numbers together so that you can make sense of it, meaning understand these numbers better.
Now that the business data is grouped, it is easier to identify them by their groupings. The groups usually have their own characteristics and behaviour. By knowing these characteristics and behaviour, you can then monitor them easily. The data will then be used to measure these characteristics and behaviour so that better decision could be made over time.
I was at this shop queuing to buy face masks. You have to show your ID to buy the face mask. Each customer is only allowed to purchase once due to limited stocks. Without checking through hundreds of records, how could you use Excel to check instantly? This is how I will do it.
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.