We have a spreadsheet that shows a list of different salespeople in a company, their age in column C, and then the amount of sales that each one generated for each year for the past 10 years. Let’s suppose that we wanted to create a simple chart that shows the individual’s name and how old they are.
Select both columns of data for Sales Person and Age.
We generally want to include the header rows because that will give us more information that we’ll feed into the graph.
Go to the Insert tab on the ribbon and find the options for charts in the middle.
There are a lot of different chart types. Excel tries to recommend charts based on the data you provide.
As you hover over the options, Excel will give you a preview of what your information would look like in that chart.
In general, you should never use a 3-D chart, unless you are showcasing a third dimension of data.
Choose the clustered bar chart. (See Figure 15.1)
It will automatically create a title based on the information you provided.
Generally, we’re going to want to make sure that the title is a very clear description of what the graph is showing. Age is too vague, so what we’d probably want to do is change it to something else.
Select the title and type in the new one: Employee Age.
Add axis titles by selecting Add Chart Element on the left of the Chart Designs tab. (See Figure 15.2)
The Primary Horizontal will adjust the axis title at the bottom of the chart. Type Age in Years.
The Primary Vertical will adjust the axis title to the left of the chart. The name should be Salesperson Name.
Once created, you can use your cursor to move the graph anywhere you’d like.
You can also copy a chart by pressing Control+C or right-clicking and choosing the Copy option.
Then, go into another area and paste the chart by pressing Control+V or right-clicking and choosing the Paste option.
Editing the Chart
If we change anything in the base data, the charts will update. For example, if we go to the Salesperson column and change Alice to Anne, notice that both charts update accordingly. Sometimes, if you have multiple charts on a single sheet, you may want to make sure they’re aligned.
Select both charts by holding down the Shift Key or Control Key.
Go to Shape Format on the ribbon, choose Align on the far right, and align them to the bottom of each other.
In addition to duplicating charts, we can move a copy into a new sheet.
Cut the duplicated sheet using Control+X or right-clicking and selecting Cut.
Create a new sheet and paste the chart into that sheet.
Whenever data is updated in the original worksheet, the chart on the new sheet updates and reflects those changes.
You can even get rid of the original chart and the copied chart on the new sheet remains.
Total Sales History
Now let’s suppose that we wanted to create a different kind of graph. In this case, we want to know the total sales history for the last ten years for each salesperson.
In the column adjacent to the final column of data, label it Total Sales.
Calculate the Total Sales for Ann by entering the equals sum formula and selecting all the sales history for her. (See Figure 15.3)
Copy this formula down for the rest of the salespeople.
An error reading Formula Omits Adjacent Cells may appear. In this case, we know that we didn’t want to include the Age column in our calculations, so we can choose to ignore the error.
You may need to make the column a little wider to accommodate the larger number.
Just like in the first chart, we need to select both columns. Because the two columns are not adjacent, you can select both of them by selecting the first, Salesperson, and while holding down the ControlKey, select the second column, Total Sales.
Go to the Insert tab, and select a chart. In this case, let’s say we want to use a line chart.
It will give you a few different options. Select the one with markers.
This may seem like a good chart to choose from, but it is not good for representing this data. In this chart, the salespeople are not sequential. If the chart showed sales over time for a single salesperson, a line chart might make sense. But comparing salespeople against each other is not effective with this type of chart, because it almost looks as if they’re related to each other based on the line.
Go up to the Change Chart Type option and select the Clustered Column chart.
The human eye is actually much better at being able to tell the differences between sizes in bars of data than dots just plotted on a chart or even circles, like pie charts.
We can see very quickly that Bill is at the top of sales. (See Figure 15.4)
Add a horizontal title and vertical title by adding axis titles in the Chart Design tab.
The numbers on the left can get pretty large, so you will need to format them to be represented by smaller numbers in the Axis Format option on the right.
If you can’t find the Format Axis tab, you can find it by right-clicking on the chart.
Change the display units to Millions.
Under the Number section, change the decimal places to 2. (See Figure 15.5)
Make the Millions label a bit smaller so that the eye is more drawn to the Total Sales label.
Rename the bottom label Salesperson Name.
The title of your chart should tell you what you will be getting out of it. In this case, we will title our chart Total Sales for the Last 10 Years.
Sorting and Filtering Charts
Now, let’s say we’d like our information sorted from highest on the left to lowest on the right. To do this we don’t edit the chart itself, but change the data behind the chart. Move the chart aside to begin.
Select all of the data.
On the right of the Home tab, select Sort and Filter.
The shortcut for this is Control+Shift+L.
Under the Filter button, choose to sort from largest to smallest.
This will automatically update the chart.
To move the chart to a different sheet go to the Chart Design tab and choose Move Chart.
This will give you the option to move the chart to an existing sheet or create a new sheet. (See Figure 15.6)
Use this tool to create a new sheet, and title it Last 10 Years.
When you select OK, it creates a new type of sheet, a sheet that only contains a chart. There is no other data, columns, rows, or cells, whereas the chart on Sheet2 is embedded in the cells.
Note that the chart on Sheet2 has been updated with the filter applied to the data, reordered not based upon age, but on sales. In this case, Bill, who has the highest sales, is at the bottom of the graph.
Let’s say we want to reverse this. In order to do that, open up Format Axis again and check the Categories in Reverse Order box. (See Figure 15.7)
This will display the categories in reverse order without having to change the base data. That may not be something that you want to do, but it is an option that is available to you.
Go back to the main sheet of data, Sheet1, and remove the filter by pressing Control+Shift+L.
Let’s now take a look at the top four salespeople and what their sales were for the full ten-year period.
Select the first four salespeople. Hold down the Control key and select their first ten years of sales as well. Be sure to not include their age in the selection.
Go to the Insert tab, find the line chart options, and insert the first option, a line chart. (See Figure 15.8)
Each line is related over time and denotes a distinct salesperson.
This should help you quickly determine what the general shape of their sales looks like.
Move the legend to the top of the chart by right-clicking the legend and selecting Format Legend on the right. It will give you options where to put it.
In English, we read left to right, top to bottom, so if you have any type of legend for your chart, it’s best to put it at the top.
Add the chart title Top 4 Salespeople - Last 10 Years.
The x-axis is self-explanatory, so you do not need to add a label.
Add the vertical axis title Sales.
These axis titles can be moved by selecting and dragging.
Once this chart has been created, Excel highlights what data is being used to display that chart. Excel has another way to show you this data, and that’s in the Select Data dialogue box. (See Figure 15.9)
The Select Data box shows you what the Legend Entries are, also called Series. In this chart, we have four series, one for each salesperson. You also have the Horizontal, also known as Category, Axis Labels, which in our case is Years 1–10.
If we wanted to change anything about these series, select the series you’d like to change and select Edit. You now have the capability to change both the series name and the values that are displayed.
To only include the first three series, do the following:
Select the fourth series (Kent).
When you select OK, you will see that Kent’s series has been removed from the chart.
To add it back, expand the highlighted range to include Kent again.
To skip some lines and include Frank at the very bottom, do the following:
Choose Select Data.
Select the series Frank.
The series values are his years 1–10.
You may have to move the chart in order to select this data.
When you select OK, you will see that Frank has been added to the chart.
Notice that in the Select Data box, on the Category Axis Labels for Frank, instead of Years 1–10, it only says 1–10.
You can manually edit that by selecting the title bar, Years 1–10, from the original data to make that the label for Frank as well.
To label the sales with an actual year instead of Years 1–10, do the following:
Create a new row at the bottom labeled Year, and in the column for Year 1, type 2000.
Notice how it automatically formats with a dollar sign.
Remove 2000 and type 12/31/2000.
Notice how it still gives you a monetary value.
Press Control+1 to open up the format menu, and change it from Currency to Custom.
In this case, we just want it to show the year, so in the Type section, enter YYYY and press Enter. (See Figure 15.10)
To have the years show up consecutively, select the cell to the right and use the =DATE formula.
For the year, reference the first cell (2000), and then add +1. The month will always be 12, and the day will always be 31.
Copy the format from 2000 to the new year so it doesn’t show up as currency, and drag it across for the rest of the years.
They should fill up all the way to 2009.
To change the Category Axis Labels from years 1–10 to the actual years, open up the Select Data option again.
In the Category Axis Labels section, select Edit and press OK.
The chart will automatically update.
Add an Axis Title to the bottom called Calendar Year.
Move this to the left.
This chart is now finished.
Move the chart to a new sheet and call it Top Four Salespeople.
Rename Sheet2 to Ages.
Rename Sheet1 to Data.
On the Data Sheet, select the Year row at the bottom and add a fill color to distinguish it from the rest of the data.
The waterfall chart is a newer feature in Excel. Waterfall Charts are meant to be cumulative displays of where you started and where you ended. They are useful to show when something is adding or sometimes taking away from a cumulative total. (See Figure 15.11)
Let’s investigate Frank at the bottom of the list.
Select Frank’s ten years of sales.
Choose a waterfall chart.
You’ll see that it’s climbing up cumulatively over time.
Add in the total sales.
This will format automatically as an eleventh year, which is incorrect.
Right-click to select Format Data Series.
Select the column on the chart that represents Frank’s cumulative total, $558,909, and in the Format Data Point option on the right, select Set as total. (See Figure 15.12)
Now, the waterfall chart should display each of the years, and then the total at the far right.
The number might appear a little cramped, so feel free to widen up the chart.
Use the Select Data tool to change the series name to Frank.
Change the category axis to the years 2000 to 2009.
Change the Chart Title to Frank’s 10 Year Sales Contribution.
Add a Primary Vertical axis and call it Sales.
Add a Primary Horizontal axis and call it Calendar Years.
Notice that it is duplicative to have both the sales increments and the sales labeled above the bars. To save space, select the sales increments on the vertical axis, right-click, and choose Format Axis.
Open up the Number option.
Under Format Code, type a semicolon (;) and press Add.
This hides those increments on the side, and as a result, you can delete the horizontal bars.
Go to the Format Data Series Options, and uncheck Show Connector Lines.
This will get rid of the gray lines connecting the bars. (See Figure 15.13)
Move this chart to a new sheet called Waterfall and move that sheet to the end.
Form and Usage
Earlier, it was mentioned that the human eye is not very good at determining the size of the pieces of a pie chart. However, what if someone were to ask to look at the top four salespeople and their total sales in a pie chart representing their total contributions?
Select the first four names of the top salespeople, along with their total sales.
Insert a pie chart.
The donut chart is increasing in popularity because of the ability to put in another data point in the center simply with a text box.
Again, a three-dimensional option is a bad form because you’re not representing three dimensions of data.
For this example, we will use a regular two-dimensional pie.
Notice that the human eye has a very tough time telling the difference between the sizes of the sections.
Use the Change Chart Type option to change the pie chart to a clustered bar chart.
It is recommended that whenever someone asks for a pie chart, you help them by recommending a different chart that might represent their data a little better, which in this case, is the bar chart.
Now we can very easily see the differences between their contributions.
Open Format Axis for the vertical axis and choose to show the values in reverse order.
Note that this will format the sales numbers at the top.
For the horizontal axis, in Format Axis, go into the Labels section and change the label position to None.
You can get rid of the grey indicator bars and the legend
In the top right of the Chart Design tab, choose Add Chart Element and add Data Labels.
Ensure the data labels are in the inside base.
Make sure the text color inside the bar contrasts against the background.
Notice how much easier it is for your eye to compare the different contributions than it was for the pie chart. For this reason, please avoid using pie charts.
Move this chart to a new sheet and call it Top 4 and move it to the end.
The last type of chart you will look at is the Scatter Plot chart. This allows you to compare two different data sets on each axis. For example, let’s say you want to compare age and total sales to see if there were any correlation or relationship between the two.
Select the Age column, hold the ControlKey, and select the Total Sales column.
Insert a scatter chart, and choose the most basic one.
The challenge is that we don’t know which dot relates to which salesperson.
To add names to the dots, select the series and use the shortcut to the right of the chart to add chart elements. (See Figure 15.14)
Select to add Data Labels.
As the data labels appear, select them and go to the Format Data Labels option on the side to choose what they show.
It gives you many options to choose from. In this case, choose Value From Cells, and select their names.
In the Format Data Labels option, you can also choose how you would like to position the labels. In this case, position them to the left.
Change their font size so that they fit nicely in the chart.
Use the Chart Elements shortcut button to insert a Trendline.
A trendline will show you what is the slope, or general direction, that you see.
In this case, you can see that there is a pretty positive relationship between how old you are, and the number of total sales you brought in.
With that trendline, you can also choose to display the equation on the chart in the Trendline options.
This could help you basically predict, depending on a given age, what their total sales amount would be.
Move this chart by pressing Control+X, and then paste it (Control+V) onto the Ages sheet.
Sparklines and Other Features
For any of the charts, you can go online and use a search engine to find videos to learn how to use them. There are many uses we won’t be covering. However, there are just a few more things we will be covering in this chapter.
The first is sparklines. In the Insert tab, on the right side you will find a section called Sparklines. (See Figure 15.15)
Select the Line option.
A box will prompt you to select a data range. Select the ten years of data for the first employee, and select OK.
This will automatically generate a trendline to the right of the data that will give you a visual insight to the data.
Drag this new trendline cell down to see the trend for every salesperson.
Because they are lined up beside each other, you will be able to note which lines are trending up and which are not trending in such a favorable manner
The next feature to learn are treemaps. Treemaps are generally meant to show what is the contribution relative to one another. For example, each different box could represent a different category, and the relative size would represent how much that category has brought in terms of sales or units. It will allow you to see that in a visual format. (See Figure 15.16)
One of the other things Excel allows you to do when it comes to charts is to create maps. We will not be going into the detail of maps in this class, but it is useful to note that this tool can be used to chart geographies.
The final thing to note when it comes to charts is there are funnel charts, box-and-whisker charts, and radar charts. All of these have a particular use case value in them. Your job as an analyst or someone that uses Excel is to determine what is the appropriate visualization to use to get a particular message across.
In a box-and-whisker chart, you may want to show the variability of a given data point over time. In a radar chart, you may want to show how five different variables relate to one another. With a funnel chart, you may want to show what is the total potential sales of the market, how much of that market you’re able to reach, and how much of those that you are able to reach are able to convert to a final sale. There are all sorts of things that these different charts can be used for, so as you’re getting ready to use Excel to create charts and visualizations, remember that the most important thing is how quickly people can read and understand the data that’s presented in a given chart. And so, it’s much less about the complexity of the chart and more about how simple you can make it to get the message across.
This content is provided to you freely by EdTech Books.