Lesson 10 - Data Visualization
Learn how to visualize data to generate business insights.
Estimated Read Time: 1 - 1,5 Hours
Learning Goals
In this lesson, you will learn:
Technical & Analytical:
- Create and format different types of Excel charts, including line, bar/column, scatter, box-whisker, and pie charts.
- Match the right chart type to the right analytical questions.
- Use visualizations to spot patterns, test assumptions, and detect outliers.
Business Impact:
-
Develop an analyst’s judgment in selecting clear, convincing, and non-misleading visualizations.
Data visualizations transform numbers into meaning. While tables and PivotTables are useful for exploration, decision-makers often need patterns, comparisons, and exceptions to be visible at a glance. In this lesson, you will learn how to create and interpret different chart types in Excel, using the Rossmann dataset. More importantly, you will understand why a specific chart is chosen for a given business question.
1. Why Visualizations Matter
Visualizations are not just about making data look attractive. They serve three important purposes for analysts:
- They uncover patterns you would miss in raw tables, such as seasonal spikes or unusual outliers.
- They act as a universal communication tool, helping non-technical stakeholders quickly understand insights.
- They build professional credibility — polished, clear visuals make your work portfolio-ready and establish you as a serious analyst.
2. Choosing the Right Chart
Not all charts answer the same type of question. Selecting the right visualization is about clarity, not decoration. Always start from the nature of the data:
- Time → Line Chart
- Categories → Column or Bar Chart
- Distribution → Histogram or Box-and-Whisker Plot
- Correlation → Scatter Plot
- Geography → Map
Making the wrong choice risks confusing stakeholders or, worse, leading them to the wrong conclusion. Good analysts always justify why they picked a chart.
Consider Figure 1 below for a quick guide on how to choose the right chart for your data.
3. Line Charts: Tracking Performance Over Time
Line charts are ideal for showing data across continuous time. They reveal patterns such as weekly cycles, seasonal peaks, or longer-term growth trends that would be buried in tables.
Business Question: How do sales and customers change over time?
- Insert → Line Chart with Date on the x-axis and Sales on the y-axis. (3-years’ daily data might lead to a cluttered chart – so create a pivot table first where you can convert data to weekly / monthly or even quarterly sales). See Figure 2a.
- Extend the chart by adding a second line for Customers. See Figure 2b.
- With two lines, you can check whether peaks in sales coincide with peaks in customer traffic, or whether promotions drive higher sales per customer even without more footfall.
These charts work because executives can quickly “see the story of time” — sales spikes around holidays, dips during off-seasons, and trends that suggest strategic adjustments.
4. Bar / Column Charts: Comparing Categories
Bar and Column charts are best for categorical comparisons. Each bar represents a group average, making differences easy to interpret.
Business Question: Do promotions increase sales?
To answer this question, you’ll use the bar / column chart:
- Create one chart comparing average daily sales with Promo = 1 Promo = 0.
See Figures 3a and 3b for the bar and column charts for Sales over promotions.
Choose bar or column orientation based on readability — e.g. in the example above, the column chart “looks” better and is easier to read.
Business Question: Do holidays bring more customers?
Again, to answer this question, you’ll use the bar / column chart:
- Create a bar / column chart comparing average customers on holidays vs. non-holidays (StateHoliday). – See Figure 4a.
- To compare the sales exclusively across holidays, filter out the no-holiday values – See Figure 4b. We can see that sales are higher on general public holidays and least over Christmas holidays.
Executives like these visuals because the difference is instantly visible. If the promotion bar towers above the no-promo bar, the business impact is undeniable. If holiday sales don’t differ much, management may reconsider staffing or discounting strategies.
You may want to plot multiple series like Sales and Customers at the same time as well – simply select both and then insert chart – See Figure 5.
Granularity and Aggregation
When presenting comparisons, remember that executives rarely want to see every subcategory or product type at once. Overly detailed charts become noisy and hard to interpret. Instead, aggregate data at a meaningful level — for example, showing total sales with and without promotions, rather than each store’s individual numbers. This balance ensures clarity without oversimplification.
5. Histograms and Distributions
Sometimes averages are not enough. A histogram shows how values are distributed — whether most days are clustered around a typical range, or whether sales are skewed by a few extreme cases. For example, sales might look healthy on average, but a histogram could reveal that most days are modest while a handful of holidays inflate the average. This matters because it helps executives judge whether challenges are systemic or just rare exceptions.
You can add a histogram of the raw data, by clicking Insert -> Chart -> Histogram. See Figure 6.
6. Scatter Plots: Showing Relationships
Scatter plots are especially powerful because they reveal when relationships do and do not exist. If sales and customer counts rise together, you will see a clear upward trend. But if the points are scattered randomly, it shows that more traffic does not always mean higher sales. This is critical for business decisions — executives may assume a correlation where none exists, and your visualization prevents costly missteps.
Business Question: What is the relationship between sales and customer traffic?
Scatter plots show how two continuous variables move together. Unlike bar or line charts, which emphasize totals or trends, scatter plots focus on relationships and correlations.
- Select Customers and Sales columns and insert Scatter (X Y) plot.
- Each point represents sales on a specific day for a store.
- Add a trendline to highlight the overall pattern.
This visualization is powerful because it reveals not only whether more customers generally lead to higher sales, but also whether some stores underperform (high traffic, low sales) or overperform (modest traffic, strong sales). These insights guide operational improvements.
7. Box-and-Whisker Plots: Identifying Outliers
Box plots summarize distributions by quartiles and highlight outliers. They are not as intuitive to non-analysts as bar or line charts, but they provide unique value in spotting anomalies.
Business Question: Which sales days were unusually high or low?
- Insert → Box and Whisker chart for Sales. The boxes show the “normal range” while outliers appear as dots. These may be exceptional holiday sales, store closures, or data entry errors.
Business Question: Do some stores face unusual competitive conditions?
- Create a box plot for CompetitionDistance in the stores file. Stores very far from competitors may represent unique market opportunities, while those with very short distances may face constant competitive pressure.
As we can see that the overall box is closer to the lower end of the plot – indicating that most of the competition stores are quite close.
Let’s also have a look at the correlation between sales and competition distance. For this, let’s first use LOOKUP to bring CompetitionDistance to Sales file, then create a Scatter plot.
These charts work because executives often need to know where the business is not normal — outliers are either risks (data errors, failing stores) or opportunities (holiday windfalls, unique markets).
8. Pie Charts: Showing Proportions
Pie charts are controversial among analysts because they can be misleading if overused. However, they are useful for showing simple proportions at a glance.
Business Question: What percentage of time are stores running promotions?
- Insert → Pie Chart with Promo = 1 Promo = 0 as slices.
- Add data labels with percentages.
This visualization is suitable here because the question is binary: either stores are in promotion or not. The pie chart clearly communicates the share of time spent in each state, something executives often like to see quickly in presentations.
Note: Use pie charts sparingly. They work only for simple, (mostly) binary proportions (e.g., promotion vs. no promotion) with large differences.
9. The Iterative Nature of Visualization
Creating a chart often sends you back a step. You may notice that the data needs to be cleaned further, that categories should be regrouped, or that different time frames reveal sharper patterns. This back-and-forth process is normal. In fact, it is a sign of good analytical practice — visualization is not the final step, but part of a continuous cycle of refining your insights.
10. Chart Selection Guide
As an analyst, one of your most important skills is knowing which chart best fits a given question. The table below summarizes the logic we applied in this lesson:
|
Business Question |
Best Chart Type |
Why This Chart Works |
|
How do sales and customers trend over time? |
Line Chart |
Shows continuous change across dates; makes seasonal patterns and trends visible. |
|
Do promotions or holidays affect sales/customers? |
Column/Bar Chart |
Makes category comparisons obvious; bar height directly shows magnitude differences. |
|
What is the relationship between customers and sales? |
Scatter Plot |
Reveals correlation and efficiency; shows whether higher traffic leads to higher sales. |
|
Which values are unusually high or low? |
Box-and-Whisker Plot |
Highlights distributions and outliers, useful for spotting anomalies or risks. |
|
What proportion of days have promotions? |
Pie Chart |
Works well for simple binary proportions (promo vs. no-promo). |
Summary
In this lesson, you learned not only how to create different chart types in Excel, but also why each chart is chosen for a particular analytical question.
- Line charts are used to reveal time trends, making seasonal peaks and dips visible.
- Bar / Column charts are used for category comparisons, showing whether promotions or holidays affect sales.
- Scatter plots uncover relationships, such as the link between customers and sales.
- Box plots highlight unusual values and outliers that may signal risks or opportunities.
- Pie charts are effective for simple proportions, such as promotion frequency.
Suggested Readings & References
Exercise
Estimated Time to Complete: 1-2 hours
Task – Asking and Answering with Visuals
You’ve now seen how different charts help answer different types of business questions. It’s time to turn the tables: you’ll create both the questions and the answers.
Your task:
-
Use the Walmart dataset.
-
Write at least one business question for each chart type we’ve covered:
-
Line Chart
-
Column Chart
-
Bar Chart
-
Scatter Plot
-
Histogram
-
Pie chart
-
Box and Whisker Plot
-
- Then, create the corresponding visual in Excel that best answers your question.
-
Use each chart type at least once.
Guidelines:
-
Your questions should be business-oriented (e.g., How do weekly sales vary during holiday weeks? or Is store size related to sales performance?).
-
Your visuals should clearly answer the question and be labeled so an executive could understand them at a glance.
Tip: Think about what a Walmart executive would want to know. Holiday impacts? Store-level performance differences? Trends over time? Relationships between features? Design your questions with that lens.
Submission Guidelines
Submit your solution as an Excel workbook:
Workbook:
- appropriate worksheets with analysis and visuals
Filename Format:
- YourName_Lesson10_Walmart_Visualization.xlsx
When you’re ready, submit your completed exercise to the designated folder in OneDrive. Drop your mentor a note about submission.
Important: Please scan your files for viruses before uploading.
Submission & Resubmission Guidelines
- Initial Submission Format: YourName_Lesson10_…
- Resubmission Format:
- YourName_Lesson10_…_v2
- YourName_Lesson10_…_v3
- Rubric Updates:
- Do not overwrite original evaluation entries
- Add updated responses in new “v2” or “v3” columns
- This allows mentors to track your improvement process
Evaluation Rubric
| Criterion | Exceeds Expectation | Meets Expectation | Needs Improvement | Incomplete / Off-Track |
|---|---|---|---|---|
| Asking and Answering using Visuals |
|
|
|
|
Got Feedback?
Contact
Talk to us
Have questions or feedback about Lumen? We’d love to hear from you.