We have learned that Excel has a very flexible charting toolkit and that it is capable of performing some very interesting operations with data. In this blog we will explore the features of Excel and how to use these features for data visualization and analysis. 

We have also learned that it is often difficult to find the appropriate type of chart when we need one. This is because Excel formats date back to 1995, so the typefaces and chart styles available no longer reflect current trends. 

Let’s explore a few of the more advanced features that can be implemented in the charts. Traditionally, charts with color have been pie charts, given their simplicity and use of data transparency.

Tips for Data Analysis

Here are a few more common considerations when you apply charts to your data:

 Make your charts interactive. An interactive chart can be challenging because you can’t really have a point of comparison without releasing other numbers into the chart and then stopping the chart in order to compare a line or column of the chart to those values.

While some developers may try to simulate that, this isn’t a scalable solution since each chart needs to use all of the numbers in the previous chart. Also, be aware of the parallax shifting on a chart because it will tend to make values that are above the bottom line look lower than those that are below the bottom line.

The Charts

Charts, but many people refer to them as bar graphs or other kinds of graphs. The distinction is between the point values of data as they relate to each other and to some visual object—in my example, a bar chart. The use of graph symbols in a piece of data visualization is sometimes problematic. For example, the line plots used to visually represent the sales/total sales comparison have a sharp break at the end that might be a problem if you were making an analogy that involved the shape of a ball, a circle, a cone, etc.

The charts you use to illustrate and communicate data insights are the most important part of any presentation.  People take on average 43% longer to recognize the message of a single data visualization than of a paragraph of text. 

On average, people take twice as long to see the importance of a data visualization than to recognize the importance of a sentence of text.  When presenting data to someone, there are several ways that people naturally process information including (information retrieval, categorization, inference, comparison, and experience) which are quite similar to the common purpose for which charts are intended.

Tips for Chart Creation

Don’t format the chart in a way that makes it difficult to read.  Don’t overload your chart with excessive information.  Design a chart to support your claim.  Don’t keep track of all the data points in a data series. Use a little Excel’s macro-scripting.  Make sure your chart is free from mathematical errors, common mistakes, or errors.  Use the charting tools in Excel to make sure your graphic functions properly.

Communicating with charts

How do you communicate the key message or meaning of the data set you’re presenting in a chart? How do you communicate the data results that you’re working with in a concise, clear way to a wider audience? These two questions are important because they influence how a chart can be used for presentation.  How to translate to answer these questions, you need to know the language in which the data set is presented in a chart or visual.

The more specific and specific that you can be in communicating the message that you want to send with your chart, the more effective your chart can be in communicating it. For example, you might not know what a bar chart is, and you wouldn’t have a problem if a client asked you to create a chart that shows sales trends.

Using pivot charts for small data sets

To produce the pivot chart, simply draw two rows, the blue one on the right, representing the overall data, and the yellow one on the left, representing the change in data from one year to the next.

The line that connects the two is the pivot axis. It is necessary to draw three labels on each axis—one in the blue box, one in the yellow box, and one in the red box.

Whenever you have a few rows of data to plot on your chart, you should consider using the pivot chart. To make a pivot chart, first convert your data to a floating bar chart format, and then align the data with the chart axis.

Look at your data, decide what you want the chart to illustrate, and plot only the relevant data. Don’t create long bars. A long line (longer than 10 rows) is hard for your audience to interpret. Use a vertical bar chart.

Few projects have more minor data than those that exist in the confines of a small sales, marketing, or manufacturing company. Let’s look at the problem in terms of trends, so small data doesn’t give us a headache.  Targeting small data may be appropriate for many projects. Using a simple sales pipeline process for our small data project, let’s determine the best way to measure different elements of this project: new customers, existing customers, repeat customers, and sales: new customers tend to have shorter sales cycles. 

Repeat customers typically have longer sales cycles.  New customers can pay more per product.  Repeat customers can be turned off by high prices.  New customers can use different promotional messages than repeat customers.

 A vertical bar chart is easy to read and understand. Use descriptive text labels. Avoid labels that read the same as the chart’s axis labels. Use a descriptive label that draws the reader’s attention to the specific data point. Use a horizontal bar chart. The horizontal axis label is narrower than the horizontal axis bar.

Avoid 3-D Charts

A 3-D chart is a device that shows only a portion of a dataset. A 3-D chart is really an exaggeration of a table and will misrepresent the size of the dataset. The 3-D look can be pretty cool, but don’t use it for data analysis.

 How to Use Excel for Data Analysis and Design-

1. Choose the data point with the smallest variance in the data.
2. Make the data point equal or close to the mean value.
3. Perform a 1-D linear regression to show the relationship between the data points.
4. Add symbols to represent the data.

If you are drawing a graph with data points, don’t forget to add a numerical value for each point and an identity symbol that represents each numeric value.

Each dimension on a chart should be relevant to the whole chart. First, we should define what “dimensionality” is. It’s more than just a visual dimension. You could have 10,000 nodes in a graph, but if it’s missing some of those nodes, the whole graph will appear skewed. Logarithmic scaling enables us to graph many, many variables at once.

For instance, a straight line can be graphed using the logarithmic scaling property. In this simple example, the line is graphed with a log scale. In essence, the line is drawn in such a way that all of its scale values are smaller than 1.0, which effectively makes the line very smooth. The straight line in the chart is also drawn symmetrically in the X and Y axes (“xy”).

Never use 3-D pie charts

It appears to me that the data would better fit a bar chart, which is a far better method for representing and visualizing that information. Do NOT add extra visual elements to your chart. The problem with bar charts is that you cannot see anything. With a bar chart, everything is focused on one point. Also, 3-D pie charts are usually overly complicated. Never use imaginary numbers.

Hypotheses are the raw data, the numbers on which to work. Let the numbers be the data points. Also, do not tell people what data points they should be looking for. Use pivot tables to plot small data sets. Pivot tables make it easy to identify and compare relatively few rows of data.

Be aware of the phantom data markers

A phantom data marker is some extra visual element on a chart that exaggerates or misleads the chart viewer. Use logarithmic scaling.

 Use area charts when “building on” or summarizing existing data. When you know the general range of data in a dataset, or the approximate size of the data set, you can use an area chart to build a summary or summation of the information.

For example, if you know that the years of 2005-2010 were particularly good, then you can plot a bar chart that looks like this and be reasonably confident that the row bars have relatively large values. It would be like being able to compare January 2004 to January 2005. But this level of detail is not what’s important.

A phantom data marker is some extra visual element on a chart that exaggerates or misleads the chart viewer. Here is a silly little column chart that was created to plot apple production in the state of Washington. Make the numbers larger.

When you’re working with small amounts of data, the size of the numbers on your chart may not be very important. In fact, it may be a good idea to make your data series visually less dense in your chart.  Final note- So, just how useful are pie charts?

They are really used only by folks who like graphical design, charting, and data visualization. I must admit that a pie chart really doesn’t do that much for me and a lot of other people. Yes, it is cool, it is entertaining, it’s easy to understand, and it’s fun to use.

Use logarithmic scaling

Use the Shift Click technique to maximize dimensionality of any chart. With logarithmic scaling, each pixel on the chart represents an amount of change. So, if you display 100 rows of data, each cell represents 20% change. Use the scale settings on a PRIMARY KEY chart. Use secondary columns to reduce noise.

 For example, if you are measuring sales of different products at different times, display sales as product SKU.  Use secondary columns to reduce noise. For example, if you are measuring sales of different products at different times, display sales as product SKU. Use the following perspective Contrast the legend in a secondary key chart with the plot itself. Sometimes, if you enlarge the legend, you will see that the plot and the legend overlap.

Conclusion

Vending machine design and analysis is a key application of data visualization to business decision making. In the context of analytics, using data to increase the value of your practice and your organization is critical.

Designing for analytics improves the quality of what you present, the effectiveness of the communication of that data, and the impact of your communication. Data visualization and analysis are likely to be one of the most important roles in managing the value of your practice and your organization. Using Excel for analytics, finding the right tool for the job, and using the right visualizations are essential to increasing the impact of your analytics and the value of your organization.