Hello! In our previous lesson, you mastered how to apply conditional formatting using custom formulas, allowing you to highlight entire rows in a table to draw attention to key data. This was our first step into the world of data visualization—telling a story visually, directly within your dataset.
Today, we take the next major step by learning how to create standalone charts. Instead of formatting data within a table, you'll learn to build professional bar, line, and pie charts that present a clear and compelling narrative about your data. This is a fundamental skill for any accountant who needs to create insightful reports, dashboards, and presentations. We'll cover not only how to create these charts but, more importantly, how to format them for maximum clarity and impact.
Choosing the Right Chart for the Right Story
The first and most important step in data visualization is selecting the correct chart type. The chart you choose depends entirely on the message you want to convey. Using the wrong chart can confuse your audience or, even worse, lead to incorrect conclusions.
For an accountant, the three most common stories you'll tell with data are about comparison, trends, and composition. Let's explore the best chart for each.
Best Types of Charts in Excel for Data Analysis, Presentation and ...
This article from OptimizeSmart provides a great overview of how to select the right chart.
Begin by reading the introductory section, How to select the best Excel chart? This will give you a framework for thinking about your visualization goals before you even open Excel.
1. Line Charts: Showing Trends Over Time
When you need to show how a value changes over a period—such as monthly revenue, quarterly expenses, or stock performance over a year—a line chart is your best tool. Your BCom and MCom studies likely covered time-series analysis; the line chart is the primary visual tool for this.
Best Types of Charts in Excel for Data Analysis, Presentation and ...
This article details several scenarios where a line chart is the ideal choice.
Focus on the first point in the section "When to use a Line chart?", which discusses tracking trends over time. This is the most common use case in financial reporting.
2. Bar and Column Charts: Comparing Categories
When you want to compare values across different categories—like revenue by department, sales by region, or expenses by type—a bar or column chart is the most effective choice.
- Column charts (vertical bars) are excellent for when you have a smaller number of categories.
- Bar charts (horizontal bars) are preferable when you have long category names that would be hard to read if placed on a horizontal axis.
3. Pie Charts: Showing Parts of a Whole
When you need to show the composition of a single total, a pie chart is a good option. For example, you could show the percentage breakdown of total expenses by category or the market share of different products.
However, pie charts should be used with caution and follow two strict rules:
- The slices must represent parts of a single whole and add up to 100%.
- They are most effective with five or fewer categories. Any more, and the chart becomes cluttered and difficult to read.
Best Types of Charts in Excel for Data Analysis, Presentation and ...
This resource outlines the best practices for using pie charts effectively.
Read the rules in the Pie Chart section. Pay close attention to the "100% composition" rule and the advice on limiting the number of categories.
Building and Formatting Charts in Excel
Now that we understand the "why," let's move on to the "how." Creating a chart is simple, but transforming Excel's default output into a professional-looking visual requires attention to detail.
This video from Kevin Stratvert provides an excellent walkthrough of the entire process, from inserting your first chart to making basic formatting changes.
Excel Charts and Graphs Tutorial
This tutorial covers the fundamentals of creating and customizing charts in Excel.
Watch the beginning to learn how to insert a chart using the "Recommended Charts" feature. Next, see how to use keyboard shortcuts and change chart types after creation. Pay close attention to the section on modifying chart elements. This will show you how to add titles, labels, and colors using the menus that appear when a chart is selected. Finally, watch the segment on creating a pie chart to see how to apply data labels showing percentages.
From Default to Professional: The Art of Formatting
As you saw in the video, Excel's default charts are a good start, but they often contain unnecessary clutter (like gridlines and borders) and can be improved with better labeling and strategic use of color. For an accountant, presenting data clearly is as important as the accuracy of the data itself.
The following guide from Depict Data Studio is a masterclass in turning a basic bar chart into a clean, professional visual. It focuses on decluttering the chart and using design elements to guide the viewer's attention.
How to Make a Bar Chart in Excel - Depict Data Studio
This step-by-step tutorial demonstrates how to refine a default Excel chart into a polished, presentation-ready visual.
While the article has 13 steps, we'll focus on the core formatting principles: Decluttering: Read the steps on how to delete unnecessary labels, remove the border, remove the legend, and remove grid lines. The goal is to remove anything that doesn't add value. Adding Clarity: See how to add data labels directly onto the bars so your audience doesn't have to guess the values. Improving Aesthetics: Learn how to adjust the gap width to make the bars more prominent. Using Color with Purpose: Read the excellent explanation in Step 7 about using a "plain color" for most data and an "action color" to highlight a key data point you want to emphasize. This is a powerful storytelling technique.
An Advanced Example: Combination Charts for Financial Analysis
Often in accounting, you need to compare two different types of data on the same chart. For instance, you might want to show monthly revenue (in dollars) alongside the gross profit margin (as a percentage). A standard column chart can't handle this well because the scales are so different.
The solution is a combination chart with a secondary axis. You can use columns for revenue (read from the left-hand primary axis) and a line for the margin percentage (read from the right-hand secondary axis).
This technique is incredibly useful for financial reporting. The following video demonstrates exactly how to create one.
Excel Charts & Graphs for Advanced Financial analysis📈
This video from Learn Accounting Finance demonstrates several advanced charts for financial analysis. We will focus on the first example.
Watch from the beginning until the end of the first example. You will see how to: Start with a clustered column chart for revenue and gross profit. Change the gross profit margin data series to a line chart. Move the line chart to a secondary axis so it's properly scaled. Format the chart for a clean, professional look.
Conclusion
You have now learned not only how to create the three most essential chart types but also how to format them for professional presentation. This ability to transform raw numbers into clear, insightful visuals is a critical skill that separates a data processor from a data analyst.
Key Takeaways:
- Choose the right chart for the job: Use line charts for trends, bar/column charts for comparisons, and pie charts for composition.
- Don't settle for the default: Always take the time to format your charts. A few small tweaks can make a huge difference in readability and impact.
- Format with a purpose: Declutter by removing unnecessary elements. Use color and labels strategically to guide your audience to the key message.
- Use combination charts with a secondary axis to compare data with different units, like revenue and profit margins.
In our next lesson, we will shift our focus back to data organization. You'll learn how to link data across multiple worksheets using 3-D references. This is a powerful technique for creating summary reports that automatically pull data from various sources, a crucial building block for creating consolidated financial statements and dashboards.