We might have the best data on our hands. Our presentation might also be outstanding. But without the proper analysis, our information loses its spot in the limelight. A histogram in Excel aims at providing a clear and precise analysis of the presented data in the best possible manner, making it clear, concise and easy to read. If plotted correctly, it can give fabulous results, making it the perfect companion for all those people looking to perform complex analysis on a data set.
Microsoft’s recent venture saw the launch of the “Data Analysis ToolPack”. It helped in the identification of the missing values and organization of data. Its main purpose was to award the users with a tool which was user friendly, and yet, easy to use at the time of creating histograms. In other words, it helped the users achieve perfection on a spreadsheet. The pack also eliminated the frequency of errors in the figures presented. However, in MS Excel 2010, the ToolPack, needs to be installed separately.
How to make a histogram in Excel 2010?
Complete your data table.
This should consist of a label column and a number column.
Click Data Analysis option found in the Analysis group. This option is found in the Data tab.
Click histogram option and click OK.
Fill in the required fields.
Edit it according to your need and click OK.
This is created by using functions like COUNTIF ( ). The number of rows and columns to be added should be selected with care and alertness.
Bin range or the Bin Array
A Bin Range represents the range. It determines the basis on which the histogram is created. It is done by dividing the entire range into smaller, more specific intervals. The accuracy is largely indebted to the right range. Arrange the range aptly from ascending to descending order. ‘Sort & Filter’ under the Edit tab provides options to arrange in Ascending and Descending Orders. The width of the range can be suitably altered. Create very small ranges to get a tidy graph.
It is not possible to align the x-axis label between the adjacent tick marks in the vertical bar. So a generalized title indicating the upper limit is commonly preferred. This complicates the histogram and provides no details in between along any of the axis. The bar chart hence becomes very imprecise and unclear. All the details become obscured. Technicians and MS Specialists are one of the few people who have handled an Excel spreadsheet and achieved accuracy.
If the first row and column is completely cleared, they can be used to insert the axis values. This enables Excel to utilize the first row and first column for labels and series names.
Data- Array refers to a series of values which are counted to give frequencies. In the absence of repetition, the frequency is zero. Frequency is the set of selected adjacent cells that can be redistributed in the returned array. The number in the returned array is one in excess of the bins array. Blank texts are generally not considered for frequency. It also requires formulae, also known as array formulae. As the name suggests, Frequency also represents the number of times a particular value repeats itself or the number of values that fulfill a particular description.
Formatting The Output:
The output is adjusted for optimal results and best views.
Vary the size of the charts by properly adjusting the bars according to the requirements.
Remove ‘Legend’ by clicking on it and pressing the delete key.
Name the axis correctly.
Fix a proper name as the heading
Adjust the column width and the gap between the columns.
Fit the axis labels by formatting them.
Do the final touches.
Check the alignment and all other details.
If done with knowledge and experience, it will come out very well.
Avoid any type of colored histograms.
This avoids the blending of various bars.
It can be exported to a new spreadsheet. This avoids the distraction that the data input provides.
It has often faltered for its disgraceful views. With the right manipulation and editing, it can present a blur free image. However, despite the seeming clarity, the words appear faded, as they blend into each other. The efficiency is lost. It can also confuse a person and produce results that need to be checked manually. The correctness is not a given parameter.
Even the best programmers and experts in Excel face challenges when it comes to presenting certain complex data in a histogram. Given certain facts, it has proved to be an excellent tool for estimating an approximation. It however fails miserably when it comes to depicting accuracy. Many claim the ToolPack has sub-standard tools. However, on close inspection, it is found that the Excel is better in comparison as compared to some of the other software available in the market.
In Business Schools:
Several attempts had been made to make the histogram an interesting subject in Business Management courses in certain business colleges. However, what matters is the interpretation which needs to be accurate. Only proficiency and experience can help in the creation of a perfect histogram. This cannot be achieved in one short course. The idea was eventually dropped. Teaching this subject leads to a waste of valuable time and resources, making the concept fade away quickly.
A Good Histogram Needs To Be:
More number of bars
Proper title for the axis
Most importantly, it needs information that is accurate in itself. Unless given the right commands and the right instructions, Excel, by default, creates a blurred histogram. It serves the purpose of graphing the value of a population against a particular range. With a set of typed data, an automatic array of details is reproduced.