Archive

Monthly Archives: January 2020

January 31, 2020

How to identify duplicates instantly

How to identify duplicates instantly

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.

January 15, 2020

Thank you Boss

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

January 15, 2020

unfair treatment

Have you treated me fairly over the years?

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

Let me explain what I mean below:

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.  


January 10, 2020

3 tell-tale signs that your date is stored wrongly

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.


January 6, 2020

3 skills you need to Excel in Reporting

1. Data source

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.

2. Analysis

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.

3. Reporting/Presentation

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.

>