XLSTAT: Frequencies, Mode & Bar Charts in Excel

 

Dataset for Example

The attached data summarize a survey on commuting methods in two cities. Each row represents a respondent, with columns for their city and mode of transportation—both qualitative (categorical) variables.

We will use the data to analyze commuting preferences by city using:

• Mode: Most common transportation method

• Frequencies: Count of each mode

• Relative frequencies: Proportion of each mode

• Bar and stacked bar charts: Visualize relative frequencies by category

This helps highlight key insights and differences in commuting habits between the two cities.

 

Setting Up

• Open XLSTAT.

• Click on the Describing data menu and select the Descriptive statistics feature.

  

• In the Descriptive Statistics dialog box, under the General tab:

  • Select the transportation mode column in the Qualitative data field.

  • Select the city column in the Subsamples field to analyze preferences by city.

  • Check Variable-Category labels to include variable and category names in the output.

  • Enable Sample labels to treat the first row as column headers.

In the Options tab:

• Activate the following option:

 

In the Outputs tab:

• Select the following statistics: Nbr of observations, Nbr of categories, Mode, Mode frequency, Categories, Frequency per category and Rel. frequency per category (%). 

In the Charts(2) tab:

• Activate the following options:

Bar charts visualize category frequencies or relative frequencies. Here, we use relative frequencies and stacked bars to highlight differences between categories within each city.

 

Interpreting the Results

Interpreting descriptive statistics for qualitative data

The results are displayed on the new sheet named Desc (see below).

 

The table summarizes the two sub-samples as follows:

• Observations: 100 total (50 per city)

• Categories: 5 transportation modes

• Mode & frequency: City A – Bicycle (21); City B – Metro (26)

• Frequency per category: In City B, 3 walk and 12 take the bus

• Relative frequency (%): In City A, 42% bike to work; only 3% drive a car

 

Interpreting bar charts and stacked bar charts

• Bar charts

The next two charts compare categories within each sub-sample. Each bar shows a category’s relative frequency, with the tallest bar representing the mode—the most frequent category.

 

The first chart confirms that bicycle is the main mode in city A, chosen by over 40% of respondents. City B favors public transport, with metro and bus making up over 70%.

This suggests city A is more cycle-friendly, while city B residents may live farther from work, making cycling or walking less practical.

Stacked bars show the percentage breakdown of commuting modes within each city, highlighting each category’s contribution.

For example, about half of city B residents commute by metro (gray section), while few walk (yellow section).

In city A, cycling is popular (orange section), whereas bus and car use (green and blue sections) are the least common.

 

See the illustration of this example in the video below.