Previously we finished with Pivot Tables, if you have any questions about that, do not hesitate to ask in comments. This time we will be doing the graphs also called charts in Excel.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
I have made a data of people of different genders who are either admitted to a class or otherwise. You can make your data and try all kinds of charts.
Convert your data into a table
As discussed previously, converting any data into a table is always a wise choice for the reasons discussed in previous parts.
Besides, give that table a unique name that you can remember.
Inserting a Graph or Chart
Click Insert and there you can see these options in Excel 2016. If you don’t see such options, you can just click on charts.
To insert a chart, your cursor must be on the data, i.e., your data table. If the cursor is on an empty cell, Excel will show you an error. Therefore, place the cursor first on the data.
For this part, I will click on the “Recommended Charts” and see what Excel has to offer to me.
I will see the above dialogue appeared for me. You can see one type of chart available to you. You can always click on the the “All charts” button at the top to get any other chart you will like for your data.
I will just click the first one and click OK. A bar chart will be inserted with my data table as shown below:
This chart has selected by default S. No on Y-axis and Gender on X-axis for the bar graphs. Also, it is showing the the sum of all genders, as you can well see that all types of genders are displayed on the x-axis and their numbers are displayed as the height of a bar according to their numbers.
As you might have noticed that this chart is inserted on a new sheet automatically. Not only the chart is inserted but a pivot table is also being inserted along with this. You can copy and paste this chart anywhere on any sheet or even on a Word file.
Let’s Play with different controls of charts
If you look at the pivot table inserted by Excel you can see the columns being selected to be used for the chart, i.e., Gender and Sum of S. No. For example, if we click on the “Gender” column arrow, we can see the whole data in this column.
Suppose we just want to select “Males” & “Females” to check their numbers. It’s easy, we will just click on the arrow and deselect everything else but the Male and Female.
As soon as we do this, you can see the change in the graph as well, as shown below:
As you can see the data fields which were automatically selected by Excel are not what we wanted. So, we change the Y-Axis and X-Axis columns data. If you click either on the chart or the associated Pivot table, you will see graph options appearing on the right. We will see how tempering with these options can give you the required graph.
Suppose we want to put the number of Males and Females in the bar chart to show how many are admitted or otherwise in each category.
You can see we are now watching a graph which is telling us how many Males and Females are being admitted. Now let’s see what else we can do to make the chart as we desire it and visually more appropriate.
Just use the chart options to change the data by dragging and dropping any data in the axis or value category and see how it changes. You can learn it by playing with it. The more you experiment the more apprehension you will get about how to make a chart.
If you have noticed, when you click on the graph, that there are a “+” sign and a “brush” sign appearing on the right corner of the chart, just click on + sign, for example, and see more options will be displayed, you can select any option desirable for you to be displayed within the chart, go ahead and practice.
Right-click on the chart and select “Format Chart Area” to open the chart options on the right side. Here you can play with the visual effects of the chart.
For example, you want the two displayed bars to be having separate colors so that viewers can easily identify visually different data. For this, click on the bars and see options changing on the right side-bar.
We will select the option “Vary colors by points”, and see the color of each bar changes to different than the other. You can choose your color by picking any color.
How to change the Chart Type
It’s easy, just right click on the chart and select, “Change chart type”
I have changed my chart to a pie graph, like this:
I have played with different options to color and showed the data labels and legend, by clicking on the “+” button, etc., try yourself. It is easy.
That’s it for this part. We will continue in the next parts with Forms. Go on following this series and you will enjoy how easily you can learn Excel, bit by bit. Thank you very much for your support.