Deadly sins of maintaining list in Excel sheet (Part 4)

4 No Blank cells, column, rows & Merging cells

In the previous article, we’ve talk about why you should plan ahead of the structure and design of a table. Also, the importance of keeping data in its column plus the after effect of having stacked headers. If you haven’t already read it, please go ahead and read it if you’re curious or having some issues with lists or tables.

Merge Cells

Blank cells

In this article, I will be talking about why merge cell and blank is one of the deadly sins! Excel has a lot of built-in intelligent in it, and it automatically detect the range of your data base on your current active cell is. In order for us to fully utilize the intelligent you will have to provide appropriate information in the right structure.

The blank cells could cause some of the functions or commands fail to work properly. Whenever there is a blank cell appear within a range, Excel will automatically assume that’s the end of the range but sometimes the cell just happened to be blank. Later I’m going to walk you through all the disadvantage of having blank cells. Continue reading “Deadly sins of maintaining list in Excel sheet (Part 4)”

Microsoft Excel: Deadly sins of maintaining list in Excel sheet Part 3

This will be the Part 3 of the Deadly Sins of Maintaining List in Excel which we have covered the first 2 part earlier.

Part 1 : Microsoft Excel : Deadly sins you should not do in Excel (Part 1)

Part 2: Microsoft Excel : Deadly sins of maintaining list in Excel sheet (Part 2)

Each data belongs to its own column

While you’re designing a table or list in Excel, plan ahead is extremely important. Always remember, while you’re designing a table, plan and think of what you need, what kind of the report that you will need to create at the end month or the end of the year. From there, you will roughly know what sort of data you’re require to collect during data entry.

First, you must plan how many columns you need, name each column as what you need to collect in its column. Each column only limited to one types of data type.

If you have seen this kind of tabulated data and calling it a table. Well, you’re not quite right. This is a report, static report not a table or list.

Continue reading “Microsoft Excel: Deadly sins of maintaining list in Excel sheet Part 3”

Microsoft Excel : Deadly sins of maintaining list in Excel sheet (Part 2)

Don’t! Do not change the formatting of date

Okay, let’s talk about date. This “Don’t” is related to the “Don’t” from the list above. Let’s do this. Insert these 2 dates into separate cells, make them side by side. The first date “1/31/2015”, the second date “31/1/2015”. *widen the width of these 2 columns*.

No Alignment! I can’t stress how important not to do this!

Remember I’ve mentioned in the earlier point, Excel will align string and labels to left while values to the right. Before we deep dive into why the alignment in these 2 dates are different, let’s talk about how Excel analyze data. Excel stores date using numbers. The number 1 represents 1 January 1900; 2 represent 2 January 1900; 42736 represent 1 January 2017. Hence, date is considerate as value because that is how Excel stores date. When formatting like alignment took place, we forcefully align them to left and right, we’re hiding the fact that which is the date that Excel recognizes and which it not. Continue reading “Microsoft Excel : Deadly sins of maintaining list in Excel sheet (Part 2)”

Data Visualization with Excel 2016 (Part 3)

Sparklines

Sparkline is not a new feature in Excel. In fact, Sparkline was introduced to Excel since version 2010, AKA tiny chart. It is helps to visualize and highlight the trend of data over a certain period without having all the clutter in it. I love how tiny it is and it simply does one job, showing trends. This tiny chart is so tiny it does not take up a lot of space. It simply fit comfortably within a cell, just a cell is enough for it. Below shows an example of sparklines.


“Sparklines enable us to create visual representations of data with one click. It makes it easier to grasp data, and it’s helping our CFO analyze trends on the spot.” Matt Stuckey, IT Director of Levick Strategic Communications.


Sparklines are located at the last column of the table.

Continue reading “Data Visualization with Excel 2016 (Part 3)”

Data Visualization with Excel 2016 New Chart (Part 2)

Check out the last article if you have missed out the Data Visualization with Excel 2016 New Chart Part 1. In this article, we’re going to explore the other new charts that is being introduced in Excel 2016.

We are going to talk about histogram, pareto and box & whiskers.

Let’s start with the first new chart Histogram.

       I.            Histogram Chart

If you take a good look at histogram, it does look similar with column chart. (below shows and example the comparison side by side of a histogram and column chart). Histogram being used to visualize the frequency of distribution of a set of data while column chart is use to visualize data base on categorical variable. Besides, you should also realize the way histogram is plotted without gaps in between the columns while column chart has gaps.

The horizontal axis which is also known as the x-axis is being label by a series of interval and then count how many of the values within the dataset fall into the intervals. It is commonly used in statistics. The horizontal axis (aka x-axis) representing the “bin” (amount of column) sized of the dataset. The “bin” size [number 1, number2] is the group of salary from the dataset. It shows that among 48 people, there is 12 people falls under the group of salary range from 1000-2900 (according to the figure above). The “bin” size can be adjusted through the horizontal axis panel. The higher “bin” size, the smaller the range gets.

The figure below shows the bin size is adjusted to 10, and the range shows [1000,1690].

With the same dataset, the bin size is adjusted to 5, the range shows [1000,2380].

If you want to have more controls over the “bin” size which is the range, the horizontal axis panel does provide the flexibility to change it. With the same dataset, I have change the “bin” size to 6, underflow (any number below this threshold, will being placed in this bin); overflow (any number above this threshold will being placed in this bin).

Bin size: 5; Overflow:6500; Underflow:2000

       I.            Pareto Chart

Pareto chart is a great chart to use to highlight the problems or issues face in an organization or department. It is named after Vilfredo Pareto, an Italian economist. With Pareto Principle, majority of the issues/problem is caused by small percentage of causes. He then came out with this ruling, 80/20. With this 80/20 principle, it says that 80% of effect comes from 20% of effort. If you manage to identify and solve 20% of the cause, you have practically solve 80% issues faced.

Examples of Pareto Principles:

80% of your sales revenue is actually coming from 20% of your customer base; 80% of complaints is causes by 20% of the factors and many other example that you could come out with. Just so you know that, this is just a principle, which may not necessarily as absolute as 80/20.

Below figure shows an example of the reason why an employee is being late for work.

Pareto chart are very similar with Histogram, the difference in between the both is Pareto chart’s horizontal axis is by categorical and being sorted from highest to lowest. Referring to the Pareto chart above showing the reason of why an employee is being late to work and the frequency is arrange from the highest to lowest. Using Pareto chart, we’ll be able to highlight the majority of the problem or reason of issue faced. If the first reason (Stuck in the traffic) out of the 5 reason is being solved, it will immediately reduce the amount of people being late to work by large percentage.

       I.            Box and Whiskers

Box and whiskers chart may seems a little intimidating, but it is one of the chart where it will quickly highlight the mean, media and percentile from dataset.

The example below illustrate what are mean, median and percentile.

A group of dataset below shows the price of Honda car from low end to high end.

1st Quartile shows the mean price of the 25% of the n items while 3rd Quartile shows the mean price of the 75% of the n items. On average, the price for Honda car is $175,291.25 according to the survey dataset. High point and low point illustrate the highest and lowest car price of the dataset while median shows the price being arrange in the middle, if there are 2 number is being arrange in the middle, then you will have to find out the mean of these 2 numbers.
Median: (136,000+145,000)/2 = 175,291.25

The graph above shows us that for Honda car, the price could vary from $76k to $315K overall. For the low end Honda car price could vary from $76K to $140K while the high end Honda car price vary from $140k to 315k. The average car price for low end (1st Quartile) is $85k while average price for high end (3rd Quartile) is $296k.

Related article: Data Visualization with Excel 2016 New Chart (Part 1)