Founder of Tiger Spreadsheet Solutions. I love bringing the powerful stuff in Excel to people like you. Fan of drumming, chess and flatcoat retrievers.
May 7, 2021
What's The Best Excel Chart For Beginners?
There is no doubt about it – visual analysis in Excel is cool! Most people would rather look at a visual than trawl through a data set. Visual analysis in Excel means one thing: Excel charts.
The way they can ‘tell the story’ of the data and add a ‘wow factor’ to your spreadsheet makes them essential. This is why I think Excel beginners should learn charts early in their Excel career. Let’s give it a try today!
So, how do you make a chart? Or, more precisely, which Excel chart should you learn first?
To answer this question, we must first understand which Excel chart works best in which situation.
For me, pie charts are a great starting point for Excel beginners. The fact they are under-rated and underutilized in Excel is a shame because, implemented correctly, they are intuitive and visually appealing. The concept of a ‘pie’ is powerful because it lends a sense of a ‘containing whole’ and proportion within that.
That’s where I suggest you use a pie chart: where you’re trying to convey a sense of ‘proportion’.:
- What proportion of people answered yes or no to this question?
- Of all voters, what proportion voted for candidate X?
All of these questions imply a ‘containing whole’ – total respondents, the electorate, a population. The categories are mutually exclusive (ie. a person can only belong to ONE of them, no more). And, crucially, there are not too many categories.
This leads us to the main problem with pie charts – too many categories creating a cluttered look, leaving the user confused. More than 4-5 categories is probably too much for a pie chart. However, you can simplify matters by using a ‘catch-all’ category, as explained in the video.
How could an Excel beginner put together a pie chart? As always, preparation is key. Use the COUNTIF or FREQUENCY formulae to count entries in each category, then pre-select the categories and values before creating the chart. Then, improve the presentation of the chart by following the tips in the video. There you go – your first visual analysis in Excel!
Line charts work well in specific situations. Particularly, where time is involved or where you want to link one factor to another factor in your situation – I’ll explain what we mean by that in a second.
If neither of the above apply, avoid line charts. In the video, I show you an example of a line chart that does not represent the data well, since neither time nor a relationship links the Y and X axis. Consider a column or bar chart in that situation …
First, let’s deal with time. Line charts allow us to understand how something has grown or declined over a period of time. The ‘linear’ quality of a line chart really emphasizes this in a way that a column or pie chart would not. There is always a sense of direction – up or down.
Before trying to create a line chart, take the time to prepare the data. Once again, Excel formulae should help you – in the video example, I use SUMIFS to get the figures required to populate the chart and generate a neat visual summary of a football betting system. It’s easy if you can prepare the data; but, poorly prepared data = chart nightmare!
The second beginner application of line charts is about proving a relationship between two factors. You must have theories about the data you are looking at. Why do these students get higher grades than these students? Is it because they are from different towns? This ‘theory of behaviour’ is known as a ‘hypothesis’ in statistics.
So line charts are used for time and hypotheses. Next!
Bar charts are supremely versatile and work in a range of situations (as long as the data is properly prepared!) It’s my preferred option when neither a pie or line chart is applicable. That means, when there are lots of categories and no strong sense of a ‘containing whole’ (use a pie); or, when there is no sense of time or need to prove a relationship between two factors (use a line chart).
Got it? Good! If not, consider bar charts the chart type you’ll create most often, even as an Excel beginner.
Download the Excel download file and take a good look at the example. The same ideas apply: preparation of data is key and there are options for customizing the chart to maximise is communicative power. Make sure you practise making one yourself!
