Contents
Most people at work sometimes do charting – this is one of the best ways to show data in a visual, understandable and easily digestible way. It’s also no secret that some people are very intimidated by the prospect of fiddling with Excel. In fact, I love Excel, but since I work in marketing, it is a must for me.
Note: Keep in mind that there are many versions of Excel, so the information in this article may not be exactly what you see in your version of Excel. Do not worry about this, continue to follow the instructions and refer to the examples given. And you will succeed!
Step 1: Enter data in Excel
The first step is to enter data into an Excel sheet. This is the easiest part of the job. Data can be exported from anywhere, such as marketing software or a survey toolkit. Or enter manually.
Take a look at the example below. In column А The answers to the question are listed below: Was the investment in marketing profitable? And in the columns B и C contains the results of a survey of two groups of participants. For example, in a column B the responses of companies where there is an SLA (service level agreement) are presented, and in the line 2 it is shown that 49% of people from such companies say that investments in marketing are profitable.
Step 2: Choose a Chart Type
The choice of charts in Excel is quite large. The data I’m working with will look best in the form of a histogram, so we’ll create it. To create a histogram:
- Highlight source data including axis labels X и Y.
- Click the Insert (Insert).
- In section Diagrams (Charts) click bar chart (Column) and select the desired type. In this example, I’ll choose the first 2D plot option, simply because I like it more than 3D.
Step 3: Switch Axes If Needed
If you want to change the data that will be shown on the axes X и Y, right click on the graph, click Select data (Select Data) and click the button Row column (Switch Row/Column).
Step 4: Set up Data Labels and Legend
To change the appearance of the data labels and legend, click on the chart and open the tab Framework (layout). Here you can choose the appearance for the chart title, axis titles, and legend.
- In this example, I clicked Chart title (Chart Title) and chose Above chart (Above Chart).
- To set the axis title format Х, I pressed Axis names (Axis Titles), chose Main horizontal (Primary Horizontal Axis Title) and clicked Title under axis (Title Below Axis).
- To set the axis format Y, I pressed Axis names (Axis Titles), chose Main vertical (Primary Horizontal Axis Title) and clicked Rotate text (Rotated Title).
- To change the position of the legend, I went to the tab Framework (Layout), clicked Legend (Legend) and chose a suitable position.
Step 5: Resize the Y-Axis If Needed
To change the dimension of an axis Y, right click on the tick marks Y and from the menu that appears select Axis Format (Format Axis). Here in the section Axis parameters (Axis Options) you can choose which axis elements to show, or change the number of decimal places in the axis labels Y.
As a result, the graph will look something like this:
Step 6: Reorder Data If Needed
If you want to arrange the data in order of decreasing importance, click on the column of the table that displays the most important information (in this example, I select the column B), open the tab Data (Data) and press Filter (Filter). Then back to the column B, click the filter arrow in the column heading, and select Descending sort (Sort Largest to Smallest).
Clicking on the arrows in the cells B1 и C1, you can select the desired sort mode for the corresponding column – ascending or descending. I chose in a cell B1 sorting the column in descending order.
It’s simple, isn’t it? Tell us in the comments what other functions and tools of Excel you could need help with?