#EXCEL TRUNCATE CURRENCY COMPARE HOW TO#

We initially saw that Category 2 made the scale go up which made one of the extremes not readable. Thanks to the truncation, we can now observe the frequencies with more precision and compare the different categories since the bars are no longer crushed by the scale of the y-axis. Following the instructions of the previous section, we’ll get something like this: What we need is a truncated bar chart to be able to see both the information at the bottom of the scale and at the top. However, it’s almost impossible to read the values for Category 1 – Gender F on that graph. This is also confirmed by the summary statistics table. If we plot the raw data using a normal bar chart, we’ll get the following visualization:Īs we can see, most of the data belongs to category 2 and is above 8 000 units. Set 0 to completely hide it, or 100 to make it fully visible. The transparency (%) option allows you to set the transparency of the squeezed part of the chart. You can choose to sort your modalities by alphabetical order, specify the Variable-Category labels (to add a legend to the plot with « Gender-F » and « Gender-M ») and check One chart to plot all three modalities on the same bar chart.Īctivate the Truncate option and choose at which level you want to truncate. Select the Gender variable (column C) in the Subsamples field in order to visualize the runners’ performance separately for female and male participants.

In this example, choose Qualitative and select column I (Category) in the demo data file. In the General tab, choose the type of the variable you want to visualize in the Data format field. The Truncated bar chart dialog box appears. Once XLSTAT is open, click on Visualizing data / Truncated bar chart as shown below: Setting up a truncated bar chart in XLSTAT The aim of this tutorial is to visualize the distribution of the 2019 Boston marathon results as well as to link the performance to the gender of participants. Category 3: runners who finished the marathon in more than 4.5 hours.Category 2: runners who crossed the finish line in 3 to 4.5 hours.Category 1: runners who finished the marathon in less than 3 hours.Result (time), our response variable, has been split into three categories and renamed into Category:

Only Gender will be used as an explanatory variable. Several variables are available such as the Rank, Age, Gender, Country, Result (time) and Country code. The original data come from the website and contain the results from the Boston 2019 marathon. Dataset for creating a truncated bar chart Furthermore, XLSTAT allows to use transparency to partly show the information that is hidden in the removed or squeezed part of the scale. This is why we should, with no loss of information, truncate the first bar.Ĭreating such a bar chart in Excel can be cumbersome while you can easily do it with XLSTAT. In this case, the y-axis will be scaled in a way that it clearly shows the bar of 20 000 observations but it will difficult to visualize the other two categories. For example, we may have one category with 20 000 individuals (observations) and two categories with 400 and 500 individuals respectively. However, it can happen that there is too much of a difference between the various frequencies. What is a truncated bar chart?īar charts are very useful for visualizing categorical distributions where each bar represents the frequency of a particular category.

#EXCEL TRUNCATE CURRENCY COMPARE HOW TO#

This tutorial explains how to generate and interpret a truncated bar chart in Excel using the statistical software XLSTAT.

Excel truncate currency compare